文章永久连接:https://tech.souyunku.com/?p=4919
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://tech.souyunku.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数据表的索引,该功能我们会在接下来的章节中介绍。
干货推荐
附录:MySQL 教程:系列文章
- 一、MySQLMariaDB 基础教程
- 二、MySQL 简介
- 三、MySQL MariaDB 安装
- 四、MySQL 管理
- 五、MySQL 日常管理
- 六、MySQL PHP 语法
- 七、MySQL 创建连接
- 八、MySQL 获取数据库列表
- 九、MySQL 创建数据库
- 十、MySQL 删除数据库
- 十一、MySQL 选择数据库
- 十二、MySQL 数据类型
- 十三、MySQL 列出数据表
- 十四、MySQL 创建数据表
- 十五、MySQL 删除表
- 十六、MySQL 插入数据
- 十七、MySQL 获取插入数据的 ID
- 十八、MySQL SELECT FROM 查询数据
- 十九、MySQL WHERE 子句有条件的查询数据
- 二十、MySQL UPDATE 更新数据
- 二十一、MySQL DELETE FROM 语句删除数据
- 二十二、MySQL 返回删改查受影响的行数
- 二十三、MySQL LIKE 子句模糊查询数据
- 二十四、MySQL UNION 操作符查询多张表
- 二十五、MySQL ORDER BY 排序
- 二十六、MySQL GROUP BY 分组查询数据
- 二十七、MySQL JOIN 进行多表查询
- 二十八、MySQL NULL 值处理
- 二十九、MySQL REGEXP 子句正则表达式查询
- 三十、MySQL 数据库事务
- 【当前读到】三十一、MySQL ALTER 命令
- 三十二、MySQL 索引
- 三十三、CREATE TEMPORARY TABLE 创建临时表
- 三十四、MySQL DROP TABLE 删除临时表
- 三十五、MySQL INSERT INTO SELECT 复制表
- 三十六、MySQL 获取服务器元数据
- 三十七、MySQL 自增序列 AUTO_INCREMENT
- 三十八、MySQL 处理重复数据
- 三十九、MySQL 安全及防止 SQL 注入攻击
- 四十、MySQL 导出数据
- 四十一、MySQL 导入数据