【运维】SQL MODE

小咪咪 2022-07-18 05:56 108阅读 0赞

# 前言 #

Github：[https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql][https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]

# 概述 #

MySql 可以运行在不同的 sql 模式，不同的模式支持的语法和数据校验不同。

**SQL MODE 有哪些作用？**

1）通过设置 SQL MODE 可以完成不同严格程度的数据校验，有效地保证数据库数据的准确性。

2）设置 SQL MODE 为 ANSI 模式，保证大多数 sql 符合标准 sql 语法，方便数据库迁移。

3）通过严格或非严格模式设置，方便数据库的迁移。

# 一 常用sql模式 #

## 1、常用三种 sql 模式 ##

**ANSI**

非严格模式，等同于 REAL\_AS\_FLOAT，PIPES\_AS\_CONCAT，ANSI\_QUOTES，IGNORE\_SPACE，ONLY\_FULL\_GROUP\_BY，ANSI 的组合，

这种模式的语法和行为更符合标准的 SQL。

**STRICT\_TRANS\_TABLES**

严格模式，适用于事务表和非事务表，不允许非法数据的插入，否则报错。

**TRADITIONAL**

严格模式，等同于 STRICT\_TRANS\_TABLES，STRICT\_ALL\_TABLES，NO\_ZERO\_IN\_DATE，NO\_ZERO\_DATE，ERROR\_FOR\_DIVISION\_BY\_ZERO，

TRADITIONAL，NO\_AUTO\_CREATE\_USER，NO\_ENGINE\_SUBSTITUTION。

适用于事务表和非事务表，对于插入不正确数据，直接报错。

## 2、ANSI 模式实例 ##

mysql> set sql_mode='ANSI';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select @@sql_mode;
    +--------------------------------------------------------------------------------+
    | @@sql_mode                                                                     |
    +--------------------------------------------------------------------------------+
    | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI |
    +--------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> create table t_user_main1(f_userName varchar(4),f_password varchar(4));
    Query OK, 0 rows affected (0.69 sec)
    
    mysql> insert into t_user_main1 values('aaaaa','bbbbb');
    Query OK, 1 row affected, 2 warnings (0.05 sec)
    
    mysql> show warnings;
    +---------+------+-------------------------------------------------+
    | Level   | Code | Message                                         |
    +---------+------+-------------------------------------------------+
    | Warning | 1265 | Data truncated for column 'f_userName' at row 1 |
    | Warning | 1265 | Data truncated for column 'f_password' at row 1 |
    +---------+------+-------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> select * from t_user_main1;
    +------------+------------+
    | f_userName | f_password |
    +------------+------------+
    | aaaa       | bbbb       |
    +------------+------------+
    1 row in set (0.00 sec)

从执行结果可以看出，数据库在 ANSI 模式下对数据校验是非严格程度，如果数据插入过长，数据库自动截掉多出的部分，

自行处理插入数据库，给出的只是一个警告。可以通过 show warnings 列表式查看警告的详细信息。

### 3、STRICT\_TRANS\_TABLES 模式实例 ###

mysql> set sql_mode =  'STRICT_TRANS_TABLES';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select @@sql_mode;
    +---------------------+
    | @@sql_mode          |
    +---------------------+
    | STRICT_TRANS_TABLES |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> create table t_user_main2(f_userName varchar(4),f_password varchar(4));
    Query OK, 0 rows affected (0.89 sec)
    
    mysql> insert into t_user_main2 values('aaaaa','bbbbb');
    ERROR 1406 (22001): Data too long for column 'f_userName' at row 1
    mysql> show errors;
    +-------+------+------------------------------------------------+
    | Level | Code | Message                                        |
    +-------+------+------------------------------------------------+
    | Error | 1406 | Data too long for column 'f_userName' at row 1 |
    +-------+------+------------------------------------------------+
    1 row in set (0.00 sec)

通过执行结果可以看出来，使用严格模式STRICT\_TRANS\_TABLES时，数据库对插入的数据做严格校验，

如果插入的数据超过长度，直接报错。可以通过show errors列表式查看错误的详细信息。

## 4、TRADITIONAL 模式实例 ##

mysql> set sql_mode = 'TRADITIONAL';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select @@sql_mode;
    +---------------------------------------------------------------------------------------------
    ----------------+
    | @@sql_mode
                    |
    +---------------------------------------------------------------------------------------------
    ----------------+
    | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZER
    NE_SUBSTITUTION |
    +---------------------------------------------------------------------------------------------
    ----------------+
    1 row in set (0.00 sec)
    
    mysql> create table t_user_main3 (f_userName varchar(4),f_password varchar(4));
    Query OK, 0 rows affected (0.41 sec)
    
    mysql> insert into t_user_main3 values('aaaaa','bbbbb');
    ERROR 1406 (22001): Data too long for column 'f_userName' at row 1
    mysql> show errors;
    +-------+------+------------------------------------------------+
    | Level | Code | Message                                        |
    +-------+------+------------------------------------------------+
    | Error | 1406 | Data too long for column 'f_userName' at row 1 |
    +-------+------+------------------------------------------------+
    1 row in set (0.00 sec)

通过执行结果可以看出来，使用严格模式STRICT\_TRANS\_TABLES时，数据库对插入的数据做严格校验，

如果插入的数据超过长度，直接报错。可以通过show errors列表式查看错误的详细信息。

## 5、修改sql mode(模式) ##

1）以上通过 set sql\_mode=''指定的修改sql模式针对的是当前线程的，也即是session级别的。

2）如果想永久性的修改 sql 模式，需修改配置文件，windows 系统中的 my.ini 文件，或 linux 系统上的 my.cnf 文件中

修改如下:

\[mysqld\]  
sql\_mode=STRICT\_TRANS\_TABLES

如果\[mysqld\]下没有sql\_mode可以自行添加，修改完后重启 mysql 服务生效。

[https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]: https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql