Mysql INSERT Statement Differences

Question

I normally found three ways of using MYSQL insert Command

INSERT INTO `table` (field1, field2, field3) VALUES ('value1', 'value2', 'value3');

or

INSERT INTO `table` VALUES ('value1', 'value2', 'value3');

or

INSERT INTO `table` SET `field1`='value1', `field2`='value2', `field3`='value3';

Is there any differences among these?

Answer 1

Only one difference, pretty obvious to me: the second one don't let you specify your own field set to insert.

Answer 2

You are looking for differences in terms of what? In terms of usage, the first and third insert statements let you to set only those columns which you need whereas the second statement always tries to insert the values to all columns in order of table structure.

Answer 3

The first two are standard SQL; the third one is non-standard and specific to MySQL, derived from the standard syntax for UPDATE.

The difference between the first two is that one specifies the fields that you want to insert, which is more flexible because you can miss out fields if you're happy with them being set to NULL. Also specifying the fields means that the statement will still work if the table layout changes; if you don't specify the fields you're relying on the table never changing.