MariaDB数据库提取单(多)表的数据并转换成INSERT语句

1. SQL语句

DROP TABLE IF EXISTS tmp_InitialData;
CREATE TABLE tmp_InitialData(sqlText text);
DROP PROCEDURE IF EXISTS up_GenerateTpp9InitialData;
DELIMITER $$

CREATE PROCEDURE up_GenerateTpp9InitialData(OUT o_str_RetValue VARCHAR(1024))
label:BEGIN
  DECLARE db_name VARCHAR(40);   -- 执行脚本所在数据库的名称
  DECLARE str_table_name VARCHAR(40);  -- 表名
  DECLARE str_table_comment VARCHAR(40); -- 表注释
  DECLARE column_group VARCHAR(2000); -- 列名使用逗号聚合
  DECLARE str_order_column VARCHAR(200); -- 排序列
  DECLARE dynamic_sql text; -- 组装SQL
  DECLARE max_column_position INT; -- 最大的列位置号
  DECLARE out_done INT DEFAULT 0 ; -- 外层游标控制变量

  -- 定义用户表游标
  DECLARE cur_table CURSOR FOR
   SELECT 'fep_dict_type' AS table_name, '数据字典类型表' AS table_comment, 'dict_type_num' AS order_column FROM dual UNION ALL
   SELECT 'fep_dict_item' AS table_name, '数据字典项表'   AS table_comment, 'dict_type_num, dict_item_num' AS order_column FROM dual; 

   /* 当游标到达尾部时，mysql自动设置done=1 */
  DECLARE CONTINUE handler FOR NOT FOUND SET out_done = 1; -- 外层游标执行结束，置为1

  -- 定义异常
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    GET DIAGNOSTICS CONDITION 1
    @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
    -- SELECT @p1 as RETURNED_SQLSTATE  , @p2 as MESSAGE_TEXT;
    ROLLBACK;
    SET o_str_RetValue = CONCAT('在数据库《' , db_name , '》中执行脚本异常,原因：', @p2) ; 
  END; 

  -- 获取数据库名称
  SELECT SCHEMA() INTO db_name;

  -- 开始事务
  START TRANSACTION;

  -- 遍历游标，组装数据
  SET out_done = 0;
  OPEN cur_table;
    loop1: LOOP 
      FETCH cur_table INTO str_table_name, str_table_comment, str_order_column;
      IF out_done = 1 THEN
         LEAVE loop1;
      END IF;

      -- 生成注释
      SET dynamic_sql = concat('SELECT ''', '-- 开始生成表：', str_table_comment , '-', str_table_name, '''');
      SET dynamic_sql = CONCAT(dynamic_sql, '\n', ' UNION ALL ', '\n');

      -- 拼接表的全部字段
      SELECT LOWER(group_concat(column_name)), max(ordinal_position) AS column_name INTO column_group, max_column_position
        FROM information_schema.columns
       WHERE table_schema = schema()
         AND table_name = str_table_name
         AND column_name NOT IN ('create_time','edit_time')
       ORDER BY ordinal_position;
      SET dynamic_sql = CONCAT(dynamic_sql, '(SELECT CONCAT('' INSERT INTO ', str_table_name, '(', column_group, ') VALUES('',');

      -- 拼接表的每个字段(开始定义内层游标)
      BEGIN -- inner BEGIN
        DECLARE str_column_name VARCHAR(40);  -- 列名
        DECLARE str_data_type VARCHAR(40);  -- 列的类型
        DECLARE inner_done int DEFAULT 0 ;  -- 内层游标控制变量
        DECLARE column_position INT;   -- 列的位置号
        DECLARE cur_column CURSOR FOR
          SELECT column_name, data_type, ordinal_position
            FROM information_schema.columns
           WHERE table_schema = schema()
             AND table_name = str_table_name
             AND column_name NOT IN ('create_time','edit_time')
           ORDER BY ordinal_position;

        DECLARE CONTINUE handler FOR NOT FOUND SET inner_done = 1 ;
          OPEN cur_column ; -- 打开内层游标
            WHILE NOT inner_done DO
              FETCH cur_column INTO str_column_name, str_data_type, column_position ; -- 从【内层游标】中获取数据，赋值到定义变量中
              IF NOT inner_done THEN
                IF(str_data_type = 'varchar' || str_data_type = 'text') THEN
                  IF(column_position != max_column_position) THEN
                    SET dynamic_sql = CONCAT(dynamic_sql, 'CASE WHEN ', str_column_name, ' IS NULL OR TRIM(', str_column_name, ') = '''' THEN ''NULL'' ELSE CONCAT('''''''',' , str_column_name, ','''''''') END, '','',', '\n');
                  ELSE
                    SET dynamic_sql = CONCAT(dynamic_sql, 'CASE WHEN ', str_column_name, ' IS NULL OR TRIM(', str_column_name, ') = '''' THEN ''NULL'' ELSE CONCAT('''''''',' , str_column_name, ','''''''') END');
                  END IF;
                ELSE
                  IF(column_position != max_column_position) THEN
                    SET dynamic_sql = CONCAT(dynamic_sql, 'CASE WHEN ', str_column_name, ' IS NULL THEN ''NULL'' ELSE ', str_column_name, ' END, '','',', '\n');
                  ELSE
                    SET dynamic_sql = CONCAT(dynamic_sql, 'CASE WHEN ', str_column_name, ' IS NULL THEN ''NULL'' ELSE ', str_column_name, ' END');
                  END IF;
                END IF;
              END IF;
            END WHILE ;
          CLOSE cur_column; -- 循环结束后，关闭内层游标
      END; -- END inner BEGIN

    -- 拼接表名、排序列名
    SET dynamic_sql = CONCAT(dynamic_sql, ', '');''', ')  AS sqlText FROM ', str_table_name, ' ORDER BY ', str_order_column, ')');

     -- 拼接空行
     SET dynamic_sql = CONCAT(dynamic_sql, '\n', ' UNION ALL ', '\n');
     SET dynamic_sql = CONCAT(dynamic_sql, 'SELECT ''-- 结束''', ' UNION ALL ');

      -- 插入数据，提交事务
      INSERT INTO tmp_InitialData(sqlText) VALUES (dynamic_sql);
      COMMIT;
    END LOOP loop1;
  CLOSE cur_table;

 SET o_str_RetValue = CONCAT('在数据库《' , db_name , '》中执行脚本成功.');
END $$

DELIMITER ;

CALL up_GenerateTpp9InitialData(@o_str_RetValue);
DROP PROCEDURE IF EXISTS up_GenerateTpp9InitialData;
SELECT @o_str_RetValue;
SELECT * FROM tmp_InitialData;

2. 生成结果