Detailed explanation of MySQL sql_mode query and setting

1. Execute SQL to view

select @@session.sql_mode;

Global level: View

select @@global.sql_mode;

2. Modification

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 valid only for the current session. If you want to modify it globally, you need advanced permissions and it will only take effect on the next connection. It does not affect the current session and will become invalid after MySQL is restarted, because MySQL will reread the corresponding value in the configuration file when it is restarted. If you need to make it permanent, you need to modify the value in the configuration file.

vi /etc/my.cnf

[mysqld]
sql-mode = "xx_mode"

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

Common values ​​for sql_mode are as follows:

ONLY_FULL_GROUP_BY

For GROUP BY aggregation operations, if the columns in SELECT do not appear in GROUP BY, then this SQL is illegal because the columns are not in the GROUP BY clause.

NO_AUTO_VALUE_ON_ZERO

This value affects inserts into auto-increment columns. By default, inserting 0 or NULL will generate the next auto-increment value. This option is useful if the user wants to insert a value of 0 and the column is auto-incrementing.

STRICT_TRANS_TABLES

In this mode, if a value cannot be inserted into a transactional table, the current operation is interrupted, and there is no restriction on non-transactional tables.

NO_ZERO_IN_DATE

In strict mode, the day or month is not allowed to be zero. If the month or day of the date contains a zero value, an error will be reported, except for '0000-00-00'

NO_ZERO_DATE

By setting this value, the MySQL database does not allow the insertion of zero date, and inserting zero date will throw an error instead of a warning. Any non-zero year, month, and day meets the requirement, only '0000-00-00' will result in an error

ERROR_FOR_DIVISION_BY_ZERO

During an INSERT or UPDATE, if data is divided by zero, an error is generated rather than a warning. If this mode is not given, MySQL returns NULL if the value is divided by zero.
update table set num = 5 / 0 ; If this mode is set, an error will be reported. If it is not set, the modification will be successful and the value of num will be null

NO_AUTO_CREATE_USER

Prevent GRANT from creating users with empty passwords

NO_ENGINE_SUBSTITUTION

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

PIPES_AS_CONCAT

Treat "||" as a string concatenation operator rather than an OR operator, which is the same as Oracle database and similar to the string concatenation function Concat

ANSI_QUOTES

When ANSI_QUOTES is enabled, you cannot use double quotes to quote strings, because they are interpreted as identifiers.

Reference: https://blog.csdn.net/wyzxg/article/details/8787878

This is the end of this article about the detailed explanation of MySQL sql_mode query and settings. For more relevant MySQL sql_mode query content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!