MySQL INSERT 和 ON DUPLICATE SELECT 可能吗？

Question

email_address
-------------
id
prefix
fqdn

前缀和 FQDN 一起形成唯一的 key 。

我想编写一个查询，如果电子邮件地址尚未在表中，则该查询将执行插入操作。如果地址已在表中，则应返回 id。

有没有一种方法可以编写类似于INSERT INTO email_address(prefix, fqdn) VALUES (?, ?) ON DUPLICATE SELECT id as LAST_INSERT_ID WHERE prefix = ? AND fqdn = ?的查询如果合法的话会吗？

基本上我想在一个查询中执行 INSERT 或 SELECT。

Answer 1

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

来自http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html