Mysql对存有大数据表进行结构修改需要注意的问题

	-- 1. 要修改结构的大数据表
	DROP TABLE
	IF EXISTS USER;
	
	CREATE TABLE `user` (
		`id` INT (11) NOT NULL AUTO_INCREMENT,
		`username` VARCHAR (32) NOT NULL DEFAULT '',
		`age` INT (11) NOT NULL DEFAULT '-1',
		PRIMARY KEY (`id`)
	) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '用户表';
	
	-- 2. 新表结构
	DROP TABLE
	IF EXISTS user_tmp;
	
	CREATE TABLE `user_tmp` (
		`id` INT (11) NOT NULL AUTO_INCREMENT,
		`username` VARCHAR (32) NOT NULL DEFAULT '',
		`age` INT (11) NOT NULL DEFAULT '-1',
		PRIMARY KEY (`id`),
		INDEX `index_username_age` (`username`, `age`)
	) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '用户表';
	
	-- 3.创建存储过程 max_id为 去最近更新的一条数据的id 11265265
	DELIMITER $$
	
	
	DROP PROCEDURE
	IF EXISTS copy_data ; CREATE PROCEDURE copy_data ()
	BEGIN
	
	DECLARE limitSize BIGINT ;
	DECLARE beginId BIGINT ;
	DECLARE endId BIGINT ;
	DECLARE maxId BIGINT ;
	SET limitSize = 5000 ;
	SET beginId = 0 ;
	SET endId = limitSize ;
	SET maxId = 11265265 ;
	WHILE beginId < maxId DO
	
	IF endId > maxId THEN
	
	SET endId = maxId ;
	END
	IF ; INSERT INTO user_tmp (`id`, `username`, `age`) SELECT
		`id`,
		`username`,
		`age`
	FROM
		USER
	WHERE
		id >= beginId
	AND id < endId ;
	SET beginId = endId ;
	SET endId = endId + limitSize ;
	END
	WHILE ;
	END ;$$
	 
	DELIMITER ;
	
	
	-- 4.执行存储过程
	CALL copy_data ();
	
	-- 5. 同步剩下的数据，并修改表名称，将临时表修改为新表
	START TRANSACTION;
	
	INSERT INTO user_tmp (`id`, `username`, `age`) SELECT
		`id`,
		`username`,
		`age`
	FROM
		USER
	WHERE
		id >= 1265265;
	
	ALTER TABLE USER RENAME user_old;
	
	ALTER TABLE user_tmp RENAME USER;
	
	COMMIT;