更改表而不锁定整个表 [英] Alter table without locking the entire table
问题描述
是
ALTER TABLE sample ADD COLUMN `hasItem` tinyint(1) DEFAULT NULL
锁定整个桌子吗?
推荐答案
简短答案:对于MySQL<需要5.6个锁.从5.6开始，并使用InnoDB，锁不是许多ALTER TABLE操作(包括添加列)所需的.
Short answer: For MySQL < 5.6 locks are required. From 5.6 on, and using InnoDB, locks are not required for many ALTER TABLE operations including adding a column.
如果您使用的是MySQL 5.5或更早版本，它将在整个操作过程中获得一个读锁，然后在最后获得一个简短的写锁.
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.
在大多数情况下，ALTER TABLE会创建原始表的临时副本...在执行ALTER TABLE时， 其他会话可以读取原始表 (例外).在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...
前面提到的例外是，ALTER TABLE块在准备安装表.frm文件的新版本，丢弃旧文件并从文件中清除过时的表结构时准备读取(而不仅仅是写入).表和表定义缓存.在这一点上，它必须获得一个排他锁.
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.
这就是说，当添加一列时，它的读操作将锁定表的大部分操作，然后在末尾获得写锁.
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添加了在线DDL InnoDB，它可以加快和改善许多事情，例如更改表和索引.在表中添加列将不再需要表锁除了在操作开始和结束时可能使用的短暂排他锁之外.
MySQL 5.6 added the Online DDL to 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.
应该会自动发生，但是请确保在ALTER TABLE语句中设置ALGORITHM=inplace和LOCK=none.
It should happen automatically, but to be sure set ALGORITHM=inplace and LOCK=none to your ALTER TABLE statement.
有一个例外...
在MySQL 5.6之前创建的InnoDB表不支持ALTER TABLE ... ALGORITHM = INPLACE，用于包含时态列(DATE，DATETIME或TIMESTAMP)并且尚未使用ALTER TABLE ... ALGORITHM = COPY重建的表.
这篇关于更改表而不锁定整个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
