MySQL data type: Sql_mode settings cannot be ignored

The "IT168 technology" Sql_mode may be a variable that is easier for developers and DBAs to ignore, and is empty by default. The Sql_mode setting is actually a more risky setting because it allows for some illegal operations, such as inserting null into a NOT null field, or inserting some illegal dates, such as "2012-12-32". Therefore, it is strongly recommended in a production environment that developers set this value to strict mode, so that some problems can be found during the design and development stages of the database, and if such problems are discovered after running the database in a production environment, the cost of the modification will become enormous. In addition, setting Sql_mode correctly can also do some constraint (Constraint) checking work.

For Sql_mode settings, it can be done in MySQL configuration files, such as MY.CNF and My.ini, in the client tool, and can be set globally or in the current session, respectively. The following command can be used to view the current Sql_mode settings.

mysql> SELECT @@ Global.　　sql_mode\g; *************************** 1. Row *************************** @@ Global sql_mode:1 Row in Set (0.00 sec)  mysql  ; SELECT @ @session.　　sql_mode\g; *************************** 1. Row *************************** @ @session. Sql_mode: no_unsigned_subtraction 1 row in Set (0.00 sec)

You can see that the current global Sql_mode setting is empty, and the current session is set to No_unsigned_subtraction. You can set the current Sql_mode to strict mode by using the following statement.

MySQL　　GLOBAL sql_mode= ' strict_trans_tables '; Query OK, 0 rows Affected (0.00 sec)

Strict mode refers to setting the Sql_mode variable to at least one of the strict_trans_tables or strict_all_tables. Now let's take a look at the options that Sql_mode can set.

Strict_trans_tables: In this mode, if a value cannot be inserted into a transaction table (for example, the storage engine of the table is InnoDB), then interrupting the current operation does not affect the non-transactional table (for example, the storage engine for the table is MyISAM).

Allow_invalid_dates: This option does not completely check the legality of the date, only checks whether the month is between 1~12 and whether the date is between 1~31. This mode is valid only for date and datetime types, but not for timestamp because timestamp always requires a valid input.

Ansi_quotes: After enabling ansi_quotes, you cannot reference a string with double quotation marks because it will be interpreted as a qualifier, as in the following example:

mysql> CREATE TABLE z (a VARCHAR (ten)) engine=INNODB;　　Query OK, 0 rows affected (0.00 sec)  MySQL>insert into z Select "AAA";　　Query OK, 1 rows Affected (0.00 sec)  mysql> SET sql_mode= ' ansi_quotes '; Query OK, 0 rows affected (0.00 sec)  mysql> INSERT into z Select "AAA"1054 (42s2 2): Unknown column ' aaa ' in ' Field list '

Error_for_division_by_zero: During an INSERT or update process, if the data is removed by 0 (or mod (x,0)), an error is generated (otherwise, a warning). If the pattern is not given, then MySQL returns NULL when the data is removed by 0. If you use Insert ignore or update ignore, the MySQL build is 0 apart from the warning, but the result of the operation is null.

High_not_precedence not: The precedence of the operator is an expression. For example, not a between B and C are interpreted as not (a between B and C), and in some older versions of MySQL, the preceding expression is interpreted as (not a) between B and C. Enable the High_not_precedence SQL mode to get higher priority results for older versions of the previous version. Let's look at an example:

MySQL> SELECT 0 BETWEEN-1 and 1\g; *************************** 1. Row *************************** 0 BETWEEN-1 and 1:1 1 row in Set (0.00 sec)

0 is between 1 and 1, so return 1, and if not, return 0, the process is as follows:

mysql> SELECT @ @sql_mode \g; *************************** 1. Row *************************** @ @sql_mode: 1 row in Set (0.00 sec)  mysql> SELECT not 0 B　　ETWEEN-1 and 1\g; *************************** 1. Row ***************************0 BETWEEN-1 and 1:0 1 row in Set (0.00 sec)

However, if High_not_precedence mode is enabled, select not 0 BETWEEN-1 and 1 are interpreted as select (not 0) BETWEEN-1 and 1, and the result is the exact opposite, as follows:

Mysql> SELECT not 0 BETWEEN-1 and 1\g; 1. Row *************************** not 0 BETWEEN-1 and 1:1 1 row in Set (0.00 sec)

As can be seen from the above example, between A and B in the MySQL database is interpreted as [a, a,]. Here are two simple tests

Mysql> SELECT 1 BETWEEN-1 and 1\g; 1.　　Row *************************** 1 BETWEEN-1 and 1:1 1 row in Set (0.00 sec) mysql> SELECT-1 BETWEEN-1 and 1\g; 1. Row ***************************-1 BETWEEN-1 and 1:1 1 row in Set (0.00 sec)

　　

Ignore_space: There is a space between the function name and the parentheses "("). In addition to adding some annoyance, this option does not seem to have any benefit, to access the database, table, or column name saved as a keyword, the user must reference this option. For example, a table has the user column, and the MySQL database has the user function, user will be interpreted as a function, if you want to select the user column, you need to reference.

No_auto_create_user: Disallow grant to create a user with a blank password.

No_auto_value_on_zero: This option affects inserts that are listed as self-growing. By default, inserting 0 or null represents the generation of the next self-growth value. This option is useful if the user wants to insert a value of 0 and the column is self-growing.

