mysql 宽松模式_mysql中的sql_mode模式实例详解

本文实例讲述了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数据库计有所帮助。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值