有效地检查组中是否存在任何值以及它是哪一个？

Question

I have a list of 100 values (might scale in the future) that I need to put into a database. However when one of them already exists I need to know which one it is and grab some info from its row on the table. That is the goal. However I can't think of an efficent way to do this.

Things I've thought of

• Check if value exists. If not, submit. This is done value by value. Advantage: Easy. Disadvantage: Slow (minimum 100 queries, max 200 queries)
• Opposite of above. If query fails due to duplicate key constraints, query the value. Same advantages and disadvantages
• Insert all values at once. Run duplicate checker. Advantage: 2 (albeit huge) queries. Disadvantage: Difficult, possibly slow

There has to be a better way. Any idea's?

Answer 1

If I understand correctly, another option is to select all rows from the database matching your list in one query, then check array_intersect() in application code to find those which already exist in the database, or array_diff() to find those that don't.

// Your list into a comma-separated string
$your_list = implode(",", $your_list);

$dbexists = array();
$result = mysql_query("SELECT id FROM tbl WHERE id IN ($your_list)");
while ($row = mysql_fetch_assoc($result)) {
  $dbexists[] = $row['id'];
}

// Already existing from your set:
$exists = array_intersect($your_list, $dbexists);