今天碰巧遇到一个以前忽视的问题,就是sql_mode对自增列值的影响,这里做个简单的测试:
(user:root time: 11:30)[db: lidan]show variables like '%mode%';
+--------------------------+---------------------+
| Variable_name | Value |
+--------------------------+---------------------+
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | STRICT_TRANS_TABLES |
+--------------------------+---------------------+
当前sql_mode 为STRICT_TRANS_TABLES
现在创建一张表自增列为id 如下:
(user:root time: 11:23)[db: lidan]create table a(id int auto_increment primary key);
Query OK, 0 rows affected (0.12 sec)
(user:root time: 11:24)[db: lidan]insert into a values(-1);
Query OK, 1 row affected (0.05 sec)
(user:root time: 11:24)[db: lidan]insert into a values(0);
Query OK, 1 row affected (0.05 sec)
然后查询结果:
(user:root time: 11:25)[db: lidan]select * from a;
+----+
| id |
+----+
| -1 |
| 1 |
+----+
发现插入负数不会触发自增长,而插入0则会,但是自增长又是从1开始的,所以这时候会出现上面的结果。
我们是从oracle -->mysql数据迁移的时候因为这个导致mysql的数据id都串了1位
解决方法:
设置会话sql_mode 为
set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_VALUE_ON_ZERO';
再次测试:
(user:root time: 11:31)[db: lidan]insert into a values(0);
Query OK, 1 row affected (0.04 sec)
(user:root time: 11:31)[db: lidan]insert into a values(-1);
Query OK, 1 row affected (0.05 sec)
(user:root time: 11:31)[db: lidan]select * from a;
+----+
| id |
+----+
| -1 |
| 0 |
+----+
这样就可以。
吐槽:mysql的各种坑,每天都让人提心吊胆。