Teach you a trick to fix the sql_mode setting in mysql

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.cnf

rrree

Save and exit, restart the server, and it will take effect permanently

The common values ​​of sql_mode are as follows:

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!