Summary of Characteristics of SQL Mode in MySQL

Preface

The SQL schema affects the SQL syntax supported by MySQL and the data validation checks performed.

The MySQL server can run in different SQL modes, and these modes can be applied in different ways for different clients, depending on the value of the sql_mode system variable. DBA can set the global SQL mode to match site server operational requirements, and each application can set its session SQL mode to its own requirements.

The schema affects the SQL syntax supported by MySQL and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL with other database servers.

The following words are not much to say, let's take a look at the detailed introduction

Setting SQL mode

To change the SQL mode at run time, sql_mode sets global or session system variables using the following SET statement

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

Pattern list

模式	注释
ALLOW_INVALID_DATES	无效日期会生成错误
ERROR_FOR_DIVISION_BY_ZERO	除0错误
NO_BACKSLASH_ESCAPES	禁止使用反斜杠字符（\）作为字符串中的转义字符。启用此模式后，反斜杠就像其他任何1个普通字符1样。
NO_UNSIGNED_SUBTRACTION	在整数值之间减去（其中1个是类型） UNSIGNED，默认情况下会产生无符号结果。如果结果否则为负，则会导致错误
NO_ZERO_IN_DATE	'0000-00-00' 则允许并且插入产生警告
ONLY_FULL_GROUP_BY	select 内指定字段必须出现在 groupby 中，否则错误
STRICT_TRANS_TABLES	为事务存储引擎启用严格的SQL模式，并在可能的情况下为非事务性存储引擎启用。
STRICT_ALL_TABLES	为所有存储引擎启用严格SQL模式。无效的数据值被拒绝。

For details, please refer to https://dev.mysql.com/doc/ref...

Strict SQL mode

The MySQL server can run in different SQL modes, and these modes can be applied in different ways for different clients, depending on the value of the sql_mode system variable. In strict SQL mode, the server will escalate some warnings to errors.

The strict SQL pattern applies to the following statements

ALTER TABLE CREATE TABLE CREATE TABLE ... SELECT DELETE INSERT LOAD DATA LOAD XML SELECT SLEEP() UPDATE

In stored programs, individual statements of the listed types are executed in strict SQL mode if the program is defined when strict mode takes effect.

The strict SQL mode applies to the following errors, which indicate Class 1 errors with invalid or missing input values. If the value has the wrong column data type or may be out of range, the value is invalid. If the new row to be inserted does not contain the value of the column whose definition has no explicit DEFAULT clause for NOT NULL, the value is missing.

ER_BAD_NULL_ERROR ER_CUT_VALUE_GROUP_CONCAT ER_DATA_TOO_LONG ER_DATETIME_FUNCTION_OVERFLOW ER_DIVISION_BY_ZERO ER_INVALID_ARGUMENT_FOR_LOGARITHM ER_NO_DEFAULT_FOR_FIELD ER_NO_DEFAULT_FOR_VIEW_FIELD ER_TOO_LONG_KEY ER_TRUNCATED_WRONG_VALUE ER_TRUNCATED_WRONG_VALUE_FOR_FIELD ER_WARN_DATA_OUT_OF_RANGE ER_WARN_NULL_TO_NOTNULL ER_WARN_TOO_FEW_RECORDS ER_WRONG_ARGUMENTS ER_WRONG_VALUE_FOR_TYPE WARN_DATA_TRUNCATED

Acknowledgement

Thank you for seeing here. I hope this article can help you. Thank you.

Summarize