动态SQL :为具有关系的表创建插入查询
动态SQL :为具有关系的表创建插入查询
提问于 2019-11-21 14:52:58
我有两张桌子，foo和bar。bar持有foo的外键，如：
foo
id | some_data
-: | :--------
1 | a
2 | b
3 | cbar
id | id_foo | more_data
-: | -----: | :--------
1 | 1 | d
2 | 1 | e
3 | 2 | f
4 | 3 | g
5 | 3 | h
6 | 3 | i我想动态地创建查询，以便插入到其他地方，foo和bar。
对于表foo，很容易做到：
SELECT CONCAT('INSERT INTO new_foo (some_data) VALUES (''', f.some_data, ''');') AS 'sql'
FROM foo f;我得到了预期的结果：
INSERT INTO new_foo (some_data) VALUES ('a');
INSERT INTO new_foo (some_data) VALUES ('b');
INSERT INTO new_foo (some_data) VALUES ('c');但是，我不得不为保存外键的表生成insert查询。
我认为使用两个UNION，一个获得最后一个插入的id，另一个使用这个id为bar生成查询：
SELECT sql
FROM
(
SELECT f.id AS 'id', CONCAT('INSERT INTO new_foo (some_data) VALUES (''', f.some_data, ''');') AS 'sql'
FROM foo f
UNION
SELECT f.id AS 'id', 'SET @last_id = SELECT LAST_INSERT_ID();' AS 'sql' -- Target will be MySQL
FROM foo f
UNION
SELECT b.id_foo AS 'id', CONCAT('INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, ''', b.more_data, ''');') AS 'sql'
FROM bar b
) s
ORDER BY s.id;这给了我输出：
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'd');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'e');
INSERT INTO new_foo (some_data) VALUES ('a');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'f');
INSERT INTO new_foo (some_data) VALUES ('b');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'g');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'h');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'i');
INSERT INTO new_foo (some_data) VALUES ('c');
SET @last_id = SELECT LAST_INSERT_ID();查询的顺序是错误的。它首先生成对bar的查询，然后生成foo，然后生成LAST_INSERT_ID()。
它应该是：
INSERT INTO new_foo (some_data) VALUES ('a');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'd');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'e');
INSERT INTO new_foo (some_data) VALUES ('b');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'f');
INSERT INTO new_foo (some_data) VALUES ('c');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'g');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'h');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'i');如何确保正确的秩序得到尊重？我可以在UNION中处理每个查询的位置，但我不确定条目的顺序是否会被保留。
下面是一个可以使用的小提琴
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-11-21 15:02:47
您有三行具有相同的id，并且没有指定顺序。您只需添加一个新列来指定顺序：
SELECT sql
FROM
(
SELECT f.id AS 'id', -1 AS seq, CONCAT('INSERT INTO new_foo (some_data) VALUES (''', f.some_data, ''');') AS 'sql'
FROM foo f
UNION
SELECT f.id AS 'id', 0 AS seq, 'SET @last_id = SELECT LAST_INSERT_ID();' AS 'sql' -- Target will be MySQL
FROM foo f
UNION
SELECT b.id_foo AS 'id', b.id AS seq, CONCAT('INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, ''', b.more_data, ''');') AS 'sql'
FROM bar b
) s
ORDER BY s.id, s.seq;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58977720
复制相关文章
相似问题
腾讯云开发者
