mysql - 在所有表中插入单个 table_name 作为列值

Question

我使用的数据库包含数百个具有完全相同结构和相同语法的表。每天都会创建更多的表，因此数据库会不断增长。为了动态附加越来越多的表，我使用了一个运行良好的 MySQL 过程。

然而，一旦附加，我就无法识别行的来源，因为表格不包含任何要区分的信息。因此，我想在以下过程中集成一个 MySQL 语句，以自动从 information_schema 中获取 TABLE_NAME 并将其设置为对应表。我已经为此创建了一个专栏，但在调整程序方面遇到了困难。我真的很感激任何帮助或建议来解决这个问题。

CREATE PROCEDURE getallarbeitsplaene(recordOffset INT, recordCount INT)

BEGIN
    DECLARE tableName VARCHAR(255);
    DECLARE endOfTables INT DEFAULT 0;

    DECLARE cur CURSOR FOR
        SELECT t.table_name
        FROM information_schema.tables t
        WHERE t.table_schema = DATABASE()
          AND t.table_type='BASE TABLE'
                        AND    NOT table_name LIKE '%x_arbeitsplan'
                        AND table_name LIKE '%_arbeitsplan';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfTables = 1;

    DROP TEMPORARY TABLE IF EXISTS alle_plaene;

    CREATE TEMPORARY TABLE alle_plaene (
        `ID` INT(11) NOT NULL,
        `AP` VARCHAR(8) NOT NULL,
                    `Name` VARCHAR(128) NOT NULL,
                    `Baugruppentyp` VARCHAR(128) NOT NULL,
                    `Basiseinheit` VARCHAR(8) NOT NULL,
                    `Zeit_Basiseinheit` NUMERIC(10,0) NOT NULL,
                    `vor_OF` BINARY(1) NOT NULL,
                    `zwischen_OF` BINARY(1) NOT NULL,
                    `nach_OF` BINARY(1) NOT NULL,
                    `Annahme_Grundlage` VARCHAR(64) NOT NULL,
                    `Budget_AG_Bankraum` VARCHAR(7) NOT NULL,
                    `Budget_AG_FU` VARCHAR(7) NOT NULL,
                    `Datum_voraus` DATE,
                    `Budget_voraus` INT(11) NOT NULL
                    ) ENGINE=MEMORY;

    OPEN cur;

    tablesLoop: LOOP
        FETCH cur INTO tableName;

        IF endOfTables = 1 THEN
            LEAVE tablesLoop;
        END IF;

        SET @s = CONCAT('INSERT INTO alle_plaene (Name, Baugruppentyp, Basiseinheit, Zeit_Basiseinheit, vor_OF, zwischen_OF, nach_OF, Annahme_Grundlage, Budget_AG_Bankraum, Budget_AG_FU, Datum_voraus, Budget_voraus) SELECT `Name`, `Baugruppentyp`, `Basiseinheit`, `Zeit_Basiseinheit`, `vor_OF`, `zwischen_OF`, `nach_OF`, `Annahme_Grundlage`, `Budget_AG_Bankraum`, `Budget_AG_FU`, `Datum_voraus`, `Budget_voraus`   FROM ', tableName, ' ORDER BY ID DESC');
        PREPARE stmt FROM @s;
        EXECUTE stmt;

    END LOOP;

    CLOSE cur;

    SELECT `Name`, `Baugruppentyp`, `Basiseinheit`, `Zeit_Basiseinheit`, `vor_OF`, `zwischen_OF`, `nach_OF`, `Annahme_Grundlage`, `Budget_AG_Bankraum`, `Budget_AG_FU`, `Datum_voraus`, `Budget_voraus` FROM alle_plaene;

END 

Answer 1

这应该可行

    DELIMITER //

    DROP PROCEDURE IF EXISTS getallarbeitsplaene //
    CREATE PROCEDURE getallarbeitsplaene()
    BEGIN
        DECLARE tableName VARCHAR(64);
        DECLARE endOfTables INT DEFAULT 0;

        DECLARE cur CURSOR FOR
            SELECT t.table_name
            FROM information_schema.tables t
            WHERE t.table_schema = DATABASE()
              AND t.table_type='BASE TABLE'
              AND NOT table_name LIKE '%x_arbeitsplan'
              AND table_name LIKE '%_arbeitsplan';

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfTables = 1;

        DROP TEMPORARY TABLE IF EXISTS alle_plaene;

        CREATE TEMPORARY TABLE alle_plaene (
          `ID` INT(11) NOT NULL,
          `AP` VARCHAR(8) NOT NULL,
          `Name` VARCHAR(128) NOT NULL,
          `Baugruppentyp` VARCHAR(128) NOT NULL,
          `Basiseinheit` VARCHAR(8) NOT NULL,
          `Zeit_Basiseinheit` NUMERIC(10,0) NOT NULL,
          `vor_OF` BINARY(1) NOT NULL,
          `zwischen_OF` BINARY(1) NOT NULL,
          `nach_OF` BINARY(1) NOT NULL,
          `Annahme_Grundlage` VARCHAR(64) NOT NULL,
          `Budget_AG_Bankraum` VARCHAR(7) NOT NULL,
          `Budget_AG_FU` VARCHAR(7) NOT NULL,
          `Datum_voraus` DATE,
          `Budget_voraus` INT(11) NOT NULL,
          `tableName` VARCHAR(64) NOT NULL
        ) ENGINE=MEMORY;

        OPEN cur;

        tablesLoop: LOOP
          FETCH cur INTO tableName;

          IF endOfTables = 1 THEN
            LEAVE tablesLoop;
          END IF;

          SET @s = CONCAT('INSERT INTO alle_plaene (ID, AP, Name, Baugruppentyp, Basiseinheit, Zeit_Basiseinheit, vor_OF, zwischen_OF, nach_OF, Annahme_Grundlage, Budget_AG_Bankraum, Budget_AG_FU, Datum_voraus, Budget_voraus, tableName)  SELECT `ID`, `AP`, `Name`, `Baugruppentyp`, `Basiseinheit`, `Zeit_Basiseinheit`, `vor_OF`, `zwischen_OF`, `nach_OF`, `Annahme_Grundlage`, `Budget_AG_Bankraum`, `Budget_AG_FU`, `Datum_voraus`, `Budget_voraus`, ''', tableName, ''' FROM `', tableName, '` ORDER BY ID DESC');
          PREPARE stmt FROM @s;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
        END LOOP;

        CLOSE cur;

        SELECT * FROM alle_plaene;

    END //

DELIMITER ;

call getallarbeitsplaene();

此解决方案仅在几个地方与您的版本有所不同，但这里有一些要点供您考虑

information_schema.table_name 是 VARCHAR(64)。

您的代码中没有使用任何输入参数，所以我删除了它们。

您应该将 table_name 添加到临时表中，并在 @s = CONCAT(...) 语句中用带引号的字符串填充该列。

ID 和 AP 列需要填充到临时表中或完全删除，您将它们设置为 NOT NULL，因此它们必须获得一个值。

我不禁想知道为什么您每天都创建多个相同的表只是为了将输出组合到一个临时表中以一起查询，当然这里使用单个表的解决方案会更好。