Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of the common value of sql_mode in MySQL5.6, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

SQL Mode defines two aspects: the SQL syntax that MySQL should support and what validation checks should be performed on the data.

SQL syntax support class

ONLY_FULL_GROUP_BY

For GROUP BY aggregation operations, if the columns in the SELECT, HAVING, or ORDER BY clause do not appear in the GROUP BY, then the SQL is illegal. It is understandable because there is a contradiction in the display that is not found in the group by list.

It is enabled by default in 5.7, so you should pay attention to the process of upgrading from 5.6 to 5.7:

1 Expression # 1 of SELECT list is not in GROUP BY

2 clause and contains nonaggregated column

3 '1066 export.ebayroomorderroomitems.TransactionID' which

4 is not functionally dependent on columns in GROUP BY

5 clause; this is incompatible with sql_mode=only_full_group_by

ANSI_QUOTES

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

After setting it, update t set F1 = ""... will report a syntax error such as Unknown column''in 'field list.

PIPES_AS_CONCAT

Treat | | as the concatenation operator of the string rather than the OR operator, which is the same as the Oracle database and similar to the string concatenation function CONCAT ().

NO_TABLE_OPTIONS

MySQL-specific syntax parts, such as ENGINE, are not output when using SHOW CREATE TABLE, which needs to be considered when using mysqldump to migrate across DB types.

NO_AUTO_CREATE_USER

Literally, users are not created automatically. We are used to using GRANT when authorizing MySQL users. ON... TO dbuser is on the way to create users. When this option is set, it is similar to oracle operation

Users must be established before authorization. 5.7.7 also defaulted at the beginning.

Data check class

NO_ZERO_DATE

It is considered that the date '0000-0000' is illegal, which is related to whether to set the later strict mode.

1. If strict mode is set, NO_ZERO_DATE is naturally satisfied. But if it is INSERT IGNORE or UPDATE IGNORE,'0000-00-00', it still allows and only shows warning.

two。 If NO_ZERO_DATE is set in non-strict mode, the effect is the same as above, '0000-00-00' allows but displays warning; if NO_ZERO_DATE,no warning is not set, as a perfectly legal value.

The 3.NO_ZERO_IN_DATE situation is similar to the above, except that it controls whether the date and day can be 0, that is, whether 2010-01-00 is legal.

NO_ENGINE_SUBSTITUTION

What to do if the required storage engine is disabled or uncompiled when you specify ENGINE using ALTER TABLE or CREATE TABLE. When NO_ENGINE_SUBSTITUTION is enabled, an error is thrown directly; when this value is not set, CREATE is replaced by the default storage engine, ATLER does not change, and a warning.

STRICT_TRANS_TABLES

Set it to enable strict mode.

Note that STRICT_TRANS_TABLES is not a combination of several policies. It refers to how to deal with low or invalid values in INSERT and UPDATE:

1. As mentioned earlier, passing''to int is illegal in strict mode, and becomes 0 if non-strict mode is enabled, resulting in a warning.

2.Out Of Range, to insert the maximum boundary value

3.A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition

Sql_mode generally pays little attention to it and does not start or stop the above items until it encounters a real problem. The sql_mode we often set is ANSI, STRICT_TRANS_TABLES, TRADITIONAL,ansi, and traditional are several combinations of the above.

ANSI: change syntax and behavior to make it more compliant with standard SQL

Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE

TRADITIONAL: more like a traditional SQL database system, the schema is simply described as "giving an error rather than a warning" when an incorrect value is inserted into a column.

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

ORACLE: equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER

No matter what kind of mode, the generation of error means that the execution of a single sql fails, and for tables that support transactions, it causes the current transaction to be rolled back; but if it is not executed in a transaction, or the storage engine table that does not support transactions, it may result in data inconsistency. MySQL believes that data inconsistencies are more serious than direct error termination. So STRICT_TRANS_TABLES still allows writes to continue for non-transactional tables as much as possible, such as giving a "most reasonable" default value or truncation. For STRICT_ALL_TABLES, if it is a single update, it will not be affected, but if more than one is updated, the first one succeeds, and then a partial update occurs if it fails.

Thank you for reading this article carefully. I hope the article "sample Analysis of commonly used values of sql_mode in MySQL5.6" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.