MySQL 使用 SELECT 插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5391344/
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 with SELECT
提问by Kyle
I have a query that inserts using a select:
我有一个使用选择插入的查询：
INSERT INTO courses (name, location, gid)
SELECT name, location, gid
FROM courses
WHERE cid = $cid
Is it possible to only select "name,location" for the insert, and set gid to something else in the query?
是否可以只为插入选择“名称，位置”，并将 gid 设置为查询中的其他内容？
回答by Andrew
Yes, absolutely, but check your syntax.
是的，绝对，但请检查您的语法。
INSERT INTO courses (name, location, gid)
SELECT name, location, 1
FROM courses
WHERE cid = 2
You can put a constant of the same type as gidin its place, not just 1, of course. And, I just made up the cidvalue.
当然，您可以gid在其位置放置一个相同类型的常量，而不仅仅是 1。而且，我只是弥补了cid价值。
回答by Dumitrescu Bogdan
Yes, it is. You can write :
是的。你可以写 ：
INSERT INTO courses (name, location, gid)
SELECT name, location, 'whatever you want'
FROM courses
WHERE cid = $ci
or you can get values from another join of the select ...
或者您可以从 select 的另一个连接中获取值...
回答by RolandoMySQLDBA
Correct Syntax: select spelling was wrong
正确语法：选择拼写错误
INSERT INTO courses (name, location, gid)
SELECT name, location, 'whatever you want'
FROM courses
WHERE cid = $ci
回答by klennepette
Sure, what do you want to use for the gid? a static value, PHP var, ...
当然，您想对 gid 使用什么？一个静态值，PHP var，...
A static value of 1234 could be like:
1234 的静态值可能类似于：
INSERT INTO courses (name, location, gid)
SELECT name, location, 1234
FROM courses
WHERE cid = $cid
回答by Tobias
I think your INSERT statement is wrong, see correct syntax: http://dev.mysql.com/doc/refman/5.1/en/insert.html
我认为您的 INSERT 语句是错误的，请参阅正确的语法：http: //dev.mysql.com/doc/refman/5.1/en/insert.html
edit: as Andrew already pointed out...
编辑：正如安德鲁已经指出的那样......
回答by Bud Damyanov
Of course you can.
当然可以。
One thing should be noted however: The INSERT INTO SELECTstatement copies data from one table and inserts it into another table AND requiresthat data types in source and target tables match. If data types from given table columns does not match (i.e. trying to insert VARCHARinto INT, or TINYINTintoINT) the MySQL server will throw an SQL Error (1366).
但是，应该注意一件事：该INSERT INTO SELECT语句从一个表复制数据并将其插入到另一个表中，并且要求源表和目标表中的数据类型匹配。如果从给定表列中的数据类型不匹配（即试图插入VARCHAR到INT，或TINYINT进入INT）MySQL服务器将抛出SQL Error (1366)。
So be careful.
所以要小心。
Here is the syntax of the command:
这是命令的语法：
INSERT INTO table2 (column1, column2, column3)
SELECT column1, column2, column3 FROM table1
WHERE condition;
Side note: There is a way to circumvent different column types insertion problem by using castingin your SELECT, for example:
旁注：有一种方法可以通过在您的 中使用强制转换来规避不同列类型的插入问题SELECT，例如：
SELECT CAST('qwerty' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;
This conversion (CAST()is synonym of CONVERT()) is very useful if your tables have different character sets on the same table column (which can potentially lead to data loss if not handled properly).
如果您的表在同一表列上具有不同的字符集（如果处理不当，可能会导致数据丢失），这种转换（CAST()是 的同义词CONVERT()）非常有用。
回答by Martin Rafael Pérez Lara
The right Syntax for your query is:
查询的正确语法是：
INSERT INTO courses (name, location, gid)
SELECT (name, location, gid)
FROM courses
WHERE cid = $cid
