时间戳

首页 > 解决方案 > 无法创建在两个表中插入数据的过程

mysql - 无法创建在两个表中插入数据的过程

问题描述

我有两张桌子tbl_pattern和tbl_pattern_features

我想根据 tbl_pattern 的 last_insert_id 将数据插入到 tbl_pattern_features 中。这是查询：

BEGIN

DROP INDEX fld_pattern_name ON tbl_pattern;
CREATE INDEX fld_pattern_name ON tbl_pattern (fld_pattern_name);

DECLARE @NewID INT(10)

IF pAction = "INSERT" THEN
INSERT INTO tbl_pattern(fld_pattern_name, fld_pattern_category, fld_pattern_utqg, pattern_image, fld_pattern_title, fld_pattern_description)
    VALUES (ppattern_name, ppattern_category, ppattern_utqg, ppattern_image, ppattern_title, ppattern_descrip);

SELECT @NewID = SCOPE_IDENTITY();

INSERT INTO tbl_pattern_features(fld_pattern_id, fld_feature_name, fld_feature_description, feature_image)
    VALUES (@NewID, ffeature_name, ppattern_utqg, ffeature_descrip, ffeature_image);
END IF;

END

标签： mysqlstored-procedures

解决方案

MySQL 没有SCOPE_IDENTITY(). 您正在寻找的功能是LAST_INSERT_ID()

SELECT LAST_INSERT_ID() as id

将返回

+----+
| id |
+----+
|  1 |
+----+

推荐阅读