mysql - 有没有更好的方法来查找表中某些内容的 ID，即使它不存在 [mysql]

Question

在写入规范化数据库时，我经常使用类似的逻辑。

伪代码:

is the thing I want in the table?:
    yes - get it's ID
else
    no - insert it, then get it's ID

在 PHP 中:

// is the useragent in the useragent table?
// if so, find the id, else, insert and find.
$useragentResult = $mysqli->query("SELECT id FROM useragent WHERE name = '".$useragent."' LIMIT 1");
if ($useragentResult->num_rows == 0) {
    // It is not in there
    $mysqli->query("INSERT INTO useragent (name) VALUES ('".$useragent."')");

    $resultID_object = $mysqli->query("SELECT LAST_INSERT_ID() as id");
    $row = $resultID_object->fetch_object();
    $useragentID = $row->id;
} else {
    // It is, so find it and set it
    $useragentData = $useragentResult->fetch_object();
    $useragentID = $useragentData->id;
}

这感觉很丑(不仅仅是 PHP 造成的!)，而且很常见，也许有更好的方法。

真正的方法是什么，或者这是最好的方法吗？

Answer 1

使用INSERT ... ON DUPLICATE KEY UPDATE。

自 MySQL 5.5 :

If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value.

或者在earlier versions中:

If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY 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;