MySQL基础运维-06 SQL Mode对程序有怎样的影响？

内容纲要

SQL Mode 是 MySQL 中比较特殊的一个概念，可以通过参数 sql_mode 进行设置。设置 SQL Mode 会影响数据库对 SQL 的语法支持，也会影响数据写入时的校验规则。早期的 MySQL 使用非严格模式，这样有一些不符合 SQL 标准的语句在 MySQL 中也能执行，一些按 SQL 标准来说不合法的数据，也能写到表里面。

不过从 MySQL 5.7 开始，默认就开启了严格模式。这一讲中，我们一起来看看 SQL Mode 是怎么影响到 SQL 语句的，以及应该怎么设置 SQL Mode。

非严格模式

非严格模式下，MySQL 会允许你执行一些不符合 SQL 标准的语句。我们通过一些例子来说明这种情况。先创建一个测试表，写入一些数据。

mysql> create table tab2(
    b int, 
    c varchar(10), 
    d varchar(30)
) engine=innodb;

Query OK, 0 rows affected (10.16 sec)

mysql> insert into tab2 values
    (10, 'AAA1', 'BBB1'),
    (20, 'AAA4', 'BBB4'), 
    (10, 'AAA3', 'BBB3'), 
    (20, 'AAA2', 'BBB2')

