Basic articles! MySQL identifier syntax, naming rules, capitalization rules

1. MySQL identifier syntax and naming rules

  • MySQL identifiers must follow the following rules
  • Unquoted identifiers can consist of uppercase and lowercase letters az, numbers 0-9, dollar signs, underscores, and Unicode extended characters ranging from U+0080 to U+FFFF.
  • The first character of the identifier can be any character allowed by the identifier, including numbers.
  • However, unquoted identifiers are not allowed to consist entirely of numeric characters :
  • Because that would make it difficult to distinguish from numbers. MySQL supports the practice of starting identifiers with numeric characters, which is not common in various database systems.
  • If you want to use such an identifier, you must pay special attention to whether it also contains an "E" or "e" character, because this combination can easily lead to ambiguities in the expression.
  • For example, the expression 23e + 14 (with spaces on both sides of the "+" sign) means column 23e plus the number 14, then how should 23e+14 be interpreted? Does it mean the same thing, or one What about the numbers represented by scientific notation?
  • Identifiers can be surrounded by backticks "`", which supports the use of any characters except NULL bytes and Unicode supplementary characters (range U+10000 and above):
  • If the identifier is an SQL reserved word, or contains spaces or other special characters, quotation marks are useful .
  • Enclose the identifier in quotation marks to make it completely composed of numeric characters ; this is not allowed for unquoted identifiers.
  • If you want to use the quote character of the identifier in a quoted identifier, then just repeat it.
CREATE TABLE `my table` (    `my-int-column` INT);
  • For identifiers such as database names and table names , the operating system may have other restrictions. More relevant information will be explained later in the introduction of "Data Directory Structure".
  • There are no restrictions on the aliases of column names and table names . If the alias to be used is a SQL reserved word, or is composed entirely of numbers, or contains spaces or other special characters, then it should be enclosed in identifier quotes. You can also use single or double quotes for column aliases.

The SQL mode of the server

If the ANSI_QUOTES mode of SQL is enabled, you can use double quotation marks to enclose the identifier (backquotes can also be used), for example:

CREATE TABLE "my table"(    "my-int-column" INT);
  • Enabling ANSI_QUOTES has a side effect, that is, the amount of string literals must be enclosed in single quotes . If double quotation marks are used, the server will interpret the value as an identifier instead of treating it as a string.
  • The names of built-in functions are usually not reserved words , so they can be used as identifiers without quotation marks. However, if the IGNORE_SPACE mode of SQL is enabled , the function name will become a reserved word. At this time, if you want to treat them as identifiers, you must enclose them in quotation marks.

The length of the identifier

  • The maximum length of most identifiers is 64 characters . The maximum length of the alias is 256 characters .

Qualifier of identifier

Depending on the specific context, the identifier may need to be qualified to make it clear what it refers to. If you want to reference the database , you only need to specify its name directly:

USE db_name;SHOW TABLES FROM dn_name;

If you want to reference the table, you have two options:

  • Use a fully qualified table name , that is, specify both the database identifier and the table identifier (shown in code snippet 1 below)
  • Table identifier they can reference to the default (current) database tables . For example, sampdb is the default database, then the two statements shown in the following code segment 2 are equivalent
SHOW COLUMNS FROM db_name.tbl_name;SELECT * FROM db_name tbl_name;
SHOW * FROM member;SHOW * FROM sampdb.member;
  • If no database is selected, then an error will occur if a table is referenced without a database qualifier; it is not known which database the table belongs to.
  • The reason for adding qualifiers to table names also applies to the names of views (they are "virtual" tables) and stored procedures.
  • For the reference of the table column, there are three options as follows:
  • Use fully qualified column names: dn_name.tbl_name.col_name
  • For a column in the named table in the default database, you can use a partially qualified name: tbl_name.col_name
  • Simply write an unqualified name to refer to the table determined by the specific environment context: the following two query statements use the same column name, but the context provided by the FROM clause of each statement will indicate that you should actually choose Which table column:
SELECT last_name, first_name FROM president;SELECT last_name, first_name FROM member;
  • If quotation marks are used when quoting a fully qualified name , then each identifier in the name needs to be quoted separately . E.g:
SELECT * FROM `sampdb`.`member` WHERE `sampdb`,`member`,`member_id`>100;
  • Please don't put this kind of name as a whole in quotation marks. The following statement is incorrect:
SELECT * FROM `sampdb.member` WHERE `sampdb`,`member`,`member_id`>100;
  • If you need to use a reserved word as an identifier, you must enclose it in quotation marks . However, when the reserved word immediately follows a period qualifier, this cannot be done, because the context at this time has shown that the reserved word is actually an identifier.

Second, SQL statement case specification

  • The upper and lower case rules in SQL statements vary depending on the components of the statement, and also depend on the content you are quoting and the operating system that the MySQL server host is running on.

SQL keywords and function names

  • SQL keywords and function names are not case sensitive . They can be any combination of upper and lower case.
  • For example, the following statements are all equivalent:
SELECT NOW();SELECT now();SELECT nOw();

Database name, table name, and view name

  • MySQL uses directories and files contained in the underlying file system on the server host to represent databases and tables . Therefore, the default case of library names and table names depends on the file name regulations of the operating system on the server host:
  • The file name of the Windows system is not case sensitive, so the MySQL server running on it does not distinguish between the case of the library name and the table name.
  • The MySQL server running on a Unix host usually needs to distinguish the case of library names and table names, because the Unix file system is case sensitive.
  • For the MacOSX platform , the names in the extended file system are an exception. They are not case sensitive.
  • For example, the following is MySQL under the Linux operating system, we failed to choose the DEMO database

MySQL uses a file to represent a view , so the discussion just now related to tables also applies to views.

The name of the stored procedure

  • The names of stored functions, stored procedures, and events are not case sensitive .
  • The trigger name is case sensitive , which is different from the behavior of standard SQL.

Column name and index name

  • In MySQL, column names and index names are not case sensitive .
  • The following statements are equivalent:
SELECT name FROM student;SELECT NAME FROM student;SELECT nAme FROM student;

Alias ​​name

  • By default, table aliases are case sensitive . You can use any combination of case (uppercase, lowercase, or mixed case) to specify an alias. But if you need to use the same alias multiple times in the same statement , you must keep them in the same case combination .
  • If the lower_case_table_names system variable is non-zero, then the table alias will be case insensitive.

String value

  • Whether a string value is case-sensitive depends on whether it is a binary or non-binary string , and for non-binary strings, it also depends on the collation of the character set . This is also true for the contents of text strings and string type columns. For more information, please refer to https://dongshao.blog.csdn.net/article/details/87366045 .
  • When you create a database and a table on a case-sensitive machine for file names, you should carefully consider the issue of case , such as "Will they be migrated to a case-insensitive machine for file names in the future?" ".
  • To avoid the problem of capitalization from evolving into a difficult problem, the feasible way is: first select a capitalization scheme, and then always follow the scheme to create databases and tables. Personally suggest using lowercase letters uniformly , especially if you want to use InnoDB tables, because the InnoDB engine stores database names and table names in lowercase letters internally.
  • If you want to use lowercase letters to create the database name and table name uniformly, even if you don't specify it in the CREATE statement, but also want this effect, you can configure the server by setting the lower_case_table_name system variable. It will be explained when the "data directory structure" is introduced later.