当前位置：Gxlcms > 数据库问题 > mysql的sql_mode模式

mysql的sql_mode模式

时间：2021-07-01 10:21:17 帮助过：3人阅读

show variables like ‘sql_mode‘;+---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> set @@sql_mode=ANSI; Query OK, 0 rows affected (0.00 sec) mysql> show variables like ‘sql_mode‘; +---------------+-------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------+ | sql_mode | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI | +---------------+-------------------------------------------------------------+ 1 row in set (0.00 sec)

这是设置之后的sql_mode.然后我们建立表格插入数据.

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> tt(name <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">4</span>), pass <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">));
  2. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.09</span><span style="color: #000000;"> sec)
  3. mysql</span><span style="color: #808080;">></span><span style="color: #000000;"> 
  4. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> tt <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">aaaaa</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">aaaaa</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bbbb</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bbbb</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  5. Query OK, </span><span style="color: #800000; font-weight: bold;">2</span> rows affected, <span style="color: #800000; font-weight: bold;">2</span> warnings (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  6. Records: </span><span style="color: #800000; font-weight: bold;">2</span>  Duplicates: <span style="color: #800000; font-weight: bold;">0</span>  Warnings: <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">
  7. mysql</span><span style="color: #808080;">></span><span style="color: #000000;"> show warnings;
  8. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+------+-------------------------------------------+</span>
  9. <span style="color: #808080;">|</span> <span style="color: #0000ff;">Level</span>   <span style="color: #808080;">|</span> Code <span style="color: #808080;">|</span> Message                                   <span style="color: #808080;">|</span>
  10. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+------+-------------------------------------------+</span>
  11. <span style="color: #808080;">|</span> Warning <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1265</span> <span style="color: #808080;">|</span> Data truncated <span style="color: #0000ff;">for</span> <span style="color: #0000ff;">column</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">name</span><span style="color: #ff0000;">‘</span> at row <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span>
  12. <span style="color: #808080;">|</span> Warning <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1265</span> <span style="color: #808080;">|</span> Data truncated <span style="color: #0000ff;">for</span> <span style="color: #0000ff;">column</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">pass</span><span style="color: #ff0000;">‘</span> at row <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span>
  13. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+------+-------------------------------------------+</span>
  14. <span style="color: #800000; font-weight: bold;">2</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  15. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> tt;
  16. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+</span>
  17. <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> pass <span style="color: #808080;">|</span>
  18. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+</span>
  19. <span style="color: #808080;">|</span> aaaa <span style="color: #808080;">|</span> aaaa <span style="color: #808080;">|</span>
  20. <span style="color: #808080;">|</span> bbbb <span style="color: #808080;">|</span> bbbb <span style="color: #808080;">|</span>
  21. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+</span>
  22. <span style="color: #800000; font-weight: bold;">2</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)

这时候发现数据可以插入成功，只是产生了2条waring数据，数据也被截取过了。

上面的是插入对应相同列的数据，我们再试下只插入一列数据的情况

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> tt(name) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">eeee</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2eeee</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  2. Query OK, </span><span style="color: #800000; font-weight: bold;">2</span> rows affected, <span style="color: #800000; font-weight: bold;">1</span> warning (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)
  3. Records: </span><span style="color: #800000; font-weight: bold;">2</span>  Duplicates: <span style="color: #800000; font-weight: bold;">0</span>  Warnings: <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
  4. mysql</span><span style="color: #808080;">></span><span style="color: #000000;"> show warnings;
  5. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+------+-------------------------------------------+</span>
  6. <span style="color: #808080;">|</span> <span style="color: #0000ff;">Level</span>   <span style="color: #808080;">|</span> Code <span style="color: #808080;">|</span> Message                                   <span style="color: #808080;">|</span>
  7. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+------+-------------------------------------------+</span>
  8. <span style="color: #808080;">|</span> Warning <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">1265</span> <span style="color: #808080;">|</span> Data truncated <span style="color: #0000ff;">for</span> <span style="color: #0000ff;">column</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">name</span><span style="color: #ff0000;">‘</span> at row <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span>
  9. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+------+-------------------------------------------+</span>
  10. <span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  11. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> tt;
  12. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+</span>
  13. <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> pass <span style="color: #808080;">|</span>
  14. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+</span>
  15. <span style="color: #808080;">|</span> aaaa <span style="color: #808080;">|</span> aaaa <span style="color: #808080;">|</span>
  16. <span style="color: #808080;">|</span> bbbb <span style="color: #808080;">|</span> bbbb <span style="color: #808080;">|</span>
  17. <span style="color: #808080;">|</span> eeee <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span> <span style="color: #808080;">|</span>
  18. <span style="color: #808080;">|</span> 2eee <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span> <span style="color: #808080;">|</span>
  19. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+</span>
  20. <span style="color: #800000; font-weight: bold;">4</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)

如果我们只设置了第一列name的value，而且另外一个列pass并没有设置为allow null的情况下，数据也是可以插入成功的。但是也有2条warning，而且数据也是被截取过的。

那接下来我们设置STRICT_TRANS_TABLES模式：

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">set</span> <span style="color: #008000; font-weight: bold;">@@sql_mode</span><span style="color: #808080;">=</span><span style="color: #000000;">STRICT_TRANS_TABLES;
  2. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  3. mysql</span><span style="color: #808080;">></span> show variables <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sql_mode</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  4. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+---------------------+</span>
  5. <span style="color: #808080;">|</span> Variable_name <span style="color: #808080;">|</span> Value               <span style="color: #808080;">|</span>
  6. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+---------------------+</span>
  7. <span style="color: #808080;">|</span> sql_mode      <span style="color: #808080;">|</span> STRICT_TRANS_TABLES <span style="color: #808080;">|</span>
  8. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+---------------------+</span>
  9. <span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)

重复我们刚刚插入数据的方式

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> tt <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">aaaaa</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">aaaaa</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bbbb</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bbbb</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  2. ERROR </span><span style="color: #800000; font-weight: bold;">1406</span> (<span style="color: #800000; font-weight: bold;">22001</span>): Data too <span style="color: #0000ff;">long</span> <span style="color: #0000ff;">for</span> <span style="color: #0000ff;">column</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">name</span><span style="color: #ff0000;">‘</span> at row <span style="color: #800000; font-weight: bold;">1</span>

发现SQL直接报错了，提示数据太长而不能插入.

修改数据插入

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> test <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">aaaa</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">aaaa</span><span style="color: #ff0000;">‘</span>),(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bbbbb</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bbbbb</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  2. ERROR </span><span style="color: #800000; font-weight: bold;">1406</span> (<span style="color: #800000; font-weight: bold;">22001</span>): Data too <span style="color: #0000ff;">long</span> <span style="color: #0000ff;">for</span> <span style="color: #0000ff;">column</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">name</span><span style="color: #ff0000;">‘</span> at row <span style="color: #800000; font-weight: bold;">2</span>
  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> test;
  2. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+</span>
  3. <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> pass <span style="color: #808080;">|</span>
  4. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+</span>
  5. <span style="color: #808080;">|</span> aaaa <span style="color: #808080;">|</span> aaaa <span style="color: #808080;">|</span>
  6. <span style="color: #808080;">|</span> bbbb <span style="color: #808080;">|</span> bbbb <span style="color: #808080;">|</span>
  7. <span style="color: #808080;">|</span> eeee <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span> <span style="color: #808080;">|</span>
  8. <span style="color: #808080;">|</span> 2eee <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span> <span style="color: #808080;">|</span>
  9. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+</span>
  10. <span style="color: #800000; font-weight: bold;">4</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)

发现数据都是插入不成功的。那我们再试试刚刚只插入1列数据的情况呢

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> test(name) value(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">wmq</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  2. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> row affected (<span style="color: #800000; font-weight: bold;">0.03</span><span style="color: #000000;"> sec)
  3. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> test;
  4. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+-------+</span>
  5. <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> pass <span style="color: #808080;">|</span> value <span style="color: #808080;">|</span>
  6. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+-------+</span>
  7. <span style="color: #808080;">|</span> test <span style="color: #808080;">|</span>      <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span>
  8. <span style="color: #808080;">|</span> wmq  <span style="color: #808080;">|</span>      <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span>
  9. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+------+-------+</span>
  10. <span style="color: #800000; font-weight: bold;">2</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)

也是可以插入成功的，所以我可以自动，sql mode跟字段是否匹配没有关系。但是我们查看table info的create SQL可以发现，有的MySQL客户端

在建立表的时候，会给字段默认加上defalut ‘‘ 这个值，例如：

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;"> `test1` (
  2.   `name` </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">4</span>) <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">
  3. ) ENGINE</span><span style="color: #808080;">=</span>InnoDB <span style="color: #0000ff;">DEFAULT</span> CHARSET<span style="color: #808080;">=</span>utf8;

和

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;"> `test2` (
  2.   `name` </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">4</span>) <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">DEFAULT</span> <span style="color: #ff0000;">‘‘</span><span style="color: #000000;">,</span><span style="color: #000000;">
  3. ) ENGINE</span><span style="color: #808080;">=</span>InnoDB <span style="color: #0000ff;">DEFAULT</span> CHARSET<span style="color: #808080;">=</span>utf8;

这是在我客户端和我同事的客户端上建表的create语句。有的客户端会让你选择default custom 有的会显示无默认值。但是我电脑的客户端就没有让你选择

default ‘‘的这个选项。技术分享

这点以后一定要非常注意，最好是统一使用相同的MySQL客户端或者干脆禁用远程账户，统一使用PMA或者命令行，这里有点偏题了

最后我们设置TRADITIONAL：

  1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">set</span> <span style="color: #008000; font-weight: bold;">@@sql_mode</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">TRADITIONAL</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  2. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)
  3. mysql</span><span style="color: #808080;">></span> show variables <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sql_mode</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  4. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+------------------------------------------------------------------------------------------------------------------------------------------------------+</span>
  5. <span style="color: #808080;">|</span> Variable_name <span style="color: #808080;">|</span> Value                                                                                                                                                <span style="color: #808080;">|</span>
  6. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+------------------------------------------------------------------------------------------------------------------------------------------------------+</span>
  7. <span style="color: #808080;">|</span> sql_mode      <span style="color: #808080;">|</span> 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 <span style="color: #808080;">|</span>
  8. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------------+------------------------------------------------------------------------------------------------------------------------------------------------------+</span>
  9. <span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)

发现和STRICT_TRANS_TABLES一样

 

mysql的sql_mode模式

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！