梦想还是要有的，万一忘了咋办？

0%

MySql修改大表

发表于 分类于
本文字数： 1.7k 阅读时长 ≈ 2 分钟

常见对表的ddl场景：

  • 增加字段
  • 删除字段
  • 修改字段类型
  • 修改字段长度

环境准备

创建数据库表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use test;
DROP TABLE if EXISTS `xxxxx` ;

CREATE TABLE `xxxxx` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `c1` int NOT NULL DEFAULT '0',
  `c2` int unsigned DEFAULT NULL,
  `c5` int unsigned NOT NULL DEFAULT '0',
  `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c4` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `c6` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_c1` (`c1`),
  KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

创建批处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

DROP PROCEDURE
IF
	EXISTS `init_data`;
	
delimiter $$ 
CREATE PROCEDURE `init_data` ( IN row_num INTEGER ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < row_num DO
			INSERT INTO helei ( c1, c2, c5, c3, c4 )
		VALUES
			(
			FLOOR(RAND()*row_num),
			FLOOR(RAND()*row_num),
			FLOOR(RAND()*row_num),
			now(),
			REPEAT('su',FLOOR(RAND()*20))
			);
	set  i = i+1;
	END WHILE;

END$$
delimiter ;

生成数据(100w)

1
call init_data(1000000);

增加字段

1
2
3
alter  table  helei  add  c6  varchar (60)  not  null  default  '' ;
-- mysql8.xx ，29ms
-- mysql5.7xx , 11.xx s

删除字段

1
2
3
alter  table  helei  DROP  c7  ;
-- mysql8.xx , 4.xx 秒
-- mysql5.7xx , 11.xx s

修改字段长度

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 默认 COPY模式
alter  table  helei   modify  c6  varchar (80)  not  null  default  '' ;
-- mysql8.xx  13.63 s
-- mysql5.7xx  13.57 s


-- INPLACE模式
-- 不能变短、只能增长
alter  table  helei ALGORITHM=INPLACE,  modify  c6  varchar (120)  not  null  default  '' ;
-- mysql8.xx  8ms ,
-- mysql5.7xx  ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

总结

  • 服务可以停止

    停止服务直接执行修改、慢慢等待就好，想要提速的可以升级mysql到8.x ；

  • 不可以停止服务

    使用工具pt-online-schema-change或者gh-ost

    不想用工具时：新表-修改字段-导入全量数据-导入新增数据- rename

相关文章