SQL 什么 SQLite 列名可以是/不能是？

What SQLite column name can be/cannot be?

提问by prosseek

Is there any rule for the SQLite's column name?

SQLite 的列名有什么规则吗？

• Can it have characters like '/'?
• Can it be UTF-8?

• 它可以有像'/'这样的字符吗？
• 可以是UTF-8吗？

采纳答案by Spooks

http://www.sqlite.org/lang_keywords.htmlthat has a complete list! enjoy!

http://www.sqlite.org/lang_keywords.html有一个完整的列表！请享用！

回答by J. Polfer

Can it have characters like '/'?

它可以有像'/'这样的字符吗？

All examples are from SQlite 3.5.9 running on Linux.

所有示例均来自在 Linux 上运行的 SQlite 3.5.9。

If you surround the column name in double quotes, you can:

如果用双引号将列名括起来，您可以：

> CREATE TABLE test_forward ( /test_column INTEGER );
SQL error: near "/": syntax error
> CREATE TABLE test_forward ("/test_column" INTEGER );
> INSERT INTO test_forward("/test_column") VALUES (1);
> SELECT test_forward."/test_column" from test_forward;
1

That said, you probably shouldn't do this.

也就是说，你可能不应该这样做。

回答by user35443

The following answer is based on the SQLite source code, mostly relying on the file parse.y(input for the lemon parser).

以下答案基于 SQLite 源代码，主要依赖于文件parse.y（柠檬解析器的输入）。

TL;DR:

特尔；博士：

The allowed series of characters for column and table names in CREATE TABLEstatements are

CREATE TABLE语句中列名和表名允许的一系列字符是

• '-escaped strings of any kind (even keywords)
• Identifiers, which means
  • ``` and "-escaped strings of any kind (even keywords)
  • a series of the MSB=18-bit ASCII characters or 7-bit ASCII characters with 1in the following table that doesn't form a keyword:
• Keyword INDEXEDbecause it's non-standard
• Keyword JOINfor reason that is unknown to me.

• '- 任何类型的转义字符串（甚至关键字）
• 标识符，这意味着
  • ``` 和"-escaped 任何类型的字符串（甚至是关键字）
  • 下表中不构成关键字的一系列MSB=18 位 ASCII 字符或 7 位 ASCII 字符： 1
• 关键字，INDEXED因为它是非标准的
• JOIN我不知道的原因关键字。

The allowed series of characters for result columns in a SELECTstatement are

SELECT语句中结果列允许的一系列字符是

• Either a string or an identifier as described above
• All of the above if used as a column alias written after AS

• 如上所述的字符串或标识符
• 以上所有如果用作列别名写在 AS

Now to the exploration process itself

现在到探索过程本身

1. let's look at the syntax for CREATE TABLEcolumns

   // The name of a column or table can be any of the following:
   //
   %type nm {Token}
   nm(A) ::= id(X).         {A = X;}
   nm(A) ::= STRING(X).     {A = X;}
   nm(A) ::= JOIN_KW(X).    {A = X;}
   

2. digging deeper, we find out that

   // An IDENTIFIER can be a generic identifier, or one of several
   // keywords.  Any non-standard keyword can also be an identifier.
   //
   %type id {Token}
   id(A) ::= ID(X).         {A = X;}
   id(A) ::= INDEXED(X).    {A = X;}
   

   "Generic identifier" sounds unfamiliar. A quick look into tokenize.chowever brings forth the definition

   /*
   ** The sqlite3KeywordCode function looks up an identifier to determine if
   ** it is a keyword.  If it is a keyword, the token code of that keyword is 
   ** returned.  If the input is not a keyword, TK_ID is returned.
   */
   
   /*
   ** If X is a character that can be used in an identifier then
   ** IdChar(X) will be true.  Otherwise it is false.
   **
   ** For ASCII, any character with the high-order bit set is
   ** allowed in an identifier.  For 7-bit characters, 
   ** sqlite3IsIdChar[X] must be 1.
   **
   ** Ticket #1066.  the SQL standard does not allow '$' in the
   ** middle of identfiers.  But many SQL implementations do. 
   ** SQLite will allow '$' in identifiers for compatibility.
   ** But the feature is undocumented.
   */
   

   For a full map of identifier characters, please consult the tokenize.c.

