Shulou(Shulou.com)06/01 Report--

See the "Server SQL Modes" section of the MySQL5.6 manual for details.

Address: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

After referring to the blog posts of several websites, the source is not easy to find. Thank you for sharing.

MySQL5.6 's sql_mode is STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION.

The sql_mode for MariaDB10.0.17 is empty.

Sql_mode can be divided into global and session.

> SETGLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

> SETSESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

> SELECT @ @ GLOBAL.sql_mode

> SELECT @ @ SESSION.sql_mode

The most important sql_mode

ANSI

Equivalent to: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI

Change the syntax and behavior to make it more compliant with standard SQL.

STRICT_TRANS_TABLES

Equivalent to: STRICT_TRANS_TABLES

If the given value cannot be inserted into the transaction table, the statement is discarded. For a non-transactional table, if the value appears in the first row of a single-line statement or multiple-row statement, the statement is discarded.

TRADITIONAL

Equivalent to: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Make MySQL behaves like a "traditional" SQL database system. The simple description of this pattern is to "give an error rather than a warning" when an incorrect value is inserted into a column. Note: discard INSERT/UPDATE as soon as an error is found. If you use a non-transactional storage engine, this approach is not what you want, because the data changes made before the error are not scrolled, and the result is that the update is "only partially carried out".

If the value of sql_mode is set to the next two values (STRICT_TRANS_TABLES or TRADITIONAL, which we call strict mode), mysql will give an error and abandon the insert/update operation when an incorrect value is inserted or updated in the column.

Instead of using the default null or ANSI mode, it is recommended to use STRICT_TRANS_TABLES or TRADITIONAL in our general application.

However, it should be noted that if the database is running in strict mode and your storage engine does not support transactions (InnoDB engine is basically used now, it is rare that transactions are not supported), then there is a risk of data inconsistency, such as two dml statements in a set of sql. If the latter has a problem, but the previous operation has been successful, then mysql cannot roll back the previous operation. Therefore, setting up sql_mode requires the application staff to weigh the various gains and losses in order to get an appropriate choice.

The following content is translated from the official document, some parameters can not be translated, the search on the Internet is rarely mentioned, the translation directly ignored.

All sql_mode

MySQL version of set session sql_mode='ALLOW_INVALID_DATES'

> insert into new (`d`) VALUES ('201')

Query OK, 1 row affected, 1 warning (0.01sec) prompt inserted successfully with a warning

This pattern is valid only for DATE and DATETIME types, but not for TIMESTAMP, because TIMESTAMP always requires a valid input. ANSI_QUOTES

When ANSI_QUOTES is enabled, you cannot refer to a string in double quotes because it is interpreted as an identifier.

> set session sql_mode='ANSI_QUOTES'

> insert into teachersvalues (22, "M")

ERROR1054 (42S22): if Unknown column 'M'in 'field list' uses double quotation marks, it directly misreports ERROR_FOR_DIVISION_BY_ZERO.

During the INSERT or UPDATE process

If this mode is not enabled, if the data is divided by zero, the final result is inserted into NULL and warnings is not prompted.

If this mode is enabled, if the data is divided by zero, the final result is inserted into NULL and prompts warnings

> set session sql_mode='STRICT_TRANS_TABLES'

> insert into T1 VALUES (5thumb 0)

Query OK, 1 row affected (0.00 sec) insert without warning, the result is a NULL

> set session sql_mode='ERROR_FOR_DIVISION_BY_ZERO'

> insert into T1 VALUES (6thumb 0)

QueryOK, 1 row affected, 1 warning (0.01sec) has a warning when inserted, and the result is a NULLHIGH_NOT_PRECEDENCE

Set priority relationship [mainly to be compatible with older versions of MySQL]

> SET sql_mode =''

L > SELECT NOT 1 BETWEEN-5 AND 5

The result is 0

> SET sql_mode = 'HIGH_NOT_PRECEDENCE'

> SELECT NOT 1 BETWEEN-5 AND 5

The result is 1.

IGNORE_SPACE

There is a space between the function name and the parenthesis "(". Apart from adding some annoyance, this option does not seem to do any good, and the user must refer to it to access the database, table, or column name saved as a keyword.

For example, if there is a user column in a table and a user function in the MySQL database, user will be interpreted as a function. If you want to select the user column, you need to refer to it.

> createtable `partition` (I int (3)). If the table name or other places that retain keywords or the built-in function have the same name, you must put backquotation marks, otherwise MySQL will report an error. NO_AUTO_CREATE_USER

Prevents GRANT from creating users with empty passwords.

> SET sql_mode = 'NO_AUTO_CREATE_USER'

> grant all on *. * to 'lirl'@'%'

ERROR 1133 (42000): Can't find any matchingrow in the user table

> grant all on *. * to 'lirl2'@'%'identified by' 123456'

QueryOK, 0 rows affected (0.00 sec) NO_AUTO_VALUE_ON_ZERO

This option affects inserts that are listed as self-growing.

By default, inserting 0 or NULL means the next self-growth value is generated.

This option is useful if the user wants to insert a value of 0 and the column is self-growing. NO_BACKSLASH_ESCAPES

Backslash "\" as a normal character rather than an escape character

> set sql_mode=''

> select'\\'

+-- +

| |\ |

+-- +

| |\ |

+-- +

> SET sql_mode = 'NO_BACKSLASH_ESCAPES'

> select'\\'

