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

grammar

ALTER TABLE tb_name new_column data_type

example

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

grammar

ALTER TABLE tb_name DROP column

After the field is deleted, the index and table constraints associated with the field are also automatically deleted.

example

ALTER TABLE user DROP status

Change field names and attributes

grammar:

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.

example:

ALTER TABLE user CHANGE status status_bak INT(8) UNSIGNED NULL DEFAULT \'1\'

Add indexes (or other properties such as table constraints)

grammar:

ALTER TABLE tb_name ADD INDEX_TYPE (column)

The syntax is equivalent to:

CREATE INDEX_TYPE index_name ON tb_name (column)

example:

-- add index for 'status'
ALTER TABLE user ADD INDEX (status) 

Modify the index name and index properties

grammar

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



Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook