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

This article mainly explains "how to set MySQL SQL mode". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's ideas to study and learn "how to set MySQL SQL mode".

MySQL servers can operate in different SQL modes, and depending on the value of the sql_mode system variable, these different SQL modes can be applied to different clients. DBA can set the global SQL mode to match server operational requirements, and each application can set its session SQL mode to meet its requirements.

The SQL schema affects the SQL syntax and data validation checks supported by MySQL. This makes it easier to use MySQL with other databases in different environments.

When using the InnoDB table, consider using the innodb_strict_mode system variable, which enables additional error checking on the InnoDB table.

Set SQL mode

The default SQL schemas in MySQL 5.7include: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION. ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES are added in MySQL 5.7.5. NO_AUTO_CREATE_USER was added in MySQL 5.7.7. ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_IN_DATE and NO_ZERO_DATE are added in MySQL 5.7.8.

To set the SQL mode when the server starts, you can use the-- sql-mode= "modes" option on the command line or the sql-mode= "modes" option in an option file such as my.cnf in Unix or my.ini file on Windows. Modes is a list of different modes separated by commas. To explicitly clear the SQL mode, you can use the-- sql-mode= "" option to set the SQL mode to an empty string on the command line, or use sql-mode= "" in the options file.

MySQL installer can configure SQL mode during installation. For example, mysql_install_db will create a default options file named my.cnf in the basic installation directory. This file contains records that set the SQL mode.

If the SQL mode is different from the default SQL mode or the SQL mode you expect, you can check the options file that the server reads at startup.

To change the SQL mode at run time, you can use the set statement to set the global or session-level sql_mode system variable:

Set global sql_mode='modes'

Set session sql_mode='modes'

Setting the global variable requires super permission and affects all connected client operations. Setting the session variable affects only the current client. Each client can change the sql_ mode of its session at any time to change the SQL mode.

To determine the current global or session-level sql_ mode value, execute the following command:

Mysql > SELECT @ @ GLOBAL.sql_mode +- -+ | @ @ GLOBAL.sql_mode | +-- -+ | ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION | +- -+ 1 row in set (0.00 sec) mysql > SELECT @ @ SESSION.sql_mode +- -+ | @ @ SESSION.sql_mode | +-- -+ | ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION | +- -+ 1 row in set (0.00 sec)

Note that changing the server's SQL schema after creating a partitioned table and inserting data can significantly change the behavior of the table and may result in loss or corruption of the data. Therefore, it is strongly recommended that you do not modify the SQL schema after creating a user-defined partition table.

Different SQL schemas between master and slave servers can also cause problems when replicating partitioned tables. So the best result is to use the same SQL mode on the master and slave servers.

The most important SQL mode

Probably the most important sql_ mode values in MySQL are these:

.ANSI

This SQL pattern changes syntax and behavior to bring it closer to standard SQL. It is a specific list of combined patterns.

.STRICT _ TRANS_TABLES

If a value cannot be inserted into a transaction table in the specified manner, the statement is terminated. For non-transactional tables, if this value terminates a single-row record statement or when a multi-row record statement appears first. The default SQL mode starting with MySQL 5.7.5 includes STRICT_TRANS_TABLES.

.TRADITIONAL

Using MySQL behaves like a traditional SQL database system. To put it simply, this mode throws an error instead of a warning when an incorrect value is inserted into a column. It is one of the specific combination patterns. Insert or update terminates as soon as an error is found. If you are using a non-transactional storage engine, this may not be what you want, because data changes made before the error may not be rolled back, resulting in a "partially completed" update.

When it comes to "strict mode" strict mode, it means that one or both of STRICT_TRANS_TABLES or STRICT_ALL_TABLES are enabled.

Complete list of SQL schemas

The following is a list of all supported SQL modes:

.ALLOW _ INVALID_DATES

Do not perform a full check on the date. Checks are performed only on months ranging from 1 to 12 and dates from 1 to 31. This is very convenient for Web applications, where you can get the year, month, and day in three different fields, and you want to store exactly what the user inserts (without performing date checking). This SQL mode applies to date and datetime columns. It does not apply to the timestamp column because it always requests a legal date value.

This SQL model requires that monthly and daily values are legal, and the range cannot exceed 1 to 12 and 1 to 31, respectively. When strict mode is disabled, an invalid date value such as' 2014-04-31'is converted to '0000-00-00' and a warning is generated. When strict mode is used, an invalid date value generates an error. To allow such a date value, ALLOW_INVALID_DATES mode is enabled.

.ANSI _ QUOTES

Use "as an identity reference character (such as a `reference character) rather than a string reference character. When using this SQL mode, you can still use `as the reference identifier, and when ANSI_QUOTES is used, you cannot use double quotation marks to reference the text string because it is parsed as an identifier.

