(Big Data Engineer Learning Path) step fifth MySQL reference manual Chinese version----MySQL data type

First, the literal value 1. String

A string is a sequence of characters that is caused by a single quotation mark ("') or double quotation mark ('" '). For example:

‘a string‘"another string"

If SQL Server mode is enabled for Nsi_quotes, you can refer to strings in single quotes only. A string referenced in double quotation marks is interpreted as a qualifier.

A string can have an optional character set with a preposition and COLLATE clause:

[_charset_name]‘string‘ [COLLATE collation_name]

For example:

SELECT _latin1‘string‘;SELECT _latin1‘string‘ COLLATE latin1_danish_ci;

In a string, some sequences have special meanings. These sequences begin with a backslash (' \ '), the so-called escape character. MySQL recognizes the following escape sequences:

These sequences are case-sensitive. For example, ' \b ' is interpreted as a backspace, but ' \b ' is interpreted as ' B '.

The ' \% ' and ' _ ' sequences are used to search for '% ' and ' text instances in a pattern matching environment that may be interpreted as wildcards. See section 12.3, 1, "string comparison function". Please note that if you use ' \% ' or ' \ ' in other environments, they return the string ' \% ' and ' _ ' instead of '% ' and ' _ '.

In other escape sequences, backslashes are ignored. In other words, the escape character is interpreted as if it were not escaped. There are several ways to include quotation marks in a string:

• "" can be written in a string with the ' ' ' reference '.

• "'" can be written in a string using ' ' ' ".

• You can add an escape character (' \ ') before the quotation marks.

• Using ' ' referenced ' within a string does not require special handling and does not need to be double-character or escaped. Similarly, the use of ' ' referenced ' within a string does not require special handling.

The following SELECT statement shows how references and escapes work:

‘hello‘, ‘"hello"‘, ‘""hello""‘, ‘hel‘‘lo‘, ‘\‘hello‘;mysql> SELECT "hello", "‘hello‘", "‘‘hello‘‘", "hel""lo", "\"hello";mysql> SELECT ‘This\nIs\nFour\nLines‘;mysql> SELECT ‘disappearing\ backslash‘;

If you want to insert binary data (such as a blob) in the string aligns, you must represent the following character through an escape sequence:

When you write an application, the strings that contain these special characters must be escaped correctly before they are used for data values that are sent to the MySQL server's SQL statement. You can do this in two ways:

• Handles strings with functions that escape special characters. For example, in a C program, you can use the mysql_real_escape_string () C API function to escape characters.

• Explicitly escaping special characters, many MySQL APIs provide placeholder functionality that allows you to insert special tags into the query string, and then bind the data values to them when you issue the query. In this case, the API focuses on special characters in the escape value

2. Values

Integers are expressed in a series of Arabic numerals. Floating point number using '. ' As a decimal separator. Both types of values can be preceded by a '-' to indicate a negative value.

Examples of legal integers:

1221 0-32 Examples of valid floating-point numbers:

294.42-32032.6809e+10 148.00 integers can be used in floating-point environments, and it is interpreted as equivalent to floating-point numbers.

3.16 Binary values

MySQL supports hexadecimal values. In a numeric context, hexadecimal numbers are like integers (64-bit precision). In a string context, like a binary string, each pair of hexadecimal digits is converted to a single character:

mysql> SELECT x‘4D7953514C‘；    -> ‘MySQL‘mysql> SELECT 0xa+0；    -> 10mysql> SELECT 0x5061756c； -> ‘Paul‘

The default type of hexadecimal value is a string. If you want to make sure that the value is treated as a number, you can use the cast (... As UNSIGNED):

0x41，CAST(0x41 AS UNSIGNED)；    -> ‘A‘，65

The 0x syntax is based on ODBC. Hexadecimal strings are typically used in ODBC to provide values for BLOB columns. The X ' hexstring ' syntax is based on standard SQL.

You can use the hex () function to convert a string or number to a hexadecimal-formatted string:

mysql> SELECT HEX(‘cat‘)；    -> ‘636174‘mysql> SELECT 0x636174；    -> ‘cat‘

4. Boolean values

A constant of true equals 1, and a constant of false equals 0. The constant name can be written in uppercase or lowercase.

TRUE，true，FALSE，false；    -> 1，1，0，0

5. Bit field values

You can use the B ' value ' notation to write bit field values. Value is a binary value written in 0 and 1.

The bit field symbol makes it easy to specify the value assigned to the bit column:

CREATE TABLE t (b BIT(8));mysql> INSERT INTO t SET b = b‘11111111‘;mysql> INSERT INTO t SET b = b‘1010‘;

6. Null value

A null value means "no data". Null can be written in uppercase or lowercase.

Note that a null value differs from an empty string of type 0 or string of numeric types.

For use with load DATA infile or select ... into outfile the text file import or export operation, and Null is represented by the sequence \ n.

II. databases, tables, indexes, columns, and aliases

databases, tables, indexes, columns, and aliases are identifiers. This section describes the allowable syntax for identifiers in MySQL.

The following table describes the maximum length and allowable characters for each type of identifier.

In addition to the restrictions noted in the table, identifiers may not contain ASCII 0 or bytes with a value of 255. Database, table, and column names should not end with a space. Quotation marks can be used in identifiers, although such use should be avoided as much as possible.

Identifiers are saved in Unicode (UTF8). Identifiers for table definitions saved in the. frm file and for authorization tables in the MySQL database are also saved with Unicode (UTF8). The size of the string columns in the authorization table (and other tables) in MySQL 5.1 equals the number of characters, which means (unlike previous versions of MySQL) you can use multibyte characters in the values stored in these columns without having to reduce the number of characters.

Identifiers can be brought up or not. If the identifier is a reserved word or contains special characters, it must be caused whenever it is used.

The identifier's reference is a counter-tick ("'):

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

