时间戳

首页 > 解决方案 > MariaDB 为 ROW 类型的变量动态提取列

mariadb - MariaDB 为 ROW 类型的变量动态提取列

问题描述

delimiter $$

CREATE OR REPLACE PROCEDURE  `populate_audit_helper`(
    IN in_db_name VARCHAR(100),
    IN in_tbl_name VARCHAR(100),
    IN in_row_id INT(10)
)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS t_agents;
    SET @SQL := CONCAT('CREATE TEMPORARY TABLE t_agents AS SELECT * FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    BEGIN
        DECLARE c_data CURSOR FOR SELECT * FROM t_agents;
        BEGIN
            DECLARE rec ROW TYPE OF c_data;
            DECLARE done INT DEFAULT FALSE;
            DECLARE col CHAR(40);
            DECLARE val TEXT(10000);

            DECLARE c_columns CURSOR FOR 
                SELECT column_name
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = in_db_name
                    AND table_name = in_tbl_name
                ORDER BY ordinal_position;

            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

            OPEN c_columns;
            OPEN c_data;

            FETCH c_data INTO rec;          

            SET @create_list := "";

            read_loop: LOOP
                FETCH c_columns INTO col;

                IF done THEN
                    LEAVE read_loop;
                END IF;

                -- SELECT rec.col; -- <<====

                SET @create_list := CONCAT(@create_list, "'", col, "', '", rec.name, "', ");

            END LOOP read_loop;

            SET @SQL := CONCAT ("INSERT INTO x1 SET dynamic_cols_l1 = COLUMN_CREATE(", TRIM(TRAILING ", " FROM @create_list), ")");
            PREPARE stmt FROM @SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            CLOSE c_columns;
            CLOSE c_data;
        END;
    END;
 END$$

在上述过程中，我正在遍历表中的所有列，并且我计划构建一个查询以使用动态列填充表。

有什么方法可以替换上述过程中突出显示的行中的 col 值？

例如。col中存储的值是'name'。所以需要的输出是 SELECT rec.name

更新

不知何故，我设法在不使用 ROW TYPE 和单个光标的情况下编写了一个肮脏的解决方案。它可以正常工作，到目前为止我没有发现任何问题。如果有更好的方法，请指导我。

delimiter $$

CREATE OR REPLACE PROCEDURE  `populate_audit_helper_new3`(
    IN in_db_name VARCHAR(100),
    IN in_tbl_name VARCHAR(100),
    IN in_row_id INT(10)
)
proc: BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE col CHAR(40);
        DECLARE val TEXT(10000);
        DECLARE q TEXT(100000);     

        DECLARE c_columns CURSOR FOR 
            SELECT column_name
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = in_db_name
                AND table_name = in_tbl_name
            ORDER BY ordinal_position;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        OPEN c_columns;

        SET q = "";
        SET @val = "";

        read_loop: LOOP
            FETCH c_columns INTO col;

            IF done THEN
                LEAVE read_loop;
            END IF;
            SET q = CONCAT(q, "'", col, "','|', IFNULL(QUOTE(", col, "), ''),'$'");

        END LOOP read_loop;

        SET q = TRIM(TRAILING "," FROM q);

        SET q = CONCAT("CONCAT(", q, ") INTO @val");

        EXECUTE IMMEDIATE CONCAT("SELECT ", q , " FROM ", in_tbl_name, " WHERE row_id = ", in_row_id);

        IF @val = '' THEN
            LEAVE proc;
        END IF;

        SELECT CONCAT("'", REPLACE(@val, "|", "',"), "'") INTO @val;

        SELECT REPLACE(@val, "$", ",") INTO @val;

        SELECT TRIM(TRAILING ",'" FROM @val) INTO @val;

        EXECUTE IMMEDIATE CONCAT("INSERT INTO x1 SET dynamic_cols_l1 = COLUMN_CREATE(", @val, ")" );

        CLOSE c_columns;
 END$$

标签： mariadbmariadb-10.3

解决方案

据我在 mariadb 文档中看到的，你不能直接这样做。ROW TYPE 也没有循环支持。但是我建议做的是只留下INFORMATION_SCHEMA.COLUMNS光标并生成动态选择，例如



 read_loop: LOOP
    FETCH c_columns INTO col;
    IF done THEN
       LEAVE read_loop;
    END IF;
    SET @SQL := CONCAT('SELECT ', col, ' into @colval FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);
    PREPARE stmt FROM @SQL;
    execute stmt using col;
    DEALLOCATE PREPARE stmt;
    SET @create_list := CONCAT(@create_list, "'", col, "', '", @colval, "', ");

END LOOP read_loop;

当您只拆分一个表格行时，它就足够了。

更新：使用其他信息可以这样做。重新思考之后：


 set @SQL := 'SELECT CONCAT(';
 read_loop: LOOP
    FETCH c_columns INTO col;
    IF done THEN
       LEAVE read_loop;
    END IF;
    SET @SQL := CONCAT(@SQL, "'\\'", col, "'\\', \\''", col, "'\\', '");

END LOOP read_loop;
set @SQL := substring(@SQL, 1, CHAR_LENGTH(@SQL) - 3);
set @SQL := concat(@SQL, ') into @create_list FROM `', in_tbl_name, '` WHERE row_id = ', in_row_id);
PREPARE stmt FROM @SQL;
execute stmt using col;
DEALLOCATE PREPARE stmt;

推荐阅读