本文实例讲述了mysql中的sql_mode模式。分享给大家供大家参考,具体如下:
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)
更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]