MySQL的索引与键

MySQL的索引与键

Posted at Last edit Comments 0 Comment277 Views2 Likes

索引(index)

MySQL索引操作语句有三个，分别为：CREATE、DROP、ALTER TABLE。另外，对于创建索引，除了创建表后再创建索引，我们还可以在创建表时顺便创建索引。

创建索引一：使用CREATE

官方文档：13.1.15 CREATE INDEX Statement。

# 普通索引
CREATE INDEX <索引名> ON `<表名>`(`<列名>`);
# <排序>有两个值，ASC或DESC，如果不指定，默认为ASC，但排序只能用于InnoDB表
CREATE INDEX <索引名> ON `<表名>`(`<列名>` <排序>);
# 举例(标准格式，字段需要反引号，但一般可以不写反引号，不指定<排序>，默认为ASC)
CREATE INDEX inx_first_name ON `employees`(`first_name`);
CREATE INDEX inx_first_name ON `employees`(`first_name` DESC);

# 唯一索引
CREATE UNIQUE INDEX `<索引名>` ON `<表名>`(`<列名>`);
CREATE UNIQUE INDEX `<索引名>` ON `<表名>`(`<列名>` <排序>);

# 联合索引
CREATE INDEX `<索引名>` ON `<表名>`(`<列名1>`, `<列名2>`, ……, `<列名n>`);
CREATE INDEX `<索引名>` ON `<表名>`(`<列名1>` <排序>, `<列2>` <排序>, ……, `<列名n>` <排序>);
CREATE UNIQUE INDEX <索引名> ON `<表名>`(`<列名1>`, <列2>, ……, <列名n>);
CREATE UNIQUE INDEX <索引名> ON `<表名>`(`<列名1>` <排序>, `<列2>` <排序>, ……, `<列名n>` <排序>);
  • 联合索引，官网文档叫multiple-column index(即多列索引)，但中文很多人又把它叫做组合索引、复合索引、多列索引，叫法不一样，但意思都一样，无论是普通索引还是唯一索引，都可以单列或多列；
  • 联合索引遵循最左匹配原则，比如有col1,col2,col3三列联合索引，能被使用的匹配有(col1),(col1,col2),(col1,col2,col3)，这一点在8.3.1 How MySQL Uses Indexes中也有说到(进去后页内搜索“If the table has a multiple-column index”)；
  • <索引名>是你自己取的一个名称，就好像你创建一个字段总得有一个字段名吧（事实上用ALTER TABLE创建索引是可以不用写索引名的，它会默认用列名作为索引名，但CREATE INDEX方式就必须指定索引名）；
  • 联合唯一索引，是指联合索引的多个列的值不能同时一样；
  • CREATE无法创建主键索引，要创建主键索引必须使用下边说到的ALTER TABLE来创建，这一点在13.1.15 CREATE INDEX Statement中有说明。
  • <排序>要看你实际使用，如果实际使用都是order by DESC比较多，那么排序就用DESC，详见：8.3.13 Descending Indexes。

创建索引二：使用ALTER TABLE

但我更愿意把它叫“添加索引”，因为语句中是有ADD INDEX这个英文的

# 创建普通索引：不指定索引名(会默认使用列名作为索引名)
ALTER TABLE `<表名>` ADD INDEX (`<列名>`);
# 创建普通索引：指定索引名
ALTER TABLE `<表名>` ADD INDEX <索引名>(`<列名>`);
# <排序>有两个值，ASC或DESC，如果不指定，默认为ASC，但排序只能用于InnoDB表
ALTER TABLE `<表名>` ADD INDEX <索引名>(`<列名>` <排序>);
# 举例
ALTER TABLE `employees` ADD INDEX (`first_name`);
ALTER TABLE `employees` ADD INDEX inx_first_name(`first_name`);
ALTER TABLE `employees` ADD INDEX inx_first_name(`first_name` DESC);

# 创建唯一索引
ALTER TABLE `<表名>` ADD UNIQUE INDEX <索引名>(`<列名>`);
ALTER TABLE `<表名>` ADD UNIQUE INDEX <索引名>(`<列名>` <排序>);

# 创建主键索引
ALTER TABLE `<表名>` ADD PRIMARY KEY(`<列名>`);

