标识符命名规则

数据库，表，索引，列，别名，视图，存储过程，分区，表空间都是常见的标识符，下面介绍的是标识符的命名规则。

标识符命名可以用#`#引用起来，当然引用的操作是可选择的，除非标识符名称中有特殊字符或者是保留字

不必引用的情况

下面的字符做标识符命名可以不用加引号：

• ASCII: [0-9,a-z,A-Z$_] (numerals 0-9, basic Latin letters, both lowercase and uppercase, dollar sign, underscore)
• Extended: U+0080 .. U+FFFF

一定需要引用的情况

下面的字符做标识符命名一定需要加引号：

• ASCII: U+0001 .. U+007F (full Unicode Basic Multilingual Plane (BMP) except for U+0000)
• Extended: U+0080 .. U+FFFF
• Identifier quotes can themselves be used as part of an identifier, as long as they are quoted.

更多规定

有关标识符命名你需要了解的更多信息：

• 标识符名称通常用 (UTF-8)格式存取。
• 标识符命名分大小写敏感的情况. See 标识符对大小写敏感.
• Database, table and column 不能以空格结尾。
• 标识符命名可以以数字开始，但如果是全数字的情况就必须添加引号引用.
• 标识符不允许包含 ASCII NUL（结束符） 字符 (U+0000) and supplementary characters (U+10000 and higher)（我的理解为：不能有结束符）.
• 类似5e6, 9e 命名不被禁止,但是绝对不建议这么用,因为在某些环境下可能会被当作数字表达式。
• 用户变量不能当作标识符的一部分，或者是sql语句的别名。

引号字符

一般情况用 `引用就可以了, 但是如果有 ANSI_QUOTES SQL_MODE 特殊规定，那就需要用"（双引号）

如果单引号是作为标识符名称的一部分，那么整个标识符需要被双引号引用起来。

最大长度

• Databases, tables, columns, indexes, constraints, stored routines, triggers, events, views, tablespaces, servers and log file groups 最大长度限定在64个字符.
• Compound statement labels 最大长度限定在16个字符
• Aliases have a maximum length of 256 characters, except for column aliases in CREATE VIEW statements, which adhere to the are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).
• Multi-byte characters do not count extra towards towards the character limit.

复杂标识符

在指代不明确的情况下，可以用数据库.表明.列名的方式来作为标识符。 MariaDB allows the column name to be used on its own if the reference will be unambiguous, or the table name to be used with the column name, or all three of the database, table and column names. A period is used to separate the identifiers, and the period can be surrounded by spaces.

举例：

Using the period to separate identifiers:

MariaDB [test]> CREATE TABLE t1 (i int);

INSERT INTO t1(i) VALUES (10);

SELECT i FROM t1;
+------+
| i    |
+------+
|   10 |
+------+

SELECT t1.i FROM t1;
+------+
| i    |
+------+
|   10 |
+------+

SELECT test.t1.i FROM t1;
+------+
| i    |
+------+
|   10 |
+------+

The period can be separated by spaces:

SELECT test . t1 . i FROM t1;
+------+
| i    |
+------+
|   10 |
+------+

解决歧义的方法:

CREATE TABLE t2 (i int);

SELECT i FROM t1 LEFT JOIN t2 ON t1.i=t2.i;
ERROR 1052 (23000): Column 'i' in field list is ambiguous

SELECT t1.i FROM t1 LEFT JOIN t2 ON t1.i=t2.i;
+------+
| i    |
+------+
|   10 |
+------+

需要引号的情况:

CREATE TABLE 123% (i int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123% (i int)' at line 1

CREATE TABLE `123%` (i int);
Query OK, 0 rows affected (0.85 sec)

CREATE TABLE `TABLE` (i int);
Query OK, 0 rows affected (0.36 sec)

双引号的使用情形:

CREATE TABLE "SELECT" (i int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"SELECT" (i int)' at line 1

SET sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.03 sec)

CREATE TABLE "SELECT" (i int);
Query OK, 0 rows affected (0.46 sec)

如果单引号作为标识符的一部分:

SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| sql_mode      | ANSI_QUOTES |
+---------------+-------------+

CREATE TABLE "fg`d" (i int);
Query OK, 0 rows affected (0.34 sec)