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

This article mainly introduces MySQL sql_mode reasonable setting example analysis, has a certain reference value, interested friends can refer to, I hope you read this article after a lot of gains, the following let Xiaobian take you to understand.

MySQL SQL_mode reasonable settings

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

1. SQL models are commonly used to solve the following types of problems:

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

(2)By setting sql model to loose mode, it ensures that most sql conforms to standard sql syntax, so that when the application is migrated between different databases, it does not need to make major modifications to business sql.

(3)Setting SQL Mode makes it easier to migrate data from MySQL to the target database before migrating data between different databases.

2. Common values for sql_mode are as follows:

1. ONLY_FULL_GROUP_BY:

For GROUP BY aggregate 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.

2. NO_AUTO_VALUE_ON_ZERO:

This value affects insertions from growing columns. By default, inserting 0 or NULL generates the next self-increasing value. This option is useful if the user wants to insert a value of 0 and the column is self-growing.

3. STRICT_TRANS_TABLES: strict mode

In this mode, if a value cannot be inserted into a transactional table, the current operation is aborted and no restrictions are placed on non-transactional tables.

4. NO_ZERO_IN_DATE:

Zero dates and months are not allowed in strict mode.

5. NO_ZERO_DATE:

Set this value,mysql database does not allow insertion of zero date, inserting zero date throws error instead of warning.

6. ERROR_FOR_DIVISION_BY_ZERO:

During INSERT or UPDATE, if the data is divided by zero, an error is generated instead of a warning. MySQL returns NULL when the data is divided by zero if the pattern is not given

7. NO_AUTO_CREATE_USER:

Disable GRANT from creating users with empty passwords

8. NO_ENGINE_SUBSTITUTION:

If the required storage engine is disabled or not compiled, an error is thrown. When this value is not set, replace it with the default storage engine and throw an exception

9. PIPES_AS_CONCAT:

will "||"A concatenation operator treated as a string rather than an OR operator is the same as Oracle Database and similar to the string concatenation function Concat

10. ANSI_QUOTES:

When ANSI_QUOTES is enabled, strings cannot be quoted in double quotes because they are interpreted as identifiers

ORACLE 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, you can set sql_mode to mysql as follows:

Add the following configuration to 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 for loose and strict modes:

Note: MySQL 5.6 and MySQL 5.7 default sql_mode parameters are different.

5.6 The mode of is NO_ENGINE_SUBSTITUTION, which actually indicates a null value, which is equivalent to no mode setting, which can be understood as loose mode.

5.7 The mode is STRICT_TRANS_TABLES, which is strict mode.

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

For example: When I create a table, there is a field in the table called name. When setting the field type for name, char(10). If I insert data, the length of a piece of data corresponding to the name field exceeds 10, such as '1234567890 abc', which exceeds the set field length of 10, then no error will be reported, and the first ten characters will be stored. That is to say, your data will be stored as' 1234567890'.'abc' doesn't exist, but we know that the data we give is wrong because it exceeds the field length, but there is no error, and mysql processes and accepts it on its own, which is the effect of loose mode.

In fact, in the development, testing, production and other environments, we should adopt strict mode, this error occurs, should report an error, so MySQL 5.7 version will sql_mode default value changed to strict mode, and even if we use MySQL 5.6, should also change it to strict mode.

In addition, MySQL and so on these databases, are trying to put all the operations on the data themselves down, including data verification, in fact, many times, we should be in their own development of the project program level to do these checks, although writing the project when the trouble of some steps, but after doing so, we in the database migration or in the project migration, it will be a lot more convenient. In addition to data validation, mysql will slowly discover that it can do many things, and it will cover many things you do in your program.

[3] Possible problems after changing to strict mode:

If NO_ZERO_DATE is included in the setup pattern, MySQL does not allow zero date insertion, and zero date insertion throws an error instead of a warning.

For example: a table column containing the field TIMESTAMP (if not declared NULL or displaying a DEFAULT clause) will automatically assign DEFAULT '0000-00 -00 00:00:00'(zero timestamp), or the table day column for this test allows zero date insertion by default '0000-00-00' COMMENT 'date'; these are obviously errors that do not satisfy NO_ZERO_DATE in sql_mode.

4. Mode setting and modification (to solve the above problems as an example):

Method 1:

First execute select @@sql_mode, copy the queried value and delete NO_ZERO_IN_DATE,NO_ZERO_DATE, and 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 only works in the current session. Closing the current session does not work.

Method 2:

First execute select @@global.sql_mode, copy the query value and delete NO_ZERO_IN_DATE,NO_ZERO_DATE, and then execute set global sql_mode = 'modified value'.

Note: This method works in the current service, and will not work after re-MySQL service.

Method 3:

1. Add sql_mode = to mysql installation directory or my.cnf file (my.ini file for windows).

"ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Add my.ini as follows:

[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 administrator, execute net stop mysql , and then execute net start mysql.

Note: This method is permanent. Of course, it is forbidden to restart MySQL service in production environment, so use 'mode 2 + mode 3' to solve online problems, so even if MySQL service is restarted one day, it will take effect permanently.

Thank you for reading this article carefully. I hope that Xiaobian's "Sample Analysis of Reasonable Setting of SQL_mode in MySQL" will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant 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.