Basic usage and acceleration optimization of alter table command in MySQL

1. Basic usage

1. Increase the column

alter table tbl_name add col_name type

For example, add one column to pet's table weight,

mysql>alter table pet add weight int;

2. Delete the column

alter table tbl_name drop col_name

For example, delete the weight column in the pet table

mysql>alter table pet drop weight;

3. Change the column

This is divided into changing column attributes and changing column names

Changing the properties of the column -- Method 1:

alter table tbl_name modify col_name type

For example, change the type of weight

mysql>alter table pet modify weight varchar(30);

Changing the properties of the column -- Method 2:

alter table tbl_name change old_col_name col_name type

For example, change the type of weight

alter table pet change weight weight varchar(30);

Change column name:

alter table tbl_name change old_col_name col_name

For example, change the name of weight in the pet table:

mysql>alter table pet change weight wei;

Change the name of the table

mysql>alter table pet add weight int;

0

For example, rename the pet table to animal

mysql>alter table pet add weight int;

1

2. Optimization of ALTER TABLE
In the daily maintenance of the system, it is often necessary to update the table structure, such as adding/removing 1 field, changing the length of 1 VARCHAR field, and so on. MySQL handles this modification by first creating a new structured table, then inserting the contents of the old table into the new table by executing Insert statements, and finally deleting the entire old table. This process is fine when the data volume is small, but may take a lot of time to process when the data volume is large.

It takes hours to perform an operation to update the table structure, which is intolerable. If you are using a version prior to 5.1, you will also perform table structure updates while the database is often out of service, which has thankfully been improved in the latest version

If you do the table structure update properly, not all of the update operations will take up your time.
For example, if you want to update the default password of the user table to "666666", the usual way to do this is

mysql>alter table pet add weight int;

2

Through SHOW STATUS you can find that there are a lot of Insert operations in the process of performing this operation. When the number of users is very large, such as millions or millions of pieces of data, it will inevitably consume a lot of time.

However, if you use the following method to update, the time will be greatly reduced

mysql> ALTER TABLE user
     -> ALTER COLUMN pwd varchar not null SETDEFAULT 5;

Performing the SHOW STATUS operation found that a large number of insert operations did not exist, and the time was greatly reduced (need to do FLUSH STATUS first)
It is possible to shorten the time because
(1) The default value of a table field is placed in the table's frm(.frm: table structure file.MYD: table data file.MYI: table index) file
(2) ALTER COLUMN updates the frm file without touching the contents of the table
(3) MODIFY COLUMN refers to the content of table data

As you can see from the previous column, table structure updates are much more efficient if only changes to the frm file are involved in the operation, but mysql often rebuilds tables when it is not necessary. If you are willing to take the risk, you can modify the frm file to speed up changes to the table summary

Not all table structure changes can be modified by the frm file to improve the efficiency of modification, the following 1 changes can be modified by the frm file to achieve the purpose of updating:
(1) Change the default value of the field
(2) Add/delete the AUTO_INCREMENT attribute of the field
(3) Add/delete/modify the constant value of ENUM. For a delete operation, if a field references the constant value, the structure of the query after the delete is an empty string

The following is an example of updating the default value properties of a field to improve the efficiency of modifying the table structure by using ALTER COLUMN and modifying frm files, respectively

1 Execute ALTER COLUMN
1.1 First prepare a dictionary table

mysql>alter table pet add weight int;

4

1.2 Insert 1 test data

mysql>alter table pet add weight int;

5

1.3 SHOW STATUS Observation Results Differences between Modify Column and Alter Column
First use MODIFY COLUMN

mysql>alter table pet add weight int;

6

ALTER COLUMN in use

mysql>alter table pet add weight int;

7

Modify the frm file
The steps to improve the efficiency of modifying the table structure by modifying the frm file are as follows
1. Backup relevant database files
2. Create a table structure that is identical to the old table

mysql>create table dictionary_new like dictionary;

3. Execute FLUSH TABLES WITH READ LOCK. All tables are closed

mysql>alter table pet add weight int;

9

5. Rename the dictionary_ES171en.frm file dictionary.frm
6. Perform UNLOCK TABLES

       mysql> unlock tables;
       mysql> insert into dictionary(word) values('Random');
       mysql> select * from dictionarywhere word='Random' ; 

As you can see from the results below, the default values have been changed and no content changes are involved

+--------+--------+----------+
| id   | word | mean   |
+--------+--------+----------+
| 110004 |Random | DEFAULR# |
+--------+--------+----------+

7. Drop dictionary_new