MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/861722/
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
MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
提问by Irmantas
What is main difference between INSERT INTO table VALUES ..and INSERT INTO table SET?
INSERT INTO table VALUES ..和 之间的主要区别是什么INSERT INTO table SET？
Example:
例子：
INSERT INTO table (a, b, c) VALUES (1,2,3)
INSERT INTO table SET a=1, b=2, c=3
And what about performance of these two?
而这两者的表现呢？
采纳答案by Vinko Vrsalovic
As far as I can tell, both syntaxes are equivalent. The first is SQL standard, the second is MySQL's extension.
据我所知，这两种语法是等效的。第一个是SQL标准，第二个是MySQL的扩展。
So they should be exactly equivalent performance wise.
所以他们应该在性能方面完全等效。
http://dev.mysql.com/doc/refman/5.6/en/insert.htmlsays:
http://dev.mysql.com/doc/refman/5.6/en/insert.html说：
INSERT inserts new rows into an existing table. The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables.
INSERT 将新行插入到现有表中。语句的 INSERT ... VALUES 和 INSERT ... SET 形式基于显式指定的值插入行。INSERT ... SELECT 表单插入从另一个表或多个表中选择的行。
回答by Vinko Vrsalovic
I think the extension is intended to allow a similar syntax for inserts and updates. In Oracle, a similar syntactical trick is:
我认为该扩展旨在为插入和更新提供类似的语法。在 Oracle 中，类似的语法技巧是：
UPDATE table SET (col1, col2) = (SELECT val1, val2 FROM dual)
回答by Aaron Wallentine
Since the syntaxes are equivalent (in MySQL anyhow), I prefer the INSERT INTO table SET x=1, y=2syntax, since it is easier to modify and easier to catch errors in the statement, especially when inserting lots of columns. If you have to insert 10 or 15 or more columns, it's really easy to mix something up using the (x, y) VALUES (1,2)syntax, in my opinion.
由于语法是等效的（无论如何在 MySQL 中），我更喜欢INSERT INTO table SET x=1, y=2语法，因为它更容易修改并且更容易捕获语句中的错误，尤其是在插入大量列时。如果您必须插入 10 或 15 列或更多列(x, y) VALUES (1,2)，在我看来，使用语法很容易混淆。
If portability between different SQL standards is an issue, then maybe INSERT INTO table (x, y) VALUES (1,2)would be preferred.
如果不同 SQL 标准之间的可移植性是一个问题，那么可能INSERT INTO table (x, y) VALUES (1,2)是首选。
And if you want to insert multiple records in a single query, it doesn't seem like the INSERT INTO ... SETsyntax will work, whereas the other one will. But in most practical cases, you're looping through a set of records to do inserts anyhow, though there could be some cases where maybe constructing one large query to insert a bunch of rows into a table in one query, vs. a query for each row, might have a performance improvement. Really don't know.
如果您想在单个查询中插入多条记录，INSERT INTO ... SET语法似乎不起作用，而另一个则可以。但在大多数实际情况下，您无论如何都要遍历一组记录以进行插入，尽管在某些情况下可能会构造一个大型查询以在一个查询中将一堆行插入到表中，而不是查询每一行，可能都有性能改进。真的不知道。