# 创建联合索引
ALTER TABLE `<表名>` ADD PRIMARY KEY(`<列名1>`, `<列名2>`, ……, `<列名n>`);
ALTER TABLE `<表名>` ADD INDEX `<索引名>(`<列名1>`, `<列名2>`, ……, `<列名n>`);
ALTER TABLE `<表名>` ADD INDEX `<索引名>(`<列名1>` <排序>, `<列名2>` <排序>, ……, `<列名n>` <排序>);
ALTER TABLE `<表名>` ADD UNIQUE INDEX <索引名>(`<列名1>`, `<列名2>`, ……, `<列名n>`);
ALTER TABLE `<表名>` ADD UNIQUE INDEX <索引名>(`<列名1>` <排序>, `<列名2>` <排序>, ……, `<列名n>` <排序>);

# 同时给多列添加索引(分别给每列添加各种类型的索引都行，不是联合索引)
ALTER TABLE `<表名>` ADD INDEX <索引名1>, ADD INDEX <索引名2>, ……, ADD UNIQUE INDEX <索引名n>;
ALTER TABLE `<表名>` ADD INDEX <索引名1> <排序>, ADD INDEX <索引名2> <排序>, ……, ADD UNIQUE INDEX <索引名n> <排序>;

关于主键索引与自增：

  • 1、虽然我们平时用主键索引都是自增的，但实际上主键索引是可以设置为不自增的；
  • 2、如果要把一个字段设置为自增字段，那么这个字段必须添加一个索引，否则报错（亲测，可以是主键索引、唯一索引、普通索引）；
  • 3、自增的字段必须为整型，毕竟只有整型数字才有自增的说法；

创建索引三：建表时创建

比如创建一个user表，id字段中的“PRIMARY KEY”意思是把它设置为主键(也就是创建主键索引)

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄'
);

当然也可以不写在字段属性中，而是在字段的后面单独声明(事实上这种用法更常见和通用)

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄',
    -- 指定给id字段添加主键
    PRIMARY KEY(`id`)
);

可同时创建多个索引：比如，同时创建主键索引+普通索引+唯一索引

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄',
    -- 指定给id字段添加主键
    PRIMARY KEY(`id`),
    -- InnoDB表可指定排序为DESC(不指定默认为ASC)，唯一索引也可指定排序
    INDEX <索引名>(`name` DESC),
    UNIQUE INDEX <索引名>(`nickname`)
);

像ALTER TABLE一样，索引名也可以不写(但CREATE INDEX方式必须写索引名)

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄',
    PRIMARY KEY(`id`),
    INDEX (`name`),
    -- InnoDB表可指定排序为DESC(不指定默认为ASC)
    UNIQUE INDEX (`nickname` DESC)
);

查看索引

加不加es都可以，但一般来说，应该用复数形式比较好

SHOW INDEX FROM `<表名>`;
SHOW INDEXES FROM `<表名>`;

# 用\G可以竖着显示（只能在命令终端中用，不能在可视化客户端用）
SHOW INDEX FROM `<表名>`\G;
SHOW INDEXES FROM `<表名>`\G;

参数解释如下

说明 参数
Table 表示创建索引的数据表名。
Non_unique 表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。
Key_name 表示索引的名称。
Seq_in_index 表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
Column_name 表示定义索引的列字段。
Collation 表示列以何种顺序存储在索引中。在MySQL中，升序显示值“A”（升序），若显示为NULL，则表示无分类。
Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为NULL。
Packed 指示关键字如何被压缩。若没有被压缩，值为NULL。
Null 用于显示索引列中是否包含NULL。若列含有NULL，该列的值为YES。若没有，则该列的值为NO。
Index_type 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment 备注

删除非主键索引

删除非主键索引(唯一索引、普通索引)

# 使用drop，对应create
DROP INDEX <索引名> ON `<表名>`;
# 举例（必须先创建名为inx_first_name的索引）
DROP INDEX inx_first_name ON `employees`;

# 使用alter
ALTER TABLE `<表名>` DROP INDEX <索引名>
# 举例（必须先创建名为inx_first_name的索引）
ALTER TABLE `employees` DROP INDEX inx_first_name;

# 同时删除多个索引
ALTER TABLE `<表名>` DROP INDEX <索引名1>, DROP INDEX <索引名2>, ……, DROP UNIQUE INDEX <索引名n>;

注意：

  • 1、CREATE创建的索引既可以使用DROP来删除，也可以使用ALTER TABLE来删除，并不是说CREATE创建的索引就一定要用DROP删除，反之亦然，因为无论用哪种方法创建索引，只要指定的参数相同，索引都是一样的；
  • 2、无论是唯一索引还是普通索引，都是索引，都叫INDEX，创建唯一索引时需要UNIQUE来指定，但删除的时候是不需要UNIQUE INDEX来指定删除唯一索引的，只需要写INDEX即可。

删除主键索引

先说怎么操作，详细解释请往下看

