什么是PREPARE？

在MySQL4.1版本之前查询文本将以字符串的形式发送到MySQL服务器进行一系列解析执行。MySQL4.1版本之后MySQL客户端必须完全解析完SQL再发到服务器执行，这就关系到了一个关键字PREPARE，翻译过来就是做准备的意思，MySQL利用该关键字实现的主要功能是将字符串进行预编译准备，并且可支持占位符的使用，最终将准备好的可执行语句发送服务器执行完再将结果集进行解析转换成字符串后返回到客户端。

PREPARE使用介绍

PREPARE的还需结合EXECUTE进行使用，PREPARE的作用仅仅是把字符串预编译定义成可执行SQL语句，最终要执行语句得到结果还需要将预编译后的可执行语句交给EXECUTE执行，最终执行完成后我们还需将定义好的可执行变量通过DEALLOCATE PREPARE进行释放，防止其他地方误操作使用到定义的语句。

• PREPARE：预编译定义可执行SQL。
• EXECUTE：执行由PREPARE语句定义的语句。
• DEALLOCATE PREPARE：释放PREPARE语句。

为了方便介绍，我们先创建一个表并添加一些数据做测试。

DROP TABLE IF EXISTS student;
CREATE TABLE student(
	id BIGINT,
	`name` VARCHAR(50),
	age INT(3),
	state CHAR(1),
	PRIMARY KEY (id)
);
-- 初始化数据
INSERT INTO student
VALUES
(1, '张三', 18, 'A'),
(2, '李四', 17, 'A'),
(3, '王五', 18, 'A');

• 步骤一：由上面初始化好的测试数据，使用PREPARE做个简单的测试，用字符串定义一个SQL查询学生信息，并使用占位符?用于变量替换使用：

PREPARE selectSql from 'select * from student where id = ?';

• 步骤二：使用EXECUTE对PREPARE定义的语句使用变量进行执行：

SET @id = 1;
EXECUTE selectSql USING @id;

执行结果如下图：

• 步骤三：使用DEALLOCATE PREPARE对定义的语句进行释放：

DEALLOCATE PREPARE selectSql;

释放前的语句可在步骤二重复使用，释放后即销毁，需要再次使用必须在步骤一重新进行定义。

实现通用数据分批处理案例

在这里我们主要以大批量数据更新或者迁移的场景为例，做一个分批次迁移与更新数据的案例。

• 首先进行一批量数据的初始化：

DROP PROCEDURE IF EXISTS init_student;
DELIMITER $$
CREATE PROCEDURE init_student()
BEGIN
	DELETE FROM student;
	SET @p = 1;
        // 测试数据数量自己定
	WHILE @p < 2345678 DO
		INSERT INTO student
		VALUES(@p, CONCAT('user', @p * 1000000), 18, 'A');
		SET @p = @p + 1;
	END WHILE;
END $$
DELIMITER ;

CALL init_student();

• 分批次更新状态为B： 定义一个存储过程，创建一个临时表，根据所指定源数据表的主键索引加上偏移量取一个批量的数据放入临时表，再根据临时表的数据使用in与源表关联做更新，此操作同时也适用insert操作。过程中拼接完SQL字符串后使用PREPARE进行预编译，紧接着将执行该sql，最后再释放定义好的sql。主要步骤如下：

• 1.初步偏移量为0，按偏移量进行limit查询一个批次的数据主键存入临时表。
• 2.取临时表主键最大值作为下一批数据的偏移量。
• 3.需要update或insert的语句根据临时表的的主键进行in操作处理此批次数据。
• 4.清空临时表，重复第1、2、3步操作，直到偏移量取值为0，即取不到临时表数据结束循环，任务执行结束。

-- 按主键划分批次大小
DROP PROCEDURE IF EXISTS update_id_batch;
DELIMITER $$
CREATE PROCEDURE update_id_batch(IN tableName VARCHAR(50), IN primaryKey VARCHAR(50), IN updateSql VARCHAR(500), IN isWhere CHAR(1), IN batchSize INT)
BEGIN
        -- 标记是否添加where
	SET @wherePrefix = ' AND ';
	IF isWhere = 'Y' THEN
		SET @wherePrefix = ' WHERE ';
	END IF;
	-- 临时表存储批次主键
	DROP TABLE IF EXISTS tmp_key_tb;
	CREATE TABLE tmp_key_tb(
	  `id` BIGINT(20)
	);

	SET @maxId = 0;
	-- 拼接sql字符串将数据插入临时表
	SET @selectSql = CONCAT('insert into tmp_key_tb select ', primaryKey, ' from ', tableName, ' where ', primaryKey, ' > ', @maxId, ' order by ', primaryKey, ' asc limit ', batchSize, ';');
        -- 预编译sql并执行
	PREPARE selectSql FROM @selectSql;
	EXECUTE selectSql;
	-- 获取最大主键作为下一次的偏移量
	SELECT IFNULL(MAX(id), 0) INTO @maxId FROM tmp_key_tb;
	
	WHILE @maxId > 0 DO
                -- 每个批次开启一个事务
		START TRANSACTION;
                -- 按主键分批更新数据
		SET @execSql = CONCAT(updateSql, @wherePrefix, primaryKey, ' IN (SELECT id FROM tmp_key_tb);');
		-- SELECT @maxId, @execSql;
		PREPARE execSql FROM @execSql;
		EXECUTE execSql;
                DEALLOCATE PREPARE execSql;
		
                -- 每批次跑完清空临时表
		DELETE FROM tmp_key_tb;
		SET @selectSql = CONCAT('insert into tmp_key_tb select ', primaryKey, ' from ', tableName, ' where ', primaryKey, ' > ', @maxId, ' order by ', primaryKey, ' asc limit ', batchSize, ';');
		-- select @selectSql;
		PREPARE selectSql FROM @selectSql;
		EXECUTE selectSql;
		SELECT IFNULL(MAX(id), 0) INTO @maxId FROM tmp_key_tb;
		COMMIT;
	END WHILE;
        -- 删除临时表
	DROP TABLE IF EXISTS tmp_key_tb;
        -- 释放定义好的SQL
        DEALLOCATE PREPARE selectSql;
END $$
DELIMITER ;

存储过程参数如下：

• tableName：数据源表名
• primaryKey：源表主键
• updateSql：需要迁移的sql
• isWhere：是否需要加where，Y：需要加where
• batchSize：每批数据大小

执行存储过程，更新student表数据，以id为主键索引按偏移量分批次，每批处理10000条数据：

CALL update_id_batch('student', 'id', 'update student set state=''B''', 'Y', 10000);

做分批次处理主要是因为有些数据库有缓存限制，单个事务无法处理太多数据，所以只能以时间换空间，将数据切分成多块逐步处理。

总结

本文简单介绍了MySQL的PREPARE是干嘛用的，以及用具体的案例展示了其使用方式，更多的只会在运维或者做数据迁移的时候使用，实际业务代码开发中还是不提倡直接使用的。