What SQLite column name can be/cannot be?

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

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

回答1:

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

回答2:

Can it have characters like '/'?

All examples are from SQlite 3.5.9 running on Linux.

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.

回答3:

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

TL;DR:

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

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

The allowed series of characters for result columns in a SELECT statement are

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

Now to the exploration process itself

let's look at the syntax for CREATE TABLE columns

// 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;}

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.c however 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.

It is still unclear what are the available names for a result-column (i. e. the column name or alias assigned in the SELECT statement). parse.y is 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;}

回答4:

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

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

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

回答5:

Except for placing "illegal" identifier names between double quotes "identifier#1" [ before and ] after works as well [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