# 如果主键有自增，必须先删除自增(原因是自增依赖于索引)
ALTER TABLE `<表名>` MODIFY id INT(11) UNSIGNED NOT NULL COMMENT '自增id';

# 再删除主键索引
ALTER TABLE `<表名>` DROP PRIMARY KEY;

如果主键有自增，则删除主键索引必须先取消主键索引的自增，否则会报错

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

事实上，并没有一个直接的语句可以取消自增，所谓的“取消自增”，实际上是“修改字段属性”，因为自增也属于字段的一个属性，比如我们创建一个user表的时候，是这么创建的

CREATE TABLE `USER`(
    `id` INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `age` TINYINT(4) COMMENT '年龄'
);

从上边的建表语句可以看到id这个字段中有以下定义(PRIMARY KEY代表设置为主键，AUTO_INCREMENT代表设置为自增)

PRIMARY KEY AUTO_INCREMENT

我们修改id字段的时候，把PRIMARY KEY AUTO_INCREMENT去掉，其它还是原样不动

ALTER TABLE `user` MODIFY id INT(11) UNSIGNED NOT NULL COMMENT '自增id';

注意：这样只是把定义里的“主键索引”和“自增”两个属性去掉了，但实际上主键索引还是存在的，我们需要把手动它删除(请往下看)。

去掉自增后，我们就可以删除主键索引了

ALTER TABLE `user` DROP PRIMARY KEY;

有人可能会说，表又不是我创建的，我也没有建表语句，我怎么知道id字段有哪些属性呢？其实用以下语句就可以查看

SHOW CREATE TABLE `user`;

以上语句输出如下，可见，我们可以很容易获得id的属性定义

