SQL of MySQL_ Reasonable setting of mode

Time:2021-1-16

SQL of MySQL_ Reasonable setting of mode

sql_ Mode is a variable that is easily ignored. The default value is null. In this setting, some illegal operations can be allowed, such as the insertion of illegal data. In the production environment, this value must be set to strict mode, so the database in the development and test environment must also be set, so that problems can be found in the development and test phase
  
sql_ The common values of mode are as follows: 
ONLY_FULL_GROUP_BY:

For group by aggregate operation, if the column in select does not appear in group by, then the SQL is illegal, because the column is not in group by clause

NO_AUTO_VALUE_ON_ZERO:

This value affects the insertion of self growing columns. By default, insert 0 or null to generate the next self growing value. This option is useful if the user wants to insert a value of 0 and the column is self growing.

STRICT_TRANS_TABLES:

In this mode, if a value cannot be inserted into a transaction table, the current operation will be interrupted and the non transaction table will not be restricted

NO_ZERO_IN_DATE:

In strict mode, zero date and month are not allowed

NO_ZERO_DATE:

Setting this value, MySQL database does not allow inserting zero date, which will throw an error instead of a warning.

ERROR_FOR_DIVISION_BY_ZERO:

During insert or update, if the data is divided by zero, an error is generated instead of a warning. If the mode is not given, MySQL returns null when the data is divided by zero

NO_AUTO_CREATE_USER:

Grant is not allowed to create users with empty password

NO_ENGINE_SUBSTITUTION:

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

PIPES_AS_CONCAT:

Treat “|” as a string join operator rather than an or operator, which is the same as Oracle database and is similar to concat

ANSI_QUOTES:

Enable ANSI_ After quotes, you cannot use double quotes to refer to a string because it is interpreted as an identifier

Oracle SQL_ Mode setting: 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 the habit of using Oracle, you can use mysql_ The mode is set as follows

stay my.cnf Add the following configuration
[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,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES’

Or directly use the following methods:

Query the SQL of the current database_ MODE 
select @@sql_mode 
The results are as follows

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 

You can see No_ ZERO_ IN_ DATE,NO_ ZERO_ Date is the reason that date cannot be all 0, so the no in the mode is changed_ ZERO_ IN_ DATE,NO_ ZERO_ Just remove the date.  
SET [SESSION|GLOBAL] sql_mode=’modes’ 
Examples are as follows:

SET GLOBAL sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, 
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Where: session only takes effect in the current session, and global takes effect globally.

This work adoptsCC agreementReprint must indicate the author and the link of this article