blocks|key|1847542|text|不是的。如果你使用的是MyISAM表，据我所知，它们只做表锁--没有记录锁，它们只是试图通过简单来保持所有东西的超快。(其他MySQL表的操作方式不同。)在任何情况下，您都可以将表复制到另一个表，更改它，然后切换它们，更新差异。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1847543|这是一个如此巨大的改变，我怀疑任何DBMS都会支持它。首先，能够对表中的数据执行此操作被认为是一个好处。|1847544|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Nope. If you are using MyISAM tables, to my best understanding they only do table locks - there are no record locks, they just try to keep everything hyperfast through simplicity. (Other MySQL tables operate differently.) In any case, you can copy the table to another table, alter it, and then switch them, updating for differences.

This is such a massive alteration that I doubt any DBMS would support it. It's considered a benefit to be able to do it with data in the table in the first place.

blocks|key|1847604|text|不怎么有意思。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1847605|毕竟，您正在改变表的底层结构，这是一些对底层系统非常重要的信息。您还(可能)在磁盘上移动大量数据。|1847606|如果您打算经常这样做，那么最好简单地用“虚拟”列填充表格，以便将来使用。|1847607|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Not really.

You ARE altering the underlying structure of the table, after all, and that's a bit of information that's quite important to the underlying system. You're also (likely) moving much of the data around on disk.

If you plan on doing this a lot, you're better off simply padding the table with "dummy" columns that are available for future use.

blocks|key|2061726|text|如果您可以预测它们的类型(并使它们可以为空)，则虚拟列是一个好主意。检查您的存储引擎如何处理空值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2061727|在机场，只要你在电话中顺便提到一个表名，MyISAM就会锁定所有东西。它就是这么做的。|2061728|话虽如此，锁实际上并不是什么大问题；只要您不想为每一行添加新列的默认值，而是让它作为null存在，并且您的存储引擎足够聪明而不去写它，那么您应该可以使用一个只持有足够长的时间来更新元数据的锁。如果你尝试写一个新的值，那么，你就完蛋了。|2061729|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Dummy columns are a good idea if you can predict their type (and make them nullable). Check how your storage engine handles nulls.

MyISAM will lock everything if you even mention a table name in passing, on the phone, at the airport. It just does that...

That being said, locks aren't really that big a deal; as long as you are not trying to add a default value for the new column to every row, but let it sit as null, and your storage engine is smart enough not to go writing it, you should be ok with a lock that is only held long enough to update the metadata. If you do try to write a new value, well, you are toast.

blocks|key|2061852|text|既然您问到了其他数据库，这里有一些关于Oracle的信息。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2061853|向Oracle表添加空列是一个非常快速的操作，因为它只更新数据字典。这会在很短的一段时间内持有表上的独占锁。但是，它将使任何无效的存储过程、视图、触发器等失效。这些将被自动重新编译。|2061854|如果需要，可以从那里使用ONLINE子句创建索引。同样，只有非常短的数据字典锁。它将读取整个表以查找要索引的内容，但在执行此操作时不会阻塞任何人。|2061855|如果您需要添加外键，您可以这样做，并让Oracle信任您数据是正确的。否则，它需要读取整个表并验证所有的值，这可能会很慢(首先创建索引)。|2061856|如果需要将默认值或计算值放入新列的每一行，则需要运行大规模更新，或者可能运行一些实用程序来填充新数据。这可能会很慢，特别是当行变得更大并且不再适合它们的块时。在此过程中可以管理锁定。由于仍在运行的应用程序的旧versino不知道此列，因此您可能需要一个偷偷摸摸的触发器或指定一个默认值。|2061857|在此基础上，您可以在应用程序服务器上切换到新版本的代码，它将继续运行。放下你那偷偷摸摸的扳机。|2061858|或者，你也可以使用DBMS_REDEFINITION，它是一个黑盒，设计用来做这类事情。|2061859|所有这些都是如此麻烦的测试，等等，以至于当我们发布一个主要版本时，我们只是在周日一大早就停机了。|2061860|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|V|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|W|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|Y|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|Q|$]]

