How to query and set sql_mode in MySQL

How to query and set sql_mode in

MySQL? The following article will introduce to you the query and setting methods of sql_mode in MySQL.

MySQL’s sql_mode query and setting

1, execute SQL to view [Related recommendations: mysql video tutorial]

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
Global modification requires advanced permissions, it will only take effect for the next connection, does not affect the current session, and MySQL It will become invalid after restarting, because MySQL will re-read the corresponding value in the configuration file when restarting. 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 GROUP BY aggregation operation, if the column in SELECT does not appear in 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- Except for 00-00'

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. If any of the year, month and day is not 0, it meets the requirements. Only '0000-00-00' will report an error

ERROR_FOR_pISION_BY_ZERO

in INSERT Or during the UPDATE process, 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
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

If the required storage engine is disabled or is not compiled, then 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 a string because it is interpreted as an identifier

For more programming related knowledge, please visit:Programming Video! !

The above is the detailed content of How to query and set sql_mode in MySQL. For more information, please follow other related articles on the PHP Chinese website!