Detailed explanation on reasonable settings of MySQL sql_mode

Reasonable setting of MySQL sql_mode

sql_mode is a variable that is easily overlooked. The default value is null. Under this setting, some illegal operations can be allowed, such as allowing the insertion of some illegal data. In the production environment, this value must be set to strict mode, so the database of the development and test environment must also be set so that problems can be discovered during the development and testing phase.

1. SQL model is often used to solve the following types of problems:

(1) By setting the sql mode, you can complete data verification with different degrees of strictness, effectively ensuring data readiness.

(2) By setting the SQL model to loose mode, we can ensure that most SQL statements conform to standard SQL syntax. This way, when migrating applications between different databases, we do not need to make major changes to the business SQL statements.

(3) Before migrating data between different databases, you can set the SQL Mode to make it easier to migrate data on MySQL to the target database.

2. Common values ​​of sql_mode are as follows:

1. ONLY_FULL_GROUP_BY:

For GROUP BY aggregation operations, if the column in SELECT does not appear in GROUP BY, then this SQL is illegal because the column is not in the GROUP BY clause.

2. NO_AUTO_VALUE_ON_ZERO:

This value affects inserts into auto-increment columns. By default, inserting 0 or NULL means generating the next auto-increment value. This option is useful if the user wants to insert a value of 0 and the column is auto-incrementing.

3. STRICT_TRANS_TABLES: Strict mode

In this mode, if a value cannot be inserted into a transactional table, the current operation is interrupted, and there is no restriction on non-transactional tables.

4. NO_ZERO_IN_DATE:

In strict mode, day and month values ​​of zero are not allowed.

5. NO_ZERO_DATE:

By setting this value, the MySQL database does not allow the insertion of zero date, and inserting zero date will throw an error instead of a warning.

6. ERROR_FOR_DIVISION_BY_ZERO:

During an INSERT or UPDATE, if data is divided by zero, an error is generated rather than a warning. If this mode is not given, MySQL returns NULL if the value is divided by zero.

7. NO_AUTO_CREATE_USER:

Prevent GRANT from creating users with empty passwords

8. NO_ENGINE_SUBSTITUTION:

If the required storage engine is disabled or not compiled in, an error is thrown. When this value is not set, the default storage engine is used instead and an exception is thrown.

9. PIPES_AS_CONCAT:

Treat "||" as a string concatenation operator rather than an OR operator, which is the same as Oracle database and is similar to the string concatenation function Concat.

10. ANSI_QUOTES:

When ANSI_QUOTES is enabled, you cannot use double quotes to quote strings, because they are interpreted as identifiers.

ORACLE's sql_mode settings are equivalent to: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

If you use MySQL, in order to continue to keep everyone's habit of using Oracle, you can set MySQL's sql_mode as follows:

Add the following configuration in my.cnf

[mysqld]
sql_mode = 'ONLY_FULL_GROUP_BY, NO_AUTO_VALUE_ON_ZERO, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,PIPES_AS_CONCAT,ANSI_QUOTES'

3. Notes on loose mode and strict mode:

【1】Note: The default sql_mode parameters of MySQL 5.6 and MySQL 5.7 are different.
The mode of 5.6 is NO_ENGINE_SUBSTITUTION, which actually represents a null value, equivalent to no mode setting, which can be understood as a loose mode.
The mode of 5.7 is STRICT_TRANS_TABLES, which is strict mode.

[2] If the loose mode is set, then when we insert data, even if we give incorrect data, it may be accepted and no error will be reported.

For example: when I create a table, there is a field called name in the table, and the field type set for name is char(10). If I insert data, and the length of a piece of data corresponding to the name field exceeds 10, such as '1234567890abc', which exceeds the set field length of 10, then no error will be reported, and the first ten characters will be stored. In other words, your data will be stored as '1234567890', and 'abc' will be gone. However, we know that the data we gave is wrong because it exceeds the field length, but no error is reported, and MySQL handles and accepts it by itself. This is the effect of loose mode.

In fact, in development, testing, production and other environments, we should use strict mode. If this error occurs, an error should be reported. Therefore, MySQL 5.7 version changes the default value of sql_mode to strict mode, and even if we use MySQL 5.6, we should change it to strict mode by ourselves.

In addition, databases such as MySQL want to take over all data operations themselves, including data verification. In fact, many times, we should do these verifications at the program level of the project we develop. Although it is troublesome to write some steps when writing the project, after doing so, it will be much more convenient when we migrate the database or the project. In addition to data verification, you will find that MySQL can do many more things as you learn it, covering many of the things you do in your program.

【3】Problems that may occur after switching to strict mode:

If the setting mode contains NO_ZERO_DATE, the MySQL database does not allow the insertion of zero date, and inserting zero date will throw an error instead of a warning.
For example, a TIMESTAMP column in a table (if not declared as NULL or with a DEFAULT clause) will automatically be assigned a DEFAULT of '0000-00-00 00:00:00' (zero timestamp), or the day column in the table of this test allows the insertion of zero date '0000-00-00' COMMENT 'date' by default; these obviously do not satisfy the NO_ZERO_DATE in sql_mode and report an error.

4. Mode setting and modification (taking solving the above problem as an example):

【Method 1】：

First execute select @@sql_mode, copy the queried value and delete NO_ZERO_IN_DATE, NO_ZERO_DATE, then execute set sql_mode = 'modified value' or set session sql_mode = 'modified value';

For example: set session sql_mode='STRICT_TRANS_TABLES'; change to strict mode

Note: This method is only effective in the current session and will not take effect if the current session is closed.

【Method 2】：
First execute select @@global.sql_mode, copy the queried value and delete NO_ZERO_IN_DATE and NO_ZERO_DATE, then execute set global sql_mode = 'modified value'.

Note: This method is effective in the current service and will become invalid after restarting the MySQL service

【Method 3】：
1. In the MySQL installation directory, or in the my.cnf file (my.ini file on Windows), add sql_mode =
"ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Add the following to my.ini:

[mysqld]

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2. Then restart mysql. Run CMD as an administrator, execute net stop mysql, and then execute net start mysql.

Note: This method is permanent. Of course, restarting the MySQL service is prohibited in the production environment, so use "Method 2 + Method 3" to solve the online problem. Then, even if the MySQL service is really restarted one day, it will be permanent.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.