MySQL Insert into Table Values.. VS Insert into Table Set

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

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.

Insert Set or Insert Values

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

Refer to the SQL-92 standard syntax.

MySQL insert into table with a set of values

Is this what you want?

INSERT IGNORE INTO customer_feature(customer_id, feature)
    select c.id, f.feature
    from customer c cross join
         (select 'NDA' as feature union all select 'SDM' union all select 'APM'
         ) f
    where c.edition = 'FREE_TRIAL';

Insert into table from CTE

Try this syntax:

INSERT INTO tmp( tmp_id )
  WITH cte AS (
    SELECT 1 AS tmp_id FROM dual
  )
  SELECT tmp_id
  FROM cte;

https://dev.mysql.com/doc/refman/8.0/en/with.html

Mysql INSERT INTO SET ? syntax

That's not valid MySQL syntax.

This is valid MySQL syntax:

INSERT INTO feedback.app_v2 SET mycolumn = ?

Yes, it is safe from SQL injection, because the dynamic value is restricted to a query parameter. This is not combined with the query until after the query has been parsed, so there is no way the parameter can introduce unintended syntax.

---

If the NPM package can do string substitution to put the key = 'value' syntax into the query string, that's not a true query parameter. The assignment must be part of the query before it is parsed, but proper query parameters are not combined with the query until after it is parsed.

So you're depending on the code in the NPM package to do the string substitution without any bugs that result in SQL injection vulnerability.

INSERT INTO table VALUES.. vs INSERT INTO table SET Error

Remove 'and' from the insert query

insert into visits set source = 'http://google.com' , country = 'en' , ref = '1234567890'

---

Related Topics