MySQLi如果值不存在则插入

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?

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.

I haven't completely understood that: there is no common KEY and therefore I can't do a DUPLICATE KEY. so I'm not sure that you will find this useful but you can choose if you use it...

Perhaps you'll find more simple if you just try INSERT IGNORE :

INSERT IGNORE INTO new_table (a,b,c,d) 
SELECT a,b,c,d FROM old_table

IGNORE word will discard automatically any insert wich generates a primary o unique key conflict.

You also can directly insert from a select query. This can make things easier for this job

Well, I believe you don't have to use a script for this, a query would be enoguh:

SELECT
  /* your complex columns, from and joins go here */
LEFT JOIN `new_table` n ON n.a = old_table.a AND n.b LIKE old_table.b
WHERE
  n.a IS NULL AND n.b IS NULL AND
  /* your WHERE and LIMIT go here */

This approach makes use of LEFT JOIN which, if there is no matching row in the right table, sets all columns to NULL (documented here).