首页
超值活动
产品中心
物理机租用
物理机租用
物理机租用
云服务器
云服务器
云服务器
云服务器
虚拟主机
虚拟主机
免费空间
免费空间
云电脑/挂机宝
订购产品
文章公告
关于我们
博客动态
登录
网站首页
    超值活动

      帮助中心/最新通知

      质量为本、客户为根、勇于拼搏、务实创新

      所有文章
      < 返回文章列表

      【开发相关】[MYSQL] 参数/变量浅析(3) -- sql_mode

      发表时间：2025-01-16 01:32:56 小编：主机乐-Yutio

      导读

      本次只讲一个参数 - - SQL_MODE, 因为这玩意的值太TM多了.

      所谓sql_mode就是控制一些sql执行的时候的行为规则,主要就是兼容性. 为了方便使用,抽象出一个"组/派"出来,

      比如:

      ANSI 就等于 REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and ONLY_FULL_GROUP_BY

      traditional 就等于 STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION

      组的效果就是设置为组的名字就可获得整个组的效果.

      有些是5.7才有的,到8.0就移除了; 有的是8.0新增的, 都是标出来了的.

      不同的版本,默认的SQL_MODE值也是不一样的,

      5.7 中默认的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, and NO_ENGINE_SUBSTITUTION.

      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

      然后我们来具体看下各值的意义:

      ALLOW_INVALID_DATE

      若启用, 则不考虑日期(date和datetime)是否有效, 比如2025-2-30 也是可以的, 但会检查月和日是否是1-12和1-31, 也就是2025-13-30是无效的(严格模式报错,非严格模式告警)

      代码语言：sql
      复制
      (root@127.0.0.1) [(none)]> insert into db1.t20251211_4 values(1,'2025-02-31');
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [(none)]> insert into db1.t20251211_4 values(1,'2025-02-32');
Query OK, 1 row affected, 1 warning (0.00 sec) -- 严格模式则是报错,而不是告警

(root@127.0.0.1) [(none)]> insert into db1.t20251211_4 values(1,'24-02-12');
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [(none)]> select * from db1.t20251211_4;
+------+------------+
| id   | aa         |
+------+------------+
|    1 | 2025-02-31 |
|    1 | 0000-00-00 |
|    1 | 2024-02-12 |
+------+------------+
3 rows in set (0.01 sec)

      不考虑timestamp

      检查月日在范围内是因为存储层就限制了大小:

      date使用3字节即可表示. 支持的范围是1000-01-01 --> 9999-12-31

      对象

      大小(bit)

      signed

      1

      year

      14

      month

      4

      day

      5

      ANSI_QUOTES

      若使用,则使用双引号"来引用字段, 此时,字符串就不能使用双引号"了,但可以使用单引号'.

      代码语言：sql
      复制
      (root@127.0.0.1) [(none)]> set session sql_mode='ansi_quotes';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [(none)]> show create table db1.t20251211_3\G
*************************** 1. row ***************************
       Table: t20251211_3
