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错误。
1ANSI模式
在ANSI模式下，当我们插入数据时，未满足列长度要求时，数据同样会插入成功，但是对超出列长度的字段进行截断，同时报告warning警告。
mysql>set@@sql_mode=ANSI;
QueryOK,0rowsaffected(0.00sec)
mysql>createtabletest(namevarchar(4),passvarchar(4));
QueryOK,0rowsaffected(0.03sec)
mysql>insertintotestvalues('aaaaa','aaaaa'),('bbbb','bbbb');
QueryOK,2rowsaffected,2warnings(0.02sec)
Records:2Duplicates:0Warnings:2
mysql>showwarnings;
+---------+------+-------------------------------------------+
|Level|Code|Message|
+---------+------+-------------------------------------------+
|Warning|1265|Datatruncatedforcolumn'name'atrow1|
|Warning|1265|Datatruncatedforcolumn'pass'atrow1|
+---------+------+-------------------------------------------+
2rowsinset(0.00sec)
mysql>select*fromtest;
+------+------+
|name|pass|
+------+------+
|aaaa|aaaa|
|bbbb|bbbb|
+------+------+
2rowsinset(0.00sec)
2STRICT_TRANS_TABLES模式
在STRICT_TRANS_TABLES模式下，当我们插入数据时，mysql会严格的进行数据的校验，当发现插入列值未满足要求，直接报告error错误，保证了错误数据无法插入到数据库中。
mysql>set@@sql_mode=STRICT_TRANS_TABLES;
QueryOK,0rowsaffected(0.00sec)
mysql>createtabletest(namevarchar(4),passvarchar(4));
QueryOK,0rowsaffected(0.02sec)
mysql>insertintotestvalues('aaaaa','aaaaa'),('bbbb','bbbb');
ERROR1406(22001):Datatoolongforcolumn'name'atrow1
mysql>showerrors;
+-------+------+------------------------------------------+
|Level|Code|Message|
+-------+------+------------------------------------------+
|Error|1406|Datatoolongforcolumn'name'atrow1|
+-------+------+------------------------------------------+
1rowinset(0.00sec)
mysql>select*fromtest;
Emptyset(0.00sec)
3TRADITIONAL模式，初看结果是不是一样
mysql>set@@sql_mode=TRADITIONAL;
QueryOK,0rowsaffected(0.00sec)
mysql>createtabletest(namevarchar(4),passvarchar(4));
QueryOK,0rowsaffected(0.02sec)
mysql>insertintotestvalues('aaaaa','aaaaa'),('bbbb','bbbb');
ERROR1406(22001):Datatoolongforcolumn'name'atrow1
mysql>showerrors;
+-------+------+------------------------------------------+
|Level|Code|Message|
+-------+------+------------------------------------------+
|Error|1406|Datatoolongforcolumn'name'atrow1|
+-------+------+------------------------------------------+
1rowinset(0.00sec)
mysql>select*fromtest;
Emptyset(0.00sec)
但是，可以看看设置后的情况
mysql>set@@sql_mode=TRADITIONAL; QueryOK,0rowsaffected(0.00sec) 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 1rowinset(0.00sec)
在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，这个表示采用groupby帅选数据的时候只能查看新组内信息
改模式之前的操作
mysql>select*fromemployeegroupbypost; +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ |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| +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 4rowsinset(0.00sec)
此时的sql_mode:
mysql>select@@sql_mode; +----------------------------------------------------------------+ |@@sql_mode| +----------------------------------------------------------------+ |STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION| +----------------------------------------------------------------+ 1rowinset(0.00sec)
修改一下，退出再进入才会生效
mysql>setglobalsql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'; QueryOK,0rowsaffected(0.00sec) mysql>select@@sql_mode; +----------------------------------------------------------------+ |@@sql_mode| +----------------------------------------------------------------+ |STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION| +----------------------------------------------------------------+ 1rowinset(0.00sec) mysql>exit Bye
再次进入
mysql>select@@sql_mode; +-----------------------------------------------------------------------------------+ |@@sql_mode| +-----------------------------------------------------------------------------------+ |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION| +-----------------------------------------------------------------------------------+ 1rowinset(0.00sec)
下面查看修改后的查看结果
mysql>select*fromemployeegroupbypost;//只能查看post ERROR1055(42000):'t1.employee.id'isn'tinGROUPBY mysql>selectpostfromemployeegroupbypost; +-----------------------------------------+ |post| +-----------------------------------------+ |operation| |sale| |teacher| |老男孩驻沙河办事处外交大使| +-----------------------------------------+ 4rowsinset(0.00sec) mysql>selectid,postfromemployeegroupbypost; ERROR1055(42000):'t1.employee.id'isn'tinGROUPBY mysql>selectname,postfromemployeegroupbypost,name;//根据groupby后面的选择查看 +------------+-----------------------------------------+ |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|老男孩驻沙河办事处外交大使| +------------+-----------------------------------------+ 18rowsinset(0.00sec)
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。