MySQL：在表中增加、删除、修改列


#MySQL 笔记

增加一列

创建表：

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

比如我们要添加 mail 列。

ALTER TABLE `user_info` 
	ADD COLUMN mail VARCHAR(20) NOT NULL DEFAULT '' COMMENT '邮箱' AFTER `name`;

查看效果：

mysql> show create table user_info;
+-----------+----------------------------------------------------------+
| Table     | Create Table                                             |
+-----------+----------------------------------------------------------+
| user_info | CREATE TABLE `user_info` (                               |
|           |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,      |
|           |   `name` varchar(45) NOT NULL,                           |
|           |   `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱', |
|           |   PRIMARY KEY (`id`)                                     |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                  |
+-----------+----------------------------------------------------------+

增加两列

创建表：

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

我们尝试追加 mail 和 pasword 列。

ALTER TABLE `user_info` 
    ADD COLUMN mail VARCHAR(20) NOT NULL DEFAULT '' COMMENT '邮箱',
    ADD COLUMN password VARCHAR(64) NOT NULL DEFAULT '' COMMENT '密码'
    AFTER `name`;

mysql> show create table user_info;
+-----------+--------------------------------------------------------------+
| Table     | Create Table                                                 |
+-----------+--------------------------------------------------------------+
| user_info | CREATE TABLE `user_info` (                                   |
|           |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,          |
|           |   `name` varchar(45) NOT NULL,                               |
|           |   `password` varchar(64) NOT NULL DEFAULT '' COMMENT '密码', |
|           |   `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',     |
|           |   PRIMARY KEY (`id`)                                         |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                      |
+-----------+--------------------------------------------------------------+

结果不符合预期，因为 password 在 mail 前面了。

正确的写法应该是：

ALTER TABLE `user_info` 
    ADD COLUMN mail VARCHAR(20) NOT NULL DEFAULT '' COMMENT '邮箱' AFTER `name`,
    ADD COLUMN password VARCHAR(64) NOT NULL DEFAULT '' COMMENT '密码' AFTER `mail`;

mysql> show create table user_info;
+-----------+--------------------------------------------------------------+
| Table     | Create Table                                                 |
+-----------+--------------------------------------------------------------+
| user_info | CREATE TABLE `user_info` (                                   |
|           |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,          |
|           |   `name` varchar(45) NOT NULL,                               |
|           |   `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',     |
|           |   `password` varchar(64) NOT NULL DEFAULT '' COMMENT '密码', |
|           |   PRIMARY KEY (`id`)                                         |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                      |
+-----------+--------------------------------------------------------------+

删除列

创建表：

 CREATE TABLE `user_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `password` varchar(64) NOT NULL DEFAULT '' COMMENT '密码',
  `mail` varchar(20) NOT NULL DEFAULT '' COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

删除name列：

ALTER TABLE `user_info` DROP COLUMN `name`;

删除password、mail列：

ALTER TABLE `user_info` DROP COLUMN `password`, DROP COLUMN `mail`;

修改列的类型，并添加注释

创建表：

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

将 name 列的类型修改为 char(45) ，并增加注释：

ALTER TABLE `user_info` 
MODIFY COLUMN `name` CHAR(45) COMMENT '姓名';

效果：

mysql> show create table user_info;
+-----------+-----------------------------------------------------+
| Table     | Create Table                                        |
+-----------+-----------------------------------------------------+
| user_info | CREATE TABLE `user_info` (                          |
|           |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, |
|           |   `name` char(45) DEFAULT NULL COMMENT '姓名',      |
|           |   PRIMARY KEY (`id`)                                |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4             |
+-----------+-----------------------------------------------------+

修改列名

CREATE TABLE `user_info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(45) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE utf8mb4_general_ci;

将 name 列名修改为user_name：

ALTER TABLE `user_info` CHANGE COLUMN `name` `user_name` VARCHAR(45) NOT NULL;

效果：

mysql> show create table user_info;
+-----------+-----------------------------------------------------+
| Table     | Create Table                                        |
+-----------+-----------------------------------------------------+
| user_info | CREATE TABLE `user_info` (                          |
|           |   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, |
|           |   `user_name` varchar(45) NOT NULL,                 |
|           |   PRIMARY KEY (`id`)                                |
|           | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4             |
+-----------+-----------------------------------------------------+

限制

修改期间，不能进行写操作。

所以如果修改时间过长，要考虑业务影响。

耗时

在我的电脑上，一张2000w数据的表，修改类型耗时约40秒，增加列耗时约45秒。



( 本文完 )