.ERROR _ FOR_DIVISION_BY_ZERO

The ERROR_FOR_DIVISION_BY_ZERO mode affects the processing of dividing by zero, which includes MOD (NP0). For data modification operations (insert,update), its impact also depends on whether strict SQL mode is enabled.

-if this mode is not enabled, dividing by zero inserts a null value without warning.

-if this mode is enabled, dividing by zero inserts a null value and generates a warning.

-if this mode and strict SQL mode are enabled, divide the heap to generate an error unless IGNORE is also specified. For insert ignore and update ignore, dividing by zero inserts a null value and generates a warning.

For select statements, dividing by zero returns a null value. Enabling ERROR_FOR_DIVISION_BY_ZERO regardless of whether strict SQL mode is enabled or not causes a warning to be generated.

In MySQL 5.7.4, ERROR_FOR_DIVISION_BY_ZERO is discarded, and ERROR_FOR_DIVISION_BY_ZERO does nothing when explicitly named in MySQL 5.7.4 through 5.7.7. Instead, its impact is included in the strict SQL schema. In MySQL 5.7.8 and later, ERROR_FOR_DIVISION_BY_ZERO has an impact when explicitly named and is not included in the strict SQL schema, just like previous versions of MySQL5.7.4. By default, however, it should be used in conjunction with it when strict mode is enabled. This warning appears if ERROR_FOR_DIVISION_BY_ZERO is enabled without strict mode or when strict mode is enabled without ERROR_FOR_DIVISION_BY_ZERO.

Because ERROR_FOR_DIVISION_BY_ZERO is discarded, it will be removed in future releases as a separate schema name and its impact is included in the strict SQL schema.

.HIGH _ NOT_PRECEDENCE

The priority of the NOT operation is the same as the NOT a between b and c will be parsed to NOT (a between b and c). In some older versions of MySQL, expressions are parsed to (NOT a) between b and c. Older versions of the higher priority behavior can be obtained by enabling HIGH_NOT_PRECEDENCE mode.

Mysql > SET sql_mode =''; Query OK, 0 rows affected, 1 warning (0 sec) mysql > SELECT NOT 1 BETWEEN-5 AND 5 +-+ | NOT 1 BETWEEN-5 AND 5 | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql > SET sql_mode = 'HIGH_NOT_PRECEDENCE' Query OK, 0 rows affected (0.00 sec) mysql > SELECT NOT 1 BETWEEN-5 AND 5 NOT 1 BETWEEN-5 AND 5 | +-+ | 1 | +-+ 1 row in set (0.00 sec)

.IGNORE _ SPACE

