专注于 JetBrains IDEA 全家桶,永久激活,教程
持续更新 PyCharm,IDEA,WebStorm,PhpStorm,DataGrip,RubyMine,CLion,AppCode 永久激活教程

MySQL存储过程举例

涉及循环、动态sql等主要的MySQL存储过程知识。

需求:

遍历所有【test_ondev_[0-9]】开头的表,将其中的所有数据按表中的col字段存储到【test_ondev_history_ + col的值】的表里面,当【test_ondev_history_ + col】不存在的时候复制test_ondev_history的结构创建该表,并将数据存储进表里。
程序思路:

  • 遍历表名
  • 将每张表的col字段的内容统计出来
  • 创建 test_ondev_history_ + col 的表
  • 将数据插入进去test_ondev_history_ + col表中
  • 继续遍历其他表

使用存储过程实现:

DELIMITER $$

CREATE PROCEDURE `p_copy_data`()
    BEGIN

    DECLARE i INT;

    DECLARE  v_sql_col VARCHAR(200);
    DECLARE j INT;
    DECLARE max_id_s INT;
    DECLARE max_id INT;
    DECLARE v_tbname VARCHAR(50);
    DECLARE v_tbname_s VARCHAR(50);
    DECLARE v_col VARCHAR(50);
    DECLARE  v_sql_create VARCHAR(200);
    DECLARE  v_sql_insert VARCHAR(200);

    CREATE TABLE IF NOT EXISTS temp_col(id INT PRIMARY KEY AUTO_INCREMENT,col VARCHAR(50));
    CREATE TABLE IF NOT EXISTS tbnames(id INT PRIMARY KEY AUTO_INCREMENT,tbname VARCHAR(50));
    TRUNCATE TABLE tbnames;
    INSERT INTO tbnames(tbname)
    SELECT  table_name FROM information_schema.`TABLES` WHERE table_name REGEXP   'test_ondev_[0-9]';
      SET i = (SELECT MIN(id) FROM   tbnames);
     SET  max_id_s = (SELECT MAX(id) FROM   tbnames);

    WHILE  i <=max_id_s
    DO
    TRUNCATE TABLE  temp_col;
    SET v_tbname_s = (SELECT tbname FROM  tbnames  WHERE id = i);
    SET v_sql_col = CONCAT('insert into temp_col(col)  select distinct   col  from  ',v_tbname_s);  
    SET  @v_sql_col := v_sql_col;
    PREPARE stmt FROM @v_sql_col;    
    EXECUTE stmt ;  
    DEALLOCATE PREPARE stmt; 

    SET j =1;
    SET  max_id = (SELECT  MAX(id) FROM temp_col );
    WHILE  j <= max_id
    DO
    SET  v_col = (SELECT col  FROM temp_col  WHERE id =j);
    SET v_tbname = CONCAT('test_ondev_history_',v_col );
    SET v_sql_create = CONCAT('create table  if not exists ',v_tbname,'  like test_ondev_history;');  
    SET  @v_sql_create := v_sql_create;
    PREPARE stmt FROM @v_sql_create;    
    EXECUTE stmt ;  
    DEALLOCATE PREPARE stmt; 
        SET  v_sql_insert = CONCAT('insert into ',v_tbname,' select * from ',v_tbname_s," where  col='", v_col ,"';");

        SET  @v_sql_insert := v_sql_insert;
    PREPARE stmt FROM @v_sql_insert;    
    EXECUTE stmt ;  
    DEALLOCATE PREPARE stmt; 

    SET j = j + 1;
    END WHILE;

    SET  i = i + 1;
    END  WHILE;

    END$$

TIPS:

MySQL不建议使用存储过程,如果临时用的话可以考虑临时使用。

另外,该过程是第一版实现历史数据的迁移,如果需要重复执行还有不少优化空间,例如:

  • 遍历表的时候需要指定对应库;
  • 上述过程不能重复执行,如果重复执行,需要怎么改进;
  • 中途涉及创建表,如果不创建哪些临时表改如何调整;

这些就留给大家思考了,也欢迎来和我沟通。文章如果有误,欢迎斧正 ,非常感谢。

文章永久链接:https://tech.souyunku.com/37705

未经允许不得转载:搜云库技术团队 » MySQL存储过程举例

JetBrains 全家桶,激活、破解、教程

提供 JetBrains 全家桶激活码、注册码、破解补丁下载及详细激活教程,支持 IntelliJ IDEA、PyCharm、WebStorm 等工具的永久激活。无论是破解教程,还是最新激活码,均可免费获得,帮助开发者解决常见激活问题,确保轻松破解并快速使用 JetBrains 软件。获取免费的破解补丁和激活码,快速解决激活难题,全面覆盖 2024/2025 版本!

联系我们联系我们