MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

sql mysql performance
Irmantas picture Irmantas · May 14, 2009 · Viewed 379.2k times · Source

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?

Answer

Vinko Vrsalovic picture Vinko Vrsalovic · May 14, 2009

As far as I can tell, both syntaxes are equivalent. The first is SQL standard, the second is MySQL's extension.

So they should be exactly equivalent performance wise.

http://dev.mysql.com/doc/refman/5.6/en/insert.html says:

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.

Related questions

Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More
Database/SQL: How to store longitude/latitude data?

Performance question ... I have a database of houses that have geolocation data (longitude & latitude). What I want to do is find the best way to store the locational data in my MySQL (v5.0.24a) using InnoDB database-engine so that …

Read More