MySQL 给数据表增加一列，一定会锁表吗？

Hmm... Add a column... MySQL... Depending on the situation...
Let's say it's 2 02 2 ... There are many tables in the company... Using InnoDB...
Previous... MySQL5 .5 version... The table is really locked... Obvious... As soon as you add a column... Even if there is just a varchar column... The entire table stops... Stop reading and writing... It's okay... Users keep asking...
Later... In 2 02 2 ... Upgrade 5 .6 ... There are improvements... Much more Better... add a nullable column... yes... add a new column... type can be varchar... null... by default... don't lock the table... as you can see... this is online DDL... MySQL5 .6 started...
but... adding a non-null column... for example, int not null... this... is not a full table lock... also through metadata operations... but... h3 tables... like millions of rows... sometimes... still... you have to wait a while... maybe a short-term lock... but... There is still some performance impact... so... when you add heat... we all do it... at noon... or in the middle of the night... during off-peak periods...
and later... at the end of 2 02 2 ... sometimes 8 .0... 8 .0... lots of optimizations... atomic DDL... i.e.... all succeed... or all rollback... no intermediate states... this is stable...
Let me tell you. About 8 .0... Add non-null fields... Even better... You don't have to copy the entire table data... It's a lightweight operation... It's simple... Metadata updates are also incremental... Just change that part... You don't have to change the whole thing... This is efficient...
Also... 8 .0 has In-Place Alter... You can just modify it in place... No extra space... InvisibleIndexes... Invisible indexes... This is all to reduce locking... Improving Concurrency...
But... we are not omnipotent...
To be completely immobile... is impossible...
Table data is so h3... huge tables... hundreds of millions of rows... add columns... metadata updates are so heavy... tables can be temporarily locked... there is no way...
And... the task is too complex... you can add columns all at once... add indexes... add constraints... you may have no choice but to rebuild the table... and then... If you lock... it's unavoidable...
Besides... in older versions... like MySQL5 .5 ... there's no way... to add a column... and lock the table...
So... to summarize...
MySQL5 .6 ... 8 .0... if you add a simple column... by default the table won't be locked... this is progress...
But... h3 tables... complex operations... lower versions... in this case... you still need to lock...
recommended... new Use version... 8 .0是最好的...评估表大小...大表...晚上做...监控它...看看影响...
就这样...

对线面试官：MySQL 给数据表增加一列，一定会锁表吗？

嘿，让我告诉你这个。
去年做项目的时候我很头疼。

当时广东有一个几百兆大小的巨大电表。
您需要添加一列来存储电子表格的字段。
在变化的表中，我担心整个系统会冻结。

幸运的是，我使用的是MySQL 5 .7 从这个版本开始，在线DDL就很强大了。
插入列而不锁定表。
执行的时候你可能会发现查询有点慢，但是根本没有死锁。
InnoDB 进程在后台运行并正在更改结构。

我整个早上都在观察这个过程，喝了三杯咖啡。
终于完成了，对系统没有任何影响。
经过查资料发现MySQL 8 .0对原子DDL进行了进一步优化，实现更快的原子DDL。
那时我不需要8 .0。

所以添加列并不一定会锁定表；这取决于您使用的 MySQL 版本。
自5 .6 以来已有改进。
但说实话，还是要看具体情况。
你的手表有多大？添加什么列类型？
如果表特别小，有几百条数据。
您可以随心所欲地添加它。
如果像我当时那样有几百兆If it was a meter with megabytes. I will have to wait until the system is activated.如果没有的话，确实可以给老板打电话询问情况。

By the way, When you add a column, If that column is not NULL; It can be locked for a longer period of time. Last time I added the NOT NULL column, It's obviously a little slower. If you enter NULL, Usually faster.
In short, my experience is: adding columns to a new table is basically not locked. Adding columns, especially in large data tables, takes time. Advanced versions like 5 .7 and 8 .0 are much better.
What is your particular situation? Which version and how h3 is the watch?看看吧。
如果手表特别大或者是旧版本。
You need to try a smaller watch first. This is all based on my experience. It may not be completely true, but it is reliable.

mysql 的表已经存在，但是我现在想添加一列 自动增长列

它无法改变。

id 已经是主键。

无法直接更改。

首先删除主键约束。

再次添加。

或者。

创建一个新表。

复制数据。

删除旧表。

重命名新表。