Spaces are allowed between the function name and the (symbol. This causes the built-in function name to be treated as a reserved keyword. Therefore, the identifier must have quotation marks the same as the function name. For example, because there is a count () function, using count as the table name in the following statement will cause an error:

Mysql > CREATE TABLE count (I INT); Query OK, 0 rows affected (0.13 sec) mysql > drop table count cascade;Query OK, 0 rows affected (0.09 sec) mysql > SET sql_mode = 'IGNORE_SPACE';Query OK, 0 rows affected (0.00 sec) mysql > CREATE TABLE count (I INT); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near' count (I INT)'at line 1mysql > create table `count` (I INT) Query OK, 0 rows affected (0.16 sec)

The IGNORE_SPACE pattern applies to built-in functions, not user-defined functions or stored procedures. It always allows spaces after a user-defined function or stored procedure name, regardless of whether IGNORE_SAPCE mode is enabled or not.

.NO _ AUTO_CREATE_USER

Unless authentication information is specified, the GRANT statement is prevented from automatically creating new user accounts. This statement must use identified by to specify a non-empty password or identified with to use an authentication plug-in.

It is best to use create user to create a MySQL account, and then use the Grant statement. NO_AUTO_CREATE_USER has been discarded and the default SQL schema contains the NO_AUTO_CREATE_USER schema. Changing sql_mode to NO_AUTO_CREATE_USER mode generates a warning that its impact will always be enabled except that specifying sql_mode as DEFAULT.NO_AUTO_CREATE_USER will be removed in future releases.

Previously, before NO_AUTO_CREATE_USER was discarded, one reason it was not enabled was that it was not a secure copy. It can now be enabled and use create user if not exists,drop user if exists and alter user if exists instead of grant statements to perform secure replication management. These statements enable secure replication when the slave server has a different authorization from the master server.

.NO _ AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects the processing of AUTO_INCREMENT columns. Normally, the next sequence value is generated for the column by inserting NULL or 0 into the visiting column. NO_AUTO_VALUE_ON_ZERO suppresses the behavior of 0, so the next sequence value is generated only when NULL is inserted. This SQL schema may be useful if 0 has been stored in the AUTO_INCREMENT column of the table. (storing 0 is not recommended) for example, if you use mysqldump to dump the table and then load it, MySQL normally generates a new sequence value when it encounters a value of 0, so the contents of the table are different from those of the dump. Enable NO_AUTO_VALUE_ON_ZERO to resolve this problem before loading the dump file. Mysqldump now automatically includes a statement in its output to enable NO_AUTO_VALUE_ON_ZERO to avoid this problem.

.NO _ BACKSLASH_ESCAPES

Disables the use of the backslash character (\) as an escape character in a string. When this mode is enabled, the backslash becomes the same normal character as other characters.

.NO _ DIR_IN_CREATE

All index catalog and data catalog instructions are ignored when creating a table. This option is useful from the replication server.

.NO _ ENGINE_SUBSTITUTION

Controls the automatic replacement of the default storage engine when a statement such as create table or alter table specifies that a storage engine has been disabled or not compiled. NO_ENGINE_SUBSTITUTION is included in the default SQL schema. Because the storage engine can be attached at run time, it cannot be treated in the same way:

When NO_ENGINE_SUBSTITUTION is disabled, the default storage engine for create table is used and a warning appears if the expected storage engine is not available. For alter table, a warning appears and the table cannot be modified.

When NO_ENGINE_SUBSTITUTION is enabled, a warning appears if the expected storage engine is not available and the table is not created or modified.

.NO _ FIELD_OPTIONS

The specific MySQL column option is not printed in the show create table output. This SQL mode is used by mysqldump in portable mode.

.NO _ KEY_OPTIONS

Specific MySQL index options are not printed in show create table output. This SQL mode is used by mysqldump in portable mode.

.NO _ TABLE_OPTIONS

Specific MySQL table options (such as ENGINE) are not printed in the show create table output. This SQL mode is used by mysqldump in portable mode.

.NO _ UNSIGNED_SUBTRACTION

Two integers are subtracted, here is a type of UNSIGNED that produces an unsigned result by default. If the result is negative, an error will occur:

Mysql > SET sql_mode =''; Query OK, 0 rows affected, 1 warning (0 sec) mysql > SELECT CAST (0 AS UNSIGNED)-1 politics error 1690 (22003): BIGINT UNSIGNED value is out of range in'(cast (0 as unsigned)-1)'

If NO_UNSIGNED_SUBTRACTION mode is enabled, the result will be negative:

Mysql > SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';Query OK, 0 rows affected (0 sec) mysql > SELECT CAST (0 AS UNSIGNED)-1 +-+ | CAST (0 AS UNSIGNED)-1 | +-+ |-1 | +-+ 1 row in set (0.00 sec)

If you update an unsigned integer column with the result of such an operation, the result is cropped to the maximum value of the column type, or to 0 if no_unsigned_subtract is enabled. If strict SQL mode is enabled, an error occurs and the column remains the same.

When no_unsigned_subtraction is enabled, even if any operation data is unsigned, the result is signed. For example, compare the C2 column in table T1 with the c2 column in table T2:

Mysql > SET sql_mode='';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > CREATE TABLE test (C1 BIGINT UNSIGNED NOT NULL); Query OK, 0 rows affected (0.20 sec) mysql > CREATE TABLE T1 SELECT C1-1 AS c2 FROM test;Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc T1 +-+ | Field | Type | Null | Key | Default | Extra | +-- -+ | c2 | bigint (21) unsigned | NO | | 0 | | +-+-+ 1 row in set (0.00 sec) mysql > SET sql_mode='NO_UNSIGNED_SUBTRACTION' Query OK, 0 rows affected (0.00 sec) mysql > CREATE TABLE T2 SELECT C1-1 AS c2 FROM test;Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc T2 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | C2 | bigint (21) | NO | | 0 | | +-+-+ 1 row in set (0.00 sec)

This means that bigint unsigned is not 100% usable in all contexts.

.NO _ ZERO_DATE

The NO_ZERO_DATE mode affects whether the server allows' 0000-00'as a valid date. Its impact also depends on whether strict SQL mode is enabled.

-if the mode is not enabled, '0000-00' is allowed and insertion does not generate a warning.

-if the mode is enabled, '0000-00-00' is allowed and insertion generates a warning.

-if mode and strict SQL mode are enabled, '0000-00' is not allowed and insertion will cause an error unless the IGNORE option is specified. For insert ignore and update ignore, '0000-00-00' is allowed and insertion generates a warning.

In MySQL 5.7.4, NO_ZERO_DATE is discarded. In MySQL 5.7.4 through 5.7.7, NO_ZERO_DATE does not work when explicitly specified. Instead, its impact has been included in the strict SQL schema. In MySQL 5.7.8 and later, it works when the NO_ZERO_DATE mode is explicitly specified and it is not part of the strict SQL mode, just as it worked before the MySQL5.7.4 version. By default, however, it should be used in conjunction with the strict SQL mode. If NO_ZERO_DATE is enabled without strict SQL mode, there is a warning or vice versa.

Because NO_ZERO_DATE has been discarded, it will be removed as a separate schema name in future releases and its impact will be included in the strict SQL schema.

.NO _ ZERO_IN_DATE

The NO_ZERO_IN_DATE mode affects whether the server allows the year portion of the date to be non-zero but the month or day portion to be zero. (this pattern affects dates such as' 2010-00-01'or '2010-01-00' rather than '0000-00-00'. To control whether the server allows' 0000-00-00', use the NO_ZERO_DATE mode). The impact of the NO_ZERO_IN_DATE mode also depends on whether the strict SQL mode is enabled.

-if this mode is not enabled, a partial discount date of 0 is allowed and the insertion does not generate a warning.

-if this mode is enabled, a date value with a part of 0 will be inserted in the format '0000-00' and a warning will be generated.

-if this mode and strict SQL mode are enabled, dates with parts of 0 are not allowed and insertion will generate an error unless you specify ignore. For insert ignore and update ignore, dates with parts of 0 will be inserted in the format '0000-00-00' and a warning will be generated.

In MySQL 5.7.4, NO_ZERO_IN_DATE is discarded. In MySQL 5.7.4 through 5.7.7, NO_ZERO_DATE does not work when explicitly specified. Instead, its impact has been included in the strict SQL schema. In MySQL 5.7.8 and later, it works when the NO_ZERO_IN_DATE mode is explicitly specified and it is not part of the strict SQL mode, just as it worked before the MySQL5.7.4 version. By default, however, it should be used in conjunction with the strict SQL mode. If NO_ZERO_IN_DATE is enabled without strict SQL mode, there is a warning or vice versa.

Because NO_ZERO_IN_DATE has been discarded, it will be removed as a separate schema name in future releases and its impact will be included in the strict SQL schema.

.ONLY _ FULL_GROUP_BY

Reject queries that select list, HAVING condition, or ORDER BY list refer to nonaggregate columns, which are neither named in the GROUP BY clause nor dependent on the GROUP BY column (determined uniquely by the GROUP BY column).

Starting with MySQL 5.7.5, the default SQL schema contains the ONLY_FULL_GROUP_BY schema. Prior to 5.7.5, MySQL did not detect functional dependencies and ONLY_FULL_GROUP_BY mode was not enabled by default.

MySQL extends the standard SQL to allow alias expressions in the select list to be referenced in the having clause. Prior to MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY mode disabled this extension, so the having clause was required to be written as a non-alias expression. Starting with MySQL5.7.5, this restriction is removed so that the having clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY mode is enabled or not.

.PAD _ CAHR_TO_FULL_LENGTH

By default, trailing spaces are cropped from the CHAR column values when retrieving. If PAD_CHAR_TO_FULL_LENGTH is enabled, cropping does not occur and is populated to its full length when retrieving the CHAR column value. This mode should not be used for varchar columns. Leave trailing spaces for it when retrieving.

Mysql > CREATE TABLE T1 (C1 CHAR (10)); Query OK, 0 rows affected (0.12 sec) mysql > INSERT INTO T1 (C1) VALUES ('xy'); Query OK, 1 row affected (0.03 sec) mysql > SET sql_mode =''; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > SELECT C1, CHAR_LENGTH (C1) FROM T1 +-+-+ | C1 | CHAR_LENGTH (C1) | +-+-+ | xy | 2 | +-+-+ 1 row in set (0.00 sec) mysql > SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH' Query OK, 0 rows affected (0.00 sec) mysql > SELECT C1, CHAR_LENGTH (C1) FROM T1 +-+-+ | C1 | CHAR_LENGTH (C1) | +-+-+ | xy | 10 | +-+-+ 1 row in set (0.00 sec)

.PIPES _ AS_CONCAT

Use | as a string concatenation operator (like concat ()) rather than as a synonym for OR.

.REAL _ AS_FLOAT

Use REAL as a synonym for FLOAT. By default, MySQL uses REAL as a synonym for DOUBLE.

.STRICT _ ALL_TABLES

Enable strict SQL mode for all storage engines. Invalid data will be rejected. From MySQL 5.7.4 to 5.7.7, the ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE and NO_ZERO_IN_DATE patterns are included in the ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE and NO_ZERO_IN_DATE patterns.

.STRICT _ TRANS_TABLES

Enable strict SQL mode for transactional storage engines and use non-transactional storage engines where possible. From MySQL 5. 7. 4 to 5. 7. 7, the ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE and NO_ZERO_IN_DATE patterns are included in the STRICTROTRANSSTABLES model.

Thank you for your reading, the above is the content of "how to set MySQL SQL mode", after the study of this article, I believe you have a deeper understanding of how to set MySQL SQL mode, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.