MySQL INSERT INTO: VALUES vs. SET – Which Syntax Should You Use?

MySQL INSERT INTO: VALUES vs. SET – Choosing the Right Syntax

The INSERT INTO statement adds new records to a MySQL table. Two common syntax variations exist: using VALUES and using SET. This article clarifies their differences and helps you choose the best approach.

Syntax Variations

• VALUES Syntax: This standard SQL syntax lists column values within parentheses. The order of values must match the table's column order.

• SET Syntax: This MySQL-specific syntax assigns values to columns individually using column_name = value pairs. Column order is irrelevant.

Illustrative Example:

<code class="language-sql">-- VALUES syntax
INSERT INTO my_table (col1, col2, col3) VALUES (10, 'value2', 30);

-- SET syntax
INSERT INTO my_table SET col1 = 10, col2 = 'value2', col3 = 30;</code>

Performance and Equivalence

MySQL documentation confirms that both methods are functionally equivalent in terms of performance. They both achieve the same result: adding a new row with the specified data.

However, a key difference lies in SQL standard compliance. VALUES is standard SQL, while SET is a MySQL extension. Therefore, VALUES offers better portability across different database systems.

Recommended Approach

While the SET syntax can enhance readability, especially when dealing with numerous columns or selectively inserting data, the standard VALUES syntax is generally preferred due to its broader compatibility. The optimal choice depends on individual project requirements and coding style preferences.

The above is the detailed content of MySQL INSERT INTO: VALUES vs. SET – Which Syntax Should You Use?. For more information, please follow other related articles on the PHP Chinese website!