When reading legacy code, I found a SQL query to MySQL of this type:

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

As far as I understand, in MySQL there are two options for processing an insert request (if you do not take into account the option with INSERT ... SELECT ):

(one)

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

and 2)

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

This second option, in my opinion, is very good, if there are a lot of fields filled in by the request - you are not tied to the order of the fields in the table and you can see which value belongs to which field.

The variant, similar to the example of the Legacy code, I did not see in the manual. There is a strong suspicion that besides simply specifying the name of a field, such a construction can be used in some other way. Or is it just a redundant code that can be cleaned and reduced to either the standard version (1) or the MySQL-specific version (2) ?

  • I have never seen such a thing: INSERT INTO table SET field1 = value1, field2 = value2, field3 = value3; But here it is: UPDATE table SET field1 = value1, field2 = value2, field3 = value3; - Andrey Arshinov
  • @ AndreyArshinov, now you know) - Nofate ♦

2 answers 2

To begin with, the expression

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

completely valid syntactically. But in the presented form, it inserts a line of NULL into the table.

We will understand what is happening here. First of all, this is no “special syntax” for specifying values ​​to specific columns. This is the usual INSERT ... VALUES .

What do field1 , field2 and field3 do there? The point is that inside VALUES(...) permissible to refer to the value of another column for setting the value of a column. For example, to insert the value 5 in the first column, two times more in the second, and three times more in the third than the second, you could write:

 INSERT INTO table (field1, field2, field3) VALUES (5, field1 * 2, field2 * 3);

Let's go back to the original expression. Why is there to set the value of columns equals sign? Obviously: this is a comparison operation. The values ​​of the columns are given in order:

  1. field1 = value1 , since the column field1 is not yet defined, this is equivalent to NULL = value1 . A comparison with NULL will always return NULL .
  2. field1 = value2 . Now field1 already explicitly set to NULL . The result is similar, paragraph 1.
  3. field3 = value3 , again the situation, as with the first column.

As a result, we assigned all three columns NULL , which probably did not correspond to the author's intention.

    There is no "hybrid" option. The detected code is just some kind of error, it must be removed.