Clever Geek Handbook

Paste "Set" or "Paste" values 

INSERT INTO `table` VALUES ('val1','val2','val3')

INSERT INTO `table` SET a='val1',b='val2',c='val3'

Both are used for the same purpose. But which one should I use? in this case? and why?

+3


source to share


3 answers


They are identical, but the first is standard SQL . Since the SQL parser treats them as synonyms, there is no difference in performance, usage, or anything. As always, prefer to use the standardized form, the first syntax in this case, for portability and compatibility.



Refer to the standard SQL-92 syntax .

+3


source


As far as I can tell, both syntaxes are equivalent.

The first is SQL standard, the second is MySQL extension.

Thus, they must be exactly equivalent characteristics.



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.
0


source


Fields are not always required in a table. Sometimes you just want to set some fields not for everyone. Then you will use the second shape.

The first form is usually generated automatically with a script like:

$query = "INSERT INTO `table` VALUES ("
for(@values) {
   $query .= "'$_'";
}
$query .= ")";

mysql_query($query);

But the correct way would be to use a more conventional form:

@fields = (qw/name firstname address/);
@values = (qw/Wall Larry Paris/);

$query = "INSERT INTO `table` (";
$query .= join(',', @fields);    
$query .= ") VALUES (";
$query .= join(',', @values);
$query .= ")";



mysql_query($query);
-1


source






More articles:


All Articles