MySQL 更改表而不锁定整个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35424543/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter table without locking the entire table
提问by Manoj
Does
做
ALTER TABLE sample ADD COLUMN `hasItem` tinyint(1) DEFAULT NULL
lock the entire table?
锁定整个表？
回答by Schwern
Short answer: For MySQL < 5.6 locks are required. From 5.6 on, and using InnoDB, locks are not required for many ALTER TABLEoperations including adding a column.
简短回答：对于 MySQL < 5.6 的锁是必需的。从 5.6 开始，使用 InnoDB，包括添加列在内的许多ALTER TABLE操作都不需要锁。
If you're using MySQL 5.5 or older, it will get a read lock for the whole operation and then a brief write lock at the end.
如果您使用 MySQL 5.5 或更早版本，它将获得整个操作的读锁，然后在最后获得一个简短的写锁。
From the MySQL documentation for ALTER TABLE...
In most cases, ALTER TABLE makes a temporary copy of the original table... While ALTER TABLE is executing, the original table is readable by other sessions(with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready...
The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock.
在大多数情况下，ALTER TABLE 会制作原始表的临时副本...当 ALTER TABLE 正在执行时，其他会话可以读取原始表（除了简短说明的例外）。在 ALTER TABLE 操作开始后开始的表更新和写入将被停止，直到新表准备好...
前面提到的例外是 ALTER TABLE 在准备安装表 .frm 文件的新版本时阻止读取（不仅仅是写入），丢弃旧文件，并从表和表中清除过时的表结构定义缓存。此时，它必须获取排他锁。
Which is to say, when adding a column it read locks the table for most of the operation, then gets a write lock at the end.
也就是说，当添加一个列时，它在大多数操作中读锁表，然后在最后获得写锁。
MySQL 5.6 added the Online DDLto InnoDB which speeds up and improves many things such as altering tables and indexes. Adding a column to a table will no longer require table locks except possibly brief exclusive locks at the start and end of the operation.
MySQL 5.6向 InnoDB添加了在线 DDL，这加快并改进了许多事情，例如更改表和索引。向表中添加列将不再需要表锁，除非在操作开始和结束时可能需要短暂的排他锁。
It shouldhappen automatically, but to be sure set ALGORITHM=inplaceand LOCK=noneto your ALTER TABLEstatement.
它应该自动发生，但要确保设置ALGORITHM=inplace并LOCK=none符合您的ALTER TABLE声明。
There is one exception...
有一个例外...
InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY.
在 MySQL 5.6 之前创建的 InnoDB 表不支持 ALTER TABLE ... ALGORITHM=INPLACE 对于包含临时列（DATE、DATETIME 或 TIMESTAMP）并且尚未使用 ALTER TABLE ... ALGORITHM=COPY 重建的表。
