Mysql表结构同步存储过程(适用于模版表)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: Mysql表结构同步存储过程(适用于模版表)

Mysql表结构同步

流水模版表新增字段需要同步到所有区域年份的流水表

CREATE DEFINER=`root`@`%` PROCEDURE `SyncTableStructure`(IN sourceTable VARCHAR(255), IN targetTable VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE columnName VARCHAR(255);
    DECLARE columnType VARCHAR(255);
    DECLARE isNullable VARCHAR(3);
    DECLARE columnDefault TEXT;
    DECLARE extra VARCHAR(255);
    -- 游标查询源表的列结构
    DECLARE columnCursor CURSOR FOR
        SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = sourceTable;
    -- 当遍历完所有行时,关闭游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -- 打开游标
    OPEN columnCursor;
    read_loop: LOOP
        -- 读取游标中的一行
        FETCH columnCursor INTO columnName, columnType, isNullable, columnDefault, extra;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 检查目标表中是否存在该列
        IF NOT EXISTS (
            SELECT * FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = targetTable AND COLUMN_NAME = columnName
        ) THEN
            -- 构建ALTER TABLE语句,添加新列
            SET @alterSQL = CONCAT('ALTER TABLE ', targetTable, ' ADD COLUMN ', columnName, ' ', columnType);
            
            -- 处理可为空字段
            IF isNullable = 'NO' THEN
                SET @alterSQL = CONCAT(@alterSQL, ' NOT NULL');
            END IF;
            
            -- 处理默认值
            IF columnDefault IS NOT NULL THEN
                SET @alterSQL = CONCAT(@alterSQL, ' DEFAULT ', QUOTE(columnDefault));
            END IF;
            -- 处理额外属性(如AUTO_INCREMENT)
            IF extra != '' THEN
                SET @alterSQL = CONCAT(@alterSQL, ' ', extra);
            END IF;
            -- 执行ALTER TABLE语句
            PREPARE stmt FROM @alterSQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END LOOP;
    -- 关闭游标
    CLOSE columnCursor;
END

调用

CALL SyncTableStructure('dev_log', 'dev_log_city_2024');

一个模版结构同步到多个表上

CREATE DEFINER=`root`@`%` PROCEDURE `SyncDevDataTablesStructure`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE currentTable VARCHAR(255);
    DECLARE columnName VARCHAR(255);
    DECLARE columnType VARCHAR(255);
    DECLARE isNullable VARCHAR(3);
    DECLARE columnDefault TEXT;
    DECLARE extra VARCHAR(255);
    -- 硬编码的源表和目标表前缀
    DECLARE sourceTableName VARCHAR(255) DEFAULT 'dev_log';
    DECLARE tablePrefix VARCHAR(255) DEFAULT 'dev_log_';
    -- 定义游标,用于查找所有符合前缀的表
    DECLARE tableCursor CURSOR FOR
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE CONCAT(tablePrefix, '%');
    -- 定义游标,用于查找源表的列结构
    DECLARE columnCursor CURSOR FOR
        SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = sourceTableName;
    -- 处理游标读取结束的情况
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -- 调试输出 sourceTableName 的值
    SELECT CONCAT('sourceTableName: ', sourceTableName) AS debug_output;
    -- 打开表游标
    OPEN tableCursor;
    table_loop: LOOP
        -- 获取一个表名
        FETCH tableCursor INTO currentTable;
        -- 调试输出 currentTable 的值
        SELECT CONCAT('tableName: ', currentTable) AS debug_output;
        IF done THEN
            LEAVE table_loop;
        END IF;
        -- 打开列游标
        SET done = 0;
        OPEN columnCursor;
        column_loop: LOOP
            -- 读取游标中的一行
            FETCH columnCursor INTO columnName, columnType, isNullable, columnDefault, extra;
            IF done THEN
                LEAVE column_loop;
            END IF;
            -- 检查目标表中是否存在该列
            IF NOT EXISTS (
                SELECT * FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = currentTable AND COLUMN_NAME = columnName
            ) THEN
                -- 构建ALTER TABLE语句,添加新列
                SET @alterSQL = CONCAT('ALTER TABLE ', currentTable, ' ADD COLUMN ', columnName, ' ', columnType);
                -- 处理可为空字段
                IF isNullable = 'NO' THEN
                    SET @alterSQL = CONCAT(@alterSQL, ' NOT NULL');
                END IF;
                -- 处理默认值
                IF columnDefault IS NOT NULL THEN
                    SET @alterSQL = CONCAT(@alterSQL, ' DEFAULT ', QUOTE(columnDefault));
                END IF;
                -- 处理额外属性(如AUTO_INCREMENT)
                IF extra != '' THEN
                    SET @alterSQL = CONCAT(@alterSQL, ' ', extra);
                END IF;
                -- 执行ALTER TABLE语句
                PREPARE stmt FROM @alterSQL;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END LOOP;
        -- 关闭列游标
        CLOSE columnCursor;
    END LOOP;
    -- 关闭表游标
    CLOSE tableCursor;
END
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5天前
|
消息中间件 canal 关系型数据库
Maxwell:binlog 解析器,轻松同步 MySQL 数据
Maxwell:binlog 解析器,轻松同步 MySQL 数据
39 11
|
5天前
|
存储 关系型数据库 MySQL
深入解析MySQL数据存储机制:从表结构到物理存储
深入解析MySQL数据存储机制:从表结构到物理存储
14 1
|
2月前
|
关系型数据库 MySQL Linux
mysql 主从同步 实现增量备份
【8月更文挑战第28天】mysql 主从同步 实现增量备份
37 3
|
2月前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版产品使用问题之使用CTAS同步MySQL到Hologres时出现的时区差异,该如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
SQL 缓存 关系型数据库
MySQL主从同步如何操作?
随着业务增长,单台MySQL服务器难以应对高并发访问和潜在的故障风险。主从同步(Master-Slave)通过读写分离提升数据库处理能力,具备多项优势:读写分离减轻主数据库压力、支持一主多从增强扩展性与高可用性、以及数据备份确保容灾恢复。MySQL利用binlog实现主从数据同步,记录所有写操作,不包含查询。binlog有三种格式:Statement(基于SQL语句)、Row(基于行更改)、Mixed(结合前两者优点)。主从复制涉及三个关键线程:主库的binlog dump thread和从库的I/O thread与SQL thread。
MySQL主从同步如何操作?
|
2月前
|
存储 关系型数据库 MySQL
MySQL主从同步如何保证数据一致性?
MySQL主从同步如何保证数据一致性?
77 0
MySQL主从同步如何保证数据一致性?
|
2月前
|
SQL 存储 关系型数据库
实时计算 Flink版产品使用问题之同步MySQL多张表的过程中,内存释放依赖于什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
SQL 存储 关系型数据库
MySQL主从同步延迟原因与解决方法
MySQL主从同步延迟原因与解决方法
136 0
|
2月前
|
SQL 关系型数据库 MySQL
mysql读写分离,主从同步
本文介绍了如何在Laravel项目中配置数据库读写分离,并实现MySQL主从同步。主要步骤包括:在`config/database.php`中设置读写分离配置;为主机授予从机访问权限;配置各MySQL服务器的`/etc/my.cnf`文件以确保唯一的`server-id`;以及通过SQL命令设置主从关系并启动从服务。文章还针对一些常见错误提供了排查方法。最后通过验证确认主从同步是否成功。[原文链接](https://juejin.cn/post/6901581801458958344)。版权所有者为作者佤邦帮主,转载请遵循相关规定。
|
2月前
|
监控 Cloud Native 关系型数据库
【跨区域PolarDB-MySQL主备互通】:揭秘如何跨越万里实现数据无缝同步,打造坚不可摧的灾备体系!
【8月更文挑战第20天】阿里云PolarDB是一款兼容MySQL协议的云原生数据库服务,提供高性能与高可用性。本文介绍如何在PolarDB-MySQL中实现跨区域主备同步。首先创建主备两个集群,接着通过MySQL复制功能配置同步:获取主节点复制信息、配置备节点复制并启动复制进程。最后,通过`SHOW SLAVE STATUS\G;`监控复制状态,确保数据同步正常。此方法可提升数据的可靠性和可用性,需考虑网络条件对性能的影响。
52 0

热门文章

最新文章

下一篇
无影云桌面