MySQLi如果值不存在则插入

Question

EDIT: I forgot to mention that my script should not input IF a row already exists in the new DB with the same column values, that is what I mean when I refer to "duplicate entries" despite there not being a common PK.

Here's my dilemma, I'm working with MySQLi to migrate data from an old table into a new table which have different designs and I want my program to be able to run multiple times without multiplying previous entries. My initial approach was to do a verification query for each inserted element:

//foreach elt of old table:
  $a = $old_table['a'];
  $b = $old_table['b'];
  $query = $db->query("SELECT `id` FROM `old_table` 
                       WHERE `a` = '$b' 
                       AND `b` LIKE '$b'")->fetch_assoc();
  if ($query == null) {
    //insert a row into the new table
  }

The problem with this method is that the run-time was horrendous and I managed to considerably cut it down by using a database transaction:

$query = $db->prepare("INSERT INTO  `new_table` 
          (`a`, `b`, `c`, `d`, `e`)
          VALUES (?, ?, ?, ?, ?)");
$query->bind_param('isssi', $a, $b, $c, $d, $e);
$db->query("START TRANSACTION");
foreach ($old_table as $old_row) {
  $a = $old_row['a'];
  ...
  $e = $old_row['e'];
  $query->execute();
}
$query->close();
$db->query("COMMIT");

The problem with this method is that it results in multiple entries if the program is run more then once. It's important to note that since both tables have different designs, there is no common Primary Key and therefore I don't think I can use DUPLICATE KEY.

Thoughts?

Answer 1

In fact, you already solved the problem, but for some reason stopped half-way.

The problem with this method is that the run-time was horrendous and I managed to considerably cut it down by using a database transaction

I wonder why didn't you include select into transaction as well.

Thoughts?

Just add select query you used to run in the first variant. That's all.