将MySQL插入到表值中。vs插入到表集中。

What is main difference between INSERT INTO table VALUES .. and 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?

那么这两个的性能呢?

3 个解决方案

#1

160

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.html says:

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.

插入将新行插入到现有表中。插入……值和插入……根据显式指定的值设置语句的设置形式。插入……选择表单插入从另一个表或表中选择的行。

#2

I think the extension is intended to allow a similar syntax for inserts and updates. In Oracle, a similar syntactical trick is:

我认为扩展的目的是允许插入和更新的语法类似。在甲骨文公司，类似的句法技巧是:

UPDATE table SET (col1, col2) = (SELECT val1, val2 FROM dual)

#3

Since the syntaxes are equivalent (in MySQL anyhow), I prefer the INSERT INTO table SET x=1, y=2 syntax, 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中)，我更喜欢插入到表集合x=1, y=2语法，因为它更容易修改，更容易在语句中捕获错误，尤其是插入大量列时。如果您必须插入10或15个或更多的列，那么在我看来，使用(x, y)值(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标准之间的可移植性是一个问题，那么可以将其插入到表(x, y)值(1,2)中。

And if you want to insert multiple records in a single query, it doesn't seem like the INSERT INTO ... SET syntax 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.

如果您想在单个查询中插入多个记录，那么插入到…设置语法会起作用，而另一个会。但在大多数实际情况下,你遍历一组记录插入总之,尽管可能有一些情况下,可能构建一个大的查询将一群行插入一个表在一个查询中,查询与对于每一行,可能有一个性能改进。真的不知道。

#1

160