MySQL的索引与键

MySQL的索引与键

索引(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要把这个当作例外,删除外键的时候连带把索引删除了不就好了嘛。

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

打赏

订阅评论
提醒
guest

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

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

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

MySQL的索引与键