系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

mysql中的sql_mode模式实例详解

时间:2020-01-03来源:系统城作者:电脑系统城

本文实例讲述了mysql中的sql_mode模式。分享给大家供大家参考,具体如下:

mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法,数据校验等!我们可以通过以下方式查看当前数据库使用的sql_mode:


 
  1. mysql> select @@sql_mode;
  2. +----------------------------------------------------------------+
  3. | @@sql_mode |
  4. +----------------------------------------------------------------+
  5. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  6. +----------------------------------------------------------------+

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

 

ANSI模式

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

TRADITIONAL模式

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

STRICT_TRANS_TABLES模式

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

 

 

1 ANSI模式

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


 
  1. mysql> set @@sql_mode=ANSI;
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> create table test(name varchar(4), pass varchar(4));
  4. Query OK, 0 rows affected (0.03 sec)
  5. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
  6. Query OK, 2 rows affected, 2 warnings (0.02 sec)
  7. Records: 2 Duplicates: 0 Warnings: 2
  8. mysql> show warnings;
  9. +---------+------+-------------------------------------------+
  10. | Level | Code | Message |
  11. +---------+------+-------------------------------------------+
  12. | Warning | 1265 | Data truncated for column 'name' at row 1 |
  13. | Warning | 1265 | Data truncated for column 'pass' at row 1 |
  14. +---------+------+-------------------------------------------+
  15. 2 rows in set (0.00 sec)
  16. mysql> select * from test;
  17. +------+------+
  18. | name | pass |
  19. +------+------+
  20. | aaaa | aaaa |
  21. | bbbb | bbbb |
  22. +------+------+
  23. 2 rows in set (0.00 sec)
  24.  

2 STRICT_TRANS_TABLES模式

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


 
  1. mysql> set @@sql_mode=STRICT_TRANS_TABLES;
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> create table test(name varchar(4), pass varchar(4));
  4. Query OK, 0 rows affected (0.02 sec)
  5. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
  6. ERROR 1406 (22001): Data too long for column 'name' at row 1
  7. mysql> show errors;
  8. +-------+------+------------------------------------------+
  9. | Level | Code | Message |
  10. +-------+------+------------------------------------------+
  11. | Error | 1406 | Data too long for column 'name' at row 1 |
  12. +-------+------+------------------------------------------+
  13. 1 row in set (0.00 sec)
  14. mysql> select * from test;
  15. Empty set (0.00 sec)
  16.  

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


 
  1. mysql> set @@sql_mode=TRADITIONAL;
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> create table test(name varchar(4), pass varchar(4));
  4. Query OK, 0 rows affected (0.02 sec)
  5. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
  6. ERROR 1406 (22001): Data too long for column 'name' at row 1
  7. mysql> show errors;
  8. +-------+------+------------------------------------------+
  9. | Level | Code | Message |
  10. +-------+------+------------------------------------------+
  11. | Error | 1406 | Data too long for column 'name' at row 1 |
  12. +-------+------+------------------------------------------+
  13. 1 row in set (0.00 sec)
  14. mysql> select * from test;
  15. Empty set (0.00 sec)
  16.  

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


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

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

最后:

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

vi /etc/my.cnf

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


 
  1. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
  2.  

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

 

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

改模式之前的操作


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

此时的sql_mode:


 
  1. mysql> select @@sql_mode;
  2. +----------------------------------------------------------------+
  3. | @@sql_mode |
  4. +----------------------------------------------------------------+
  5. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  6. +----------------------------------------------------------------+
  7. 1 row in set (0.00 sec)
  8.  

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


 
  1. mysql> set globalsql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> select @@sql_mode;
  4. +----------------------------------------------------------------+
  5. | @@sql_mode |
  6. +----------------------------------------------------------------+
  7. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  8. +----------------------------------------------------------------+
  9. 1 row in set (0.00 sec)
  10. mysql> exit
  11. Bye
  12.  

再次进入


 
  1. mysql> select @@sql_mode;
  2. +-----------------------------------------------------------------------------------+
  3. | @@sql_mode |
  4. +-----------------------------------------------------------------------------------+
  5. |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  6. +-----------------------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)
  8.  

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


 
  1. mysql> select * from employee group by post; //只能查看post
  2. ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
  3. mysql> select post from employee group by post;
  4. +-----------------------------------------+
  5. | post |
  6. +-----------------------------------------+
  7. | operation |
  8. | sale |
  9. | teacher |
  10. | 老男孩驻沙河办事处外交大使 |
  11. +-----------------------------------------+
  12. 4 rows in set (0.00 sec)
  13. mysql> select id,post from employee group by post;
  14. ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
  15. mysql> select name,post from employee group by post,name; //根据group by 后面的选择查看
  16. +------------+-----------------------------------------+
  17. | name | post |
  18. +------------+-----------------------------------------+
  19. | 张野 | operation |
  20. | 程咬金 | operation |
  21. | 程咬铁 | operation |
  22. | 程咬铜 | operation |
  23. | 程咬银 | operation |
  24. | 丁丁 | sale |
  25. | 丫丫 | sale |
  26. | 星星 | sale |
  27. | 格格 | sale |
  28. | 歪歪 | sale |
  29. | alex | teacher |
  30. | jingliyang | teacher |
  31. | jinxin | teacher |
  32. | liwenzhou | teacher |
  33. | wupeiqi | teacher |
  34. | xiaomage | teacher |
  35. | yuanhao | teacher |
  36. | egon | 老男孩驻沙河办事处外交大使 |
  37. +------------+-----------------------------------------+
  38. 18 rows in set (0.00 sec)
  39.  

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载