31、MySQL ALTER 命令

MySQL ALTER 命令可以用来修改数据表名或者修改数据表字段

测试数据

先运行下面的 SQL 语句准备测试数据

你可以有选择性的删除某些语句，我这是重新构建数据库和表

DROP DATABASE IF EXISTS souyunku;

CREATE DATABASE souyunku default character set utf8mb4 collate utf8mb4_unicode_ci;

USEsouyunku;

DROP TABLE IF EXISTS tbl_language;

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tbl_language VALUES
(1,'Python','https://ddkk.com','1991-2-20'),
(2,'PHP','http://www.php.net','1994-1-1'),
(3,'Ruby','https://www.ruby-lang.org/','1996-12-25'),
(4,'Kotlin','http://kotlinlang.org/','2016-02-17');

删除表字段

ALTER TABLE tablename DROP fieldname 命令可以用来删除表中的某个字段

如果表中只剩余一个字段，那么无法使用 DROP 来删除字段

比如下面的 SQL 语句可以删除 tbl_language 表中的 founded_at 字段

ALTER TABLE tbl_language  DROP founded_at;

我们先用 DESC tbl_language; 命令看看 tbl_language 有哪些字段

MariaDB [souyunku]> DESC tbl_language;
+------------+------------------+------+-----+---------+----------------+
|Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
|id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
|name       | varchar(64)      | NO   |     | NULL    |                |
|url        | varchar(128)     | NO   |     | NULL    |                |
|founded_at | date             | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4rows in set (0.02 sec)

然后运行 ALTER TABLE tbl_language DROP founded_at; 语句删除 founded_at 字段

MariaDB [souyunku]> ALTER TABLE tbl_language  DROP founded_at;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

再使用DESC tbl_language; 命令看看 tbl_language 有哪些字段

MariaDB [souyunku]> DESC tbl_language;
+-------+------------------+------+-----+---------+----------------+
|Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
|id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
|name  | varchar(64)      | NO   |     | NULL    |                |
|url   | varchar(128)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3rows in set (0.01 sec)

哇，说明字段真的删除成功了

添加表字段

ALTER TABLE tablename ADD field 命令可以给一张表添加字段

比如下面的 SQL 命令给表 tbl_language 添加一个字段 url 并定义了数据类型

ALTER TABLE tbl_language ADD founded_at DATE;

我们先用 DESC tbl_language; 命令看看 tbl_language 有哪些字段

MariaDB [souyunku]> DESC tbl_language;
+-------+------------------+------+-----+---------+----------------+
|Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
|id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
|name  | varchar(64)      | NO   |     | NULL    |                |
|url   | varchar(128)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3rows in set (0.02 sec)

然后运行 ALTER TABLE tbl_language ADD founded_at DATE; 添加字段 founded_at

MariaDB [souyunku]> ALTER TABLE tbl_language ADD founded_at DATE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

再使用DESC tbl_language; 命令看看 tbl_language 有哪些字段

MariaDB [souyunku]> DESC tbl_language;
+------------+------------------+------+-----+---------+----------------+
|Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
|id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
|name       | varchar(64)      | NO   |     | NULL    |                |
|url        | varchar(128)     | NO   |     | NULL    |                |
|founded_at | date             | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4rows in set (0.01 sec)

嗯，字段由给加回来了

指定位置添加字段

默认情况下字段会添加到数据表字段的末尾

如果需要指定新增字段的位置，可以使用 MySQL 提供的关键字 FIRST (设定位第一列)， AFTER 字段名 (设定位于某个字段之后)

比如运行以下 ALTER TABLE 语句，会发现字段都插在我们想要的位置

ALTER TABLE tbl_language ADD lid int FIRST;
ALTER TABLE tbl_language ADD llid int AFTER url;
ALTER TABLE tbl_language ADD lllid int AFTER lid;

执行结果如下

MariaDB [souyunku]> ALTER TABLE tbl_language ADD lid int FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [souyunku]> ALTER TABLE tbl_language ADD llid int AFTER url;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [souyunku]> ALTER TABLE tbl_language ADD lllid int AFTER lid;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后使用 desc tbl_language 查看表结构，显示如下