Since you asked about other databases, here's some information about Oracle.

Adding a NULL column to an Oracle table is a very quick operation as it only updates the data dictionary. This holds an exclusive lock on the table for a very short period of time. It will however, invalidate any depedant stored procedures, views, triggers, etc. These will get recompiled automatically.

From there if necessary you can create index using the ONLINE clause. Again, only very short data dictionary locks. It'll read the whole table looking for things to index, but does not block anyone while doing this.

If you need to add a foreign key, you can do this and get Oracle to trust you that the data is correct. Otherwise it needs to read the whole table and validate all the values which can be slow (create your index first).

If you need to put a default or calculated value into every row of the new column, you'll need to run a massive update or perhaps a little utility program that populates the new data. This can be slow, especially if the rows get alot bigger and no longer fit in their blocks. Locking can be managed during this process. Since the old versino of your application, which is still running, does not know about this column you might need a sneaky trigger or to specify a default.

From there, you can do a switcharoo on your application servers to the new version of the code and it'll keep running. Drop your sneaky trigger.

Alternatively, you can use DBMS_REDEFINITION which is a black box designed to do this sort of thing.

All this is so much bother to test, etc that we just have an early Sunday morning outage whenever we release a major version.

blocks|key|1847901|text|使用Innodb插件，只添加或删除辅助索引的ALTER+table语句可以“快速”完成，即无需重新构建表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1847902|但是，一般而言，在MySQL中，任何ALTER+TABLE都涉及重建整个表，这可能需要很长时间(即，如果表中包含大量有用的数据)。|1847903|您确实需要将应用程序设计为不需要定期执行ALTER+TABLE语句；您肯定不希望在应用程序正常运行期间执行任何ALTER+TABLE语句，除非您准备等待或更改小表。|1847904|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Using the Innodb plugin, ALTER TABLE statements which only add or drop secondary indexes can be done "quickly", i.e. without rebuilding the table.

Generally speaking however, in MySQL, any ALTER TABLE involves rebuilding the entire table which can take a very long time (i.e. if the table has a useful amount of data in it).

You really need to design your application so that ALTER TABLE statements do not need to be done regularly; you certainly don't want any ALTER TABLE done during normal running of the application unless you're prepared to wait or you're altering tiny tables.

blocks|key|2062450|text|一般来说，答案是“不”。你正在改变表的结构，这可能需要大量的更新“，我绝对同意这一点。如果你希望经常这样做，那么我将提供一种”虚拟“列的替代方案--使用VIEWs代替用于SELECT数据的表。IIRC，改变视图的定义是相对轻量级的，通过视图的间接访问是在编译查询计划时完成的。代价是你必须将列添加到新的表中，并在列中创建JOIN视图。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2062451|当然，只有当您可以使用外键执行级联删除等操作时，这才能起作用。另一个好处是，您可以创建一个包含数据组合的新表，并将视图指向该表，而不会干扰客户端的使用。|2062452|这只是个想法。|2062453|entityMap^0|24|4|2D|6|4G|4|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|T|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|K|$]]

In general, the answer is going to be "No". You're changing the structure of the table which potentially will require a lot of updates" and I definitely agree with that. If you expect to be doing this often, then I'll offer an alternative to "dummy" columns - use <code>VIEW</code>s instead of tables for <code>SELECT</code>ing data. IIRC, changing the definition of a view is relatively lightweight and the indirection through a view is done when the query plan is compiled. The expense is that you would have to add the column to a new table and make the view <code>JOIN</code> in the column.

Of course this only works if you can use foreign keys to perform cascading of deletes and whatnot. The other bonus is that you can create a new table containing a combination of the data and point the view to it without disturbing client usage.

Just a thought.

blocks|key|2062461|text|请参阅Facebook的在线模式更改工具。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2062462|http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932|offset|length|2062463|不是为胆小的人准备的；但它可以完成这项工作。|2062464|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2J|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

See Facebook's online schema change tool.

<a href="http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932">http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932</a>

