MySQL 学习笔记

MySQL是一个开源的关系型数据库,非常流行。

如无特殊说明,本专题文章的 SQL 均在 5.6 版本中验证。

👉文章列表
MySQL 学习笔记 ⏬ 创建和删除数据库 切换和查看数据库 创建和删除表 在表中增加、删除、修改列 添加和删除索引 增删查改 创建和删除主键 大小写和反引号 字符串类型值的大小写 SQL注释 CHAR类型 VARCHAR类型 整型数字 时间戳 不要使用utf8 唯一索引与NULL 唯一索引的单列长度限制 NULL的判等 DDL、DML、DCL的含义 查看表的状态 show table status InnoDB存储引擎的限制 if和case的使用 自定义函数 InnoDB 索引 事务 事务隔离级别的概念、查看和修改 读已提交(read committed)隔离级别下的锁 可重复读(repeatable read)隔离级别下的锁 修改自增主键id的类型 使用 load data 快速导入数据 使用 select into outfile 导出数据

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;

我们尝试追加 mailpasword 列。

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`;

删除passwordmail列:

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秒。


( 本文完 )

文章目录