使用 SubQuery MySQL 插入 INTO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10644149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT INTO with SubQuery MySQL
提问by John Nu?ez
I have this Statement:
我有这个声明：
INSERT INTO qa_costpriceslog (item_code, invoice_code, item_costprice)
VALUES (1, 2, (SELECT item_costprice FROM qa_items WHERE item_code = 1));
I'm trying to insert a value copy the same data of item_costprice, but show me the error:
我正在尝试插入一个值复制 item_costprice 的相同数据，但显示错误：
Error Code: 1136. Column count doesn't match value count at row 1
How i can solve this?
我该如何解决这个问题？
回答by Michael Berkowski
Use numeric literals with aliases inside a SELECTstatement. No ()are necessary around the SELECTcomponent.
在SELECT语句中使用带有别名的数字文字。不需要()围绕SELECT组件。
INSERT INTO qa_costpriceslog (item_code, invoice_code, item_costprice)
SELECT
/* Literal number values with column aliases */
1 AS item_code,
2 AS invoice_code,
item_costprice
FROM qa_items
WHERE item_code = 1;
Note that in context of an INSERT INTO...SELECT, the aliases are not actually necessary and you can just SELECT 1, 2, item_costprice, but in a normal SELECTyou'll need the aliases to access the columns returned.
请注意，在 an 的上下文中INSERT INTO...SELECT，别名实际上不是必需的，您可以只使用SELECT 1, 2, item_costprice，但在正常情况下，SELECT您需要别名来访问返回的列。
回答by Elendurwen
You can just simply e.g.
你可以简单地例如
INSERT INTO modulesToSections (fk_moduleId, fk_sectionId, `order`) VALUES
((SELECT id FROM modules WHERE title="Top bar"),0,-100);
回答by Andrew Landsverk
Your insert statement contains too many columns on the left-hand side or not enough columns on the right hand side. The part before the VALUES has 7 columns listed, but the second part after VALUES only has 3 columns returned: 1, 2, then the sub-query only returns 1 column.
您的插入语句在左侧包含太多列或右侧没有足够的列。VALUES 之前的部分列出了 7 列，但 VALUES 之后的第二部分仅返回了 3 列：1、2，那么子查询只返回 1 列。
EDIT:Well, it did before someone modified the query....
编辑：嗯，它在有人修改查询之前就做了......
回答by John
I was disappointed at the "all or nothing" answers. I needed (again) to INSERTsome data and SELECTan idfrom an existing table.
我对“全有或全无”的答案感到失望。我需要（再次）INSERT一些数据和SELECT一个id来自现有表的数据。
INSERT INTO table1 (id_table2, name) VALUES ((SELECT id FROM table2 LIMIT 1), 'Example');
The sub-select on an INSERTquery should use parenthesis in addition to the comma as deliminators.
INSERT除了逗号作为分隔符之外，查询上的子选择还应使用括号。
For those having trouble with using a SELECTwithin an INSERTI recommend testing your SELECTindependently first and ensuring that the correct number of columns match for both queries.
对于那些使用麻烦SELECT内的INSERT，我建议你测试SELECT独立第一，并确保列的正确数量相匹配的两个查询。
回答by angularNoob
As a sidenote to the good answer of Michael Berkowski: You can also dynamically add fields (or have them prepared if you're working with php skripts) like so:
作为 Michael Berkowski 的好答案的旁注：您还可以像这样动态添加字段（或者如果您正在使用 php skripts，则准备好它们）：
INSERT INTO table_a(col1, col2, col3)
SELECT
col1,
col2,
CURRENT_TIMESTAMP()
FROM table_B
WHERE b.col1 = a.col1;
If you need to transfer without adding new data, you can use NULL as a placeholder.
如果您需要在不添加新数据的情况下进行传输，则可以使用 NULL 作为占位符。