| user  | CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` tinyint DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE/DROP和ALTER TABLE的区别

索引操作语句有三个，分别为：CREATE、DROP、ALTER TABLE。

  • CREATE和DROP是一对，CREATE用于创建索引，DROP用于删除索引；
  • ALTER TABLE既可创建索引，也可删除索引，但用ALTER TABLE创建索引时，一般不叫创建索引，而叫添加索引(ADD INDEX)；
  • CREATE和DROP无法创建和删除主键索引，需要用ALTER TABLE来代替，这一点在13.1.15 CREATE INDEX Statement中有提到，请往下看；
  • CREATE INDEX创建索引必须指定索引名称，但ALTER TABLE创建索引可以不指定索引名称(会默认使用列名作为索引名名)；
  • CREATE/DROP INDEX一条语句只能创建一个索引，而ALTER TABLE一条语句可创建/删除多个索引；
  • CREATE和DROP本质上只是对ALTER TABLE的一个映射，意思就是说，MySQL内部还是会把CREATE或DROP转换成ALTER TABLE的形式的，这一点在CREATE和DROP语句的官方文档中都有提到：

在13.1.15 CREATE INDEX Statement中有提到

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.9, “ALTER TABLE Statement”. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. For more information about indexes, see Section 8.3.1, “How MySQL Uses Indexes”.

翻译：CREATE INDEX映射到ALTER TABLE语句以创建索引（也就是说CREATE INDEX本质上还是调用ALTER TABLE语句来创建索引的）。CREATE INDEX无法创建主键，使用ALTER TABLE来代替。

在13.1.27 DROP INDEX Statement中有提到

DROP INDEX drops the index named index_name from the table tbl_name. This statement is mapped to an ALTER TABLE statement to drop the index. See Section 13.1.9, “ALTER TABLE Statement”.

翻译：DROP INDEX用于删除名为index_name的索引。该语句会被映射到ALTER TABLE语句来删除索引。

键(key)

“键(key)”依附于“列(字段)”，用于约束和规范数据库的结构完整性的。

如果你懂化学，那么你应该知道有“化学键”这个东西，化学键正是让分子或原子聚集在一起的原因，从这点上讲，我们可以认为，化学键是用于维护物质的完整性的，用于约束分子或原子，不让他们分开。因为一旦分开，物质就变了，比如水分子，有两个氢原子和一个氧原子，如果他们分开了，那么就不再是水分子了，而是氢离子和氧离子了。

举这个化学的例子是为了说明“键”是一个起约束作用的东西，如果你不懂化学，那可以不看，如果懂，那么这将更有助于你理解“键”到底是什么东西。

MySQL中的“键”也跟化学键类似，也是具有约束作用，并由这个约束作用来维护数据库的结构完整性。

约束的意思是，你必须按它的规定来做，不能不符合规定！比如：它规定你不能为空，这就是一种约束，规定你不能重复也是一种约束。

MySQL中的键有：主键(primary key)、唯一键(unique key)、外键(foreign key)、普通键(key)。

主键(PRIMARY KEY)

一个字段中，不能为空、可用于唯一标识表中的一行记录的键，叫做主键(primary key)。

如何给字段创建主键？

在创建表时，用PRIMARY KEY指定

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄'
);

当然也可以不写在字段属性中，而是在字段后面单独声明(事实上这种用法更常见和通用)

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄',
    -- 指定给id字段添加主键
    PRIMARY KEY(`id`)
);

使用ALTER TABLE语句添加主键

-- 单列主键，最常用
ALTER TABLE `<表名>` ADD PRIMARY KEY(`<列名>`);
-- 举例
ALTER TABLE `user` ADD PRIMARY KEY(`id`);

-- 多列主键：虽然支持多列共同作为主键，但在实际使用中比较少见这种用法
ALTER TABLE `<表名>` ADD PRIMARY KEY(`<列名1>`, `<列2>`, ……, `<列名n>`);
-- 举例
ALTER TABLE `user` ADD PRIMARY KEY(`id`,`id2`,`id3`);

主键与自增之间的关系：

  • 主键通常都会设置为自增，但主键和自增是没有必然的联系的，主键是可以不设置为自增的；
  • 自增的字段也未必是主键（有普通索引，唯一索引都可以设置自增）；
  • 自增要求一定是整型，但主键不要求是整型(虽然大多数时候都是用整型)，很多类型都可以作为主键；
  • 主键可以有默认值，但自增不能有默认值(会报错)；

唯一键(UNIQUE KEY)

唯一键用于约束列中的数据不能重复(所以叫“唯一”)。有人可能会说，那你这样跟主键有什么区别呢？主键列的数据也是不能重复呀。

主键和唯一键的区别

  • 一个表中只能有一个主键，但可以有多个唯一键；
  • 主键一定是非空的，但唯一键允许为空（创建主键时，就算你在语句中不加NOT NULL，它也会自动加上的）；

如何给字段添加唯一键？
在创建表的时候给字段添加唯一键(其中<键名>可以不写，不写默认使用字段名作为键名)

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
    `username` varchar(50) COMMENT '用户名',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄',
    -- 给username添加唯一键
    UNIQUE KEY <键名>(`username`)
);

使用ALTER TABLE语句添加唯一键

-- 格式
ALTER TABLE `<表名>` ADD UNIQUE KEY <键名>(`<列名>`);
-- 举例
ALTER TABLE `user` ADD UNIQUE KEY key_username(`username`);

当一个表没有主键，只有一个非空唯一键时(不要求unsigned，也不要求自增)，用desc <表名>查看字段信息，它会把这个非空唯一键显示成PRI

-- 创建一个user表，id字段为非空唯一键
CREATE TABLE `user`(
    `id` INT NOT NULL COMMENT 'id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄',
    UNIQUE KEY (`id`)
);

使用desc user查看表结构，可以发现它的Key为PRI

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| nickname | varchar(50) | YES  |     | NULL    |       |
| age      | tinyint     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

我们知道，pri是primary key的意思，也就是说，在这种情况下，它会把这个非空唯一键当成“主键”，虽然它不是实际上的主键，但由于它是非空的唯一键，所以它可以被当成类似主键的功能使用。事实上，主键的两个主要特点就是：非空且唯一。

那如果表中有两个非空唯一键呢？我们再添加一个百空唯一键字段id2，并且把它排序到第一位

ALTER TABLE `user` ADD `id2` INT NOT NULL UNIQUE KEY COMMENT 'id2' FIRST;

desc查看后，发现新添加的id2虽然也是非空唯一键，而且它还排在第一位，但它也被显示成UNI(unique key)，而前面比较早添加的id却被显示成了PRI(主键)

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id2      | int         | NO   | UNI | NULL    |       |
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| nickname | varchar(50) | YES  |     | NULL    |       |
| age      | tinyint     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

由以上可知，如果一个表中有两个非空唯一键，那么先添加的会被显示为PRI，会被当作主键。但是在Sequel Pro中(macOS系统上的一款免费数据库管理软件)，两个非空唯一键都被显示成了UNI(unique key)

但是如果我们给该表再添加一个真正的主键id3(primary key)后

ALTER TABLE `user` ADD `id3` INT NOT NULL PRIMARY KEY COMMENT 'id3';

我们再用desc查看表信息，发现即使id3排在最后面，它也是显示成PRI(primary key)，而前面显示成PRI的id，现在已经显示成UNI了

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id2      | int         | NO   | UNI | NULL    |       |
| id       | int         | NO   | UNI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| nickname | varchar(50) | YES  |     | NULL    |       |
| age      | tinyint     | YES  |     | NULL    |       |
| id3      | int         | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

“非空唯一键”会被显示成PRI的原因：
由于InnoDB的存储引擎原理是使用聚簇索引(行数据存放在叶子节点)，所以InnoDB必须要有一个“行唯一标识符”，我们称它为rowid。

通常来说，一个表的rowid都是主键，但如果你没有创建主键，它就会退而求其次，寻找其它适合做rowid的键，而由于“非空唯一键”可唯一标识一行，所以当表中没有主键的时候，“非空唯一索引”就被选作rowid(这时它起到的效果与主键一样，所以会显示成PRI)，而如果“非空唯一索引”都没有，那其实mysql内部是会自己创建一个隐藏的对用户不可见的rowid(占6个字节)。

关于这个，在官方文档中也是有说明的：14.6.2.1 Clustered and Secondary Indexes。

删除唯一键：

-- 只能使用alter，不能像删除唯一索引那样，使用DROP...ON...方式
ALTER TABLE `<表名>` DROP KEY <键名>
-- 举例（必须先创建名为inx_first_name的索引）
ALTER TABLE `user` DROP KEY <键名>;

外键(FOREIGN KEY)

指定B表中某个字段的值与A表某个字段的值关联，则称B表该字段拥有一个“外键”，之所以叫“外键”，是因为它的值来自于“另外一个表”的对应字段。

注意：两个关联的字段类型必须一样、是否为无符号、是否允许为空都必须一样(比如都是INT、都允许为空、都允许不为空、都是无符号、都是有符号)，否则创建外键时会提示两个字段不兼容(incompatible)。

详见官方文档：13.1.20.5 FOREIGN KEY Constraints。

在创建表时添加外键：

-- 创建文章标题表(主表)：post_titles
CREATE TABLE IF NOT EXISTS `post_titles`(
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
    `title` VARCHAR (255) COMMENT '标题'
);

-- 创建文章内容表(附表)：post_contents
CREATE TABLE IF NOT EXISTS `post_contents`(
    `id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增id',
    `tid` INT   UNSIGNED NOT NULL COMMENT '关联title id',
    `content` VARCHAR (255) COMMENT '标题',
    -- 给tid字段定义一个外键，让它关联post_titles表的主键(即id字段)
    FOREIGN KEY (`tid`) REFERENCES `post_titles`(`id`)
);