3. It is still unclear what are the available names for a result-column(i. e. the column name or alias assigned in the SELECTstatement). parse.yis again helpful here.

   // An option "AS <id>" phrase that can follow one of the expressions that
   // define the result set, or one of the tables in the FROM clause.
   //
   %type as {Token}
   as(X) ::= AS nm(Y).    {X = Y;}
   as(X) ::= ids(Y).      {X = Y;}
   as(X) ::= .            {X.n = 0;}
   

1. 让我们看看CREATE TABLE列的语法

   // The name of a column or table can be any of the following:
   //
   %type nm {Token}
   nm(A) ::= id(X).         {A = X;}
   nm(A) ::= STRING(X).     {A = X;}
   nm(A) ::= JOIN_KW(X).    {A = X;}
   

2. 深入挖掘，我们发现

   // An IDENTIFIER can be a generic identifier, or one of several
   // keywords.  Any non-standard keyword can also be an identifier.
   //
   %type id {Token}
   id(A) ::= ID(X).         {A = X;}
   id(A) ::= INDEXED(X).    {A = X;}
   

   “通用标识符”听起来很陌生。tokenize.c然而，快速浏览一下定义

   /*
   ** The sqlite3KeywordCode function looks up an identifier to determine if
   ** it is a keyword.  If it is a keyword, the token code of that keyword is 
   ** returned.  If the input is not a keyword, TK_ID is returned.
   */
   
   /*
   ** If X is a character that can be used in an identifier then
   ** IdChar(X) will be true.  Otherwise it is false.
   **
   ** For ASCII, any character with the high-order bit set is
   ** allowed in an identifier.  For 7-bit characters, 
   ** sqlite3IsIdChar[X] must be 1.
   **
   ** Ticket #1066.  the SQL standard does not allow '$' in the
   ** middle of identfiers.  But many SQL implementations do. 
   ** SQLite will allow '$' in identifiers for compatibility.
   ** But the feature is undocumented.
   */
   

   有关标识符字符的完整映射，请参阅tokenize.c.

3. 目前还不清楚 a 的可用名称是什么result-column（即在SELECT语句中分配的列名或别名）。parse.y在这里再次有帮助。

   // An option "AS <id>" phrase that can follow one of the expressions that
   // define the result set, or one of the tables in the FROM clause.
   //
   %type as {Token}
   as(X) ::= AS nm(Y).    {X = Y;}
   as(X) ::= ids(Y).      {X = Y;}
   as(X) ::= .            {X.n = 0;}
   

回答by Klaas-Z4us-V

Except for placing "illegal" identifier names between double quotes "identifier#1", [before and ]after works as well [identifire#2].

除了在双引号之间放置“非法”标识符名称之外"identifier#1"，[before 和]after 也可以[identifire#2]。

Example:

例子：

sqlite> create table a0.tt ([id#1] integer primary key, [id#2] text) without rowid;
sqlite> insert into tt values (1,'test for [x] id''s');
sqlite> select * from tt
   ...> ;
id#1|id#2
1|test for [x] id's

回答by collectordave

Valid field names are subject to the same rules as valid Table names. Checked this with SQlite administrator.

有效的字段名称与有效的表名称遵循相同的规则。与 SQLite 管理员核对了这一点。

1. Only Alphanumeric characters and underline are allowed
2. The field name must begin with an alpha character or underline

1. 只允许使用字母数字字符和下划线
2. 字段名称必须以字母字符或下划线开头

Stick to these and no escaping is needed and it may avoid future problems.

坚持这些，不需要逃避，它可能会避免未来的问题。