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:
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
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.
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
In strict mode, zero date and month are not allowed
Setting this value, MySQL database does not allow inserting zero date, which will throw an error instead of a warning.
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
Grant is not allowed to create users with empty password
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
Treat “|” as a string join operator rather than an or operator, which is the same as Oracle database and is similar to concat
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
Or directly use the following methods:
Query the SQL of the current database_ MODE
The results are as follows
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