If the SQL Server mode includes the ansi_quotes mode option, you can also enclose the identifier in double quotation marks:

"test" (col INT);ERROR 1064: You have an error in your SQL syntax. (...)mysql> SET sql_mode=‘ANSI_QUOTES‘;mysql> CREATE TABLE "test" (col INT);Query OK, 0 rows affected (0.00 sec)

If you reference the identifier, you can include the identifier reference within the identifier. If the character included in the qualifier is the same as the character that refers to the identifier, two characters are required. The following statement creates a table named a ' B ' containing column C "D:

mysql> CREATE TABLE `a``b` (`c"d` INT)；

It is not recommended to use the name of the XEX mode, such as 1e or 2e2, because expressions similar to 1e+1 are ambiguous. Depending on the context, it can be interpreted as an expression of 1e + 1 or the number 1e+1.

Use MD5 to produce a table name carefully, as it may produce an illegal table name, as described above.

1. Qualifier Limit conditions

MySQL allows you to use a name that consists of a single identifier or more than one identifier. Multipart name each component should be preceded by a period ('. ') Spaced apart. The beginning of the multipart name is used as the qualifier, and the subsequent identifier is interpreted.

In MySQL, you can reference columns in the following form:

If multipart-named components need to be referenced, they should be raised separately instead of the entire name. For example, my-tables . my-column valid, and my-tables.my-column invalid.

You do not need to specify a tbl_name or db_name.tbl_name prefix for the column in the statement, unless the column is ambiguous. Assuming that the table T1 and T2 each contain a column C, you use the SELECT statement to search for C in T1 and T2. In this case, C is ambiguous because it is not unique within the table used in the statement. You must qualify it with the table name t1.c or t2.c to indicate which table you mean. Similarly, to use the same statement to search for table T in database db1 and table T in database DB2, you must refer to the columns in those tables as Db1.t.col_name and Db2.t.col_name.

The word after a period in a qualified name must be a qualifier, so it does not need to be caused, even if it is a reserved word.

Syntax. Tbl_name represents the Tbl_name in the current database. This syntax is compatible with ODBC because some ODBC programs prefix the table name with '. ' Character.

2. Identifier Case sensitivity

In MySQL, the database corresponds to the directory in the data directory. Each table in the database corresponds to at least one file in the database directory (and possibly multiple, depending on the storage engine). Therefore, the case sensitivity of the operating system used determines the case sensitivity of the database name and the table name. This means that in most Unix, database names and table names are case-sensitive and case insensitive in Windows. A notable exception is Mac OS X, which is UNIX-based but uses the default file system type (hfs+), which is not case sensitive. However, Mac OS X also supports UFS volumes, which are case-sensitive, just like Unix.

Note: Although database names and table names are not case sensitive in some platforms, you should not use a different case in the same query to refer to a given database or table. The following query does not work because it references both the table My_tables and the as My_tables:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

columns, indexes, stored subroutines, and trigger names are not case sensitive on any platform, and the alias of the column is not sensitive.

