MySQL ALTER 命令可以用来修改数据表名或者修改数据表字段
测试数据
先运行下面的 SQL 语句准备测试数据
你可以有选择性的删除某些语句，我这是重新构建数据库和表
DROP DATABASE IF EXISTS souyunku;
CREATE DATABASE souyunku default character set utf8mb4 collate utf8mb4_unicode_ci;
USE souyunku;
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 | |
+------------+------------------+------+-----+---------+----------------+
4 rows 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 | |
+-------+------------------+------+-----+---------+----------------+
3 rows 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 | |
+-------+------------------+------+-----+---------+----------------+
3 rows 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 | |
+------------+------------------+------+-----+---------+----------------+
4 rows 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 | |
+------------+------------------+------+-----+---------+----------------+
4 rows 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 | |
+------------+------------------+------+-----+---------+----------------+
4 rows 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 | |
+------------+------------------+------+-----+---------+----------------+
4 rows 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 | |
+------------+------------------+------+-----+---------+----------------+
4 rows 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:
1 row 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:
1 row 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 |
+----------------+
2 rows 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 |
+----------------+
2 rows in set (0.00 sec)
发现表已经被重命名了有没有
ALTER 修改索引
ALTER 命令还可以用来创建及删除MySQL数据表的索引，该功能我们会在接下来的章节中介绍。