1. 首页
  2. mysql教程

31-三十一、MySQL ALTER 命令

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

测试数据

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

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


DROP DATABASE IF EXISTS `ycbbs`; CREATE DATABASE ycbbs default character set utf8mb4 collate utf8mb4_unicode_ci; USE ycbbs; 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://www.ycbbs.vip','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 [ycbbs]> 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 | | +------------+------------------+------+-----+---------+----------------+ rows in set (0.02 sec)

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


MariaDB [ycbbs]> 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 [ycbbs]> 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 | | +-------+------------------+------+-----+---------+----------------+ 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 [ycbbs]> 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 | | +-------+------------------+------+-----+---------+----------------+ rows in set (0.02 sec)

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


MariaDB [ycbbs]> 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 [ycbbs]> 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 | | +------------+------------------+------+-----+---------+----------------+ 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 [ycbbs]> ALTER TABLE tbl_language ADD lid int FIRST; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [ycbbs]> ALTER TABLE tbl_language ADD llid int AFTER url; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [ycbbs]> 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 [ycbbs]> 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 | | +------------+------------------+------+-----+---------+----------------+

FIRSTAFTER 关键字只适用于 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 [ycbbs]> 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 [ycbbs]> 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 | | +------------+------------------+------+-----+---------+----------------+ 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 [ycbbs]> 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 | | +------------+------------------+------+-----+---------+----------------+ 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 [ycbbs]> 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 [ycbbs]> 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 | | +------------+------------------+------+-----+---------+----------------+ 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 [ycbbs]> 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 [ycbbs]> 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 | | +------------+------------------+------+-----+---------+----------------+ 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 [ycbbs]> 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: row in set (0.00 sec) ERROR: No query specified

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


MariaDB [ycbbs]> 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 [ycbbs]> 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: row in set (0.00 sec) ERROR: No query specified

修改表名

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

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


SHOW TABLES;

执行结果如下


MariaDB [ycbbs]> SHOW TABLES; +----------------+ | Tables_in_ycbbs | +----------------+ | tbl_language | | tbl_rank | +----------------+ rows in set (0.01 sec)

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


ALTER TABLE tbl_language RENAME TO tbl_lang;

执行结果如下


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

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


MariaDB [ycbbs]> SHOW TABLES; +----------------+ | Tables_in_ycbbs | +----------------+ | tbl_lang | | tbl_rank | +----------------+ rows in set (0.00 sec)

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

ALTER 修改索引

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

希望读者能够给小编留言,也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")

JS中文网,Javascriptc中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区,是给开发者用的 Hacker News,技术文章由为你筛选出最优质的干货,其中包括:Android、iOS、前端、后端等方面的内容。目前已经覆盖和服务了超过 300 万开发者,你每天都可以在这里找到技术世界的头条内容。

本文著作权归作者所有,如若转载,请注明出处

转载请注明:文章转载自「 Java极客技术学习 」https://www.javajike.com

标题:31-三十一、MySQL ALTER 命令

链接:https://www.javajike.com/article/1594.html

« 32-三十二、MySQL 索引
30-三十、MySQL 数据库事务»

相关推荐

QR code