乘风原创程序

  • 如何在mysql中实现一个sql_mode模式
  • 2021/1/26 15:59:05
  • mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法,数据校验等!我们可以通过以下方式查看当前数据库使用的sql_mode:

    mysql> select @@sql_mode;
    +----------------------------------------------------------------+
    | @@sql_mode                           |
    +----------------------------------------------------------------+
    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +----------------------------------------------------------------+

    mysql5.0以上版本支持三种sql_mode模式

    ANSI模式

    宽松模式,对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。

    TRADITIONAL模式

    严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误。用于事物时,会进行事物的回滚。

    STRICT_TRANS_TABLES模式

    严格模式,进行数据的严格校验,错误数据不能插入,报error错误。

     

    1 ANSI模式

    在ANSI模式下,当我们插入数据时,未满足列长度要求时,数据同样会插入成功,但是对超出列长度的字段进行截断,同时报告warning警告。

    mysql> set @@sql_mode=ANSI;
    Query OK, 0 rows affected (0.00 sec)
    mysql> create table test(name varchar(4), pass varchar(4));
    Query OK, 0 rows affected (0.03 sec)
    mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
    Query OK, 2 rows affected, 2 warnings (0.02 sec)
    Records: 2 Duplicates: 0 Warnings: 2
    mysql> show warnings;
    +---------+------+-------------------------------------------+
    | Level  | Code | Message                  |
    +---------+------+-------------------------------------------+
    | Warning | 1265 | Data truncated for column 'name' at row 1 |
    | Warning | 1265 | Data truncated for column 'pass' at row 1 |
    +---------+------+-------------------------------------------+
    2 rows in set (0.00 sec)
    mysql> select * from test;
    +------+------+
    | name | pass |
    +------+------+
    | aaaa | aaaa |
    | bbbb | bbbb |
    +------+------+
    2 rows in set (0.00 sec)

    2 STRICT_TRANS_TABLES模式

    在STRICT_TRANS_TABLES模式下,当我们插入数据时,mysql会严格的进行数据的校验,当发现插入列值未满足要求,直接报告error错误,保证了错误数据无法插入到数据库中。

    mysql> set @@sql_mode=STRICT_TRANS_TABLES;
    Query OK, 0 rows affected (0.00 sec)
    mysql> create table test(name varchar(4), pass varchar(4));
    Query OK, 0 rows affected (0.02 sec)
    mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    mysql> show errors;
    +-------+------+------------------------------------------+
    | Level | Code | Message                 |
    +-------+------+------------------------------------------+
    | Error | 1406 | Data too long for column 'name' at row 1 |
    +-------+------+------------------------------------------+
    1 row in set (0.00 sec)
    mysql> select * from test;
    Empty set (0.00 sec)

    3 TRADITIONAL模式,初看结果是不是一样

    mysql> set @@sql_mode=TRADITIONAL;
    Query OK, 0 rows affected (0.00 sec)
    mysql> create table test(name varchar(4), pass varchar(4));
    Query OK, 0 rows affected (0.02 sec)
    mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    mysql> show errors;
    +-------+------+------------------------------------------+
    | Level | Code | Message                 |
    +-------+------+------------------------------------------+
    | Error | 1406 | Data too long for column 'name' at row 1 |
    +-------+------+------------------------------------------+
    1 row in set (0.00 sec)
    mysql> select * from test;
    Empty set (0.00 sec)

    但是,可以看看设置后的情况

    mysql> set @@sql_mode=TRADITIONAL;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select @@sql_mode\G
    *************************** 1. row ***************************
    @@sql_mode: 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
    1 row in set (0.00 sec)

    在TRADITIONAL模式下,对所有的事务存储引擎,非事务存储引擎检查,日期类型中的月和日部分不能包含0,不能有0这样的日期(0000-00-00),数据不能除0,禁止grant自动创建新用户等一些校验。

    最后:

    set @@只是在sessions级别设置的,要想所有的都生效,还是要设置配置文件

    vi /etc/my.cnf

    在[mysqld]下面添加如下列:

    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    #NO_ENGINE_SUBSTITUTION对于不存在的引擎就报错,不加的话,指定不支持的引擎时指定默认的innodb

    另外:sql_mode还有一个配置ONLY_FULL_GROUP_BY,这个表示采用group by帅选数据的时候只能查看新组内信息

    改模式之前的操作

    mysql> select * from employee group by post;
    +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
    | id | name  | sex  | age | hire_date | post                  | post_comment | salary   | office | depart_id |
    +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
    | 14 | 张野  | male  | 28 | 2016-03-11 | operation                | NULL     |  10000.13 |  403 |     3 |
    | 9 | 歪歪  | female | 48 | 2015-03-11 | sale                  | NULL     |  3000.13 |  402 |     2 |
    | 2 | alex  | male  | 78 | 2015-03-02 | teacher                 | NULL     | 1000000.31 |  401 |     1 |
    | 1 | egon  | male  | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使       | NULL     |  7300.33 |  401 |     1 |
    +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
    4 rows in set (0.00 sec)

    此时的sql_mode:

    mysql> select @@sql_mode;
    +----------------------------------------------------------------+
    | @@sql_mode                           |
    +----------------------------------------------------------------+
    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +----------------------------------------------------------------+
    1 row in set (0.00 sec)

    修改一下,退出再进入才会生效

    mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
    Query OK, 0 rows affected (0.00 sec)
    mysql> select @@sql_mode;
    +----------------------------------------------------------------+
    | @@sql_mode                           |
    +----------------------------------------------------------------+
    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +----------------------------------------------------------------+
    1 row in set (0.00 sec)
    mysql> exit
    Bye

    再次进入

    mysql> select @@sql_mode;
    +-----------------------------------------------------------------------------------+
    | @@sql_mode |
    +-----------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +-----------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    下面查看修改后的查看结果

    mysql> select * from employee group by post;  //只能查看post
    ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
    mysql> select post from employee group by post;
    +-----------------------------------------+
    | post                  |
    +-----------------------------------------+
    | operation                |
    | sale                  |
    | teacher                 |
    | 老男孩驻沙河办事处外交大使       |
    +-----------------------------------------+
    4 rows in set (0.00 sec)
    mysql> select id,post from employee group by post;
    ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
    mysql> select name,post from employee group by post,name; //根据group by 后面的选择查看
    +------------+-----------------------------------------+
    | name | post |
    +------------+-----------------------------------------+
    | 张野 | operation |
    | 程咬金 | operation |
    | 程咬铁 | operation |
    | 程咬铜 | operation |
    | 程咬银 | operation |
    | 丁丁 | sale |
    | 丫丫 | sale |
    | 星星 | sale |
    | 格格 | sale |
    | 歪歪 | sale |
    | alex | teacher |
    | jingliyang | teacher |
    | jinxin | teacher |
    | liwenzhou | teacher |
    | wupeiqi | teacher |
    | xiaomage | teacher |
    | yuanhao | teacher |
    | egon | 老男孩驻沙河办事处外交大使 |
    +------------+-----------------------------------------+
    18 rows in set (0.00 sec)