By default, table aliases are case-sensitive in Unix, but not case-sensitive in Windows or Mac OS x. The following query does not work in Unix because it references aliases A and a at the same time:

AS a    -> WHERE a.col_name = 1 OR A.col_name = 2;

However, the query is available in Windows. To avoid differences, it is best to use a consistent conversion, such as always creating and referencing the database name and table name in lowercase. This conversion is recommended in most porting and use.

How to save and use table names and database names on your hard disk in MySQL is determined by the LOWER_CASE_TABLES_NAME system variable and can be set when you start Mysqld. Lower_case_tables_name can take any of the following values:

In Windows and Mac OS X, the default value for Lower_case_tables_name is 1.

If you use MySQL on only one platform, you typically do not need to change the lower_case_tables_name variable. However, if you want to transfer tables between platforms that are sensitive to the size of different file systems, you will encounter difficulties. For example, in Unix, My_tables and my_tables are two different tables, but in Windows, the two table names are the same. To avoid data transfer problems due to database or table name capitalization, you can use two options:

• Lower_case_tables_name=1 can be used in any system. The disadvantage of using this option is that when you use show tables or show databases, you cannot see whether the name is originally capitalized or lowercase.

• Using Lower_case_tables_name=0 in Unix, use lower_case_tables_name=2 in Windows. This preserves the case of the database name and table name. The disadvantage is that you must ensure that queries in Windows always refer to the database name and table name with the correct case. If queries are transferred to UNIX, they do not work because of the importance of casing in Unix, if the casing is not correct.

Exception: If you are using the InnoDB table, you should set Lower_case_tables_name to 1 on any platform to force the name to lowercase.

Please note that before Lower_case_tables_name is set to 1 in Unix, the old database name and table name must be converted to lowercase before restarting mysqld.

Third, user variables

You can first save the value in a user variable and then reference it later, so that you can pass the value from one statement to another. User variables are related to connections. In other words, a client-defined variable cannot be seen or used by other clients. When the client exits, all the variables for that client connection are automatically freed.

The user variable is in the form @varname, where the variable name Var_name can be composed of the literal numeric characters of the current character set, '. ',' ' and ' $ '. The default character set is cp1252 (Latin1). You can change the character set with the mysqld--default-character-set option.

One way to set up a user variable is to execute the SET statement:

SET @var_name = expr [, @var_name = expr] ...

For set, you can use = or: = as an assignment. The expr assigned to each variable can be an integer, a real number, a string, or a null value.

You can also assign a value to a user variable by using a statement instead of set. In this case, the quantifier must be: = instead of =, because in a non-set statement = is treated as a comparison operator:

@t1=0, @t2=0, @t3=0;mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

A user variable can be used in an expression. Currently does not include a context that clearly requires literal values, such as the limit clause of a SELECT statement, or the Ignore number lines clause of the LOAD data statement.

If you use a variable that is not initialized, its value is null.

If a user variable is assigned a string value, its character set and proofing rules are the same as the string. The compressibility of User variables (coercibility) is implicit. (That is, the same compressibility (coercibility) for the table column values.)

Note: In a SELECT statement, the expression is sent to the client before it is evaluated. This indicates that an expression containing the variables set in the select list cannot be used in a having, GROUP by, or ORDER BY clause. For example, the following statement cannot be scheduled to work:

mysql> SELECT (@aa:=id) AS a，(@aa+3) AS b 从tbl_name HAVING b=5；

The HAVING clause references the alias of an expression in the select list, using @aa. Can't expect to work: @aa does not contain the value of the current row, but rather the ID value of the previously selected row.

The general principle is that you do not assign a value to a user variable in one part of the statement and use that variable in other parts of the same statement. The desired result may be obtained, but cannot be guaranteed.

Another problem with setting a variable and using it in the same statement is that the type of the default result of the variable depends on the variable type before the statement. The following example illustrates the point:

@a=‘test‘;mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this SELECT statement, MySQL reports to the client that the 1th column is a string and converts all access to the @a to a string, even if @a is set to a number in line 2nd. After the SELECT statement is executed, @a is treated as a number for the next statement.

To avoid this problem, either set and use the same variable in the same statement, or set the variable to 0, 0.0, or "before use" to define its type.

Unassigned variable has a value of NULL, type is string

Iv. System Variables

MySQL can access many system and connection variables. Many variables can be changed dynamically when the server is running. This usually allows you to modify the server operation without having to stop and restart the server.

