如何将这些SQL查询组合在一起以提高MySQL的效率?

Question

我有以下查询，我想可能合并成一个或两个更大的查询:

SELECT user, password, email FROM users WHERE user = 'njp316' LIMIT 1;
INSERT IGNORE INTO collectors_users (username, password, email, dateadded, subscribed)
VALUES (user, password, email, NOW(), 'Y');

这个将拉回多行，所以它需要循环:

SELECT barcode FROM collections WHERE user = user;
INSERT IGNORE INTO collections_collections (username, barcode)
VALUES (user, barcode);

显然我不先进的SQL，所以只是寻找一些帮助。谢谢。

Answer 1

除非我错过了什么…这很简单:

INSERT IGNORE INTO collectors_users
(username, password, email, dateadded, subscribed)
SELECT user, password, email, NOW(), 'Y'
FROM users 
WHERE user = 'njp316' 
LIMIT 1;
INSERT IGNORE INTO collections_collections 
(username, barcode)
SELECT user, barcode 
FROM collections 
WHERE user = user;

Answer 2

可以使用子选择

INSERT INTO collectors_users (username, password, email, dateadded, subscribed)
SELECT user, password, email, NOW(), 'Y' FROM users WHERE user='njp316' LIMIT 1;

它使用SELECT作为输入INSERT的数据源。注意，列的顺序很重要。

Answer 3

试试这个:

INSERT IGNORE INTO collectors_users (username, `password`, email, dateadded, subscribed) 
SELECT `user`, `password`, email, NOW(), 'Y' 
FROM users WHERE `user` = 'njp316' LIMIT 1;
INSERT IGNORE INTO collections_collections (username, barcode) 
SELECT `user`,barcode 
FROM collections WHERE `user` = 'njp316';