数据迁移(一)——统一替换指定数据库所有表中指定字段的值（存储过程应用）

最新推荐文章于 2022-04-18 11:03:13 发布

勿忘初心1221

最新推荐文章于 2022-04-18 11:03:13 发布

阅读量815

点赞数 1

分类专栏： # 数据库 # 存储过程所遇问题文章标签： mysql 数据迁移

本文链接：https://blog.csdn.net/weixin_39960529/article/details/102504169

版权

所遇问题同时被 3 个专栏收录

7 篇文章 0 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

存储过程

3 篇文章 0 订阅

订阅专栏

项目相关需求：

最近做项目的3期优化，需求是将之前的运维系统项目嵌入到一个大的项目中充当一个模块，但是数据库还是保留之前的，只是把用户，组织以及权限相关表删除，相关关系应用大系统中处理。在最后有一个需求是：将之前系统中保存的数据迁移到新的数据库（指没有那些用户，组织，权限的数据库）中。

数据迁移考虑相关问题：

其实以上迁移数据，最简单的就是把老的数据导出来sql文件形式，再在新的数据库中去执行sql文件；但是考虑到一个新的问题，之前的用户，组织那些原本是统一在一个数据库中，对于组织id，用户id都是以原系统组织表和用户表来的，，现在如果迁移到新的数据库中，由于新数据库缺少相关表，并且用户，组织那些保存在新的数据库中，那主键id肯定就不一致，，那在数据迁移肯定会出现关系数据错误（例如：查看该用户创建的设备，显然用新的用户id是无法查到迁移的数据，因为用户id不匹配）。

根据以上问题，考虑到可以通过用该用户新创建的用户id/组织id 来替换迁移数据中老的用户id/组织id以及其他要替换的其他信息。但是数据库中该用户/该组织的关系关联表数据牵扯到很多表，如果一个一个表去检索替换，无疑是巨大的工作量，而且还有漏掉的危险。对于这个问题通过在网上查阅资料，最后选定可以用存储过程的方式进行批修改。网址如下：https://blog.csdn.net/qq_37345604/article/details/90289280

我项目使用的mysql数据库，可视化工具是navicat。

通过一天的努力，最终编写出成功的实现统一替换指定字段值需求的存储过程。完整代码如下：

-- 存储过程统一更改数据库中所有表指定含义（如电站id）的值（解决指定含义会在多个表中呈现不同字段名称情况如电站id（station_id，powerstation_id，power_station_id））

-- 如果该存储过程存在则删除并以此命名新创建存储过程
DROP PROCEDURE IF EXISTS stationId;
CREATE PROCEDURE `stationId`(IN oldValue VARCHAR(36),IN newValue VARCHAR(36))
BEGIN
-- 定义循环条件
DECLARE flag INT DEFAULT 0; 
-- 保存表名
DECLARE tableName VARCHAR(50);
-- 要修改的字段名
DECLARE columnName VARCHAR(50);
-- 查询数据库test中含有power_station_id列的表,如果区分大小写使用binary COLUMN_NAME = 'organization_id'
DECLARE result CURSOR FOR SELECT TABLE_NAME AS tableName, COLUMN_NAME AS columnName 
                                                    FROM INFORMATION_SCHEMA.Columns 
                                                    WHERE 
                                                        (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'station_id' )
                                                        OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'powerstation_id')
                                                        OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'power_station_id');
-- 退出循环
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag = 1; 
-- 打开游标
OPEN result;
    WHILE flag <> 1 DO
    -- 游标指向下一个位置,可以有多个数据,比如FETCH result INTO tname,ttype,...;
    FETCH result INTO tableName, columnName;
        -- 拼接字符串表名sql,根据需要使用CONCAT函数连接
                -- 同一含义不同定义名称 判断定义名称 统一更改含义
                IF columnName='station_id' THEN
                        SET @execSql = CONCAT('UPDATE ', tableName, ' SET station_id =  ',newValue,' WHERE station_id = ',oldValue,' ;');
                    ELSEIF columnName='powerstation_id' THEN
                            SET @execSql = CONCAT('UPDATE ', tableName, ' SET powerstation_id =  ',newValue,' WHERE powerstation_id = ',oldValue,' ;');
                        ELSEIF columnName='power_station_id' THEN
                                SET @execSql = CONCAT('UPDATE ', tableName, ' SET power_station_id =  ',newValue,' WHERE power_station_id = ',oldValue,' ;');
                END IF;
        PREPARE stmt FROM @execSql; -- 预处理需要执行的动态SQL，
        EXECUTE stmt;
                deallocate prepare stmt; -- 释放执行中使用的所有数据库资源（如游标）
    END WHILE;
