我已经看过SQL主键约束，尽管不存在记录，所以SO上所有问题中的哪个似乎最接近我的问题，但并不相同。

毫无疑问，我可能正在做一些愚蠢的事情，但是这里有：

我正在尝试编写一个脚本（用php编写），该脚本将从任何给定的PDO数据库迁移数据（没有结构，它假定结构已经完成）到任何其他给定的PDO数据库-
在我的情况下，我正在sqlite3上对其进行测试- > MySQL。

当我在测试数据库上运行脚本时，我收到“完整性约束冲突：1062项“ PRIMARY”的重复项“
1””，我不太了解，因为表中没有数据（即使在脚本运行之前也是如此） DELETE语句）。

我假设这与主键是auto_increment有关，但是我尝试将下一个增量值设置为除要插入的任何值之外的其他值（认为我尝试将其设置为80）-没什么区别。

我一直在寻找一种在交易过程中禁用auto_increment的方法，但没有事先更改表，然后再更改回去的方法，我想不出办法-
更改整个表似乎是错误的，我并不想参与任何DDL。

  1 <?php
  2
  3 $abspath = dirname(__FILE__)."/";
  4
  5 $source_dsn = 'sqlite:'.$abspath.'db.sqlitedb';
  6 $source_username = null;
  7 $source_password = null;
  8 $target_dsn = "mysql:dbname=name;host=127.0.0.1";
  9 $target_username = "name";
 10 $target_password = "pass";
 11
 12 $transfer_data = array();
 13 $table_data = array();
 14
 15 try {
 16
 17         // connect to source
 18         $source = new PDO($source_dsn, $source_username, $source_password);
 19         $source->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 20
 21         // connect to target
 22         $target = new PDO($target_dsn, $target_username, $target_password);
 23         $target->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 24
 25         //TODO Generalise this statement to all database types.
 26         $stmt = $source->prepare("SELECT * FROM sqlite_master WHERE type='table';");
 27         $stmt->execute();
 28
 29         // get all tables
 30         while($tablerow = $stmt->fetch(PDO::FETCH_ASSOC))
 31         {
 32                 // TODO Generalise these statements to all database types.
 33                 $transfer_data[$tablerow['tbl_name']] = array();
 34                 $table_data[$tablerow['tbl_name']] = array();
 35         }
 36         $stmt->closeCursor();
 37
 38         // for each table, load data
 39         foreach($transfer_data as $tablename => $void)
 40         {
 41                 $stmt = $source->prepare("SELECT * FROM $tablename;");
 42                 $stmt->execute();
 43                 // load data row at a time
 44                 while($datarow = $stmt->fetch(PDO::FETCH_ASSOC))
 45                 {
 46                         // store data for later
 47                         $transfer_data[$tablename][] = $datarow;
 48                         // if we haven't gained column data yet, do so now
 49                         if(!array_key_exists($tablename,$table_data))
 50                         {
 51                                 $t_data = array();
 52                                 foreach($datarow as $colname => $void)
 53                                 {
 54                                         $t_data[] = $colname;
 55                                 }
 56                                 $table_data[$tablename] = $t_data;
 57                         }
 58                 }
 59                 $stmt->closeCursor();
 60                 echo "Read $tablename\n";
 61         }
 62
 63         //start a transaction (if driver supports transactions / if not then this is noop)
 64         $target->beginTransaction();
 65         // for each table clear existing data and insert copied data
 66         foreach($table_data as $tablename => $columns)
 67         {
 68                 // not using an empty/truncate because mysql and possibly others autocommit
 69                 $stmt = $target->prepare("DELETE FROM $tablename;");
 70                 $stmt->execute();
 71                 $stmt->closeCursor();
 72
 73                 // prepare the insert statement - we don't know how many columns so is dynamic
 74                 $querystr = "INSERT INTO $tablename (".join(", ",$columns).") VALUES (";
 75                 foreach($columns as $k => $column)
 76                 {
 77                         $columns[$k] = ':'.$column;
 78                 }
 79                 // using named placeholders so order doesn't matter
 80                 $querystr = $querystr.join(", ",$columns).");";
 81                 $stmt = $target->prepare($querystr);
 82                 //echo "Using: $querystr\n";
 83                 $rowcount = 0;
 84                 // for each row of data, bind data and execute insert statement
 85                 foreach($transfer_data[$tablename] as $rowdata)
 86                 {
 87                         foreach($rowdata as $rowname => $rowvalue)
 88                         {
 89                                 $stmt->bindParam(':'.$rowname, $rowvalue);
 90                         }
 91                         $stmt->execute();
 92                         $stmt->closeCursor();
 93                         $rowcount++;
 94                 }
 95                 echo "Written $rowcount rows to $tablename\n";
 96         }
 97         $target->commit();
 98
 99 }
100 catch (PDOException $e)
101 {
102         echo 'PDO Error: '.get_class($e).' - '.$e->getMessage()."\n";
103         echo 'Query String was: '.$querystr."\nData:\n";
104         var_export($transfer_data[$tablename]);
105         if($target->inTransaction()){
106                 $target->rollBack();
107         }
108 }
109

现在我的目标数据库中有一个表，该表是：

+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| channel_id        | int(11)          | NO   | PRI | NULL    | auto_increment |
| channel_parent_id | int(10) unsigned | YES  |     | NULL    |                |
| server_id         | int(10) unsigned | NO   | MUL | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+

输出为：

PDO Error: PDOException - SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'
Query String was: INSERT INTO channels (channel_id, channel_parent_id, server_id) VALUES (:channel_id, :channel_parent_id, :server_id);
Data:
array (
  0 =>
  array (
    'channel_id' => '1',
    'channel_parent_id' => '0',
    'server_id' => '1',
  ),
  1 =>
  array (
    'channel_id' => '24',
    'channel_parent_id' => '0',
    'server_id' => '1',
  ),
  2 =>
  array (
    'channel_id' => '34',
    'channel_parent_id' => '0',
    'server_id' => '1',
  ),