MySQL Alter table
ALTER TABLE allows you to modify table information: you can add or delete columns, change the data type and property of the field and name, create or remove index, and even change the table's comment and table type.
Add a column
ALTER TABLE tb_name new_column data_type
ALTER TABLE user ADD status TINYINT (1) UNSIGNED NULL
add multiple columns
ALTER TABLE user ADD COLUMN `column1` VARCHAR(12) NOT NULL, ADD COLUMN `column2` VARCHAR(12) NOT NULL, ADD COLUMN `column3` VARCHAR(12) NOT NULL
When adding columns, you need to specify the attributes of the columns. For more information, see "MySQL Data Types" . By default, the newly added field is at the end of the table. If you need to specify a field to be added, you can add the AFTER keyword:
ALTER TABLE tb_name ADD new_column data_type AFTER column
Delete the field
ALTER TABLE tb_name DROP column
After the field is deleted, the index and table constraints associated with the field are also automatically deleted.
ALTER TABLE user DROP status
Change field names and attributes
ALTER TABLE tb_name CHANGE column new_column data_type
New_column for the new field name, if the new name and the same old name, then do not change the name of the field and only change the field properties.
ALTER TABLE user CHANGE status status_bak INT(8) UNSIGNED NULL DEFAULT \'1\'
Add indexes (or other properties such as table constraints)
ALTER TABLE tb_name ADD INDEX_TYPE (column)
The syntax is equivalent to:
CREATE INDEX_TYPE index_name ON tb_name (column)
-- add index for 'status' ALTER TABLE user ADD INDEX (status)
Modify the index name and index properties
ALTER TABLE tb_name DROP INDEX_TYPE index_name, ADD INDEX_TYPE new_index_name (column)
The essence of the syntax is to delete the original index, and then re-establish the index.
Renames the table
The RENAME TABLE syntax can be used to rename one or more tables.grammar:
RENAME TABLE tb_name TO new_tb_name, tb_name2 TO new_tb_name2, ...
The rename operation is done automatically, which means that other threads can not read any tables while renaming is running.
If you want to exchange the names of two tables, you can do the following (assuming there is no table named tmp_table):
RENAME TABLE old_table TO tmp_table, New_table TO old_table, Tmp_table TO new_table