使用ALTER TABLE添加外键：

ALTER TABLE `post_contents` ADD FOREIGN KEY (`tid`) REFERENCES `post_titles`(`id`)

特别注意：外键关联的字段并不是必须是主键，唯一键和普通键也是可以作为外键的关联字段的。

给post_titles插入一条数据

-- 给post_titles表插入一条内容
INSERT INTO `post_titles`(`title`) VALUES('这是标题1');

-- 插入一条数据后，可以看到id为1
mysql> select * from `post_titles`;
+----+---------------+
| id | title         |
+----+---------------+
|  1 | 这是标题1     |
+----+---------------+
1 row in set (0.00 sec)

然后我再给post_contents插入一条内容，注意tid我给的是2

INSERT INTO `post_contents`(`tid`, `content`) VALUES(2, '这是内容1');

以上插入肯定会报错，因为tid为2，但这个2在post_titles表中并不存在，所以会报错，报错如下

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (employees.post_contents, CONSTRAINT post_title_content FOREIGN KEY (tid) REFERENCES post_titles (id))

翻译：无法添加或更新子行：外键约束失败。

如果把tid换成1，则插入成功，因为post_titles表中存在1这个id，所以说它能起约束作用，约束外键所在字段的值必须是post_titles表的主键字段(即id字段)中存在的值

INSERT INTO `post_contents`(`tid`, `content`) VALUES(1, '这是内容1');

这个实验有可能遇到要把AUTO_INCREMENT重置为从1开始的需求，所以这里顺便说一下

-- 把表的auto_increment值重置为1
ALTER TABLE `<表名>` AUTO_INCREMENT=1;

我们再来看一下添加外链的这句关键语句

FOREIGN KEY (`tid`) REFERENCES `post_titles`(`id`)

其实它后面还能添加语句“ON DELETE”和“ON UPDATE”语句，分别表示父表删除或更新一行时，子表对应行应该有怎样的反应，“ON DELETE”和“ON UPDATE”都有四种设置，分别为：

  • NO ACTION，也可写成：RESTRICT
  • SET NULL
  • CASCADE
  • SET DEFAULT

NO ACTION(或RESTRICT)：表现为如果你删除父表中的一行，如果子表有关联的行，那么它会不允许你删除父表这一行，你必须先去删除子表那行后，才能删除父表对应的行

-- ON DELETE NO ACTION ON UPDATE NO ACTION是默认的，就是你不写这两个，它默认就是这两个动作
FOREIGN KEY (`tid`) REFERENCES `post_titles`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

-- NO ACTION是标准sql的语句，在mysql中也可以写成RESTRICT，表示“限制”，用限制来描述好像更正确一点，不过这是mysql专用的
FOREIGN KEY (`tid`) REFERENCES `post_titles`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT

-- 也可以省略不写，因为：ON DELETE NO ACTION ON UPDATE NO ACTION是默认值
FOREIGN KEY (`tid`) REFERENCES `post_titles`(`id`)

SET NULL：把外键所在字段的值设置为空，如下所示，因为子表的tid必须是父表中的主键id，现在你把主表那行删掉了，就代表那个id没有了，所以子表的tid也没必要存在了，根据ON DELETE SET NULL这个设置，它会被设置为NULL(这就要求tid字段允许为空，否则会报错)

FOREIGN KEY (`tid`) REFERENCES `post_titles`(`id`) ON DELETE SET NULL ON UPDATE SET NULL

mysql> select * from `post_contents`;
+----+------+---------------+
| id | tid  | content       |
+----+------+---------------+
|  1 | NULL | 这是内容1     |
+----+------+---------------+
1 row in set (0.04 sec)

CASCADE：级联删除，即你删除父表的一行，子表对应行会被mysql服务器自动删除(这种情况用的最多)

FOREIGN KEY (`tid`) REFERENCES `post_titles`(`id`) ON DELETE CASCADE ON UPDATE CASCADE

SET DEFAULT：把外键所在字段的值设置为该字段的默认值，这个其实跟SET NULL类似，只不过它是设置为默认值，这就要求tid有默认值，否则会报错

FOREIGN KEY (`tid`) REFERENCES `post_titles`(`id`) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT

大家可以发现，上边我只对删除动作(ON DELETE)进行了说明，没有对更新动作(ON UPDATE)进行说明，其实更新跟删除是一样的，就拿SET NULL来说，如果父表的主键id字段的值被修改了，那么子表中对应的tid由于没有可以关联的父id，就会被设置为NULL，只不过，一般来说，主键id都是自增的比较多，我们一般不会手动去修改(即UPDATE)它的值，所以能用到ON UPDATE的情况比较少见。

当然也有可能主键并不是自增的，而是那种类似批量随机生成的号码，这时如果需要修改主表号码(比如长度不够，全部统一增加长度)，那么只需要更新主表的号就行，子表关联的号因为有外键，它会全部自动更新为主表中对应行更新的值（当然这要求你把外键设置成ON UPDATE CASCADE）。

普通键(KEY)

建表时添加普通键(<键名>可省略，省略了默认用字段名作为键名)

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄',
    -- 指定给id字段添加主键
    KEY <键名>(`id`)
);

注意，添加普通键，不能把KEY关键字直接写在字段属性里，因为写在这里会被认为是PRIMARY KEY的简写，这样添加的就是主键而不是普通键了

CREATE TABLE `user`(
    `id` INT(11) UNSIGNED NOT NULL KEY AUTO_INCREMENT COMMENT '自增id',
    `name` VARCHAR(20) COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄'
);

使用ALTER TABLE添加普通键(<键名>可省略，省略了默认用字段名作为键名)

ALTER TABLE `user` ADD KEY <键名>(`<字段名>`);

-- 举例：给id字段添加KEY
ALTER TABLE `user` ADD KEY key_id(`id`);

键与索引的关系

在13.1.20 CREATE TABLE Statement中可以搜索到以下说明

KEY | INDEX

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

KEY通常是INDEX的同义词。当在列定义中给出键属性PRIMARY KEY时，也可以仅将其指定为KEY。这是为了与其他数据库系统兼容而实现的。

所以，键和索引的关键字可以这样对应

-- 也就是说，你写成KEY和写成INDEX，它们是一样的
KEY => INDEX
-- 也就是说，你写成UNIQUE KEY和写UNIQUE INDEX，它们是一样的
UNIQUE KEY => UNIQUE INDEX

但是PRIMARY KEY却不能写成PRIMARY INDEX，虽然主键确实会生成一个主键索引，但它却不能这么写，同理还有FOREIGN KEY也不能写成FOREIGN INDEX(因为外键不仅仅是索引)。

