使用MySQL ALTER TABLE语句修改表定义

Summary: in this tutorial, you will learn about the MySQL ALTER TABLE statement that changes existing table structure such as adding or removing column, changing column attribute, etc.

MySQL ALTER TABLE syntax

The ALTER TABLE statement is used to change the structure of existing tables. You can use the ALTER TABLE statement to add or drop column, change data type of column, add primary key, rename table and many more. The following illustrates the ALTER TABLE statement syntax:

ALTER TABLE table_name action1[,action2,…]

To alter an existing table:

• First, you specify the table name that you want to change after the ALTER TABLE keywords.

• Second, you list a set of actions that you want to apply to the table. An action can be anything such as adding a new column, adding primary key, renaming table, etc. The  ALTER TABLE statement allows you to apply multiple actions in a single ALTER TABLE statement, each action is separated by a comma (,).

Let’s create a new table for practicing the ALTER TABLE statement.

We’re going to create a new table named  tasks in our sample database. The following is the script for creating the tasks table.

CREATE  TABLE tasks (   task_id INT NOT NULL ,   subject VARCHAR(45) NULL ,   start_date DATE NULL ,   end_date DATET NULL ,   description VARCHAR(200) NULL ,   PRIMARY KEY (task_id) ,   UNIQUE INDEX task_id_UNIQUE (task_id ASC) );

Changing columns using MySQL ALTER TABLE statement

Using MySQL ALTER TABLE statement to set auto-increment attribute for a column

Suppose you want the value of the  task_id column to be increased automatically by one whenever you insert a new record into the  tasks table. To do this, you use the ALTER TABLE statement to set the attribute of the task_id  column to AUTO_INCREMENT as follows:

ALTER TABLE tasks CHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;

You can verify the change by adding some records to the tasks table.

INSERT INTO tasks(subject,                   start_date,                   end_date,   description) VALUES('Learn MySQL ALTER TABLE', Now(), Now(), 'Practicing MySQL ALTER TABLE statement'); INSERT INTO tasks(subject,                   start_date,                   end_date,           description) VALUES('Learn MySQL CREATE TABLE', Now(), Now(), 'Practicing MySQL CREATE TABLE statement');

And you can query data to see if the value of the  task_id column is  increased by 1 each time you insert a new record:

SELECT task_id, description FROM tasks

Using MySQL ALTER TABLE statement to add a new column into a table

Because of the new business requirement, you need to add a new column called complete to store the percentage of completion for each task in the tasks table. In this case, you can use the ALTER TABLE to add a new column to the tasks table as follows:

ALTER TABLE tasks  ADD COLUMN complete DECIMAL(2,1) NULL AFTER description;

Using MySQL ALTER TABLE to drop a column from a table

Suppose you don’t want to store the description of tasks in the tasks table and you have to remove it. The following statement allows you to remove the  description column of the tasks table:

ALTER TABLE tasks DROP COLUMN description;

Renaming table using MySQL ALTER TABLE statement

You can use the ALTER TABLE statement to rename a table. Notice that before renaming a table, you should take a serious consideration to see if the change affects both database and application layers.

The following statement rename the  tasks table to work_items:

ALTER TABLE tasks RENAME TO work_items;

In this tutorial, you’ve learned how to use the MySQL ALTER TABLE statement to change existing table structure and to rename the table.

Related Tutorials

• Creating Tables Using MySQL CREATE TABLE Statement

• MySQL DROP TABLE - Removing Existing Tables

原文链接：http://outofmemory.cn/mysql/mysql-alter-table