MySQL Table Columns

Databases

MySQL

MySQL Schema

MySQL Tables

Adding Columns

ALTER TABLE mytable ADD mynewcol CHAR(1);

In older versions of MySQL (before 8.0) adding a column caused a copy of the table to be created with the new column then the original table would be dropped. On very large tables this causes a lock to be held (i.e. DML not permitted) for a long time.

In newer versions (8.0.12+), by default, adding columns is done by a quick update of the transactional data dictionary leading to a very short lock time even for large tables.

If an ALTER using the old copy algoritm fails you are likely to see leftover temporary tables lying around (they start with #sql-). Do not delete these at OS level. You will need to DROP from within the database.

For older versions of MySQL you may be able to use the Percona Toolkit to perform an online change...

pt-online-schema-change --alter "ADD COLUMN mynewcol INT" D=mydatabase,t=mytable

Changing Columns

ALTER TABLE mytable CHANGE myoldcolname mynewcolname BIGINT;

Changes column name and definition in a single command

ALTER TABLE mytable MODIFY mycolname BIGINT;

Changes only the definition

ALTER TABLE mytable RENAME myoldcolname TO mynewcolname;

Changes only the column name

Dropping Columns

ALTER TABLE mytable DROP COLUMN mycol;

Bibliography

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-add-drop-columnhttps://dev.mysql.com/blog-archive/mysql-8-0-innodb-now-supports-instant-add-column/https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.htmlhttps://stackoverflow.com/questions/24660456/mysql-performance-of-adding-a-column-to-a-large-tablehttps://serverfault.com/questions/364391/huge-sql-xxxx-xxxx-ibd-files-in-mysql-data-folder

Page updated

Google Sites

Report abuse