专栏首页杨建荣的学习笔记MySQL修改数据类型的问题总结(r10笔记第74天)

MySQL修改数据类型的问题总结(r10笔记第74天)

昨天快下班的时候,突然开发的同事找我说有个紧急需求,负责这个业务的DBA同事回家了,想让我帮忙看看,运行个SQL语句,几秒钟就好。我一听,就本着人道主义的精神留下来处理,但是发现似乎留给我的是一个大坑。 了解了问题之后,让我有些后背发凉,这个表根据开发同事反馈有20亿的数据,这得多大的一个表啊,当前的问题是这个表里的主键id数据类型是int,因为数据类型的限制已经达到了最大值,现在插入不了数据了。希望我帮忙处理一下,把数据类型修改为bigint. 我们简单来了解一下MySQL的数据类型。 对于数据类型有下面的一些总结,更详细可以参见之前写的一篇。MySQL数据类型(r3笔记第87天)

所以现在的int数据类型已经达到了最大值2 147 483 647。 修改数据类型,扩展一般是可行的,但是这个环境MySQL版本还比较低,所以pt-osc的工具是别想了,而且20亿的数据就算处理也得耗上不少的时间。 简答了解了下问题,我一直纠结这个修改数据类型的操作影响时长。 20亿的数据做这样的操作,想必经历的人也不会太多,偏偏当了友情支持,我登录到指定的环境,仔细一看,这个表原来没有20亿的数据,只是id递增到了20亿的级别,表里有几百万的数据,对应的数据文件看有500M左右,所以这个问题让我悬着的心终于踏实了一些。

# ll -h activity_actor_info_log*
-rw-rw---- 1 mysql mysql 8.7K Sep 29  2014 activity_actor_info_log.frm
-rw-rw---- 1 mysql mysql 560M Nov  4 19:05 activity_actor_info_log.ibd这个修改数据类型的操作持续了大概1分多钟就结束了。

提供的语句如下:

> ALTER TABLE activity_actor_info_log modify id  BIGINT;
Query OK, 3144626 rows affected (1 min 22.64 sec)
Records: 3144626  Duplicates: 0  Warnings: 0

查看线程的情况,可以看到存在这么一个copy to tmp table的操作,证明在后台重建表数据。

修改完成之后查看,发现有个地方不对劲,怎么没有了auto_increment的属性。

> show create table activity_dj_actor_info_log\G
*************************** 1. row ***************************
       Table: activity_dj_actor_info_log
