方案概述
SQL (Structured Query Language) 是具有数据操纵和数据定义等多种功能的数据库语言，这种语言具有交互性特点，能为用户提供极大的便利，数据库管理系统应充分利用SQL语言提高计算机应用系统的工作质量与效率。SQL语言不仅能独立应用于终端，还可以作为子语言为其他程序设计提供有效助力，在程序应用中，SQL可与其他程序语言一起优化程序功能，进而为用户提供更多更全面的信息。在mysql中sql_mode变量控制着SQL的写法与行为。
参数sql_mode的值 在mysql5.7是22个值，在8.0是19个值。本文就以8.0的19个值来进行测试说明。
实施步骤
1.ANSI 这个值相当于以下5个值 REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY.
2.REAL_AS_FLOAT 表示把real当作float处理,默认是把real当作double类型，double是有精度和标度的。如double(M,D),M=整数位+小数位，D为小数位。
mysql [localhost:8028] {test} (test) > create table test_double(c1 double(4,2),c2 real);
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql [localhost:8028] {test} (test) > show create table test_double;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_double | CREATE TABLE `test_double` (
`c1` double(4,2) DEFAULT NULL,
`c2` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)查有结构时，real自动变成float了。
3.PIPES_AS_CONCAT 用||表示字符的连接。
mysql [localhost:8028] {test} (test) > set session sql_mode='PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8028] {test} (test) > select "hu"||"a"||"ng";
+-----------------+
| "hu"||"a"||"ng" |
+-----------------+
| huang |
+-----------------+
1 row in set (0.00 sec)4.ANSI_QUOTES 表示把双引号作为标识符
mysql [localhost:8028] {test} (test) > show variables like 'sql_mode';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| sql_mode | ANSI_QUOTES |
+---------------+-------------+
1 row in set (0.00 sec)
mysql [localhost:8028] {test} (test) > show create table test_double;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_double | CREATE TABLE "test_double" (
"c1" double(4,2) DEFAULT NULL,
"c2" float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8028] {test} (test) > select concat("hu","a","ng");
ERROR 1054 (42S22): Unknown column 'hu' in 'field list'5.IGNORE_SPACE表示函数名之间允许有空格，只适用于内置函数。
mysql [localhost:8028] {test} (test) > set session sql_mode='IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8028] {test} (test) > show variables like 'sql_mode';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| sql_mode | IGNORE_SPACE |
+---------------+--------------+
1 row in set (0.00 sec)
mysql [localhost:8028] {test} (test) > create table count (i 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 'count (i int)' at line 1
mysql [localhost:8028] {test} (test) > create table `count` (i int);
Query OK, 0 rows affected (0.01 sec)6.ONLY_FULL_GROUP_BY表示在标准SQL-92中，做聚合运算时，选择的字段必段出现在group by后面。
mysql [localhost:8028] {test} (test) > CREATE TABLE mytable (
-> id INT UNSIGNED NOT NULL PRIMARY KEY,
-> a VARCHAR(10),
-> b VARCHAR(10),
-> c INT
-> );
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8028] {test} (test) > INSERT INTO mytable
-> VALUES (1, 'abc', 'qrs', 1000),
-> (2, 'abc', 'tuv', 2000),
-> (3, 'def', 'qrs', 4000),
-> (4, 'def', 'tuv', 8000),
-> (5, 'abc', 'qrs', 16000),
-> (6, 'def', 'tuv', 32000);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql [localhost:8028] {test} (test) > show variables like 'sql_mode';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| sql_mode | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.01 sec)
mysql [localhost:8028] {test} (test) > SELECT id, MAX(c) FROM mytable;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'test.mytable.id'; this is incompatible with sql_mode=only_full_group_by小结
- 从以上几个值测试结果来看，主从复制时sql_mode一定要一致，如果不一致，可能导库主从数据有问题
- 在做迁移升级时，这个sql_mode参数必须保持和原低版本一致，不一致可能导致应用sql做聚合运算报错。