MySQL自增列的重复值问题(r12笔记第25天)

如果需要把一台MySQL中的数据定期归档到另外一台MySQL历史库中,那么很可能会发现会有重复值的问题,导致数据导入会失败,而这个问题其实是和自增列的重复值有关,我们来简单看看。

这方面丁奇大师也做了很多详细的说明,还定制了参数,具体可以参见 http://www.csdn.net/article/2015-01-16/2823591

我们来看看这个问题,由此做一个简单的总结。

我们创建一个表t1,指定存储引擎为InnoDB

use test; [test]> drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.01 sec) > create table t1(id int auto_increment, a int, primary key (id)) engine=innodb; Query OK, 0 rows affected (0.02 sec)然后插入3条数据,第一条指定id为1,后面两条id值自增。

insert into t1 values (1,2); insert into t1 values (null,2); insert into t1 values (null,2);

数据的分布情况如下: [test]> select *from t1; +----+------+ | id | a | +----+------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | +----+------+

到此为止,我们的数据初始化工作就完成了。

这个时候使用show create table查看,定义信息中自增列的值为4,即再插入一条记录,id值为4.

> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

我们接着清理id为2和3的数据。

delete from t1 where id=2; delete from t1 where id=3;

在此吐槽一句,MySQL竟然能够支持下面这样的语句,我都方了。

[test]> delete from t1 where id; Query OK, 2 rows affected (0.00 sec)

当然我们继续往下做,查看删除数据之后的情况,只保留了一条id为1的数据。

> select * from t1; +----+------+ | id | a | +----+------+ | 1 | 2 | +----+------+ 1 row in set (0.00 sec)

接下来我们如果继续插入一条记录,那么id就会是4.

但是我们不这么做,我们重启MySQL。

service mysql stop service mysql start

然后插入一条记录,这个时候id值是从2开始计算了,而不是4.

insert into t1 values (null,2); [test]> select *from t1; +----+------+ | id | a | +----+------+ | 1 | 2 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)

这个时候如果查看表定义信息,就会发现自增列目前是3

> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

这是什么原因呢,如果你试试MyISAM,就不会出现这类问题,而对于InnoDB来说,它的自增列的实现在重启之后内存中肯定是没有了,它是根据max(id)+1的方式来计算的。

这个情况不光是在MySQL 5.5存在,在MySQL 5.7也依旧存在。

而这类问题是否在数据迁移中会出现呢,我们也需要注意一下。

比如我们使用mysqldump导出数据,然后导入到另外一个环境。

导出数据

mysqldump test t1 > t1.sql 导出的sql文本如下,可以看到里面是指定id值的方式,而非空。 LOCK TABLES `t1` WRITE; /*!40000 ALTER TABLE `t1` DISABLE KEYS */; INSERT INTO `t1` VALUES (1,2),(2,2); /*!40000 ALTER TABLE `t1` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

所以一个看起来很简单的数据库重启工作可能带给我们的会有一些潜在的隐患。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-04-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏用户画像

sql server 实验5.2 触发器的建立与使用

一、实验目的                                                     

20320
来自专栏Netkiller

数据库安全·Token 认证

以下节选择《Netkiller Architect 手札》 作者:netkiller 地址 http://www.netkiller.cn/archit...

39840
来自专栏杨建荣的学习笔记

巧用shell生成数据库检查脚本 (74天)

在生产环境中需要部署大量的数据变更。对于新增的表,需要注意权限和同义词等。但是手动去检查这些变更是否生效就很麻烦。而且也不易维护,比如写好了一个脚本,可能在过一...

34170
来自专栏程序猿

MySQL优化方案(一)优化SQL脚本与索引

MySQL的优化方案有哪一些? 本文记录MySQL优化方案 ,梗概如下: 优化SQL 优化索引 (一)优化SQL 1、通过MySQL自有的优化语句 优化SQL语...

45470
来自专栏轮子工厂

数据库性能优化,原来还可以有这种操作

7310
来自专栏Python

Innodb与Myisam引擎的区别与应用场景

1.区别: (1)事务处理: MyISAM是非事务安全型的,而InnoDB是事务安全型的(支持事务处理等高级处理); (2)锁机制不同: MyISAM是表级锁,...

40370
来自专栏码神联盟

mysql数据库常见锁机制

关于互联网常见层次架构,由于小编还没整理完毕(预计周四推送),先来一篇数据库的干货,来满足下大家的胃口,关于mysql的行级锁、表级锁、页级锁的分析,这个在行业...

43390
来自专栏PHP在线

MYSQL 优化常用方法

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得...

35540
来自专栏Rgc

mysql数据库优化(三)--分区

分区:把一个数据表的文件和索引分散存储在不同的物理文件中。 特点:业务层透明,无需任何修改,即使从新分表,也是在mysql层进行更改(业务层代码不动)

26730
来自专栏idba

死锁案例之二

一 前言 死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有...

10250

扫码关注云+社区

领取腾讯云代金券