MySql修改大表

常见对表的ddl场景：

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

环境准备

创建数据库表

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;

创建批处理

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)

call init_data(1000000);

增加字段

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

删除字段

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

修改字段长度

-- 默认 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