Not for the faint of heart; but it will do the job.

blocks|key|2062663|text|如果有人还在读这篇文章，或者碰巧来到这里，这就是使用NoSQL数据库系统的最大好处。我在修改表时遇到了同样的问题，要么为附加特性添加列，要么在包含数百万行和高写入的大型表上添加索引。它最终会锁定很长一段时间，所以在实时数据库上这样做会让我们的用户感到沮丧。在小桌子上，你可以逃脱惩罚。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2062664|我讨厌这样一个事实，即我们必须“设计我们的表格以避免改变它们”。我只是不认为这在今天的网站世界是行不通的。你无法预测人们将如何使用你的软件，这就是为什么你会根据用户的反馈快速改变。使用mongodb，您可以在不停机的情况下随意添加“列”。你甚至不需要添加它们，你只需插入带有新列的数据，它就会自动完成。|2062665|值得一看:+www.mongodb.com|2062666|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

If anyone is still reading this or happens to come here, this is the big benefit of using a NoSQL database system like mongodb. I had the same issue dealing with altering the table to either add columns for additional features or indexes on a large table with millions of rows and high writes. It would end up locking for a very long time so doing this on the LIVE database would frustrate our users. On small tables you can get away with it.

I hate the fact that we have to "design our tables to avoid altering them". I just don't think that works in today's website world. You can't predict how people will use your software that's why you rapidly change things based on user feedback. With mongodb, you can add "columns" at will with no downtime. You don't really even add them, you just insert data with new columns and it does it automatically.

Worth checking out: www.mongodb.com

blocks|key|1848684|text|TokuDB可以添加/删除列和“热”添加索引，表在整个过程中完全可用。它可以通过www.tokutek.com获得|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1848685|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

TokuDB can add/drop columns and add indexes "hot", the table is fully available throughout the process. It is available via www.tokutek.com

blocks|key|1848782|text|临时解决方案...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1848783|其他解决方案可能是，使用原始表的主键添加另一个表，以及您的新列。|1848784|将主键填充到新表中，并为新表中的新列填充值，并修改查询以联接此表以进行select操作，您还需要为此列值单独插入和更新。|1848785|当您能够获得停机时间时，您可以更改原始表、修改DML查询并删除之前创建的新表|1848786|否则，您可以使用percona提供的集群方法、复制、pt-online-schema工具|1848787|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

Temporary solution...

Other solution could be, add a another table with primary key of the original table, along with your new column. 

Populate your primary key onto the new table and populate values for new column in your new table, and modify your query to join this table for select operations and you also need to insert, update separately for this column value. 

When you able to get downtime, you can alter the original table, modify your DML queries and drop your new table created earlier

Else, you may go for clustering method, replication, pt-online-schema tool from percona

blocks|key|2062860|text|Percona开发了一个名为pt-online-schema-change的工具，可以实现这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2062861|它本质上是制作一个表的副本并修改新的表。为了使新表与原始表保持同步，它使用触发器进行更新。这允许在后台准备新表的同时访问原始表。|2062862|这类似于上面建议的Dems方法，但它是以自动方式完成的。|2062863|他们的一些工具有一个学习曲线，即连接到数据库，但一旦你有了它，他们就是很好的工具。|2062864|例如：|2062865|pt-online-schema-change+--alter+"ADD+COLUMN+c1+INT"+D=db,t=numbers_are_friends|code-block|syntax|javascript|2062866|entityMap|0|LINK|mutability|MUTABLE|url|https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html^0|E|N|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|15|8|@]|9|@]|C|$]]|$1|L|3|M|5|N|7|16|8|@]|9|@]|C|$O|P]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

Percona makes a tool called <a href="https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html">pt-online-schema-change</a> that allows this to be done.

It essentially makes a copy of the table and modifies the new table. To keep the new table in sync with the original it uses triggers to update. This allows the original table to be accessed while the new table is prepared in the background.

This is similar to Dems suggested method above, but this does so in an automated fashion.

Some of their tools have a learning curve, namely connecting to the database, but once you have that down, they are great tools to have.

