MySQL 8 Cookbook

Altering tables

ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.

While performing certain alter operations such as changing a column data type, adding a SPATIAL INDEX, dropping a primary key, converting a character set, adding/removing encryption, and so on, DML operations on the table are blocked. If the table is big, it will take even more time to alter, and the application cannot access the table during that time, which is not desired. In those situations, a pt-online-schema change is helpful, where DML statements are allowed.

There are two algorithms for alter operations:

• In-place (default): ...