mysql中的sql_mode模式实例详解
16lz
2021-12-19
本文实例讲述了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 |+----------------------------------------------------------------+
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: 2mysql> 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)
在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 1mysql> 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> 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 1mysql> 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_SUBSTITUTION1 row in set (0.00 sec)
最后:
set @@只是在sessions级别设置的,要想所有的都生效,还是要设置配置文件
vi /etc/my.cnf
在[mysqld]下面添加如下列:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
另外: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)
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> exitBye
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; //只能查看postERROR 1055 (42000): 't1.employee.id' isn't in GROUP BYmysql> 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 BYmysql> 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系列多表连接查询92及99语法示例详解教程
- 《Android和PHP最佳实践》官方站
- android用户界面之按钮(Button)教程实例汇
- Android(安卓)- Manifest 文件 详解
- TabHost与RadioGroup结合完成的菜单【带效果图】5个Activity
- Android的Handler机制详解3_Looper.looper()不会卡死主线程
- Android(安卓)UI开发第十七篇——Android(安卓)Fragment实例(Lis
- Android——Activity四种启动模式
- Selector、shape详解(一)