KEY(键)和INDEX(索引)的区别：

  • KEY(键)是一种逻辑结构，是一个虚拟的东西，正如前面内容所说，用于起约束作用，并通过这个约束作用来维护数据库完整性的；
  • INDEX(索引)是一种物理结构(它会生成一个索引文件)，是用于加快查找速度的；
  • KEY的逻辑功能是由INDEX来负责完成的，比如给一个字段添加UNIQUE KEY(唯一键)，表示该字段的值不能重复，而实现不能重复这个功能的，是UNIQUE INDEX(唯一索引)，所以你可以认为，给一个字段添加UNIQUE INDEX后，该字段就具有了UNIQUE KEY，反过来，如果你给一个字段添加了UNIQUE KEY，那么它一定会创建一个UNIQUE INDEX，因为UNIQUE KEY代表唯一键，而唯一这个功能必须通过唯一索引(UNIQUE INDEX)来实现；
  • 我再拿化学键来说一次，如果不懂化学可以不看这句。化学键这个“键”其实是由“原子(离子)之间的电荷作用力”来实现这个“键”的功能的，也就是说“键”本身并不是一个实质存在的东西，而是我们把这种相互作用力实现的这个功能称为“键”，而在mysql里，索引就相当于这个“原子(离子)之间的电荷作用力”，“键”只是一个名词，而实现它的原理是通过“索引”；
  • 所以为什么说KEY和INDEX是同义词，因为KEY的功能是由INDEX实现的，所谓给一个字段添加一个KEY(键)，只不过是一个概念罢了，它本质上就是添加一个索引；
  • 但是有一个例外，那就是“外键”，别的键其实只有一个索引来维持，但外键不只是索引，这表现为你删除外键要删两次，一次删除外键，一次删除外键索引，下面会讲到。

查看键

查看键的方法如下，你没看错，查看键，竟然是查看索引，不过看过前面的键与索引的关系之后，我想大家也不会觉得奇怪了

SHOW INDEX FROM `<表名>`;
-- INDEX也可写成复数形式，即加个ES
SHOW INDEXES FROM `<表名>`;

删除键(KEY)

删除普通键、唯一键：

-- 使用DROP KEY删除
ALTER TABLE `<表名>` DROP KEY `<键名>`;

-- 也可使用DROP INDEX删除（前面说过了，KEY和INDEX是同义词，键的本质就是由索引实现的）
ALTER TABLE `<表名>` DROP INDEX `<键名>`;

删除主键：这个其实在前面的删除主键索引里已经讲过了，这里再重复说一次

-- 如果有自增，必须先删除自增(原因是自增依赖于索引)
ALTER TABLE `<表名>` MODIFY id INT(11) UNSIGNED NOT NULL COMMENT '自增id';

-- 然后再删除主键
ALTER TABLE `<表名>` DROP PRIMARY KEY;

-- 也可以用标准格式删除（DROP KEY表示删除键，`PRIMARY`是被删除的键的名称）
ALTER TABLE `<表名>` DROP KEY `PRIMARY`;

删除外键：比较特殊，必须先用DROP FOREIGN KEY删除外键，然后再用DROP KEY(或DROP INDEX)删除外键索引

-- 先删除外键
ALTER TABLE `<表名>` DROP FOREIGN KEY `<键名>`;

-- 再删除索引，用DROP KEY也行，反正都是同义词
ALTER TABLE `<表名>` DROP INDEX `<键名>`;

外键索引留着不删除，并不影响重新建立外键，外键的约束作用也没有了，既然这样那就相当于垃圾，所以要把它删除。

约束(CONSTRAINT)

前面一直在说KEY(键)是起约束作用的，但是我前面一直没有提CONSTRAINT这个关键字。其实前面所有添加KEY的地方(除了普通KEY之外)，primary key，unique key，foreign key前面都可以添加一个

CONSTRAINT <约束名>

在建表语句里可以，在ALTER TABLE语句里也可以，比如

-- 创建一个user表，在PRIMARY KEY前面添加CONSTRAINT关键字以及自定义约束名称“const_pri”
CREATE TABLE `user`(
    `id` INT NOT NULL COMMENT 'id',
    `id2` INT NOT NULL COMMENT 'id2',
    `id3` INT NOT NULL COMMENT 'id3',
    `name` VARCHAR(20)  COMMENT '姓名',
    `nickname` VARCHAR(50) COMMENT '昵称',
    `age` TINYINT(4) COMMENT '年龄'
    CONSTRAINT const_pri PRIMARY KEY(`id`)
);

-- 给user表的id2字段添加一个唯一键，在它的前面添加CONSTRAINT关键字以及自定义一个约束名称“const_id2”
ALTER TABLE `user` ADD CONSTRAINT `const_id2` UNIQUE KEY(`id2`);