The MYSQLD server maintains two variables. Global variables affect the overall operation of the server. Session variables affect the operation of a specific client connection.

When the server starts, it initializes all global variables to their default values. These default values can be changed in the options file or in the options specified on the command line. After the server starts, you can dynamically change these global variables by connecting to the server and executing the SET global var_name statement. To change global variables, you must have super permissions.

The server also maintains a series of session variables for each connected client. Initializes the client's session variable with the current value of the corresponding global variable at connection time. For dynamic session variables, clients can change them by using the SET session Var_name statement. Setting a session variable does not require special permissions, but the client can only change its own session variables, not the session variables of other clients.

Changes to global variables can be seen by any client that accesses the global variable. However, it only affects the corresponding session variable that is initialized from the global variable for the connected customer after the change. does not affect session variables for clients that are currently connected, even if the client executes the SET global statement.

You can use several syntax forms to set or retrieve global or session variables. The following example uses Sort_buffer_sizeas as the example variable name.

To set the value of a global variable, use the following syntax:

mysql> SET GLOBAL sort_buffer_size=value;mysql> SET @@global.sort_buffer_size=value;

To set the value of a session variable, use the following syntax:

mysql> SET SESSION sort_buffer_size=value;mysql> SET @@session.sort_buffer_size=value;mysql> SET sort_buffer_size=value;

Local is synonymous with the session.

If you do not specify global, session, or local when setting a variable, the session is used by default.

To retrieve the value of a global variable, use the following syntax:

mysql> SELECT @@global.sort_buffer_size;mysql> SHOW GLOBAL VARIABLES like ‘sort_buffer_size‘;

To retrieve the value of a session variable, use the following syntax:

mysql> SELECT @@sort_buffer_size;mysql> SELECT @@session.sort_buffer_size;mysql> SHOW SESSION VARIABLES like ‘sort_buffer_size‘;

Here, Local is also synonymous with the session.

When you search for a variable with the SELECT @ @var_name (that is, do not specify global, session, or local), MySQL returns the session value (if it exists), otherwise returns the global value.

For show VARIABLES, if you do not specify global, session, or Local,mysql, the session value is returned.

The Global keyword is required when the global variable is set but is not needed for retrieval because it prevents future problems. If we remove a session variable with the same name as a global variable, a customer with super privileges may accidentally change the global variable instead of its own connected session variable. If we add a session variable with the same name as a global variable, the customer who wants to change the global variable may find that only its own session variable has been changed.

1. Structural System Variables

A structural variable differs from a regular system variable in two ways:

• The value is a structure with components that can specify server parameters, which are generally closely related.

• May be several instances of a struct variable of a given type. Each instance has a different name, pointing to the different resources that the server maintains.

MySQL 5.1 supports structured variable types, which can specify parameters for monitoring key-value cache operations. The key-value cache structure variable has the following components:

• Key_buffer_size

• Key_cache_block_size

• Key_cache_division_limit

• Key_cache_age_threshold

To reference a component of a struct variable instance, you can use the compound name in the Instance_name.component_name format. For example:

hot_cache.key_buffer_sizehot_cache.key_cache_block_sizecold_cache.key_cache_block_size

For each structural system variable, an instance named default is always pre-defined. If you do not use any instance name to reference a component of a structural variable, the default instance is used. In this way, both Default.key_buffer_size and Key_buffer_sizeboth point to the same system variable.

The naming conventions for structured variable instances and components are:

• For a structured variable of a given type, each instance must have a unique name in that class variable. However, instance names do not need to be unique in different types of structured variables. For example, each structured variable has an instance of default, so the default is not unique in different variable types.

• The component name of each structured variable type must be unique across all system variable names. If this is not the case (that is, if two different types of structured variables can share the component member name), it is unclear which default structure variable to use as a member name that is not qualified with the instance name.

• If the struct variable instance name is not valid as an unrecognized identifier, it is caused by a counter-tick as the identifier. For example, Hot-cache is not legal, but ' hot-cache ' is legal.

• Global, session, and local are not legal instance names. This avoids conflicts with symbols that reference non-structural system variables, such as @ @global. Var_name.

Currently, the first two rules cannot be violated because the only structured variable type is the key-value cache. Creating other types of structural variables in the future will have important implications.

One exception is that you can use a compound name to refer to a structural variable component in a context where a simple variable name might appear. For example, you can use a command-line option that does not assign a value to a struct variable:

