小技巧快速修改sql_mode

1、sql_mode概述

MySQL服务器可以在不同的SQL模式下运行，并且可以根据sql_mode系统变量的值对不同的客户端应用不同的模式。DBA可以设置全局SQL模式以匹配站点服务器的操作要求，每个应用程序可以根据自己的要求设置其会话SQL模式。

模式影响MySQL支持的SQL语法和它执行的数据验证检查。这使得在不同的环境中使用MySQL以及与其他数据库服务器一起使用MySQL变得更加容易。

2、sql_mode信息

MySQL 8.0默认的sql_mode有:

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.

当然还有一些其他的sql_mode值，这里不一一列举。

3、修改sql_mode

在日常的MySQL数据库维护中，可能会遇到需要修改sql_mode的情况，那么如何修改呢，常见的就是在基于session、global、参数文件去修改。

案例为添加一个值(NO_BACKSLASH_ESCAPES),

3.1、常规操作

先查询

root@localhost [(none)] (12:10:29) >show variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

再添加，persist/session/global都可以

root@localhost [(none)] (12:15:16) >set persist sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] (12:15:41) >set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] (12:29:11) >set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

只需要简单复制张贴就可以了，但是没点X操作的DBA能行吗？不行

3.2、快速操作

同样是先查询

root@localhost [(none)] (12:32:12) >show variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

添加,三种情况都使用

root@localhost [(none)] (12:34:12) >set persist sql_mode=concat('NO_BACKSLASH_ESCAPES,',@@sql_mode);
Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)] (12:34:28) >set session sql_mode=concat('NO_BACKSLASH_ESCAPES,',@@sql_mode);
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] (12:34:39) >set global sql_mode=concat('NO_BACKSLASH_ESCAPES,',@@sql_mode);
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)] (12:34:46) >q
Bye

检查

[root@db02 ~]# mysql
root@localhost [(none)] (12:34:50) >show variables like 'sql_mode'G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.01 sec)
root@localhost [(none)] (12:34:57) >