MariaDB [souyunku]> desc tbl_language;
+------------+------------------+------+-----+---------+----------------+
|Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
|lid        | int(11)          | YES  |     | NULL    |                |
|lllid      | int(11)          | YES  |     | NULL    |                |
|id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
|name       | varchar(64)      | NO   |     | NULL    |                |
|url        | varchar(128)     | NO   |     | NULL    |                |
|llid       | int(11)          | YES  |     | NULL    |                |
|founded_at | date             | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

FIRST 和 AFTER 关键字只适用于 ADD 子句

如果想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置

修改字段类型和其它字段元数据

ALTER TABLE tablename MODIFY field 命令可以用来修改字段类型

例如下面的语句将 tbl_language 表中的 url 字段的 VARCHAR(128) 改成 VARCHAR(255)

ALTER TABLE tbl_language MODIFY url VARCHAR(255);

因为上面的操作已经把 tbl_language 弄乱了，我们执行以下语句重置 tbl_language 表

DROP TABLE IF EXISTS tbl_language;

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后运行 ALTER TABLE tbl_language MODIFY url VARCHAR(255); 修改 url 字段

MariaDB [souyunku]> ALTER TABLE tbl_language MODIFY url VARCHAR(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后使用 DESC tbl_language 查看修改后的表结构

MariaDB [souyunku]> DESC tbl_language;
+------------+------------------+------+-----+---------+----------------+
|Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
|id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
|name       | varchar(64)      | NO   |     | NULL    |                |
|url        | varchar(255)     | YES  |     | NULL    |                |
|founded_at | date             | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4rows in set (0.02 sec)

发现已经把 url 改过来了

重命名字段

ALTER TABLE tablename MODIFY old_field new_field 命令可以用来重命名字段

和MODIFY 不一样，CHANGE 后面紧跟着的是要修改的字段名，然后指定新字段名及类型

比如下面的语句用于将 tbl_language 表中的 url 字段改名为 home

ALTER TABLE tbl_language CHANGE url home VARCHAR(128);

对的，如果只想重命名，也要添加后面哪些数据类型等数据，不然会报错

因为上面的操作已经把 tbl_language 弄乱了，我们执行以下语句重置 tbl_language 表

DROP TABLE IF EXISTS tbl_language;

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后运行 ALTER TABLE tbl_language CHANGE url home VARCHAR(128); 重命名 url 字段为 home

ALTER TABLE tbl_language CHANGE url home VARCHAR(128);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后使用 DESC tbl_language 查看修改后的表结构

MariaDB [souyunku]> DESC tbl_language;
+------------+------------------+------+-----+---------+----------------+
|Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
|id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
|name       | varchar(64)      | NO   |     | NULL    |                |
|home       | varchar(128)     | YES  |     | NULL    |                |
|founded_at | date             | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4rows in set (0.01 sec)

ALTER TABLE 对 Null 值和默认值的影响

不管是MODIFY 还是 CHANGE，大家注意 Null 那栏，发现变成了 YES，我们创建的时候还是 NOT NULL 的，这是因为我们修改或者重命名字段的时候没有传递 NOT NULL 所以 MySQL 使用了默认的

也就是说，大家使用 CHANGE 修改的时候，要带上全部的参数，一个没带，就会使用默认的

修改字段默认值

ALTER TABLE tablename ALTER field SET DEFAULT default_value 可以修改字段的默认值

例如下面的 SQL 语句 url 的默认值由 NULL 改成了空字符串('')

ALTER TABLE tbl_language ALTER url SET DEFAULT '';

因为上面的操作已经把 tbl_language 弄乱了，我们执行以下语句重置 tbl_language 表

DROP TABLE IF EXISTS tbl_language;

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后运行 ALTER TABLE tbl_language ALTER url SET DEFAULT ''; 修改 url 的默认值为 ''

MariaDB [souyunku]> ALTER TABLE tbl_language ALTER url SET DEFAULT '';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后使用 DESC tbl_language 查看修改后的表结构

MariaDB [souyunku]> DESC tbl_language;
+------------+------------------+------+-----+---------+----------------+
|Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
|id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
|name       | varchar(64)      | NO   |     | NULL    |                |
|url        | varchar(128)     | NO   |     |         |                |
|founded_at | date             | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4rows in set (0.01 sec)

删除字段默认值

ALTER TABLE tablename ALTER field DROP DEFAULT 可以删除字段的默认值

例如下面的 SQL 语句 url 的默认值刚刚设置的 '' 改成了 NULL

ALTER TABLE tbl_language ALTER url DROP DEFAULT;

然后运行 ALTER TABLE tbl_language ALTER url DROP DEFAULT; 删除 url 的默认值

MariaDB [souyunku]> ALTER TABLE tbl_language ALTER url DROP DEFAULT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后使用 DESC tbl_language 查看修改后的表结构

MariaDB [souyunku]> DESC tbl_language
-> ;
+------------+------------------+------+-----+---------+----------------+
|Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
|id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
|name       | varchar(64)      | NO   |     | NULL    |                |
|url        | varchar(128)     | NO   |     | NULL    |                |
|founded_at | date             | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4rows in set (0.01 sec)

修改数据表引擎

ALTER TABLE tablename ENGINE = engine 可以修改表的类型(数据库引擎)

例如下面的语句将 tbl_language 的数据表引擎由 InnoDB 改成 MYISAM

ALTER TABLE tbl_language ENGINE = MYISAM;

我们先用 SHOW TABLE STATUS LIKE 'tbl_language'\G; 查看当前表的元数据

MariaDB [souyunku]> SHOW TABLE STATUS LIKE 'tbl_language'\G;
*************************** 1. row ***************************
       Name: tbl_language
     Engine: InnoDB
    Version: 10
 Row_format: Dynamic
       Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
  Data_free: 0
Auto_increment: 1
Create_time: 2018-04-08 19:07:23
Update_time: NULL
 Check_time: NULL
  Collation: utf8mb4_general_ci
   Checksum: NULL
Create_options: 
    Comment: 
1row in set (0.00 sec)

ERROR: No query specified

然后运行 ALTER TABLE tbl_language ENGINE = MYISAM; 把表引擎改成 MYISAM

MariaDB [souyunku]> ALTER TABLE tbl_language ENGINE = MYISAM;
Query OK, 0 rows affected (0.05 sec)               
Records: 0  Duplicates: 0  Warnings: 0

最后使用 SHOW TABLE STATUS LIKE 'tbl_language'\G; 查看 tbl_language 的元数据

MariaDB [souyunku]> SHOW TABLE STATUS LIKE 'tbl_language'\G;
*************************** 1. row ***************************
       Name: tbl_language
     Engine: MyISAM
    Version: 10
 Row_format: Dynamic
       Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
  Data_free: 0
Auto_increment: 1
Create_time: 2018-04-08 19:13:34
Update_time: 2018-04-08 19:13:34
 Check_time: NULL
  Collation: utf8mb4_general_ci
   Checksum: NULL
Create_options: 
    Comment: 
1row in set (0.00 sec)

ERROR: No query specified

修改表名

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现

先在mysql>` 终端中运行以下语句查看当前的所有表

SHOW TABLES;

执行结果如下

MariaDB [souyunku]> SHOW TABLES;
+----------------+
|Tables_in_souyunku |
+----------------+
|tbl_language   |
|tbl_rank       |
+----------------+
2rows in set (0.01 sec)

然后运行下面的 SQL 语句将 tbl_language 修改成 tbl_lang

ALTER TABLE tbl_language RENAME TO tbl_lang;

执行结果如下

MariaDB [souyunku]> ALTER TABLE tbl_language RENAME TO tbl_lang;
Query OK, 0 rows affected (0.02 sec)

然后再使用 SHOW TABLES 命令查看有哪些表

MariaDB [souyunku]> SHOW TABLES;
+----------------+
|Tables_in_souyunku |
+----------------+
|tbl_lang       |
|tbl_rank       |
+----------------+
2rows in set (0.00 sec)

发现表已经被重命名了有没有

ALTER 修改索引

ALTER 命令还可以用来创建及删除MySQL数据表的索引，该功能我们会在接下来的章节中介绍。