MySQL中怎么更改数据类型
发布时间：2022-01-21 12:41:59 所属栏目：MySql教程 来源：互联网
导读：这期内容当中小编将会给大家带来有关MySQL中怎么修改数据类型，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。 类型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 字节 (-128，127) (0，255) 小整数值 SMALLINT
|
这期内容当中小编将会给大家带来有关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_logG *************************** 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_logG *************************** 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_logG *************************** 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) 上述就是小编为大家分享的MySQL中怎么修改数据类型了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。 （编辑：重庆站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