END;

-- 存储过程统一更改数据库中所有表指定含义（如组织id）的值

-- 如果该存储过程存在则删除并以此命名新创建存储过程
DROP PROCEDURE IF EXISTS organizationId;
CREATE PROCEDURE `organizationId`(IN oldValue VARCHAR(36),IN newValue VARCHAR(36))
BEGIN
-- 定义循环条件
DECLARE flag INT DEFAULT 0; 
-- 保存表名
DECLARE tname VARCHAR(50);
-- 查询数据库test中含有power_station_id列的表,如果区分大小写使用binary COLUMN_NAME = 'power_station_id'
DECLARE result CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_SCHEMA = 'test' AND  COLUMN_NAME = 'organization_id';
-- 退出循环
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag = 1; 
-- 打开游标
OPEN result;
    WHILE flag <> 1 DO
    -- 游标指向下一个位置,可以有多个数据,比如FETCH result INTO tname,ttype,...;
    FETCH result INTO tname;
        -- 拼接字符串表名sql,根据需要使用CONCAT函数连接
        SET @execSql = CONCAT('UPDATE ', tname, ' SET organization_id =  ',newValue,' WHERE organization_id = ',oldValue,' ;'); 
        PREPARE stmt FROM @execSql; -- 预处理需要执行的动态SQL，
        EXECUTE stmt;
                deallocate prepare stmt; -- 释放执行中使用的所有数据库资源（如游标）
    END WHILE;
END;

-- 存储过程统一更改数据库中所有表指定含义（如用户id）的值（解决指定含义会在多个表中呈现不同字段名称情况如用户id（create_user_id，resolve_user_id，handler_id，acceptor_id，inner_user_id，upload_by，create_by_user_id，handover_user_id））

-- 如果该存储过程存在则删除并以此命名新创建存储过程
DROP PROCEDURE IF EXISTS userId;
CREATE PROCEDURE `userId`(IN oldValue VARCHAR(36),IN newValue VARCHAR(36))
BEGIN
-- 定义循环条件
DECLARE flag INT DEFAULT 0; 
-- 保存表名
DECLARE tableName VARCHAR(50);
-- 要修改的字段名
DECLARE columnName VARCHAR(50);
-- 查询数据库test中含有power_station_id列的表,如果区分大小写使用binary COLUMN_NAME = 'organization_id'
DECLARE result CURSOR FOR SELECT TABLE_NAME AS tableName, COLUMN_NAME AS columnName 
                                                    FROM INFORMATION_SCHEMA.Columns 
                                                    WHERE 
                                                        (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'create_user_id' )
                                                        OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'resolve_user_id')
                                                        OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'handler_id')
                                                        OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'acceptor_id')
                                                        OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'inner_user_id')
                                                        OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'upload_by')
                                                        OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'create_by_user_id')
                                                        OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'handover_user_id');
-- 退出循环
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag = 1; 
-- 打开游标
OPEN result;
    WHILE flag <> 1 DO
    -- 游标指向下一个位置,可以有多个数据,比如FETCH result INTO tname,ttype,...;
    FETCH result INTO tableName, columnName;
        -- 拼接字符串表名sql,根据需要使用CONCAT函数连接
                -- 同一含义不同定义名称 判断定义名称 统一更改含义
                IF columnName='create_user_id' THEN
                    SET @execSql = CONCAT('UPDATE ', tableName, ' SET create_user_id =  ',newValue,' WHERE create_user_id = ',oldValue,' ;');
                    ELSEIF columnName='resolve_user_id' THEN
                        SET @execSql = CONCAT('UPDATE ', tableName, ' SET resolve_user_id =  ',newValue,' WHERE resolve_user_id = ',oldValue,' ;');
                        ELSEIF columnName='handler_id' THEN
                            SET @execSql = CONCAT('UPDATE ', tableName, ' SET handler_id =  ',newValue,' WHERE handler_id = ',oldValue,' ;');
                            ELSEIF columnName='acceptor_id' THEN
                                SET @execSql = CONCAT('UPDATE ', tableName, ' SET acceptor_id =  ',newValue,' WHERE acceptor_id = ',oldValue,' ;');
                                ELSEIF columnName='inner_user_id' THEN
                                    SET @execSql = CONCAT('UPDATE ', tableName, ' SET inner_user_id =  ',newValue,' WHERE inner_user_id = ',oldValue,' ;');
                                    ELSEIF columnName='upload_by' THEN
                                        SET @execSql = CONCAT('UPDATE ', tableName, ' SET upload_by =  ',newValue,' WHERE upload_by = ',oldValue,' ;');
                                        ELSEIF columnName='create_by_user_id' THEN
                                            SET @execSql = CONCAT('UPDATE ', tableName, ' SET create_by_user_id =  ',newValue,' WHERE create_by_user_id = ',oldValue,' ;');
                                            ELSEIF columnName='handover_user_id' THEN
                                                SET @execSql = CONCAT('UPDATE ', tableName, ' SET handover_user_id =  ',newValue,' WHERE handover_user_id = ',oldValue,' ;');
                END IF;
        PREPARE stmt FROM @execSql; -- 预处理需要执行的动态SQL，
        EXECUTE stmt;
                deallocate prepare stmt; -- 释放执行中使用的所有数据库资源（如游标）
    END WHILE;
