MySQL中怎么修改数据类型

45次阅读

共计 4594 个字符，预计需要花费 12 分钟才能阅读完成。

这期内容当中丸趣 TV 小编将会给大家带来有关 MySQL 中怎么修改数据类型，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

类型
范围（有符号）
范围（无符号）
TINYINT
1 字节
(-128，127)
(0，255)
小整数值
SMALLINT
2 字节
(-32 768，32 767)
(0，65 535)
大整数值
MEDIUMINT
3 字节
(-8 388 608，8 388 607)
(0，16 777 215)
大整数值
INT 或 INTEGER
4 字节
(-2 147 483 648，2 147 483 647)
(0，4 294 967 295)
大整数值
BIGINT
8 字节
(-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 的操作，证明在后台重建表数据。

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