Mysql 在现有表中插入数据。如果数据已经存在，如何自动为数据提供新的ID

Question

我有两个表在两个不同的数据库中运行，但结构是相同的。我想将一个表的数据导入到另一个表中，但行的 id 是自动增量的。这会导致两个表中的 id 具有相同的值，但内容不同。如何将 table1 的内容插入到 table 2 中并自动将 id 更新为尚不存在的值？

因为该表包含大约 1000 行，所以我无法手动更改数字或声明每一行。类似ON DUPLICATE 'id' AUTO INCRMENT 'id'？

Answer 1

这可能就是这样

Hitesh> desc test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| name  | varchar(200) | YES  |     | NULL    |                |
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Hitesh> desc test_new;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| name  | varchar(200) | YES  |     | NULL    |                |
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Hitesh> insert into test_new(name) select name from test;
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0

Hitesh> select * from test_new;
+-------------------------+----+
| name                    | id |
+-------------------------+----+
| i am the boss           |  1 |
| You will get soon       |  2 |
| Happy birthday bro      |  3 |
| the beautiful girl      |  4 |
| oyee its sunday         |  5 |
| cat and dog in a park   |  6 |
| dog and cat are playing |  7 |
| cat                     |  8 |
| dog                     |  9 |
+-------------------------+----+
9 rows in set (0.00 sec)