Introduction to MySQL sql_mode and Its 10 Modes

SQL_MODE的10模式介绍

To view the current sql_mode setting in MySQL, run:

<code>mysql&gt; SHOW VARIABLES LIKE 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.16 sec)</code>

1. STRICT_TRANS_TABLES

When inserting or updating data, if a column value cannot be converted to the target data type, MySQL will raise an error. This mode applies only to InnoDB tables, and inconsistencies can cause the same code to fail in different environments.

2. STRICT_ALL_TABLES

Similar to STRICT_TRANS_TABLES, but it applies to all tables, including non‑InnoDB tables.

3. ERROR_FOR_DIVISION_BY_ZERO

If a division by zero occurs, MySQL throws an error; otherwise it returns NULL.

4. NO_AUTO_CREATE_USER

When using GRANT to create a new user, MySQL will not automatically create a non‑existent user; the user must be created manually first.

5. NO_ENGINE_SUBSTITUTION

If the specified storage engine does not exist, MySQL will raise an error instead of substituting another engine.

6. ANSI_QUOTES

Enables treating double quotes as identifier delimiters rather than string delimiters.

7. IGNORE_SPACE

When enabled, MySQL ignores spaces after function names, allowing a more compact SQL syntax.

8. ONLY_FULL_GROUP_BY

Requires that all non‑aggregated columns appear in the GROUP BY clause, otherwise MySQL reports an error.

9. NO_ZERO_IN_DATE

Disallows zero values in date or time parts.

10. NO_ZERO_DATE

Prevents the use of the '0000-00-00' date.

Tips: php中文网《第23期PHP线上培训班》已正式开课！目前还有少量名额，预报名请联系微信：phpcn01（月月老师）。

授课方式：全程在线直播授课并同步录播、内部群老师辅导解答、作业批改、电子课件源码下载、专人督促学习、防止拖延、学员就业群内推岗位兼职等。