shell> mysqld --hot_cache.key_buffer_size=64K

In the options file, use:

[mysqld]hot_cache.key_buffer_size=64K

If you start the server with this option, in addition to the default key-value cache with a default size of 8MB, a key-value cache named Hot_cache is created with a size of 64KB.

Suppose you start the server this way:

shell> mysqld --key_buffer_size=256K        --extra_cache.key_buffer_size=128K        --extra_cache.key_cache_block_size=2048

In this case, the server sets the default key-value cache size to 256KB. (can also be written as--default.key_buffer_size=256k). Also, the server creates a 2nd key-value cache named Extra_cache, with a size of 128KB, and the block buffer size of the cache table index block is set to 2048 bytes.

In the following example, the server is started with 3 different key-value caches (3:1:1 size ratio):

shell> mysqld --key_buffer_size=6M        --hot_cache.key_buffer_size=2M        --cold_cache.key_buffer_size=2M

You can also set and retrieve structural variable values at run time. For example, to set the size of a key-value cache named Hot_cache to 10MB, use any of the following statements:

mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;mysql> SET @@global.hot_cache.key_buffer_size = 10*1024*1024;

To retrieve the cache size, execute:

mysql> SELECT @@global.hot_cache.key_buffer_size;

However, the following statement does not work. A variable is not interpreted as a compound name, but rather as a simple string that is interpreted as a like pattern match operation:

GLOBAL VARIABLES LIKE ‘hot_cache.key_buffer_size‘;

This is an example of using a struct variable name when a simple variable name can occur.

V. Syntax of annotations

MySQL server supports 3 kinds of annotation styles:

• From the ' # ' character from the end of the line.

• From the '--' sequence to the end of the line. Note that the '--' (double dash) annotation style requires a 2nd dash followed by at least one space character (such as spaces, tab, line breaks, and so on). This syntax is slightly different from the standard SQL comment syntax, which is discussed in 1.8.5.7, "'--' as an annotation start tag."

• The From/ sequence to the back /sequence. The end sequence is not necessarily in the same row, so the syntax allows comments to span multiple lines.

The following example shows comments in 3 styles:

1+1;     # This comment continues to the end of linemysql> SELECT 1+1; -- This comment continues to the end of linemysql> SELECT 1 /* this is an in-line comment */ + 1;mysql> SELECT 1+/*this is amultiple-line comment*/1;

The comment syntax above applies to how the MYSQLD server parses the SQL statement. The MySQL client also performs partial statement parsing before it is sent to the server. (for example, it resolves to determine the statement boundary in a multi-statement row).

In MySQL 5.1, MySQL parsing/ ... The only limitation of the/comment is that the conditional execution of the SQL statement part is marked with an exclamation mark that combines the style's comment delimiter. Applies to interactively running MySQL and putting commands into a file, and using MySQL in batch mode to process MySQL < file_name files.

Vi. processing of reserved words in MySQL

Attempting to use a qualifier, such as using an embedded MySQL data type or function name as a table or column name, such as timestamp or group, can cause a common problem. Allows you to do this (for example, ABS can be used as a column name). However, by default, spaces are not allowed between the function name and the following ' (' character) in a number call. This requirement makes the function call different from the column name reference.

The disadvantage of this behavior is that omitting a space in some contexts causes the identifier to be interpreted as the function name. For example, the statement is valid:

abs (val INT)；

However, omitting the space behind ABS causes a syntax error because the statement appears to call the ABS () function when omitted:

CREATE TABLE abs(val INT);

If the SQL Server pattern includes the Ignore_space mode value, the server allows the function to be called with a space between the function name and the following ' (' character). This causes the function name to be treated as a reserved word.

The word after a period in a qualified name must be a qualifier, so it does not need to be caused, even if it is a reserved word.

In MySQL, the characters in the following table are explicitly reserved. Most of these digital binaries are used by standard SQL as column names and/or table names (for example, GROUP). A few are retained because MySQL needs them and (currently) uses the YACC parser. Reserved words can be used as identifiers when they are caused.

MySQL allows some of the keywords to be used as unrecognized identifiers, because many people have been using them before. Some examples are listed below:

• ACTION
• BIT
• DATE
• Enum
• NO
• TEXT
• Time
• TIMESTAMP

(Big Data Engineer Learning Path) step fifth MySQL reference manual Chinese version----MySQL data type