No_backslash_escapes: the backslash "\" is an ordinary character rather than an escape character, as shown in the following example:

mysql> SET sql_mode= ";　　Query OK, 0 rows Affected (0.00 sec) mysql> SELECT ' \ \ ' \g; 1.　　Row *************************** \: 1 row in Set (0.00 sec) mysql> set sql_mode= ' No_backslash_escapes ';　　Query OK, 0 rows Affected (0.00 sec) mysql> SET ' \ \ ' \g; 1. Row *************************** \: 1 row in Set (0.00 sec)

　　

No_dir_in_create: Ignores all index directory and data directory options when creating a table.

No_engine_substitution: Throws an error if the required storage engine is disabled or not compiled. Default is replaced with the default storage engine, and an exception is thrown.

No_unsigned_subtraction: Previously introduced, the two UNSIGNED types are returned signed type after enabling this option.

No_zero_date: In non-strict mode, you can insert an illegal date like "0000-00-00 00:00:00", and the MySQL database only throws a warning. When this option is enabled, the MySQL database does not allow insertion of the 0 period, and inserting the 0 period throws an error instead of a warning.

No_zero_in_date: In strict mode, the date and month are not allowed to be zero. Formats such as "2011-00-01" and "2011-01-00" are not allowed. MySQL throws an error instead of a warning when using a date or month-zero format, as shown in the following example:

mysql> SET sql_mode= ' no_zero_in_date ';　　Query OK, 0 rows Affected (0.00 sec) mysql> CREATE TABLE A (a DATETIME);　　Query OK, 0 rows affected (0.04 sec) mysql> INSERT into a SELECT ' 2011-01-00 '; ERROR 1292 (22007): Incorrect datetime value: ' 2011-01-00 ' for column ' A ' at row 1

Only_full_group_by: For a group by aggregation operation, if the column in select does not appear in group BY, then this SQL is not legal because the column A is not in the GROUP BY clause, as shown in the following example:

mysql> SET sql_mode= ' only_full_group_by ';　　Query OK, 0 rows Affected (0.00 sec) mysql> SELECT a,sum (b) from the T GROUP by B; ERROR 1055 (42000): ' test.t.a ' isn ' t in GROUP by

Pad_char_to_full_length: For CHAR type fields, do not truncate the empty data. Empty data is automatically populated with values of 0x20. Let's look at the MySQL database performance by default.

 mysql 　　> CREATE TABLE T (a CHAR (10, 0 rows affected (0.04 sec)  mysql<　　/span>> INSERT into T select ' a ' ; Query OK , 1 row affected (0.01 sec) Records : 1 duplicates:0 warnings:0 
      
       mysql 
       >select A,char_length (a), hex (a) from t\g;  *************************** 1. Row *************************** a :  a Char_ LENGTH (a) : 1 HEX (a) : 1 row in Set (0.04 sec)  

As you can see, by default, although column A is of type char, the returned length is 1 because the MySQL database has truncated the subsequent empty data. If the Pad_char_to_full_length option is enabled, the actual stored content is reflected, for example:

Mysql> SELECT A,char_length (a), HEX (a) from t\g; 1. Row *************************** a:a Char_length (a): Ten HEX (a): 61202020202020202020 1 row in Set (0.00 sec)

　　

You can see that the value that is actually stored in char column A is 0x61202020202020202020.

Pipes_as_concat: Will "| |" The concatenation operator, rather than the operator, that is treated as a string, which is the same as the Oracle database, and is similar to the concatenation function concat of the string, for example:

mysql> SET sql_mode= ' Pipes_as_concat '; Query OK, 0 rows affected (0.01 sec) mysql> SELECT ' a ' | | ' B ' | | '　　C ' \g; 1. Row *************************** ' a ' | | ' B ' | | ' C ': ABC 1 row in Set (0.00 sec)

　　

Real_as_float: Treats real as a synonym for FLOAT, rather than a double synonym.

Strict_all_tables: Strict mode is enabled for all engine tables. (Strict_trans_tables only enables strict mode for tables that support transactions.)

In strict mode, the current operation is terminated once the data of any operation has a problem. For non-transactional engines with the Strict_all_tables option enabled, data may remain in an unknown state. This may not be a situation that all non-transactional engines would like to see, so be very careful about the potential impact this option may have.

The following several sql_mode settings are a combination of the options discussed earlier.

ANSI: Equivalent to the combination of real_as_float, Pipes_as_concat, and Ansi_quotes, Ignore_space.

ORACLE: Equivalent to Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, no_field_options, and No_auto_ A combination of create_user.

Traditional: Equivalent to Strict_trans_tables, Strict_all_tables, No_zero_in_date, No_zero_date, Error_for_division_by_zero, Combination of No_auto_create_user and no_engine_substitution.

MSSQL: Equivalent to Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, no_table_options and no_field_options combinations.

DB2: Equivalent to Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, no_table_options, and no_field_options combinations.

MYSQL323: Equivalent to the combination of no_field_options and high_not_precedence.

MYSQL40: Equivalent to the combination of no_field_options and high_not_precedence.

MAXDB: Equivalent to Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options and NO_AUTO_ A combination of create_user.

MySQL data type: Sql_mode settings cannot be ignored