What do square brackets around an identifier mean in SQLite?-CodePudding

In certain SQL dialects, identifiers in square brackets have special semantics. What is the effect of enclosing an identifier in square brackets in SQLite?

As an example, SQLite (at least as of 3.40.1) accepts the following table definition.

CREATE TABLE [Banana] (
  [Weight] REAL NOT NULL,
  [Color] TEXT DEFAULT 'yellow' NOT NULL,
  [Ripe] BOOLEAN NOT NULL DEFAULT 1
);

You can then insert and select bananas:

INSERT INTO [Banana] ([Weight]) VALUES (1.0), (2.0), (3.0);

SELECT * FROM [Banana];

CodePudding user response：

The square brackets escape the database identifier, such as a table name. For example, if you wanted to name your table using a reserved SQLite keyword, such as TABLE, you could escape the name in square brackets:

CREATE TABLE [Table] (
    [Weight] REAL NOT NULL,
    [Color] TEXT DEFAULT 'yellow' NOT NULL,
    [Ripe] BOOLEAN NOT NULL DEFAULT 1
);

In earlier versions of SQLite, you could have used double quotes to achieve the same thing:

CREATE TABLE "Table" (

Note that in general you should avoid such naming schemes, as you would forever have to escape the table name.