Ex:

<pre><code>pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends
</code></pre>

blocks|key|2063041|text|Postgres和MySQL在这方面的不同之处在于，Postgres不会重新创建表，而是修改类似于Oracle的数据字典。因此，操作很快，同时仍然需要分配排他的DDL表锁很短的时间，如上所述。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2063042|在MySQL中，该操作将在阻塞事务的同时将数据复制到新表中，这是5.6版之前的MySQL+DBA的主要痛苦。|2063043|好消息是，自从MySQL5.6发布以来，限制一直是mostly+lifted，现在您可以享受MySQL的真正功能了。|offset|length|2063044|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html^0|0|0|P|D|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@$F|S|G|T|1|U]]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

The difference between Postgres and MySQL in this regard is that in Postgres it doesn't re-creates a table, but modifies data dictionary which is similar to Oracle. Therefore, the operation is fast, while it's still requires to allocate an exclusive DDL table lock for very short time as stated above by others. 

In MySQL the operation will copy data to a new table while blocking transactions, which has been main pain for MySQL DBAs prior to v. 5.6. 

The good news is that since MySQL 5.6 release the restriction has been <a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html" rel="nofollow" title="mostly lifted">mostly lifted</a> and you now can enjoy the true power of the MYSQL DB.

blocks|key|2063308|text|正如SeanDowney提到的，pt-online-schema-change是完成您在这里的问题中描述的功能的最佳工具之一。我最近在一个实时数据库上做了很多模式更改，它运行得很好。你可以在我的博客上阅读更多关于它的文章：http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2063309|entityMap|0|LINK|mutability|MUTABLE|url|http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/^0|G|N|33|21|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

As SeanDowney has mentioned, <code>pt-online-schema-change</code> is one of the best tools to do what you have described in the question here. I recently did a lot of schema changes on a live DB and it went pretty well. You can read more about it on my blog post here: <a href="http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/" rel="nofollow">http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/</a>.

blocks|key|2063345|text|你一定要试试pt-online-schema-change。我一直在使用这个工具在AWS+RDS上使用多个从机进行迁移，它对我来说工作得非常好。我写了一篇关于如何做到这一点的详细的博客文章，这可能会对你有所帮助。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2063346|博客：http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/|2063347|entityMap|0|LINK|mutability|MUTABLE|url|http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/^0|6|N|0|3|21|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@]|D|@$9|T|A|U|1|V]]|E|$]]|$1|H|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

You should definitely try <code>pt-online-schema-change</code>. I have been using this tool to do migrations on AWS RDS with multiple slaves and it has worked very well for me. I wrote an elaborate blog post on how to do that which might be helpful for you. 

Blog: <a href="http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/" rel="nofollow">http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/</a>

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked (allowing concurrent reads, but prohibiting concurrent writes) for the duration of the statement. If it's a big table, INSERT or UPDATE statements could be blocked for a looooong time. Is there a way to do a "hot alter", like adding a column in such a way that the table is still updatable throughout the process?

Mostly I'm interested in a solution for MySQL but I'd be interested in other RDBMS if MySQL can't do it.

To clarify, my purpose is simply to avoid downtime when a new feature that requires an extra table column is pushed to production. Any database schema will change over time, that's just a fact of life. I don't see why we should accept that these changes must inevitably result in downtime; that's just weak.

ALTER TABLE without locking the table?

数据库管理系统

数据库管理

SQL数据库

数据库

服务器

MySQL

NoSQL

在MySQL中执行ALTER TABLE语句时，在语句执行期间，整个表都是读锁的(允许并发读取，但禁止并发写入)。如果是一个大表，INSERT或UPDATE语句可能会被阻塞很长一段时间。有没有一种“热修改”的方法，比如添加一列，这样表在整个过程中仍然是可更新的？我主要对MySQL的解决方案感兴趣，但如果MySQL不能做...

问是否在不锁定表的情况下更改表？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否在不锁定表的情况下更改表？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否在不锁定表的情况下更改表？
EN