时间戳

首页 > 解决方案 > 使用表字段作为存储过程参数（将给定的表字段重新分配到其他表中）

mysql - 使用表字段作为存储过程参数（将给定的表字段重新分配到其他表中）

问题描述

我有下表的结构：

ITEMS:
╔═══════════╤══════════════╤══════╤═════╤═════════╤════════════════╗
║ FIELD     │ TYPE         │ NULL │ KEY │ DEFAULT │ EXTRA          ║
╠═══════════╪══════════════╪══════╪═════╪═════════╪════════════════╣
║ id        │ int          │ NO   │ PRI │         │ auto_increment ║
╟───────────┼──────────────┼──────┼─────┼─────────┼────────────────╢
║ image_url │ varchar(255) │ NO   │     │         │                ║
╚═══════════╧══════════════╧══════╧═════╧═════════╧════════════════╝

ITEM_TRANSLATIONS:
╔═════════════╤══════════════╤══════╤═════╤═════════╤════════════════╗
║ FIELD       │ TYPE         │ NULL │ KEY │ DEFAULT │ EXTRA          ║
╠═════════════╪══════════════╪══════╪═════╪═════════╪════════════════╣
║ id          │ int          │ NO   │ PRI │         │ auto_increment ║
╟─────────────┼──────────────┼──────┼─────┼─────────┼────────────────╢
║ description │ varchar(255) │ NO   │     │         │                ║
╟─────────────┼──────────────┼──────┼─────┼─────────┼────────────────╢
║ title       │ varchar(45)  │ NO   │     │         │                ║
╚═════════════╧══════════════╧══════╧═════╧═════════╧════════════════╝

我还有一个存储过程，它以这种方式将其参数重新分配到所需的表中：

DELIMITER //
    DROP PROCEDURE IF EXISTS addItem //
    CREATE PROCEDURE addItem (
        IN _item__image_url VARCHAR(255),
        IN _item_translations__title VARCHAR(45),
        IN _item_translations__description VARCHAR(255)
    )

    BEGIN
        START TRANSACTION;
            INSERT INTO item (
                image_url
            )
            VALUES (
                _item__image_url
            );


            INSERT INTO item_translations (
                item_id,
                title,
                `description`
            )
            VALUES (
                LAST_INSERT_ID(),
                _item_translations__title,
                _item_translations__description
            );
        COMMIT ;
    END //
DELIMITER ;

如果我这样调用这个过程：

CALL addBrand(
    "/images/items.png",
    "My Item",
    "An oversimplified item just for this question."
);

我得到以下预期：

ITEMS:
╔════╤═══════════════════╗
║ ID │ IMAGE_URL         ║
╠════╪═══════════════════╣
║ 19 │ /images/items.png ║
╚════╧═══════════════════╝

ITEM_TRANSLATIONS:
╔════╤═════════╤═════════╤════════════════════════════════════════════════╗
║ ID │ ITEM_ID │ TITLE   │ DESCRIPTION                                    ║
╠════╪═════════╪═════════╪════════════════════════════════════════════════╣
║ 7  │ 19      │ My Item │ An oversimplified item just for this question. ║
╚════╧═════════╧═════════╧════════════════════════════════════════════════╝

我有第三张表，其中有N行，其中包含所有必填字段：

IMPORTED_TABLE
╔════╤══════════════╤══════════════════════════════════╤════════════════════════╗
║ ID │ TITLE        │ DESCRIPTION                      │ IMAGE_URL              ║
╠════╪══════════════╪══════════════════════════════════╪════════════════════════╣
║ 42 │ Another Item │ Yet another oversimplified item. │ /images/items_2.png    ║
╟────┼──────────────┼──────────────────────────────────┼────────────────────────╢
║ 43 │ This Item    │ A nice item                      │ /images/thanks.png     ║
╟────┼──────────────┼──────────────────────────────────┼────────────────────────╢
║ 44 │ Trixie Item  │ The great and powerful item!     │ /images/mlp/trixie.png ║
╚════╧══════════════╧══════════════════════════════════╧════════════════════════╝

如何将此表内容用作存储过程的参数，以便能够根据需要填充所需的表？ 为了得到这个：

ITEMS:
╔════╤════════════════════════╗
║ ID │ IMAGE_URL              ║
╠════╪════════════════════════╣
║ 19 │ /images/items.png      ║
╟────┼────────────────────────╢
║ 20 │ /images/items_2.png    ║
╟────┼────────────────────────╢
║ 21 │ /images/thanks.png     ║
╟────┼────────────────────────╢
║ 22 │ /images/mlp/trixie.png ║
╚════╧════════════════════════╝

ITEM_TRANSLATIONS
╔════╤═════════╤══════════════╤════════════════════════════════════════════════╗
║ ID │ ITEM_ID │ TITLE        │ DESCRIPTION                                    ║
╠════╪═════════╪══════════════╪════════════════════════════════════════════════╣
║ 7  │ 19      │ My Item      │ An oversimplified item just for this question. ║
╟────┼─────────┼──────────────┼────────────────────────────────────────────────╢
║ 8  │ 20      │ Another Item │ Yet another oversimplified item.               ║
╟────┼─────────┼──────────────┼────────────────────────────────────────────────╢
║ 9  │ 21      │ This Item    │ A nice item                                    ║
╟────┼─────────┼──────────────┼────────────────────────────────────────────────╢
║ 10 │ 22      │ Trixie Item  │ The great and powerful item!                   ║
╚════╧═════════╧══════════════╧════════════════════════════════════════════════╝

显然，这是一个过于简单的例子。在存储过程中，对所有参数都有不同的数据处理，所以我不必重新创建存储过程。

标签： mysqlstored-procedures

解决方案

您的ID值是否IMPORTED_TABLE应该用于IDin ITEMS？

如果是这样，那么你可以这样做：

START TRANSACTION;

INSERT INTO ITEMS (ID, IMAGE_URL) 
 SELECT ID, IMAGE_URL FROM IMPORTED_TABLE;

INSERT INTO ITEM_TRANSLATIONS (ITEM_ID, TITLE, DESCRIPTION)
 SELECT ID, TITLE, DESCRIPTION FROM IMPORTED_TABLE;

COMMIT;

这将ID逐字使用ITEMS.ID和的值ITEM_TRANSLATIONS.ITEM_ID。

但是，如果您想插入 URL 并忽略ID导入数据中的值，并让ITEMS表生成新的 ID 值，那么您可以分批进行，并假设该批是一组连续值。

START TRANSACTION;

INSERT INTO ITEMS (IMAGE_URL) 
 SELECT IMAGE_URL FROM IMPORTED_TABLE;

SET @START_ID = LAST_INSERT_ID() - 1;

INSERT INTO ITEM_TRANSLATIONS (ITEM_ID, TITLE, DESCRIPTION)
 SELECT (@START_ID := @START_ID+1), TITLE, DESCRIPTION FROM IMPORTED_TABLE;

COMMIT;

假设这些值是连续的是否安全？默认情况下，是的，它是安全的。例如，当您执行批量插入时，MySQL 的 JDBC 驱动程序会做出此假设，因此它可以返回一组生成的 ID 值。

例外情况是，如果您在具有选项innodb_autoinc_lock_mode=2集的 MySQL 实例上，则不能保证这些值是连续的。这不是默认设置，因此它可能不适用于您的情况。

（阅读https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html了解详情）

推荐阅读