+-- +

| |\ |

+-- +

| |\ |

+-+ NO_DIR_IN_CREATE

Ignore all options for INDEX DIRECTORY and DATA DIRECTORY when creating tables.

> set sql_mode=''

> create table T1 (`age` char (3))

DATADIRECTORY= "/ tmp"

INDEX DIRECTORY= "/ tmp"

Description: there will be relevant ibd tablespace files under / tmp at this time.

> set sql_mode='NO_DIR_IN_CREATE'

> create table T2 (`age` char (3))

DATADIRECTORY= "/ tmp"

INDEX DIRECTORY= "/ tmp"

Note: at this time, the relevant ibd tablespace file can not be found under / tmp, the table file is still under the original datadir.

NO_ENGINE_SUBSTITUTION

Do not use default engine overrides

After setting this parameter, an error is thrown if the required storage engine is disabled or not compiled.

If you do not set this parameter, if the specified storage engine cannot be found, it will be replaced by the default storage engine, but there will be a warning prompt.

> set sql_mode=''

> create table T4 (`age` int (3)) ENGINE=Inff

> show create table T4\ G

* 1. Row**

Table: t4

Create Table: CREATE TABLE `t4` (

`age`int (3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Above we casually used a non-existent storage engine to build the table without error, as long as there is a warning prompt, the system comes with the default storage engine instead.

> set sql_mode='NO_ENGINE_SUBSTITUTION'

> create table T4 (`age` int (3)) ENGINE=Inff

ERROR 1286 (42000): Unknown storage engine'Inff'

When sql_mode='NO_ENGINE_SUBSTITUTION' is explicitly set, an error is reported directly when you specify a storage engine that does not exist when creating the table. NO_FIELD_OPTIONS

NO_KEY_OPTIONS

NO_TABLE_OPTIONS

NO_UNSIGNED_SUBTRACTION

NO_ZERO_DATE

If enabled, the insertion of dates like 0000-00-00 is allowed, but there will be warnings

If it is not enabled, it means that the insertion of dates like 0000-00-00 is allowed and there will be no warnings.

If the strict mode is also enabled when the NO_ZERO_DATE module is started, the insertion is not allowed in 0000-00-00, and the insertion can only be successful if the IGNORE parameter is explicitly used.

> setsql_mode='NO_ZERO_DATE,.STRICT_TRANS_TABLES'

> insert into tb2 VALUES ('0000-0000'); if inserted in this way, an error will be reported.

> INSERT IGNORE INTO tb1 VALUES ('0000-0000'); # add IGNORE to insert NO_ZERO_IN_DATE successfully

> set sql_mode='NO_ZERO_IN_DATE'

Illegal dates such as 2015-00-02 and 2015-03-00 are allowed, but 0000-00-00 is actually inserted. It is no problem to insert normal data like 2013-02-01.

> set sql_mode=''

> insert into tb2 VALUES ('2010-00-02'); allow date insertion in this format ONLY_FULL_GROUP_BY for GROUP BY aggregation operations, if the column in SELECT does not appear in GROUP BY, then the SQL is illegal because the column is not in the GROUP BY clause. PAD_CHAR_TO_FULL_LENGTH

PIPES_AS_CONCAT treats "| |" as the concatenation operator of the string rather than the OR operator, which is the same as the Oracle database and similar to the concatenation function Concat of the string. REAL_AS_FLOAT

Real numbers are synonymous with floating point numbers. [by default, MySQL uses real numbers as synonyms for double]

Double 8 byt

Float 4 byt

Real8 byte STRICT_ALL_TABLES strict mode STRICT_TRANS_TABLES strict mode

Both STRICT_ALL_TABLES and STRICT_TRANS_TABLES indicate that strict mode is enabled. But there are some differences between the two:

Strict mode controls how MySQL handles illegal or missing input values. There are several reasons to make a value illegal. For example, the data type is incorrect, does not fit the column, or is out of range. When the newly inserted row does not contain a column that does not display a value that defines the DEFAULT clause, the value is lost.

For transaction tables, an error occurs if there are illegal or missing values in the statement when STRICT_ALL_TABLES or STRICT_TRANS_TABLES mode is enabled. Statement is discarded and scrolled.

For non-transactional tables, if a bad value appears in the first row of the insert or update, the behavior of the two modes is the same. The statement is discarded and the table remains unchanged. If the statement inserts or modifies multiple rows and bad values appear in the second or later row, the result depends on which strict option is enabled:

Returns an error for STRICT_ALL_TABLES,MySQL and ignores the remaining lines. However, in this case, the previous row has been inserted or updated. This means that you can update partially, which may not be what you want. To avoid this, it's best to use a single-line statement, because you can give up without changing the table.

For STRICT_TRANS_TABLES,MySQL, convert the illegal value to the legal value closest to the column and insert the adjusted value. If the value is missing, MySQL inserts an implicit default value in the column. In any case, MySQL generates a warning instead of giving an error and continues to execute the statement.

In general, do not use MySQL to check the contents of a column. The safest way (and usually faster) is to let the application be responsible for passing only valid values to the database.

In strict mode, we can still use [INSERT | UPDATE] IGNORE INTO TB_NAMEVALUES (xxxx); treat warnings among errors, but this is not actually recommended.

MySQL version > = 5.6.6

Default is: NO_ENGINE_SUBSTITUTION

The optional parameters are the same as above. I won't explain them one by one.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.