Query OK, 4 rows affected (0.56 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tab2;
+------+------+------+
| b    | c    | d    |
+------+------+------+
|   10 | AAA1 | BBB1 |
|   20 | AAA4 | BBB4 |
|   10 | AAA3 | BBB3 |
|   20 | AAA2 | BBB2 |
+------+------+------+
4 rows in set (0.00 sec)

执行下面这个带了 GROUP BY 的语句时，你会发现执行会报错。因为按 SQL 标准语法，如果 SQL 带了 GROUP BY，那么 SELECT 列表中的字段，要么也出现在 GROUP BY 的字段列表中，要么就加上聚合函数，比如 avg、max、min 等。

mysql> select b,c,d from tab2 group by b;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'src_db.tab2.c' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

但是在 MySQL 中，如果你把 SQL Mode 中的 only_full_group_by 选项去掉，就可以正常执行上面这个 SQL。从输出结果看，字段 C 和 D 的取值跟数据写入的顺序有关。

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select b, c, d from tab2 group by b;
+------+------+------+
| b    | c    | d    |
+------+------+------+
|   10 | AAA1 | BBB1 |
|   20 | AAA4 | BBB4 |
+------+------+------+

严格模式下，下面这两个 SQL 都会报错。第一个 SQL 是因为往 int 类型的字段中写入了非数字的字符，第二个 SQL 是因为写入的字符串长度超过了字段定义时允许的范围。

mysql> insert into tab3(b,c,d) values('a', 'a', 'a');
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'b' at row 1

mysql> insert into tab3(b,c) values('100', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
ERROR 1406 (22001): Data too long for column 'c' at row 1

但是在非严格模式下，这两个 SQL 都能执行，虽然执行时会有 Warning。

mysql> delete from tab3;
Query OK, 4 rows affected (0.54 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tab3 values('a', 'a', 'a');
Query OK, 1 row affected, 1 warning (0.45 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'a' for column 'b' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into tab3(b,c) values('100', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
Query OK, 1 row affected, 1 warning (0.70 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'c' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

我们再来查看数据，第一个 SQL 插入的数据，非法的整数值被替换成了 0。第二个 SQL 插入的数据，超出长度的字符串被截去了，留下了一个前缀。

mysql> select * from tab3;
+------+------------+------+
| b    | c          | d    |
+------+------------+------+
|    0 | a          | a    |
|  100 | ABCDEFGHIJ | NULL |
+------+------------+------+
2 rows in set (0.01 sec)

在 MySQL 5.6 和更早版本中，默认使用非严格模式，上面这样的 SQL 都可以正常执行。如果你的应用中存在这些情况，然后由于某种原因 SQL Mode 切换成了严格模式，那么原先正常的应用程序，就可能无法正常运行了。

SQL Mode 的各种选项

SQL Mode 有很多选项，MySQL 8.0 中，sql_mode 的默认设置可以通过下面这个方法获取。

mysql> set sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

mysql> 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.01 sec)

这些选项中，ONLY_FULL_GROUP_BY 和 STRICT_TRANS_TABLES 我们已经遇到过了。此外，还有一些选项不在这个默认的设置中，后面我们也会分别进行介绍。

ONLY_FULL_GROUP_BY

前面的例子已经演示过 ONLY_FULL_GROUP_BY 的效果了。设置 ONLY_FULL_GROUP_BY 后，对有 GROUP BY 的 SQL，SELECT 的字段要么也出现 GROUP BY 中，要么使用聚合函数，否则 SQL 执行会报错。

我们可以对 SQL 进行改写，在 GROUP BY 之外的那些字段上使用 ANY_VALUE 函数，这样 SQL 就可以正常执行了。

mysql> set sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> select b,c,d from tab2 group by b;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'src_db.tab2.c' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql> select b, any_value(c) as c, any_value(d) as d from tab2 group by b;
+------+------+------+
| b    | c    | d    |
+------+------+------+
|   10 | AAA1 | BBB1 |
|   20 | AAA4 | BBB4 |
+------+------+------+
2 rows in set (0.00 sec)

STRICT_TRANS_TABLES

设置 STRICT_TRANS_TABLES 后，在数据写入时，如果数据不符合字段定义，比如字符串超出长度，或者数值类型数据超出范围时，SQL 会报错。如果不设置 STRICT 模式，会对异常数据进行截断处理，SQL 会显示 Warning，但不报错。

mysql> create table t_strict(a tinyint, b tinyint unsigned, c decimal(6,2), d varchar(10));
Query OK, 0 rows affected (6.36 sec)

mysql> set sql_mode='strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_strict(a) values(512);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

mysql> insert into t_strict(b) values(512);
ERROR 1264 (22003): Out of range value for column 'b' at row 1

mysql> insert into t_strict(c) values(1000000);
ERROR 1264 (22003): Out of range value for column 'c' at row 1

mysql> insert into t_strict(d) values('0123456789ABCDEF');
ERROR 1406 (22001): Data too long for column 'd' at row 1

去掉 STRICT_TRANS_TABLES 后，虽然能写入数据，但是数据被截断了，和应用本来想写入的数据有很大的差异。

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_strict(a,b,c,d) values(512, 512, 1000000, '0123456789ABCDEF');
Query OK, 1 row affected, 4 warnings (0.04 sec)

mysql> select * from t_strict;
+------+------+---------+------------+
| a    | b    | c       | d          |
+------+------+---------+------------+
|  127 |  255 | 9999.99 | 0123456789 |
+------+------+---------+------------+
1 row in set (0.00 sec)

对不支持事务的存储引擎，比如 MyISAM，STRICT_TRANS_TABLES 的作用就比较复杂了。如果使用了批量 INSERT，也就是同时 INSERT 了多行记录，那么当第一行记录中有数据和字段定义不符合时，SQL 会报错，如果第一行数据没问题，但是后续的记录有问题，那么 SQL 能执行成功，但是会对超出范围的数据进行截断处理。下面这个例子中的第二个 INSERT 语句，插入了两行记录，第二行记录的数据被截断了。

mysql> create table t_strict_myisam(a tinyint, b tinyint unsigned, c decimal(6,2), d varchar(10)) engine=myisam;
Query OK, 0 rows affected (0.19 sec)

mysql> set sql_mode='strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into t_strict_myisam values(512, 512, 1000000, '0123456789ABCDEF');
ERROR 1264 (22003): Out of range value for column 'a' at row 1

mysql> insert into t_strict_myisam values(100, 200, 9999, '0123456789'), (512, 512, 1000000, '0123456789ABCDEF');
Query OK, 2 rows affected, 4 warnings (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 4

mysql> select * from t_strict_myisam;
+------+------+---------+------------+
| a    | b    | c       | d          |
+------+------+---------+------------+
|  100 |  200 | 9999.00 | 0123456789 |
|  127 |  255 | 9999.99 | 0123456789 |
+------+------+---------+------------+
2 rows in set (0.00 sec)

STRICT_ALL_TABLES

STRICT_ALL_TABLES 对所有存储引擎都生效。对于 MyISAM 这类不支持事务的存储引擎，使用批量 INSERT 时，如果 SQL 中存在超出范围的值，SQL 执行就会报错，但是对于已经写入的数据，无法回滚。下面这个例子就演示了这种情况。

例子中 INSERT 语句的第一行数据是合法的，第二行数据超出了范围，SQL 执行时，第一行数据写入成功，第二行数据无法写入，因此 SQL 就报错了，第三行数据虽然没问题，但是也不会再写入了。虽然 SQL 失败了，但是第一行数据已经写入了，而 MyISAM 不支持事务，无法回滚这一行记录。

mysql> create table t_strict_all(a tinyint, b tinyint unsigned, c decimal(6,2), d varchar(10)) engine=myisam;
Query OK, 0 rows affected (0.40 sec)

mysql> set  sql_mode='strict_all_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_strict_all values(100, 200, 9999, '0123456789'), (512, 512, 1000000, '0123456789ABCDEF'), (10, 20, 1000, 'ABCD');
ERROR 1264 (22003): Out of range value for column 'a' at row 2

mysql> select * from t_strict_all;
+------+------+---------+------------+
| a    | b    | c       | d          |
+------+------+---------+------------+
|  100 |  200 | 9999.00 | 0123456789 |
+------+------+---------+------------+

NO_ZERO_DATE 和 NO_ZERO_IN_DATE

设置 STRICT_TRANS_TABLES 和 STRICT_ALL_TABLES 后，表中无法写入非法的日期值。

mysql> create table t_date(a date, b datetime, c timestamp);
Query OK, 0 rows affected (5.47 sec)

mysql> set sql_mode='strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_date(a) values('2024-02-30');
ERROR 1292 (22007): Incorrect date value: '2024-02-30' for column 'a' at row 1

mysql> insert into t_date(b) values('2024-02-30 01:23:45');
ERROR 1292 (22007): Incorrect datetime value: '2024-02-30 01:23:45' for column 'b' at row 1

mysql> insert into t_date(c) values('2024-02-30 01:23:45');
ERROR 1292 (22007): Incorrect datetime value: '2024-02-30 01:23:45' for column 'c' at row 1

但是却可以往 date 和 datetime 类型的字段中写入日期为 0 或年月日中存在 0 的数据。

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_date values('0000-00-00', '0000-01-00 01:23:45', '1970-01-02 01:23:45');
Query OK, 1 row affected (0.98 sec)

mysql> select * from t_date;
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 0000-00-00 | 0000-01-00 01:23:45 | 1970-01-02 01:23:45 |
+------------+---------------------+---------------------+

如果要阻止往数据库中写入年月日为 0 的数据，就需要设置 NO_ZERO_DATE 和 NO_ZERO_IN_DATE。

mysql> set sql_mode='no_zero_date,no_zero_in_date,strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_date(a) values('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'a' at row 1

mysql> insert into t_date(a) values('0000-01-00');
ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'a' at row 1

mysql> insert into t_date(b) values('0001-00-00 01:23:45');
ERROR 1292 (22007): Incorrect datetime value: '0001-00-00 01:23:45' for column 'b' at row 1

需要注意的是，NO_ZERO_DATE 和 NO_ZERO_IN_DATE 需要跟 STRICT_TRANS_TABLES 一起设置，如果只是设置了 NO_ZERO_DATE 和 NO_ZERO_IN_DATE，还是能往数据库中写入日期为 0 或年月日中有 0 的数据。

mysql> delete from t_date;
Query OK, 1 row affected (0.85 sec)

mysql> set sql_mode='no_zero_date,no_zero_in_date';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_date(a,b,c) values('0000-00-00', '0000-01-00 01:23:45', '1970-01-02 01:23:45');
Query OK, 1 row affected, 2 warnings (0.83 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
| Warning | 1264 | Out of range value for column 'b' at row 1 |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t_date;
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 0000-00-00 | 0000-00-00 00:00:00 | 1970-01-02 01:23:45 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

ALLOW_INVALID_DATES

MySQL 中默认无法写入不合法的日期。不开启严格模式时，非法的日期值都会被转换成 0000-00-00。

mysql> create table t_date2(a date, b datetime, c timestamp);
Query OK, 0 rows affected (5.15 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_date2 values('2024-02-30', '2024-02-30 01:23:45', '2024-02-30 01:23:45');
Query OK, 1 row affected, 3 warnings (0.26 sec)

mysql> select * from t_date2;
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+------------+---------------------+---------------------+

但如果设置了 ALLOW_INVALID_DATES 这个 SQL Mode，就可以在 date 和 datetime 类型中写入不存在的日期值了。注意，即使设置了 ALLOW_INVALID_DATES，timestamp 类型的字段中还是无法写入这些日期值。

mysql> insert into t_date2 values('2024-02-30', '2024-02-30 01:23:45', '2024-02-30 01:23:45');
Query OK, 1 row affected, 1 warning (1.53 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'c' at row 1 |
+---------+------+--------------------------------------------+

mysql> select * from t_date2;
+------------+---------------------+---------------------+
| a          | b                   | c                   |
+------------+---------------------+---------------------+
| 2024-02-30 | 2024-02-30 01:23:45 | 0000-00-00 00:00:00 |
+------------+---------------------+---------------------+

ERROR_FOR_DIVISION_BY_ZERO

我们知道，从数学的意义上看，除数不能为 0。在 MySQL 中，如果除数为 0，会出现什么结果呢？这其实和 SQL Mode 有关系。如果 SQL Mode 中同时设置了 ERROR_FOR_DIVISION_BY_ZERO 和 STRICT_TRANS_TABLES，那么当除数为 0 时，SQL 会报错。否则除数为 0 时，结果为 NULL。

mysql> create table t_n(a int);
Query OK, 0 rows affected (4.25 sec)

mysql> set sql_mode='strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_n values(1/0);
Query OK, 1 row affected (2.10 sec)

mysql> set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_n values(1/0);
Query OK, 1 row affected, 1 warning (0.83 sec)

mysql> show warnings;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+

mysql> select * from t_n;
+------+
| a    |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.01 sec)

同时设置 ERROR_FOR_DIVISION_BY_ZERO 和 STRICT_TRANS_TABLES 后，如果除数为 0，SQL 会报错。

mysql>  set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_n values(1/0);
ERROR 1365 (22012): Division by 0

NO_BACKSLASH_ESCAPES

在 MySQL 中，反斜杠“\”是一个转义符，有特殊的含义。下面这个例子中，本来我们想写入一个 Windows 下的文件路径，但是查询数据时，发现路径分隔符“\”不见了。

mysql> create table t_char(a varchar(100));
Query OK, 0 rows affected (3.23 sec)

mysql> set sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_char values('C:\Downloads\File');
Query OK, 1 row affected (0.80 sec)

mysql> select * from t_char;
+-----------------+
| a               |
+-----------------+
| C:DownloadsFile |
+-----------------+
1 row in set (0.00 sec)

这是因为在 MySQL 中“\”是一个转义符，如果你想写入“\”这个符号，需要对它进行转义。

mysql> delete from t_char;
Query OK, 1 row affected (1.50 sec)

mysql> insert into t_char values('C:\\Downloads\\File');
Query OK, 1 row affected (0.89 sec)

mysql> select * from t_char;
+-------------------+
| a                 |
+-------------------+
| C:\Downloads\File |
+-------------------+
1 row in set (0.00 sec)

但是在别的数据库中，符号“\”可能没有任何特殊含义。如果你需要将数据从别的数据库迁移到 MySQL 中，需要对数据进行转换。

其实 MySQL 中可以通过 SQL Mode 来进行控制，设置 NO_BACKSLASH_ESCAPES 选项后，反斜杠“\”就变成一个普通的字符了，没有特殊含义。所以如果你需要从别的数据库迁移到 MySQL，设置 NO_BACKSLASH_ESCAPES 可能会帮你减少一些麻烦。

mysql> set sql_mode='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t_char;
Query OK, 1 row affected (0.32 sec)

mysql>  insert into t_char values('C:\Downloads\File');
Query OK, 1 row affected (1.18 sec)

mysql> select * from t_char;
+-------------------+
| a                 |
+-------------------+
| C:\Downloads\File |
+-------------------+
1 row in set (0.00 sec)

ANSI_QUOTES

在 MySQL 中，字符串常量可以使用单引号或双引号来引用。

mysql> set sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_quote values("Let's go");
Query OK, 1 row affected (1.41 sec)

mysql> insert into t_quote values('String s = "Helloworld"');
Query OK, 1 row affected (1.79 sec)

mysql> select * from t_quote;
+-------------------------+
| a                       |
+-------------------------+
| Let's go                |
| String s = "Helloworld" |
+-------------------------+

但是在其他数据库中，双引号用来引用标识符，和在 MySQL 中的反引号“`”的作用类似。比如下面这个例子中，order 是 MySQL 中的一个关键词，不能用作表名，但是加上反引号之后就可以了。

mysql> create table order(a int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order(a int)' at line 1

mysql> create table `order`(a int);
Query OK, 0 rows affected (8.92 sec)

mysql> select * from `order`;
Empty set (0.00 sec)

在 SQL Mode 中设置 ANSI_QUOTES 选项可以改变双引号的作用。设置 ANSI_QUOTES，双引号不再是用来引用字符串常量，而是用来引用标识符。

mysql> set sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from "order";
Empty set (0.00 sec)

mysql> insert into t_quote values ("some data");
ERROR 1054 (42S22): Unknown column 'some data' in 'field list'

NO_ENGINE_SUBSTITUTION

MySQL 支持多种存储引擎，存储引擎可以用插件的方式动态加载。在编译 MySQL 时，也可以通过 cmake 选项指定是否要将某个存储引擎编译出来。我们在建表的时候可以指定使用哪个存储引擎。如果指定的存储引擎不存在，那么 MySQL 可以将引擎替换为默认的存储引擎。在下面这个例子中，我们想创建一个 federated 表，但是我们的环境中没有 federated 存储引擎，因此存储引擎被改成了 InnoDB。

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t_engine(a int) engine=federated;
Query OK, 0 rows affected, 2 warnings (8.95 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1286 | Unknown storage engine 'federated'               |
| Warning | 1266 | Using storage engine InnoDB for table 't_engine' |
+---------+------+--------------------------------------------------+

mysql> show create table t_engine\G
*************************** 1. row ***************************
       Table: t_engine
Create Table: CREATE TABLE `t_engine` (
  `a` int DEFAULT NULL
) ENGINE=InnoDB 
1 row in set (0.00 sec)

如果 SQL Mode 中开启 NO_ENGINE_SUBSTITUTION 选项，建表时如果指定的存储引擎不可用或不存在，SQL 就会报错。

mysql> set sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t_engine2(a int) engine=federated;
ERROR 1286 (42000): Unknown storage engine 'federated'

PIPES_AS_CONCAT

在 MySQL 中，管道符“||”相当于 OR，这可能和别的数据库不一样。比如在 Oracle 中，经常使用管道符连接字符串。

mysql> set sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a' || 'b';
+------------+
| 'a' || 'b' |
+------------+
|          0 |
+------------+
1 row in set, 3 warnings (0.00 sec)

SQL Mode 中设置 PIPES_AS_CONCAT 选项后，管道符就变成了字符串连接符。

mysql> set sql_mode='pipes_as_concat';
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a' || 'b';
+------------+
| 'a' || 'b' |
+------------+
| ab         |
+------------+
1 row in set (0.00 sec)

REAL_AS_FLOAT

设置 REAL_AS_FLOAT 后，MySQL 会将 REAL 类型映射为 Float 类型。不设置 REAL_AS_FLOAT 的话，REAL 类型映射为 Double 类型。

IGNORE_SPACE

MySQL 中，函数和参数列表之间默认是不允许加空格的。比如下面这个例子中，函数 count 和括号之间加了几个空格，语句就无法执行了。

mysql> select count  (*) from information_schema.tables;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from information_schema.tables' at line 1

mysql> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|      438 |
+----------+
1 row in set (2.56 sec)

这种行为有时候可能比较讨厌。你可以在 SQL Mode 中加上 IGNORE_SPACE 选项，这样函数名之后有空格也不影响 SQL 的正确执行。

mysql> set sql_mode='ignore_space';
Query OK, 0 rows affected (0.00 sec)

mysql> select count  (*) from information_schema.tables;
+------------+
| count  (*) |
+------------+
|        438 |
+------------+
1 row in set (0.06 sec)

ANSI

ANSI 是一个组合的 SQL Mode，设置 ANSI 相当于同时设置 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE 和 ONLY_FULL_GROUP_BY 这几个选项。设置 ANSI 后，MySQL 的语法支持和标准 SQL 更接近。

TRADITIONAL

TRADITIONAL 也是一个组合的 SQL Mode，设置 TRADITIONAL 相当于同时设置 STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO 和 NO_ENGINE_SUBSTITUTION 这几个选项。

如何设置 SQL Mode？

SQL Mode 有这么多的选项，那么我们平时应该怎么设置呢？大部分情况下，我建议使用默认的严格模式设置，也就是设置上 ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO 和 NO_ENGINE_SUBSTITUTION 这几个选项，这可以避免往表中写入意外的错误数据。尽量在所有的环境中将 SQL Mode 设置成一样的，避免因为 SQL Mode 设置不一样引起一些不必要的麻烦。

如果你是从别的数据库迁移到 MySQL，一些 SQL 可能和 MySQL 的默认模式不兼容，比如使用管道符“||”连接字符串，使用引号“"”引用标识符，如果你可以修改 SQL，我的建议是将 SQL 按 MySQL 的方式进行修改。如果实在是无法修改 SQL，或者修改 SQL 的成本太高了，再考虑设置某些 SQL Mode 选项来解决。

MySQL 数据库从低版本升级或迁移到高版本时，也需要全面测试应用程序。MySQL 5.7 开始默认开启严格模式，避免由于 SQL Mode 默认值的变化而影响程序的正常运行。

下面这个表格对一些 SQL Mode 的作用做了简单总结，你可以参考。

总结

设置 SQL_MODE 可能会使原先能执行的 SQL 无法执行，也可能影响数据写入操作，因此在生产环境修改这个参数前，需要对应用进行完整的测试验证。

如果你进行了数据库迁移或升级，新环境数据库 sql_mode 和原先的设置不一样，也可能引起应用程序出错。因此需要注意迁移或升级前后 sql_mode 的设置。MySQL 5.7 开始，sql_mode 的默认设置跟之前的版本相比，有很大的改动，如果你是从更早的版本升级过来，需要特别注意。

思考

MySQL 的备库复制中断了，查看错误信息，发现是有一个建表的语句报错了。

               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'c1a67221-f9fc-11ed-bffd-fa8338b09400:106' at master log binlog.000020, end_log_pos 4203259. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids:

mysql> select * from performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
                                           CHANNEL_NAME:
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 1118
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'c1a67221-f9fc-11ed-bffd-fa8338b09400:106' at master log binlog.000020, end_log_pos 4203259; Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.' on query. Default database: 'repl'. Query: 'create table t_inno1(
       c01 varchar(768),
       c02 varchar(768),
       c03 varchar(768),
       c04 varchar(768),
       c05 varchar(768),
       c06 varchar(768),
       c07 varchar(768),
       c08 varchar(768),
       c09 varchar(768),
       c10 varchar(768),
       c11 varchar(398)
    ) engine=innodb row_format=compact charset latin1'

但是到主库上查看后，发现这个表创建成功了。

mysql >show create table t_inno1\G
*************************** 1. row ***************************
       Table: t_inno1
Create Table: CREATE TABLE `t_inno1` (
  `c01` varchar(768) DEFAULT NULL,
  `c02` varchar(768) DEFAULT NULL,
  `c03` varchar(768) DEFAULT NULL,
  `c04` varchar(768) DEFAULT NULL,
  `c05` varchar(768) DEFAULT NULL,
  `c06` varchar(768) DEFAULT NULL,
  `c07` varchar(768) DEFAULT NULL,
  `c08` varchar(768) DEFAULT NULL,
  `c09` varchar(768) DEFAULT NULL,
  `c10` varchar(768) DEFAULT NULL,
  `c11` varchar(398) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
1 row in set (0.00 sec)

为什么会出现这种情况呢？

回答：

解答

出现主库创建表成功，而备库在复制该建表语句时失败的情况，通常是由于主备库之间的配置不一致导致的。根据您提供的错误信息，我们可以分析出可能的原因并提供相应的解决方案。

错误分析

错误信息：

Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.'

表结构：

create table t_inno1(
   c01 varchar(768),
   c02 varchar(768),
   c03 varchar(768),
   c04 varchar(768),
   c05 varchar(768),
   c06 varchar(768),
   c07 varchar(768),
   c08 varchar(768),
   c09 varchar(768),
   c10 varchar(768),
   c11 varchar(398)
) engine=innodb row_format=compact charset=latin1;

从错误信息和表结构来看，问题出在 行大小超过了 InnoDB 的限制。但是主库创建成功，说明主库和备库之间存在某些配置差异。

可能原因

innodb_strict_mode 参数设置不同：
- 主库： innodb_strict_mode=OFF
- 备库： innodb_strict_mode=ON
- 当 innodb_strict_mode 关闭时，InnoDB 在创建超过行大小限制的表时会发出警告，但仍然创建表。
- 当 innodb_strict_mode 开启时，InnoDB 在遇到同样情况时会发出错误，导致表创建失败。
innodb_default_row_format 参数设置不同：
- 主库： 使用 DYNAMIC 行格式。
- 备库： 使用 COMPACT 行格式。
- DYNAMIC 行格式支持更大的行大小，而 COMPACT 行格式支持的行大小较小。
- 如果主库默认行格式为 DYNAMIC，则能够创建成功，而备库使用 COMPACT 则会失败。
MySQL 版本不一致：
- 主库： 较新的版本，默认支持更大的行大小。
- 备库： 较旧的版本，行大小限制更严格。
- 不同版本的 MySQL 在默认设置和支持的特性上可能存在差异，导致相同的语句在不同版本上执行结果不同。
innodb_large_prefix 参数设置不同：
- 主库： innodb_large_prefix=ON
- 备库： innodb_large_prefix=OFF
- 当该参数开启时，允许索引前缀长度更大，可以支持更大的列大小。
- 在某些 MySQL 版本中，该参数的默认值不同，可能导致行为差异。

详细解释

1. innodb_strict_mode 的影响：

作用： 控制 InnoDB 是否严格执行 SQL 语法和大小限制。
关闭时（OFF）： InnoDB 遇到超出限制的情况会给出警告，但操作仍会继续。
开启时（ON）： InnoDB 遇到超出限制的情况会直接报错，阻止操作继续。
解决方案：
- 统一设置： 将主库和备库的 innodb_strict_mode 参数设置为一致，建议开启以确保数据一致性和完整性。
- 修改表结构： 调整列的数据类型或长度，使其符合行大小限制。

2. 行格式的影响：

COMPACT 行格式：
- 限制： 最大行大小约为 8126 字节。
- 特点： 较旧的行格式，支持的行大小较小。
DYNAMIC 行格式：
- 限制： 支持更大的行大小，因为大字段会存储在 off-page。
- 特点： 较新的行格式，默认在新版本 MySQL 中使用。
解决方案：
- 显式指定行格式： 在创建表时使用 ROW_FORMAT=DYNAMIC。
- 统一默认行格式： 将主库和备库的 innodb_default_row_format 参数设置为一致，建议使用 DYNAMIC。

3. MySQL 版本的影响：

版本差异： 不同版本的 MySQL 在默认参数和特性支持上存在差异。
解决方案：
- 升级或降级： 确保主库和备库使用相同的 MySQL 版本。
- 检查兼容性： 在升级或降级前，检查各版本之间的兼容性和默认设置差异。

4. innodb_large_prefix 的影响：

作用： 允许索引前缀长度超过 767 字节。
默认值： 在较新版本中默认开启，但在较旧版本中可能默认关闭。
解决方案：
- 开启该参数： 在备库上将 innodb_large_prefix 设置为 ON。
- 注意事项： 该参数需要与 innodb_file_format=Barracuda 一起使用，并且文件系统支持。

解决方案总结

检查并统一配置：
- 确保主库和备库的以下参数设置一致：
  - innodb_strict_mode
  - innodb_default_row_format
  - innodb_large_prefix
  - innodb_file_format
- 建议在主备库上开启严格模式和使用 DYNAMIC 行格式，以确保数据一致性和完整性。
修改表结构：
- 将列的数据类型从 VARCHAR(768) 调整为较小的长度，或者使用 TEXT 类型。
- 这样可以减少单行数据的大小，避免超过行大小限制。
升级 MySQL 版本：
- 如果主备库版本不一致，考虑将备库升级到与主库相同的版本。
- 较新版本的 MySQL 对大行支持更好，默认设置也更合理。
重新同步备库：
- 在调整配置和表结构后，重新初始化并同步备库，以确保数据的一致性。

建议

开发与运维协作： 在进行数据库设计和变更时，开发人员应与运维人员密切协作，确保所有环境的配置一致。
测试环境验证： 在生产环境变更前，先在测试环境中验证配置和表结构的兼容性，避免生产环境出现问题。
监控与告警： 建立完善的监控和告警机制，及时发现和处理主备库不一致的问题。

参考命令

查看相关参数：

-- 查看 innodb_strict_mode
SHOW VARIABLES LIKE 'innodb_strict_mode';

-- 查看 innodb_default_row_format
SHOW VARIABLES LIKE 'innodb_default_row_format';

-- 查看 innodb_large_prefix
SHOW VARIABLES LIKE 'innodb_large_prefix';

-- 查看 innodb_file_format
SHOW VARIABLES LIKE 'innodb_file_format';

-- 查看 MySQL 版本
SELECT VERSION();

修改参数设置：

-- 设置 innodb_strict_mode 为 OFF
SET GLOBAL innodb_strict_mode=OFF;

-- 设置 innodb_default_row_format 为 DYNAMIC
SET GLOBAL innodb_default_row_format='DYNAMIC';

-- 设置 innodb_large_prefix 为 ON
SET GLOBAL innodb_large_prefix=ON;

-- 设置 innodb_file_format 为 Barracuda
SET GLOBAL innodb_file_format='Barracuda';

修改表结构：

-- 修改行格式为 DYNAMIC
ALTER TABLE t_inno1 ROW_FORMAT=DYNAMIC;

-- 将 VARCHAR 列类型修改为 TEXT
ALTER TABLE t_inno1 MODIFY COLUMN c01 TEXT;

总结

主备库之间的配置一致性对于保持数据同步和系统稳定性至关重要。出现主库操作成功而备库失败的情况，多半是由于配置参数、版本或存储引擎设置的不一致导致的。通过仔细检查和调整相关参数，确保主备库环境的一致性，可以有效解决此类问题，并防止类似问题的再次发生。

希望以上解答能够帮助您理解并解决问题。如有其他疑问，欢迎继续讨论！

要点总结

SQL Mode对MySQL中的语法支持和数据写入校验规则产生影响，早期MySQL使用非严格模式，但从MySQL 5.7开始默认开启严格模式。
在非严格模式下，MySQL允许执行不符合SQL标准的语句，而在严格模式下，会对数据写入时的合法性进行严格校验。
SQL Mode中的选项包括ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES，设置ONLY_FULL_GROUP_BY后，对有GROUP BY的SQL，SELECT的字段要么也出现在GROUP BY中，要么使用聚合函数，否则SQL执行会报错。
设置STRICT_TRANS_TABLES后，在数据写入时，如果数据不符合字段定义，比如字符串超出长度，或者数值类型数据超出范围时，SQL会报错。
SQL Mode的默认设置可以通过设置sql_mode=default来获取，其中包括了一系列选项，如ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES等。
SQL Mode的设置对数据库的语法支持和数据写入校验规则产生重要影响，需要根据实际需求进行合理设置。
在MySQL 5.6及更早版本中，默认使用非严格模式，而在MySQL 5.7及之后版本，默认开启严格模式，这可能导致原先正常的应用程序无法正常运行。
SQL Mode的设置对数据库的数据完整性和合法性起到重要作用，需要根据具体情况进行合理选择和配置。
SQL Mode的各种选项对数据库的数据写入和查询操作产生重要影响，需要根据实际情况进行灵活配置。
设置SQL_MODE可能会使原先能执行的SQL无法执行，也可能影响数据写入操作，因此在生产环境修改这个参数前，需要对应用进行完整的测试验证。

问题

问题1 - 严格模式在MySQL主备库中的重要性与最佳实践分析

思考题：
首先查看数据库的版本是否一致，mysql在5.7默认开启严格模式，通过show variables like 'innodb_strict_mode'查看，当前情况下，主库是在非严格模式下，而备库在严格模式下

解决方案：
1、设置备库为非严格模式，除非是业务需求，尽量不要使用非严格模式
2、修改主库表的DDL，开启严格模式（在业务改动不大的情况下），让备库忽略该GTID，不执行该语句，后由主库重新创建后会自动同步

另外，请教下老师，非严格模式使用会多吗，我们生产要求必须要使用严格模式

回答：
思考题里那个问题就是参数innodb_strict_mode在主备库不一样引起的。

一般都建议用严格模式，这样能尽早暴露错误。warning很多时候都会被忽略。