Create Table: CREATE TABLE "t20251211_3" (
  "id" int DEFAULT NULL,
  "type" int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> insert into db1.t20251211_3 values(2,"123");
ERROR 1054 (42S22): Unknown column '123' in 'field list'
(root@127.0.0.1) [(none)]>

      反引号(`)还是能正常使用的,

      ERROR_FOR_DIVISION_BY_ZERO

      若启用,则除以0的时候有warning,若有严格模式,则是报错.

      代码语言：sql
      复制
      (root@127.0.0.1) [(none)]> set sql_mode='error_for_division_by_zero,strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [(none)]> insert into db1.t20251211_5 values(1/0,'2025-12-12');
ERROR 1365 (22012): Division by 0
(root@127.0.0.1) [(none)]> select 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec) -- 即使严格模式下,select也不会报错

(root@127.0.0.1) [(none)]> set sql_mode='error_for_division_by_zero';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [(none)]> select 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

(root@127.0.0.1) [(none)]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [(none)]> select 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

      在严格模式下启用error_for_division_by_zero时,select还是返回null和warning

      HIGH_NOT_PRECEDENCE

      若启用,则not优先级更高

      代码语言：sql
      复制
      (root@127.0.0.1) [(none)]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [(none)]> select not 1 between -5 and 5; -- 先计算between,再not
+------------------------+
| not 1 between -5 and 5 |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> set sql_mode='high_not_precedence';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [(none)]> select not 1 between -5 and 5; -- 先计算not,再between
+------------------------+
| not 1 between -5 and 5 |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

      IGNORE_SPACE

      若启用,则允许系统(内置)函数名后面有空格

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> select count(1);
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> select count (1);
ERROR 1630 (42000): FUNCTION db1.count does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
(root@127.0.0.1) [db1]> set sql_mode='ignore_space';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> select count(1);
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> select count (1);
+-----------+
| count (1) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

      业务自建函数/存储过程不受此限制

      NO_AUTO_CREATE_USER(5.7)

      若启用,则grant时没有使用identified by就不会自动创建用户.(有identified by还是能自动创建)

      代码语言：sql
      复制
      (root@127.0.0.1) [(none)]> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [(none)]> 
(root@127.0.0.1) [(none)]> grant all on db1.* to u20251212_1@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [(none)]> set sql_mode='no_auto_create_user';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [(none)]> grant all on db1.* to u20251212_2@'%';
ERROR 1133 (42000): Can't find any matching row in the user table
(root@127.0.0.1) [(none)]> grant all on db1.* to u20251212_2@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

(root@127.0.0.1) [(none)]> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

      NO_FIELD_OPTIONS (5.7)

      据传是一些字段的特殊属性,开启此参数后就不再显示, 但没测出来具体是哪些属性....

      NO_KEY_OPTIONS (5.7)

      据传是一些索引的特殊属性,开启此参数后就不再显示, 但我懒得测了.

      NO_TABLE_OPTIONS(5.7)

      若启用,则不显示表级别的属性

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> show create table t20251212_33\G
*************************** 1. row ***************************
       Table: t20251212_33
Create Table: CREATE TABLE `t20251212_33` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(200) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_SAMPLE_PAGES=30
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='no_table_options';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [db1]> show create table t20251212_33\G
*************************** 1. row ***************************
       Table: t20251212_33
Create Table: CREATE TABLE `t20251212_33` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(200) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL
)
1 row in set (0.00 sec)

      NO_AUTO_VALUE_ON_ZERO

      若启用, 则自增字段使用0的时候不会自增了, 而是作为真正的0插入数据库(如果主键不冲突的话).

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> create table db1.t20251212_11(id int primary key auto_increment);
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_11 values(null);
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_11 values(0);
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_11 values(0);
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [db1]> select * from db1.t20251212_11;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='no_auto_value_on_zero';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> create table db1.t20251212_12(id int primary key auto_increment);
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_12 values(null);
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_12 values(0);
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_12 values(0);
ERROR 1062 (23000): Duplicate entry '0' for key 't20251212_12.PRIMARY'
(root@127.0.0.1) [db1]> select * from db1.t20251212_12;
+----+
| id |
+----+
|  0 |
|  1 |
+----+
2 rows in set (0.00 sec)

      主要是在mysqldump备份的时候有用, 备份的时候有些表的自增字段值可能就是0, 不开启此参数的话, 恢复的时候就直接自增了, 也就可能和原始数据不一致了. 所以mysqldump出来的文件有个/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

      NO_BACKLASH_ESCAPES

      若启用,则反斜杠(\)被当作普通字符,也就是不做转义了.

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> select '123\t';
+------+
| 123	 |
+------+
| 123	 |
+------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='no_backslash_escapes';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> select '123\t';
+-------+
| 123\t |
+-------+
| 123\t |
+-------+
1 row in set (0.00 sec)

      NO_DIR_IN_CREATE

      若启用,则建表的时候指定的索引路径和数据路径无效.(主从的时候比较好用)

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> select @@innodb_directories;
+---------------------------------------------+
| @@innodb_directories                        |
+---------------------------------------------+
| /data/mysql_3314/ext1;/data/mysql_3314/ext2 |
+---------------------------------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> create table db1.t20251212(id int) data directory '/data/mysql_3314/ext1';
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> select * from information_schema.innodb_datafiles where path like '%t20251212%';
+--------------+-----------------------------------------+
| SPACE        | PATH                                    |
+--------------+-----------------------------------------+
| 0x3535313234 | /data/mysql_3314/ext1/db1/t20251212.ibd |
+--------------+-----------------------------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='no_dir_in_create';
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> create table db1.t20251212_1(id int) data directory '/data/mysql_3314/ext1';
Query OK, 0 rows affected, 1 warning (0.01 sec)

(root@127.0.0.1) [db1]> show warnings;
+---------+------+---------------------------------+
| Level   | Code | Message                         |
+---------+------+---------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored |
+---------+------+---------------------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> select * from information_schema.innodb_datafiles where path like '%t20251212%';
+--------------+-----------------------------------------+
| SPACE        | PATH                                    |
+--------------+-----------------------------------------+
| 0x3535313235 | ./db1/t20251212_1.ibd                   |
| 0x3535313234 | /data/mysql_3314/ext1/db1/t20251212.ibd |
+--------------+-----------------------------------------+
2 rows in set (0.00 sec)

      innodb不支持指定index directory, 因为数据和索引都TM在一个文件的呢

      NO_ENGINE_SUBSTITUTION

      若启用,建表时指定的存储引擎不可用就会报错; 若不启用则只是告警

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> create table db1.t20251212_14(id int) engine='1234';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

(root@127.0.0.1) [db1]> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1286 | Unknown storage engine '1234'                        |
| Warning | 1266 | Using storage engine InnoDB for table 't20251212_14' |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

(root@127.0.0.1) [db1]> show create table db1.t20251212_14\G
*************************** 1. row ***************************
       Table: t20251212_14
Create Table: CREATE TABLE `t20251212_14` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='no_engine_substitution';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> create table db1.t20251212_15(id int) engine='1234';
ERROR 1286 (42000): Unknown storage engine '1234'

      NO_UNSIGNED_SUBTRACTION

      若启用,则允许存在无符号数的减法运算结果出现负数(有符号)

      若未启用,则允许存在无符号数的减法运算结果出现负数(有符号)

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> create table db1.t20251212_16(id int unsigned);
Query OK, 0 rows affected (0.02 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_16 values(0),(1),(2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@127.0.0.1) [db1]> select * from db1.t20251212_16;
+------+
| id   |
+------+
|    0 |
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> select id - 1 from db1.t20251212_16;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`db1`.`t20251212_16`.`id` - 1)'
(root@127.0.0.1) [db1]> 
(root@127.0.0.1) [db1]> set sql_mode='no_unsigned_subtraction';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> select id - 1 from db1.t20251212_16;
+--------+
| id - 1 |
+--------+
|     -1 |
|      0 |
|      1 |
+--------+
3 rows in set (0.00 sec)

      NO_ZERO_DATE

      若启用, 则date字段有0000-00-00时,告警(严格模式则是error)

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> create table db1.t20251212_17(id int, birthday date);
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_17 values(1,'0000-00-00');
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [db1]> set sql_mode='no_zero_date';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_17 values(2,'0000-00-00');
Query OK, 1 row affected, 1 warning (0.01 sec)

(root@127.0.0.1) [db1]> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1264 | Out of range value for column 'birthday' at row 1 |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='no_zero_date,strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [db1]> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_17 values(3,'0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'birthday' at row 1

      NO_ZERO_IN_DATE

      若启用,则date中月/日为00就告警(严格模式报错)

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> create table db1.t20251212_17(id int, birthday date);
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_17 values(1,'0000-00-00');
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [db1]> set sql_mode='no_zero_in_date';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_17 values(1,'2025-12-00');
Query OK, 1 row affected, 1 warning (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_17 values(1,'2025-00-12');
Query OK, 1 row affected, 1 warning (0.01 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_17 values(1,'0000-12-12');
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_17 values(1,'0000-00-00');
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [db1]> select * from db1.t20251212_17;
+------+------------+
| id   | birthday   |
+------+------------+
|    1 | 0000-00-00 |
|    1 | 0000-00-00 |
|    1 | 0000-00-00 |
|    1 | 0000-12-12 |
|    1 | 0000-00-00 |
+------+------------+
5 rows in set (0.00 sec)

      年份为0,或者全部为0 ,是没有warning的

      ONLY_FULL_GROUP_BY

      若启用,则要求非聚合字段要在group by中.

      代码语言：sql
      复制
      (root@127.0.0.1) [(none)]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [(none)]> select table_schema,table_name,sum(data_length) from information_schema.tables group by 1 limit 1;
+--------------+-------------+------------------+
| TABLE_SCHEMA | TABLE_NAME  | sum(data_length) |
+--------------+-------------+------------------+
| db1          | t20251212_1 |            65536 |
+--------------+-------------+------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> set sql_mode='only_full_group_by';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [(none)]> select table_schema,table_name,sum(data_length) from information_schema.tables group by 1 limit 1;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'information_schema.tables.TABLE_NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
(root@127.0.0.1) [(none)]>

      PAD_CHAR_TO_FULL_LENGTH

      若启用,则自动将char字段补充为定义长度.

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> create table db1.t20251212_18(id int, name char(20));
Query OK, 0 rows affected (0.02 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_18 values(1,'aa');
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> select id,name,char_length(name) from db1.t20251212_18;
+------+------+-------------------+
| id   | name | char_length(name) |
+------+------+-------------------+
|    1 | aa   |                 2 |
+------+------+-------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='pad_char_to_full_length';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@127.0.0.1) [db1]> select id,name,char_length(name) from db1.t20251212_18;
+------+----------------------+-------------------+
| id   | name                 | char_length(name) |
+------+----------------------+-------------------+
|    1 | aa                   |                20 |
+------+----------------------+-------------------+

      char类型在innodb存储上是完整存储的. 如果字符集是latin1等最多使用1字节表示的话, 甚至不需要额外空间来记录其数据存储长度.

      PIPES_AS_CONCAT

      若启用,则可以使用双竖线||来作为字符串拼接符(同concat),和oracle一样了.

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> select 123||456;
+----------+
| 123||456 |
+----------+
|        1 |
+----------+
1 row in set, 1 warning (0.00 sec)

(root@127.0.0.1) [db1]> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
+---------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='pipes_as_concat';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> select 123||456;
+----------+
| 123||456 |
+----------+
| 123456   |
+----------+
1 row in set (0.01 sec)




-- oracle效果如下
SYS@ddcw202>show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 DDCW202PDB			  READ WRITE NO
SYS@ddcw202>alter session set container=ddcw202pdb;

Session altered.

SYS@ddcw202>select 123||456 from dual;

123||4
------
123456

      启用之后,||就不再是or了.

      REAL_AS_FLOAT

      若启用, 则real就是float

      若未启用,则real是double

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> create table db1.t20251212_21(id int, aa real);
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> show create table db1.t20251212_21\G
*************************** 1. row ***************************
       Table: t20251212_21
Create Table: CREATE TABLE `t20251212_21` (
  `id` int DEFAULT NULL,
  `aa` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> 
(root@127.0.0.1) [db1]> set sql_mode='real_as_float';
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> create table db1.t20251212_22(id int, aa real);
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> show create table db1.t20251212_22\G
*************************** 1. row ***************************
       Table: t20251212_22
Create Table: CREATE TABLE `t20251212_22` (
  `id` int DEFAULT NULL,
  `aa` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

      STRICT_ALL_TABLES

      若启用,则所有存储引擎均使用严格模式(无效的date之类的直接报错)

      STRICT_TRANS_TABLES

      若启用, 则支持事务的存储引擎使用严格模式(无效的date之类的直接报错)

      TIME_TRUNCATE_FRACTIONAL (8.0)

      若启用,则对时间类型超出精度范围部分进行截断

      若未启用,则对时间类型超出精度范围部分进行四舍五入

      代码语言：sql
      复制
      (root@127.0.0.1) [db1]> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> create table db1.t20251212_23(id int, aa time(1));
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_23 values(1,'12:12:12.123');
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_23 values(2,'12:12:12.789');
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [db1]> set sql_mode='time_truncate_fractional';
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_23 values(3,'12:12:12.123');
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [db1]> insert into db1.t20251212_23 values(4,'12:12:12.789');
Query OK, 1 row affected (0.00 sec)

(root@127.0.0.1) [db1]> select * from db1.t20251212_23;
+------+------------+
| id   | aa         |
+------+------------+
|    1 | 12:12:12.1 |
|    2 | 12:12:12.8 |
|    3 | 12:12:12.1 |
|    4 | 12:12:12.7 |
+------+------------+
4 rows in set (0.00 sec)

      总结

      SQL_MODE 是mysql的 "行为开关", 设置不同的值, 可让mysql适配不同的sql标准(ansi,traditional)或业务场景(有些业务就非要关闭only_full_group_by)

      该参数可以在会话级修改, 也就是业务可以修改自己需要的sql_mode值, 而不需要修改全局的sql_mode. 所以建议保持该参数为默认值(如果有相关的标准,就请按照标准来设置即可.)

      参考:

      https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

      https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

      https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_mode


      联系我们
      返回顶部