杨建荣的学习笔记

每天坚持一点点,个人微信公众号: jianrong-notes, 个人邮箱:jeanrock@126.com

  • 博客访问: 13047393
  • 博文数量: 1399
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-14 23:24
  • 认证徽章:
个人简介

每日发文,或技术、或总结,偶有日间小事也以为记,谓之学习笔记,成年累月1300多天,中间几乎没有间断,要旨只有一个:学习交流,共同进步 。 学习笔记精华整理,个人新书《Oracle DBA工作笔记》已开售,在京东,当当,亚马逊,淘宝,天猫均有售,欢迎选购。

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(1399)

文章存档

2018年(12)

2017年(321)

2016年(358)

2015年(360)

2014年(278)

2013年(48)

2012年(21)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

分类: MySQL

   如果需要把一台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 */;所以一个看起来很简单的数据库重启工作可能带给我们的会有一些潜在的隐患。


阅读(2837) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册