-- 再添加一个user表的附表user_info，用于建立外键
CREATE TABLE `user_info`(
    `id` INT NOT NULL COMMENT 'id',
    `uid` INT NOT NULL COMMENT 'uid',
    `info` VARCHAR(255) COMMENT 'user info'
);

-- 给user_info添加一个外键，并在它前面添加CONSTRAINT关键字以及自定义的约束名称“foreign_key_uid”
ALTER TABLE `user_info` ADD CONSTRAINT `foreign_key_uid` FOREIGN KEY(`uid`) REFERENCES `user`(`id`);

但是我们用show create table查看这两个表的建表语句时，发现刚刚添加的那些CONSTRAINT关键字都没了，那是不是代表CONSTRAINT关键字没有任何作用呢？

mysql> SHOW CREATE TABLE `user`;
| user  | CREATE TABLE `user` (
  `id` int NOT NULL COMMENT 'id',
  `id2` int NOT NULL COMMENT 'id2',
  `id3` int NOT NULL COMMENT 'id3',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `nickname` varchar(50) DEFAULT NULL COMMENT '昵称',
  `age` tinyint DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  UNIQUE KEY `const_id2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> SHOW CREATE TABLE `user_info`;
| user_info | CREATE TABLE `user_info` (
  `id` int NOT NULL COMMENT 'id',
  `uid` int NOT NULL COMMENT 'uid',
  `info` varchar(255) DEFAULT NULL COMMENT 'user info'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

说它没什么用，其实还真的没什么用，所以我们平时都省略它不写，估计也是为了兼容标准SQL用的吧，要真说用处，有一个小用处，就是指定KEY名(即索引名)

-- 在同时指定<约束名>和<键名>同时指定时，会选用<键名>而不会选用<约束名>
ALTER TABLE `<表名>` ADD CONSTRAINT <约束名> UNIQUE KEY <键名>(`<字段名>`);
-- 举例，Key名为uni_key，删除时要用uni_key
ALTER TABLE `user` ADD CONSTRAINT const_id2 UNIQUE KEY uni_key(`id2`);

-- 在只指定<约束名>，不指定<键名>时，<约束名>会作为<键名>
ALTER TABLE `<表名>` ADD CONSTRAINT <约束名> UNIQUE KEY(`<字段名>`);
-- 举例，Key名为const_id2，删除时要用const_id2
ALTER TABLE `user` ADD CONSTRAINT const_id2 UNIQUE KEY(`id2`);

-- 当然，如果<约束名>和<键名>都不指定，那它会把<字段名>作为<键名>
ALTER TABLE `<表名>` ADD UNIQUE KEY(`<字段名>`);
-- 举例，Key名为id2，删除时要用id2
ALTER TABLE `user` ADD UNIQUE KEY(`id2`);

另外就是删除“主键”、“唯一键”、“普通键”，可以用CONSTRAINT代替KEY，删除“外键”时，可以用CONSTRAINT代替FOREIGN KEY

ALTER TABLE `<表名>` DROP CONSTRAINT `<键名>`;

当然如果主键是自增的，需要先去除自增属性，才能删除

ALTER TABLE `<表名>` MODIFY id INT(11) UNSIGNED NOT NULL COMMENT '自增id';

如果是外键，还要把外键索引删除掉

ALTER TABLE `<表名>` DROP INDEX `<键名>`;

前面说过，只删除外键，是无法完全删除外键的，因为外键除了有“外键”本身，还有“外键索引”，它们两个的名字完全一样，并且显示的时候只显示成一个索引，但如果你直接删除索引是不允许的(会报错)，必须先册外键，再删索引，虽然删除外键后，这个索引不删除出不会导致无法重新创建外键，以及这个索引也不具有外键的约束作用，但不删除留着就是垃圾，所以还是要把它删除掉比较好，我也实在是不理解为什么mysql要把这个当作例外，删除外键的时候连带把索引删除了不就好了嘛。

一个不错的网站：数据库教程。

扫一扫打赏
觉得文章对你有用的话鼓励一下我吧
打赏
Mysql

©版权声明

本文章由博主『Bruce』撰写，采用『CC BY-NC-SA 4.0(创作共用许可协议4.0)』授权发布，转载请以链接形式标明本文地址。
订阅评论
提醒
guest

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据。

0 评论
内联反馈
查看所有评论
0
希望看到您的想法，请您发表评论x

扫码在手机查看
iPhone请用自带相机扫
安卓用UC/QQ浏览器扫

MySQL的索引与键