I have two MySQL tables.

我有两个MySQL表。

One is a large list of entries which is basically the main table. The second is a table of pending entries. Pending entries will either be merged into the large list of entries if they're approved, or get discarded without any change to the main table if they're not.

一个是一个很大的条目列表，它基本上是主表。第二个是挂起条目的表。挂起的条目如果获得批准，将被合并到条目的大列表中，或者如果未获得批准，则在不更改主表的情况下被丢弃。

Both tables have an auto increment primary key and the exact same structure for all the other columns.

这两个表都有一个自动递增主键，其他所有列的结构完全相同。

I use the primary key on the pending entries table for edits of the pending entries, but it doesn't matter beyond that point once it's approved.

我使用挂起条目表上的主键来编辑挂起条目，但一旦批准，超过这个点就无关紧要了。

I'd like to do an insert similar to this:

我想做一个类似的插入：

INSERT INTO `main_table` SELECT * FROM `pending_table`

It's super simple and easy! However, of course, there is an error:

它超级简单！但是，当然也有一个错误：

Error! Duplicate entry '1' for key 'PRIMARY'

At the moment, I'm using the following query:

目前，我正在使用以下查询：

INSERT INTO `main_table` (`col1`, `col2`, ..., `coln`)
SELECT `col1`, `col2`, ..., `coln` FROM `pending_table`

This can also work and is a bit shorter:

这也可以工作，而且更短：

INSERT INTO `main_table`
SELECT NULL, `col1`, `col2`, ..., `coln` FROM `pending_table`

col1, col2, ..., coln represent all the columns except the primary key column, and I have to list them out one by one.

col1，col2。。。，coln表示除主键列之外的所有列，我必须逐一列出它们。

Of course, that query immediately breaks if any of the columns in the table ever change, and there's a really good chance I will want to add new columns in the future. It's pretty easy to add the same column to both tables. What I'd like to avoid is having to go into the PHP file(s) and manually add the column there as well in every place that it's used.

当然，如果表中的任何列发生更改，该查询会立即中断，而且我很有可能在将来添加新列。将同一列添加到两个表中非常容易。我想避免的是，必须进入PHP文件，并在使用的每个地方手动添加列。

What I would like instead is to set so the primary keys on the pending_table will be discarded and replaced with unique primary keys that are unused in main_table. All the other columns will copy across. I haven't been able to find any option to do this.

相反，我想设置pending_table上的主键，使其被丢弃，并替换为main_table中未使用的唯一主键。所有其他列都将进行复制。我一直找不到做这件事的任何选择。

• The REPLACE command doesn't help because that's going to overwrite all the entries that already exist. I also couldn't find anything on the DUPLICATE KEY UPDATE option of INSERT.


• I found a question here that might be asking something similar but I can't understand the solution proposed and I'm hoping for something simple. This one is just a generic explanation of the problem potentially. This just seems completely different as does this one. This guy just made a mistake and he's already doing my "solution" above. I haven't been able to find any other questions that seem related.


• I can make code to check the table structure and produce a dynamic column list. However, that just seems like a large kludge and I'm hoping to find a simpler and more elegant solution without adding/maintaining/running a large block of extra PHP code.


• I also thought of dropping the primary key column from the pending table and then reconstructing the whole table afterward, but that also feels kind of kludgey. I'm also not sure it would work because the column count would be different.


• Similarly, manually updating the primary key column by reading the main table ahead of time could work but also feels really kludgy. And it could run into problems (race condition) if some other process adds a row to the main table while those keys are being assigned.

Is there any simpler solution, or am I already doing the simplest solution?

有没有更简单的解决方案，或者我已经在做最简单的解决方法了？

Thanks for any help/ideas.

谢谢你的帮助/想法。

I did find one potential solution.

我确实找到了一个潜在的解决方案。

Rather than deleting the primary key column, I can remove the auto increment and index as follows. id needs to be the name of the primary key column and the type has to match:

我可以删除自动递增和索引，而不是删除主键列，如下所示。id需要是主键列的名称，并且类型必须匹配：

ALTER TABLE `pending_table` DROP PRIMARY KEY,
CHANGE `id` `id` int(11)

From here, the primary key field needs to be set to NULL for the insert.

从这里开始，插入时需要将主键字段设置为NULL。

UPDATE `pending_table` SET `id` = NULL

Now, the insert will work without any error from duplicate primary keys.

现在，插入操作将不会出现任何来自重复主键的错误。

INSERT INTO `main_table` SELECT * FROM `pending_table`

Then, we just need to restore the primary key in the pending table with this query:

然后，我们只需要用这个查询恢复挂起表中的主键：

ALTER TABLE `pending_table` ADD PRIMARY KEY (`id`),
CHANGE `id` `id` int(11) AUTO_INCREMENT

It's not the simplest or most elegant solution in the world but it's all SQL, works regardless of the column structure, and it doesn't require any extra PHP to be maintained or updated.

它不是世界上最简单或最优雅的解决方案，但它都是SQL，无论列结构如何都能工作，而且不需要任何额外的PHP来维护或更新。

Note that the pending table keys could be changed in the process.

请注意，在此过程中可以更改挂起的表键。

It's super simple and easy!

No, as you've discovered it is not.

不，正如你所发现的那样。

Leaving aside the primary key issue, this method (INSERT INTO table SELECT *...) is dependent on both tables having the SAME attributes, in the same order AND honouring the order of attributes within the table (which IIRC is not part of standard SQL). So while having to enumerate the attributes from both tables could result in undesirable consequences if the table structure changes, in the absence of a primary key there are several other things which can go wrong.

抛开主键问题不谈，此方法（INSERT INTO表SELECT*…）依赖于具有相同属性的两个表，其顺序相同，并遵循表中属性的顺序（IIRC不是标准SQL的一部分）。因此，尽管如果表结构发生变化，必须枚举两个表中的属性可能会导致不希望的结果，但在没有主键的情况下，还有其他一些事情可能会出错。

I did find one potential solution.

It avoids the specific issue you encountered. But is not a good solution.

它避免了您遇到的特定问题。但这不是一个好的解决方案。

OTOH as presented it is an accident waiting to happen. There are no concurrency controls in your code to manage the safe transition of data from one table to the other. With concurrent access, there are race conditions where you will lose data.

OTOH目前是一场等待发生的事故。代码中没有并发控制来管理数据从一个表到另一个表的安全转换。对于并发访问，在竞争条件下您将丢失数据。

As usual, the RIGHT way to fix this is to normalize your data - as per @P.Salmon's comment above.

和往常一样，解决这个问题的正确方法是规范化你的数据——正如@P.Salmon在上面的评论。

IMHO the issues arising from assuming SELECT * are worse than those from enumerating attributes. Using SELECT * also has a performance cost (cache flushing, no covering indexes). So as a general rule, I always avoid SELECT *.

IMHO假设SELECT*产生的问题比枚举属性产生的问题更糟糕。使用SELECT*也会带来性能成本（缓存刷新，没有覆盖索引）。因此，作为一般规则，我总是避免SELECT*。