Translation: Identifier Name, identifier naming rules

Translation: Identifier Name, identifier naming rules

This article is a translation of the mariadb official manual: Identifier Names.

Original article: https://mariadb.com/kb/en/library/identifier-names/
Https://mariadb.com/kb/zh-cn/library/identifier-names/ I submitted to the MariaDB official manual

Identifier Names

Databases, tables, indexes, fields, aliases, views, stored procedures, triggers, events, variable partitions, tablespaces, storage points, tags, users, and roles are common identifiers, they all have specific naming rules.

The identifier can be caused by the backslash. It is optional to reference an identifier using quotation marks unless it contains special characters or reserved keywords. If you set SQL _MODE to ANSI_QUOTES, double quotation marks (") can also be used to reference identifiers.

When using the full name (fully qualified names), you do not need to quote the identifier, even if the name uses a reserved keyword. For example, test. select has only one explanation, so it can be correctly parsed without using quotation marks to enclose the reserved word "select.

No reference required

The following characters are valid and do not need to be referenced by quotation marks:

• ASCII: [0-9, a-z, A-Z $ _] (value 0-9, uppercase/lowercase Latin characters, dollar signs, underscores)
• Extended: U + 0080 .. U + FFFF

Situations to be referenced

The following characters are valid but must be referenced in quotation marks:

• ASCII: U + 0001 .. U + 007F (full Unicode Basic Multilingual Plane (BMP) Before t for U + 0000)
• Extended: U + 0080 .. U + FFFF
• Identifiers can also be part of identifiers as long as they are referenced in quotation marks.

Other naming rules

The following are other naming rules for identifiers:

• Identifiers are stored in Unicode (UTF-8) format.
• The identifier may or may not be case sensitive. See Indentifier Case-sensiti.pdf.
• The database, table, and field names cannot end with a blank character.
• The identifier name can start with a value, but cannot only contain a value, unless quoted in quotation marks.
• The identifier that starts with a value and follows the character "e". It must be referenced in quotation marks and will be resolved as a floating point value.
• The identifier cannot contain ascii nul (U + 0000) characters and supplementary characters (U + 10000 or greater ).
• Identifiers such as 5e6 and 9e are allowed, but we strongly recommend that you do not use them because they produce ambiguity in a specific context and are treated as a value or expression.
• User variables cannot be part of identifiers, nor can they be directly used as identifiers in SQL statements.

Reference character

The common quote character uses the reverse quotation mark "'", but if SQL _MODE is set to ANSI_QUOTES, the double quotation mark (") can also be used as the reference character.

The quotation mark can be a part of the identifier, but it needs to be referenced. The quote character can be an anti-quotation mark, but in this case, the anti-quotation mark must be escaped using another anti-quotation mark.

Maximum length

• Databases, tables, columns, indexes, constraints, stored routines, triggers, events, views, tablespaces, servers, and log file groups can contain a maximum of 64 characters.
• The combined statement label can contain a maximum of 16 characters.
• An alias can contain a maximum of 256 characters. However, the field alias in the create view statement can be up to 64 characters (not the maximum alias length is 256 characters ).
• Users can contain a maximum of 80 characters.
• The maximum length of Roles is 128 characters.
• Multi-byte characters do not count characters that exceed the character length limit.

Combination identifier

MariaDB allows a single field name to reference a field, as long as it does not produce ambiguity, or specify the table name for the field, or add the database name to use a 3-segment identifier to reference the field. Use periods (.) to separate identifiers. The separators before and after periods (.) can contain spaces (tabs and line breaks ).

Example

Use periods to separate identifiers:

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 |+------+

Periods can be separated by spaces:

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

Resolve ambiguity:

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 ambiguousSELECT t1.i FROM t1 LEFT JOIN t2 ON t1.i=t2.i;+------+| i    |+------+|   10 |+------+

Create a table to be referenced by quotation marks:

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 1CREATE 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)

Double quotation marks are used as reference characters:

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 1SET 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)

Reference a symbol as part of the identifier Name:

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)

Create a table named "*" (Unicode number: U + 002A) that needs to be referenced.

CREATE TABLE `*` (a INT);

Floating Point ambiguity:

CREATE TABLE 8984444cce5d (x INT);Query OK, 0 rows affected (0.38 sec)CREATE TABLE 8981e56cce5d (x 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 '8981e56cce5d (x INT)' at line 1CREATE TABLE `8981e56cce5d` (x INT);Query OK, 0 rows affected (0.39 sec)

 

Go back to the Linux series article outline: workshop!