三十一、MySQL ALTER 命令
云少三十一、MySQL ALTER 命令
MySQL ALTER 命令可以用来修改数据表名或者修改数据表字段
测试数据
先运行下面的 SQL 语句准备测试数据
你可以有选择性的删除某些语句，我这是重新构建数据库和表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| DROP DATABASE IF EXISTS `test2`;
CREATE DATABASE test2 default character set utf8mb4 collate utf8mb4_unicode_ci;
USE test2;
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','python,'1991-2-20'),
(2,'PHP','php','1994-1-1'),
(3,'Ruby','ruby','1996-12-25'),
(4,'Kotlin','kotlin','2016-02-17');
|
删除表字段
ALTER TABLE tablename DROP fieldname 命令可以用来删除表中的某个字段
如果表中只剩余一个字段，那么无法使用 DROP 来删除字段
比如下面的 SQL 语句可以删除 tbl_language 表中的 founded_at 字段
1
| ALTER TABLE tbl_language DROP founded_at;
|
我们先用 DESC tbl_language; 命令看看 tbl_language 有哪些字段
1
2
3
4
5
6
7
8
9
10
| MariaDB [test2]> 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 字段
1
2
3
| MariaDB [test2]> 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 有哪些字段
1
2
3
4
5
6
7
8
9
| MariaDB [test2]> 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 并定义了数据类型
1
| ALTER TABLE tbl_language ADD founded_at DATE;
|
我们先用 DESC tbl_language; 命令看看 tbl_language 有哪些字段
1
2
3
4
5
6
7
8
9
| MariaDB [test2]> 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
1
2
3
| MariaDB [test2]> 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 有哪些字段
1
2
3
4
5
6
7
8
9
10
| MariaDB [test2]> 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 语句，会发现字段都插在我们想要的位置
1
2
3
| 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;
|
执行结果如下
1
2
3
4
5
6
7
8
9
| MariaDB [test2]> ALTER TABLE tbl_language ADD lid int FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test2]> ALTER TABLE tbl_language ADD llid int AFTER url;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test2]> 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 查看表结构，显示如下
1
2
3
4
5
6
7
8
9
10
11
12
| MariaDB [tes2]> 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)
1
| ALTER TABLE tbl_language MODIFY url VARCHAR(255);
|
因为上面的操作已经把 tbl_language 弄乱了，我们执行以下语句重置 tbl_language 表
1
2
3
4
5
6
7
8
9
| 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 字段
1
2
3
| MariaDB [test2]> 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 查看修改后的表结构
1
2
3
4
5
6
7
8
9
10
| MariaDB [test2]> 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
1
| ALTER TABLE tbl_language CHANGE url home VARCHAR(128);
|
对的，如果只想重命名，也要添加后面哪些数据类型等数据，不然会报错
因为上面的操作已经把 tbl_language 弄乱了，我们执行以下语句重置 tbl_language 表
1
2
3
4
5
6
7
8
9
| 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
1
2
3
| 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 查看修改后的表结构
1
2
3
4
5
6
7
8
9
10
| MariaDB [test2]> 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 改成了空字符串('')
1
| ALTER TABLE tbl_language ALTER url SET DEFAULT '';
|
因为上面的操作已经把 tbl_language 弄乱了，我们执行以下语句重置 tbl_language 表
1
2
3
4
5
6
7
8
9
| 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 的默认值为 ''
1
2
3
| MariaDB [test2]> 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 查看修改后的表结构
1
2
3
4
5
6
7
8
9
10
| MariaDB [test2]> 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
1
| ALTER TABLE tbl_language ALTER url DROP DEFAULT;
|
然后运行 ALTER TABLE tbl_language ALTER url DROP DEFAULT; 删除 url 的默认值
1
2
3
| MariaDB [test2]> 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 查看修改后的表结构
1
2
3
4
5
6
7
8
9
10
11
| MariaDB [test2]> 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
1
| ALTER TABLE tbl_language ENGINE = MYISAM;
|
我们先用 SHOW TABLE STATUS LIKE 'tbl_language'\G; 查看当前表的元数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| MariaDB [test2]> 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: 2022-5-26 18:32:45
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
1
2
3
| MariaDB [test2]> 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 的元数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| MariaDB [test2]> 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: 2022-5-26 18:32:45
Update_time: 2022-5-27 18:43:26
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> 终端中运行以下语句查看当前的所有表
执行结果如下
1
2
3
4
5
6
7
8
| MariaDB [test2]> SHOW TABLES;
+
| Tables_in_test2 |
+
| tbl_language |
| tbl_rank |
+
2 rows in set (0.01 sec)
|
然后运行下面的 SQL 语句将 tbl_language 修改成 tbl_lang
1
| ALTER TABLE tbl_language RENAME TO tbl_lang;
|
执行结果如下
1
2
| MariaDB [test2]> ALTER TABLE tbl_language RENAME TO tbl_lang;
Query OK, 0 rows affected (0.02 sec)
|
然后再使用 SHOW TABLES 命令查看有哪些表
1
2
3
4
5
6
7
8
| MariaDB [test2]> SHOW TABLES;
+
| Tables_in_test2 |
+
| tbl_lang |
| tbl_rank |
+
2 rows in set (0.00 sec)
|
发现表已经被重命名了有没有
ALTER 修改索引
ALTER 命令还可以用来创建及删除MySQL数据表的索引，该功能我们会在接下来的章节中介绍。
