在所有表中插入单独的表名作为列值

9fkzdhlc 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(376)

我使用的数据库有数百个表，这些表的结构和语法都非常相同。每天创建更多的表，因此数据库不断增长。为了动态追加数量不断增加的表，我使用 MySQL 这个程序很有效。
但是，一旦附加了行，我就无法识别行的起源，因为表不存在´不包含任何需要区分的信息。因此，我想集成一个 MySQL 语句自动获取 TABLE_NAME 从 information_schema 在相应的表中设置为一个值。我已经为此创建了一个专栏，但正在努力调整程序。我真的很感激任何帮助或建议来解决这个问题。

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

mysql Procedure information-schema tablename

来源：https://stackoverflow.com/questions/51258231/insert-individual-table-name-as-column-value-in-all-tables

1条答案

按热度按时间

ufj5ltwl1#

这应该管用

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\u schema.table\u name是varchar（64）。
你的代码中没有使用任何一个输入参数，所以我删除了它们。
您应该将表名称添加到临时表中，并在@s=concat（…）语句中用带引号的字符串填充该列。
这个 ID 以及 AP 列需要在temp表中填充或完全删除，因为它们不能为null，所以它们必须获得一个值。
我不禁要问，为什么每天都要创建多个相同的表，只是为了将输出合并到一个临时表中进行查询，单表解决方案当然更好。

赞(0）回复(0）举报 2021-06-21

我来回答

在所有表中插入单独的表名作为列值

1条答案

相关问题

热门标签

最新问答