阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL修改数据类型的问题总结

218次阅读
没有评论

共计 4946 个字符,预计需要花费 13 分钟才能阅读完成。

开发的同事找我说有个紧急需求,负责这个业务的 DBA 同事回家了,想让我帮忙看看,运行个 SQL 语句,几秒钟就好。我一听,就本着人道主义的精神留下来处理,但是发现似乎留给我的是一个大坑。

了解了问题之后,让我有些后背发凉,这个表根据开发同事反馈有 20 亿的数据,这得多大的一个表啊,当前的问题是这个表里的主键 id 数据类型是 int, 因为数据类型的限制已经达到了最大值,现在插入不了数据了。希望我帮忙处理一下,把数据类型修改为 bigint.

我们简单来了解一下 MySQL 的数据类型。

对于数据类型有下面的一些总结,更详细可以参见 http://www.linuxidc.com/Linux/2016-11/137176.htm。

类型大小范围(有符号)范围(无符号)用途
TINYINT1 字节(-128,127)(0,255)小整数值
SMALLINT2 字节(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 字节(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT 或 INTEGER4 字节(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 字节(-9 233 372 036 854 775 808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值

所以现在的 int 数据类型已经达到了最大值 2 147 483 647。

修改数据类型,扩展一般是可行的,但是这个环境 MySQL 版本还比较低,所以 pt-osc 的工具是别想了,而且 20 亿的数据就算处理也得耗上不少的时间。

简答了解了下问题,我一直纠结这个修改数据类型的操作影响时长。

20 亿的数据做这样的操作,想必经历的人也不会太多,偏偏当了友情支持,我登录到指定的环境,仔细一看,这个表原来没有 20 亿的数据,只是 id 递增到了 20 亿的级别,表里有几百万的数据,对应的数据文件看有 500M 左右,所以这个问题让我悬着的心终于踏实了一些。

# ll -h activity_dj_actor_info_log*

-rw-rw—- 1 mysql mysql 8.7K Sep 29  2014 activity_dj_actor_info_log.frm

-rw-rw—- 1 mysql mysql 560M Nov  4 19:05 activity_dj_actor_info_log.ibd

这个修改数据类型的操作持续了大概 1 分多钟就结束了。

提供的语句如下:

> ALTER TABLE activity_dj_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 的操作,证明在后台重建表数据。

MySQL 修改数据类型的问题总结

 修改完成之后查看,发现有个地方不对劲,怎么没有了 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

 > 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 还是存在一些区别,还是需要严格区别对待。

本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-11/137175.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4946字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中