mysql 使用循环及变量同步数据

• 问题描述

一般业务性质的操作数据库是放在代码中进行，比如之前是一张表，现在由于业务修改，需要修改为两张表来存储数据。

假设有原始表user_one_friend结构如下：

一个用户只留存一个朋友名称。现在由于业务需求，修改为朋友可留存多个朋友名称，所以需要重新建表，用户表和朋友表。

用户表user结构如下：

朋友表user_friend结构如下：

现在需要将原表中的数据同步到新表中，建立关联关系。

• 解决办法

可以使用mysql中的循环和变量设置来完成多个表的数据同步。代码如下：

-- 首先定义结束符为 $$，这么做的目的是为了在定义下面的存储过程时，作为一个整体而不是单个的语句执行。
delimiter $$
-- 删除原有的存储过程，与建表时类似
drop PROCEDURE if exists user_sync;
-- 创建存储过程
create PROCEDURE user_sync()
-- 存储过程实际内容开始
BEGIN
-- 声明变量，并初始化
declare i int;
set i = 1;
-- 创建临时表，从user_one_friend中读出原始数据，同时构建连续的数字序列。@num变量用来作为连续的临时数字id，在循环体中取值使用。
set @num = 0;
drop table if exists tmp_user;
create TEMPORARY table tmp_user (select @num := @num + 1 as tmp_id, user_name, friend_name from user_one_friend);
-- 设置变量循环次数，应为原表的总记录行数
set @origin_num = (select count(*) from user_one_friend);
-- while循环开始
while i <= @origin_num do
-- 查询原始数据，根据当前循环的 i 的值来取行数。使用 := 语法在查询时设置好变量。
select @user_name := user_name, @friend_name := friend_name from tmp_user where tmp_id = i;
-- 设置 user_id变量
set @user_id = UUID();
-- 插入user表
insert into user(`user_id`, `user_name`) values (@user_id, @user_name);
-- 插入 user_friend表
insert into user_friend(`friend_id`, `user_id`, `friend_name`) values (UUID(), @user_id, @friend_name);
-- 将指针 i 加1，继续循环
set i = i + 1;
-- 当条件不满足时结束循环
end while;
-- 删除创建的临时表
drop table tmp_user;
-- 结束存储过程
end $$
-- 重置结束符
delimiter ;

-- 调用存储过程
call user_sync();

主要使用 := 在查询时创建变量、设置变量、建立临时表和创建存储过程循环插入的方式完成数据的同步。

执行结果：