END;

以上存储过程叙述：

1.由于指定含义的值会在多个表中呈现不同字段名称情况如用户id（create_user_id，resolve_user_id，handler_id，acceptor_id，inner_user_id，upload_by，create_by_user_id，handover_user_id），因此在根据指定字段查询数据库包含该指定字段表时，要加多次条件，如用户id会有多个不同字段表示，加多字段为条件查询表。代码如下：

SELECT TABLE_NAME AS tableName, COLUMN_NAME AS columnName 
    FROM INFORMATION_SCHEMA.Columns 
        WHERE 
                (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'create_user_id' )
                OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'resolve_user_id')
                OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'handler_id')
                OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'acceptor_id')
                OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'inner_user_id')
                OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'upload_by')
                OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'create_by_user_id')
                OR (TABLE_SCHEMA = 'test' AND COLUMN_NAME = 'handover_user_id');

该代码替换数据库和字段名可直接复制运行查询。查询出来会有两个值，一个是tableName列和columnName 列；

2.在存储过程中通过以下代码可创建变量。代码如下：

-- 保存表名
DECLARE tableName VARCHAR(50);
-- 要修改的字段名
DECLARE columnName VARCHAR(50);

3.通过以下代码可将查出来的信息存入声明的result变量中。代码如下：

DECLARE result CURSOR FOR    -- 定义游标result，并使用游标的对象存储查到的结果值信息。

下面就是通过游标指向下一个位置，并在其中添加if判断来区分不同的字段名来执行不同的更新语句。

以上都是创建存储过程的代码。

4.调用存储过程代码就一行。代码如下：

-- 调用存储过程
-- 调用统一替换电站id存储过程
call stationId('"NpFnUGEe"','"NpFnUGEe110"');

-- 调用统一替换组织id存储过程
call organizationId('"123"','"o15664455314061"');

-- 调用统一替换用户id存储过程
call userId('"2"','"191011"');

5.删除存储过程代码还是一行搞定。代码如下：

-- 删除存储过程
-- 删除统一替换电站id存储过程
DROP PROCEDURE IF EXISTS stationId;

-- 删除统一替换组织id存储过程
DROP PROCEDURE IF EXISTS organizationId;

-- 删除统一替换用户id存储过程
DROP PROCEDURE IF EXISTS userId;

在这里目前只使用到带有输入参数的存储过程，至于无参和输出参数语法是一致的，后面应用在加上；

以上是在navicat中创建，调用，删除存储过程的应用，还有一种方式是通过cmd命令行的形式应用存储过程，将在下一篇中编写。

navicat通过sql语句形式创建，调用，删除mysql存储过程的应用到这里就结束了，感谢大家的捧场，希望能帮助到有需要的人。以上是存储过程的应用，由于是第一次编写存储过程，如有讲述不清晰的，或者有其他好的建议，欢迎各位大佬留言指点，在这里先谢过啦。

查阅相关资料如下：

https://blog.csdn.net/qq_37345604/article/details/90289280 --- mysql查询和修改指定数据库中所有表中包含的某个字段

https://www.cnblogs.com/accumulater/p/6109056.html --- Sql Cursor 基本用法

勿忘初心1221

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
数据迁移(一)——统一替换指定数据库所有表中指定字段的值（存储过程应用）

最近做项目的3期优化，需求是将之前的运维系统项目嵌入到一个大的项目中充当一个模块，但是数据库还是保留之前的，只是把用户，组织以及权限相关表删除，相关关系应用大系统中处理。在最后有一个需求是：将之前系统中保存的数据迁移到新的数据库（指没有那些用户，组织，权限的数据库）中。其实以上迁移数据，最简单的就是把老的数据导出来sql文件形式，再在新的数据库中去执行sql文件；但...
复制链接

扫一扫