MySQL insert where not exists / if not exists

问题:

I have tried the following query:

INSERT INTO `surfed_site` (user, site)
VALUES ('123', '456')
WHERE NOT EXISTS (SELECT site FROM `surfed_site` WHERE site=456)

But I keep getting a MySQL error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT site FROM surfed_site WHERE site=456)' at line 3

I have no clue what I'm doing wrong, would anybody be able to guide me?

回答1:

INSERT statements support two¹ syntaxes: one that uses VALUES, and one that uses a query. You can't combine them, and only the query syntax supports WHERE clauses. So:

INSERT INTO `surfed_site` (user, site)
SELECT '123', '456' FROM (SELECT 1) t
WHERE NOT EXISTS (SELECT site FROM `surfed_site` WHERE site=456)

---

1. Actually three syntaxes; you can also use SET. If you're only inserting one record, this one is functionally equivalent to VALUES, but arguably more readable.