Create Table: CREATE TABLE `activity_dj_actor_info_log` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `cnMaster` varchar(50) NOT NULL,
。。。
  PRIMARY KEY (`id`),
  UNIQUE KEY `dss_cnMaster` (`cnMaster`,`serverId`,`guid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)使用下面的方式修改,让字段id递增,竟然抛出了错误。
> ALTER TABLE activity_dj_actor_info_log modify id  BIGINT AUTO_INCREMENT;
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

就是这个错误让我纠结了半天。 而且稍后继续尝试,修改auto_increment的值,竟然没有反应。

> ALTER TABLE activity_dj_actor_info_log AUTO_INCREMENT=2147483649;
Query OK, 3144627 rows affected (1 min 20.65 sec)
Records: 3144627  Duplicates: 0  Warnings: 0查看表定义的情况:
> show create table activity_dj_actor_info_log\G
*************************** 1. row ***************************
       Table: activity_dj_actor_info_log
Create Table: CREATE TABLE `activity_dj_actor_info_log` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `cnMaster` varchar(50) NOT NULL,
 。。。
  PRIMARY KEY (`id`),
  UNIQUE KEY `dss_cnMaster` (`cnMaster`,`serverId`,`guid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

这问题就很纠结了,修改成功,但是查看表定义没有生效,查看数据字典里的递增序列值还是NULL,证明自增序列没有生效。

> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name="activity_dj_actor_info_log";
+----------------+
| AUTO_INCREMENT |
+----------------+
|           NULL |
+----------------+
2 rows in set (0.00 sec)在经过几次尝试之后,最后是采用下面的方式才修复了这个问题。
> alter table `activity_dj_actor_info_log` change `id` `id` bigint  
NOT NULL AUTO_INCREMENT , drop primary key,add primary key(id);
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
> alter table `activity_dj_actor_info_log`  drop primary key;
Query OK, 3144627 rows affected (1 min 13.75 sec)
Records: 3144627  Duplicates: 0  Warnings: 0

> alter table `activity_dj_actor_info_log` change `id` `id` bigint  NOT NULL AUTO_INCREMENT , add primary key(id);
Query OK, 3144627 rows affected (1 min 32.32 sec)
Records: 3144627  Duplicates: 0  Warnings: 0

而修复之后,再次查看auto_increment就没有问题了。

> show create table activity_dj_actor_info_log\G
*************************** 1. row ***************************
       Table: activity_dj_actor_info_log
Create Table: CREATE TABLE `activity_dj_actor_info_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cnMaster` varchar(50) NOT NULL,
  。。。
  PRIMARY KEY (`id`),
  UNIQUE KEY `dss_cnMaster` (`cnMaster`,`serverId`,`guid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2150192178 DEFAULT CHARSET=utf8和开发的同事简单沟通之后,没过一会查看就发现数值是递增了。
> select max(id) from activity_dj_actor_info_log;
+------------+
| max(id)    |
+------------+
| 2150195418 |
+------------+

而对于这个问题,自己也简单总结了下,其实最开始处理的时候就不严谨,导致了后面的不断修复,如果一步到位就不会有这么多的麻烦了。 所以在本地有简单测试了下。

CREATE TABLE `activity_dj_actor_info_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cnMaster` varchar(50) NOT NULL,
。。。
  PRIMARY KEY (`id`),
  UNIQUE KEY `dss_cnMaster` (`cnMaster`,`serverId`,`guid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8;插入一部分测试数据。
> insert into activity_dj_actor_info_log select *from activity_log.activity_dj_actor_info_log limit 1,1000;
Query OK, 1000 rows affected (0.07 sec)
Records: 1000  Duplicates: 0  Warnings: 0修改表字段数据类型
> alter table activity_dj_actor_info_log modify  `id` bigint  NOT NULL AUTO_INCREMENT;
Query OK, 1000 rows affected (0.43 sec)
Records: 1000  Duplicates: 0  Warnings: 0再次查看递增序列就修改完善了。
> show create table activity_dj_actor_info_log;
| Table                      | Create Table       
| activity_dj_actor_info_log | CREATE TABLE `activity_dj_actor_info_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cnMaster` varchar(50) NOT NULL,
  。。。
  PRIMARY KEY (`id`),
  UNIQUE KEY `dss_cnMaster` (`cnMaster`,`serverId`,`guid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8 
1 row in set (0.00 sec)在这一点上,Oracle的处理和MySQL还是存在一些区别,还是需要严格区别对待。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes),作者:杨建荣

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-11-05

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL分布式管理初步设计

    中间件方案对于业务的使用相对是透明的,而且扩展性相对较好,这里说较好,是基于良好的架构设计,对于弹性伸缩的支持还是有限的。

    jeanron100
  • 通过shell定制ash脚本(r3笔记第33天)

    ash是在10g以来一个很有用的特性,能够作为awr的补充,对于排查一些历史的问题能够提供更加详细和针对性的数据。 当然个人在使用ash的时候感觉最慢的地方就是...

    jeanron100
  • Oracle和MySQL的数据导入,差别为什么这么大

    经常会有一些朋友咨询我一些数据库的问题,我注意到一个很有意思的现象,凡是数据导入的问题,基本上都是Oracle类的,MySQL类的问题脑子里想了下竟然一次都没有...

    jeanron100
  • 宝塔Windows面板的安装Discuz! Q方法

    Web服务器推荐启用SSL(https协议),如果没有SSL证书腾讯云免费申请一个 或者宝塔直接生成

    砸漏
  • 前途未卜的智能音箱,语音助手还差一个杀手级应用

    近日,亚马逊推出了一款可以用语音控制的智能微波炉。至此,亚马逊推出的由 Alexa 语音控制的智能家居产品的数量已累计超过十种。由此,我们可以看到亚马逊推广自家...

    AI科技大本营
  • 中国的智能音箱泡沫,90%玩家或在明年消亡

    允中 编译自 WSJ,原文作者:Li Yuan 量子位 出品 | 公众号 QbitAI ? 美国市场上,AI驱动的智能音箱是亚马逊最热门的消费级产品之一,Goo...

    量子位
  • 【直播】我的基因组55:简单的PCA分析千人基因组的人群分布

    好久不见,我们的直播又开始啦!今天,我们主要讲的是人群分布,先用简单的PCA来分析一下千人基因组的人群分布吧! PCA分析,就是主成分分析,我博客有讲过(点击最...

    生信技能树
  • php基于Redis消息队列实现的消息推送的办法

    以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持网站事(zalou.cn)。

    砸漏
  • php基于Redis消息队列实现的消息推送的方法

    砸漏
  • Oracle 参数文件及相关操作介绍

    服务器参数文件是一个二进制文件,作为初始化参数的存储仓库。实例运行时,可用ALTER SYSTEM来改变参数值,且任何对初始化参数的改变设置不会随实例的关闭、...

    授客

扫码关注云+社区

领取腾讯云代金券