Question

我有一个非常大的 MySQL 表格( 百万列), 获得新数据的时间非常短 20 分钟。我一开始犯了一个错误, 并且有一个时间戳数据, 而我真正想要的是一个日期时间列。我怎样才能将当前表格插入或迁移到一个新的表格, 越快越好, WHILE 仍然能够将新数据附加到旧表格或新表格?

Answer 1

这取决于什么/谁在使用数据库, 以及你有什么控制...

一种办法可以是:

create a new table (with desired data structure) (CREATE TABLE...)
amend code/processes that are writing to the database, so that they write to both the old and the new tables (but continue to read from the old table)
when everything is writing to both, copy the old data from the old table to new table (INSERT ... SELECT ...)
amend code/processes that are reading from the database so that they read from the new table (but continue to write to both)
when everything is reading from the new table, amend the code/processes to only write to the new table
when everything is reading from and writing to the new table (alone), you can delete the old table. (DROP TABLE...)

您也可以通过改变现有表格(即创建新列)来做类似的事情,但如果您的数据每20分钟更新一次,您可能会有一个非常大的表格,在这种情况下,ALTER Table查询需要一段时间才能运行,所以我建议您迁移到一个新的表格。