三十五、MySQL基础系列笔记之SQL Mode

作者: 温新

分类: 【MySql】

阅读: 1608

时间: 2021-01-11 14:21:19

什么是SQL Mode(SQL模式)

SQL Mode是一组MySql支持的基本语法、数据校验的规则。

SQL Mode解决如下问题

  • 设置SQL Mode,用于完成不同严格程度的数据校验,有效的保障数据准确性;
  • 设置SQL Mode为ANSI模式,用于保证大多数SQL符合标准的SQL语法,可应用于不同数据库之间的迁移;
  • 在不同数据库之间进行数据迁移之前,通过设置SQL Mode可以使MySQL上的数据更方便地迁移到目标数据库中。

SQL Mode

举个栗子用于理解SQL Mode,来看看如下字段,长度为2

<span style="padding-right: 0.1px; box-sizing: border-box; color: rgb(198, 120, 221) !important;">create table t2(name varchar(2));</span><br></br>

MySQL5.7之前的版本,对于插入3个字是不会报错的,可以插入进去,但是MySQL5.7之后的版本则会直接报错。来看实际案例。

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> t2 (name) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> (<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'你好'</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> affected (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 可以看到,一旦超过3个字将直接报错</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> t2 (name) <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span> (<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'你好ya'</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">ERROR <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1406</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">22001</span>): <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Data</span> too <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">long</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">for</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">column</span> <span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'name'</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">at</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span></span>

SQL Mode演示

还是基于上面的t2表,演示不同的SQL Mode的效果。

案例一:取消sql mode

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">session</span> sql_mode=<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">''</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 查看是否设置成功</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">@@sql_mode</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">@@sql_mode</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">|            |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

插入一条超过字段长度限制的数据。

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> t2 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span>(<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'你好呀'</span>);      </span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> affected, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> warning (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> * <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">from</span> t2;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+--------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| name   |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+--------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| h2     |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| 你好   |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| 你好   |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+--------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">3</span> rows <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

可以看到,超过字段长度的数据插入成功,没有报错,但是超过长度的数据被截断了。

案例二:STRICT_TRANS_TABLES(严格事务表模式)

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">session</span> sql_mode=<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'STRICT_TRANS_TABLES'</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> rows affected, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> warning (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> t2 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span>(<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'自如初'</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">ERROR <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1406</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">22001</span>): <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Data</span> too <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">long</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">for</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">column</span> <span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'name'</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">at</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span></span>

可以看到,设置为STRICT_TRANS_TABLES模式时,超过字段长度的数据插入时将会报错。

关于修改sql_mode的方法,即设置当前会话或全局模式,具体可以参考 MySQL基础系列笔记之变量 这篇文章;本篇文章还是简单记录一下:

修改sql_mode的方式,set [global|session] sql_mode='modes'

session表示只在本次连接中生效;global表示在本次连接中并不生效,而对于新的连接生效。关于其他模式,多多练习。

案例三:ANSI模式

验证日期合法性。2021-02-30这个日期是错误的,2月没有30号。

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">create</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">table</span> <span style="box-sizing: border-box;color: rgb(224, 108, 117) !important">`t3`</span>(d <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">datetime</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 修改模式</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">session</span> sql_mode=<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'ANSI'</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 插入数据</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> t3 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span>(<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'2021-02-30'</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> affected, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> warning (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 查看</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> * <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">from</span> t3;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+---------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| d                   |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+---------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0000</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">00</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">00</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">00</span>:00:00 |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+---------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

可以神奇的看到,非法日期被插入成功了,但是,查询出来的日期值却变成了'0000-00-00 00:00:00'。

案例四:TRADITIONAL模式

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">session</span> sql_mode=<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'TRADITIONAL'</span>;</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> t3 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span>(<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'2021-02-30'</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">ERROR <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1292</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">22007</span>): Incorrect <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">datetime</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">value</span>: <span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'2021-02-30'</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">for</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">column</span> <span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'d'</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">at</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">into</span> t3 <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">values</span>(<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'2021-02-28'</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">Query</span> OK, <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> affected (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

该模式下,非法日期将无法插入。

常用的SQL Mode

对于表格显示出来的部分内容将就看吧,后期改改版,时间不定。

我是小白,期待和优秀的你一起同行!

小白

2021年01月11日

请登录后再评论