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. This value must be set to strict mode in the production environment, so the databases in the development and test environments must also be set, so that problems can be discovered during the development and testing phase.
1, execute SQL to view
select @@session.sql_mode;
global level: view
select @@global.sql_mode;
2, modify
set @@session.sql_mode='xx_mode' set session sql_mode='xx_mode'
global level: modify
set global sql_mode='xx_mode'; set @@global.sql_mode='xx_mode';
session can be omitted. The default session is only valid for the current session.
If you modify it globally, you need advanced permissions. It will only take effect for the next connection and will not affect the current session. It will be invalid after MySQL restarts because it will Re-read the corresponding value in the configuration file. If it needs to be permanent, you need to modify the value in the configuration file.
vi /etc/my.cnfrrree
Save and exit, restart the server, and it will take effect permanently
ONLY_FULL_GROUP_BY
For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is illegal because the column is not in the GROUP BY clause
NO_AUTO_VALUE_ON_ZERO
This value affects the insertion of auto-growing columns. Under the default settings, inserting 0 or NULL represents generating the next auto-increasing value. This option is useful if the user wants to insert a value of 0 and the column is auto-increasing.
STRICT_TRANS_TABLES
In this mode, if a value cannot be inserted into a transaction table, the current operation will be interrupted and there will be no restrictions on non-transaction tables
NO_ZERO_IN_DATE
In strict mode, the date or month is not allowed to be zero. As long as the month or day of the date contains a 0 value, an error will be reported, but '0000-00-00 'Except
NO_ZERO_DATE
Setting this value, mysql database does not allow the insertion of zero dates, and inserting zero dates will throw an error instead of a warning. Any one of the year, month and day that is not 0 meets the requirements. Only '0000-00-00' will report an error
ERROR_FOR_pISION_BY_ZERO
During the INSERT or UPDATE process, if Dividing data by zero produces an error instead of a warning. If the mode is not given, MySQL returns NULL when the data is divided by zero
update table set num = 5 / 0; An error will be reported after setting the mode. If not set, the modification will be successful and the value of num will be null
NO_AUTO_CREATE_USER
Prohibit GRANT from creating users with empty passwords
NO_ENGINE_SUBSTITUTION
Storage engine if required 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 connection operator Instead of the OR operator, this is the same as the Oracle database, and is similar to the string splicing function Concat
ANSI_QUOTES
After enabling ANSI_QUOTES, you cannot use double Quotation marks to quote the string because it is interpreted as the identifier
Related recommendations: "mysql tutorial"
The above is the detailed content of Teach you a trick to fix the sql_mode setting in mysql. For more information, please follow other related articles on the PHP Chinese website!