从将timestamp列添加到mysql表中来处理尴尬 [英] Deal with awkwardness from adding timestamp column to mysql table

问题描述

我有一个非常大的表，要向其中添加一个timestamp列。该表的更新次数远多于插入表。我试图找到一种方法来解决此问题，而又不会使桌子长时间停工，这使我陷入困境。我可以这样做：

I have a very large table that I want to add a timestamp column to. It is a table that gets a lot more updates than inserts. I am trying to figure out a way to do this without taking the table out of production for a significant amount of time and it has me in knots. I can do:

alter table stuff add column mod_time timestamp;

好吧，我可以这样做，然后将桌子锁定3-5个小时。对于用户来说不是一个愉快的时光。

Well, I could do this and then the table is locked for 3-5 hours. Not a happy time for users.

例如，要添加varchar列，我可以创建一个新表，添加该列，将数据复制过来，然后替换新的旧表。在较慢的时间中，可以在短短的一两秒钟内完成最后的切换。没问题。当然，我可能需要分阶段进行复制。我可以做一个副本，然后对第一个副本开始后更改的所有内容进行副本。我可以重复此操作，直到一切都没有改变，然后进行交换。当然，如果我有 mod_time列，则确定更改的内容要容易得多。

For adding a varchar column, for example, I could create a new table, add the column, copy the data over and then replace the old table with the new. This last switch can be done in just a second or two during a slow-ish time. No problems from that. Of course, I may need to do the copy in stages. I can do one copy, and then do a copy of everything that changed after the first copy started. I can repeat this until nothing has changed and do the swap. Of course, it is much easier to determine what changed if I have a "mod_time" column.

我真正想做的是：（1）创建新的表，（2）复制数据，（3）添加时间戳列，（4）交换表。

What I really would rather to do is: (1) create the new table, (2) copy the data, (3) add the timestamp column, (4) swap the tables.

但是步骤（3）仍然要花费几个小时。

But step (3) takes, again, hours.

我希望在添加此列之前更新的行的mod_time值设置为NULL。

I want rows updated before this column was added to have a mod_time value set to NULL.

如果我在上面的步骤中切换（2）和（3），交换是可行的，但是当我执行此操作以添加列时，我得到了mod_time =。我想要NULL。

If I switch (2) and (3) in the step above, the swap is doable, but I get a mod_time = when I did this stuff to add the column. I want NULL.

我可以尝试在进行交换之前将值设置为NULL，但是当然，如​​果您更新该行以将其设置为NULL，则可以正在更新该行，并且mod_time列被设置为当前时间。 ：-）

I can try to set the values to NULL just before I do the swap, but of course if you update the row to set it to NULL, you are updating the row and the mod_time column gets set to the current time. :-)

我希望能做：（1）​​创建新表，（2）添加 mod_time作为日期时间列（2）复制数据， （3）将mod_time更改为timestamp列，（4）交换表。

I wish I could do: (1) create the new table, (2) add "mod_time" as a datetime column (2) copy the data, (3) change mod_time to a timestamp column, (4) swap the tables.

理论上（3）可以非常快速地完成（假设存储日期时间和时间戳记）是兼容的），因为我在将列更改为tmestamp时所做的一切实际上是在将来更改其行为，而不是当前存储。因此，这完全不需要时间，是吗？可能不是。

Theoretically (3) could be done very very quickly (assuming the storage for datetime and timestamp are compatible) because all I am really doing in changing the column to tmestamp is changing its behavior in the future, not its current storage. So this should take no time at all, yes? Probably not.

如果要这样做，只需澄清一下：

Just to clarify, if I do:

 alter table stuff add column mod_time timestamp;

我得到：

 +----------+--------+----------+---------------------+
 |     col1 |   col2 |     col3 | mod_time            |
 +----------+----- --+----------+---------------------+
 |     5001 |     50 |     2463 | 0000-00-00 00:00:00 |
 |     5002 |     50 |     2467 | 0000-00-00 00:00:00 |
 |     5003 |     50 |     2459 | 0000-00-00 00:00:00 |

这就是我想要的。只是alter语句将花费太长时间。我正在尝试的所有其他东西都给了我

This is what I want. It is just that the alter statement will take too long. Everything else I am trying gives me:

 +----------+--------+----------+---------------------+
 |     col1 |   col2 |     col3 | mod_time            |
 +----------+----- --+----------+---------------------+
 |     5001 |     50 |     2463 | 2013-12-05 18:11:21 |
 |     5002 |     50 |     2467 | 2013-12-05 18:11:21 |
 |     5003 |     50 |     2459 | 2013-12-05 18:11:21 |

推荐答案

您应使用 pt-online-schema-change ，该工具可自动执行MySQL的ALTER TABLE操作，不锁定表格。它是Percona工具包的一部分，它是MySQL开发人员和DBA的免费必备工具免费集合。

You should use pt-online-schema-change, a tool for automating ALTER TABLE operations for MySQL, without locking the table. It's part of the Percona Toolkit, a free collection of indispensable helper tools for MySQL developers and DBA's.

$ pt-onlines-chema-change h=localhost,D=mydatabase,t=mytable \
  --alter="add column mod_time timestamp"

在pt-online-schema-change运行时，您可以继续读取和写入原始表。完成后，它会自动交换表格名称，并删除原始名称。

You can continue reading and writing the original table while pt-online-schema-change is working. When it's done, it automatically swaps the tables' names, and drops the original.

您可以通过其设计者查看有关此工具的在线讲座的录像。查看是免费的，但需要注册： http ：//www.percona.com/webinars/2012-05-02-zero-downtime-schema-changes-in-mysql

You can view a recording of a webinar about this tool by its designer. Viewing is free, but requires registration: http://www.percona.com/webinars/2012-05-02-zero-downtime-schema-changes-in-mysql

这篇关于从将timestamp列添加到mysql表中来处理尴尬的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！