03MySQL中的约束与索引

约束和 索引是两种重要的数据库对象。常用的数据库对象还有 表、 数据字典、 视图、 函数、 存储过程、 触发器。

约束

约束是插入和处理数据库数据的规则，用来保证数据库中数据的完整性。创建约束有以下两种方式：

• 创建表时就给字段名定义约束。
• 表建成后使用 ALTER TABLE 命令来创建约束。

约束的种类很多，比较常用的约束有以下几种：主键约束、外键约束、唯一约束、检查约束以及默认值约束。

主键约束

主键约束（PRIMARY KEY）：用来建立表的主键，是表中每一条记录的标识符，被确定为主键的字段名必须是非空并且主键列上不能有相同的值。主键可以定义一个字段名或者多个字段名，定义多个字段名的主键叫做复合主键。

主键约束的创建有以下两种格式：

# 建表时，创建主键
CREATE TABLE <表名>（
    <字段名> <数据类型> PRIMARY KEY,
    ...
);

# 建表后，添加主键
ALTER TABLE <表名> MODIFY <字段名> <字段类型> PRIMARY KEY;

在数据库中创建一张名为 info1 的表，并设置id字段为主键：

# 建表时，创建主键
CREATE TABLE info1(
    # 设置 id 为主键
    id int(4) PRIMARY KEY,
    name varchar(20)
);

若不小心把想要设置主键的字段名给弄错了，我们可以使用 ALTER TABLE <表名> DROP PRIMARY KEY删除该主键约束。例如，我们要删除 info1 表中创建的主键，可以使用以下语句：

# 删除主键
ALTER TABLE info1 DROP PRIMARY KEY;

唯一约束

唯一约束（UNIQUE）：在设置字段名为唯一约束后，这列数据不能重复，但允许存在空值，可以设多列为唯一约束，但是主键所在的列不能设置为 UNIQUE。

唯一约束的创建格式有以下两种：

# 建表时，创建唯一约束
CREATE TABLE <表名>（
    <字段名> <数据类型> UNIQUE,
    ...
);
# 建表后，添加唯一约束
ALTER TABLE <表名> MODIFY <字段名> <字段类型> UNIQUE;

创建一张名为 info2 的表并设置唯一约束：

# 创建一张名为 info2 的表，将字段 name 设为唯一约束
CREATE TABLE info2(
    id int(4),
    name char(10) UNIQUE
);

一个表中可以有多个唯一约束，我们在 info2 表中，使用第二种方式再添加一个唯一约束：

# 将 info2 表中的 id 字段名创建唯一约束
ALTER TABLE info2 MODIFY id int(4) UNIQUE;

同样创建的唯一约束也是可以删除的。例如，我们删除 info2 表中字段名 id这个唯一约束：

# 删除唯一约束
ALTER TABLE info2 DROP INDEX id;

若忘记在表中添加了哪些约束，我们可以使用 SHOW CREATE TABLE <表名> 来查看表中的所有约束。

检查约束

检查约束（CHECK） ：是保证一列的数据满足指定的条件，它通过限制输入到列中的值来强制域的完整性。但目前 MySQL 版本只是对 CHECK 约束进行了分析处理，会被直接忽略掉，并不会报错。

检查约束的创建格式有以下两种：

# 建表时，创建检查约束
CREATE TABLE <表名>（
    <字段名> <数据类型>,
    CHECK <检查约束条件>
    ...
);
# 建表后，添加检查约束
ALTER TABLE <数据表名> ADD CONSTRAINT <检查约束名> CHECK(<检查约束条件>)

例如，我们创建一张名为 info3 的表，在表中对年龄设置检查约束，约束年龄在 18 岁到 30 岁之间：

CREATE TABLE info3(
    id int(4),
    score int(4),
    age int(4),
    CHECK (age > 18 and age < 30)
);

使用第二种方式，在表中添加检查约束：

# 在 info3 表中的 score 添加检查约束，约束条件为分数大于 50
ALTER TABLE info3 ADD CONSTRAINT score_check CHECK(score > 50);

默认值约束

默认值约束（DEFAULT） ：是用于在进行插入操作时，如果某一列没有值，系统会自动把之前设置的默认值赋值到该列。

默认值约束的创建格式有以下两种：

# 在建表时，创建默认值约束
CREATE TABLE <表名>(
    <字段名> <数据类型> DEFAULT <默认值>,
    ...
);
# 在建表后，添加默认值约束
ALTER TABLE <表名> MODIFY <字段名> <字段类型> DEFAULT VALUE;

例如，我们创建一张名为 info4 的表，在里面添加默认约束：

# 创建表同时给 gender 字段创建默认值约束，默认值为 Female
CREATE TABLE info4(
    id int(4),
    name char(20),
    gender char(7) DEFAULT 'Female');
)
# 查看表中信息
DESCRIBE info4;

在已经创建的表中也可以添加默认值。如下：

ALTER TABLE info4 MODIFY name char DEFAULT '无名';

使用 ALTER TABLE <表名> ALTER COLUMN <字段名> DROP DEFAULT 来删除默认值索引：

ALTER TABLE info4 ALTER COLUMN name DROP DEFAULT;

外键约束

外键约束（FOREIGN KEY） ：是表中的一列，其值必须列在另一表的主键中。外键约束有以下三种：

• 严格模式：被子表用的记录不能被父表删除或更新。
• 级联模式：父表改变，子表也跟着改变。
• 置空模式：父表改变，子表对应的字段会被置空。
  设置外键约束，保证两张表之间具有父子关系，也就是说子表外键的取值范围由父表决定，子表外键的数据类型要与父表一致。

创建外键的格式有以下两种：

# 创建表时，建立外键
CREATE TABLE <表1>（
    <字段名1> <数据类型>,
    ...
);

CREATE TABLE <表2>（
    <字段名2> <数据类型>,
    ...
    # 外键字段名2 参照表1 中的字段名1
    FOREIGN KEY (<字段名2>) REFERENCES <表1>（<字段名1>）
);
# 创建表后，添加外键
ALTER TABLE <表名1> ADD CONSTRAINT<外键名> FOREIGN KEY <外键字段名> REFERENCES <表名2>(主键字段名);

例如，我们在数据库中创建两张表，一张名为 class 的表，一张名为 student 的表，在创建第二张表中创建外键约束：

CREATE TABLE class(
    id int PRIMARY KEY,
    name varchar(20)
);
CREATE TABLE student(
    stu_id int PRIMARY KEY,
    stu_name varchar(10),
    score int(4),
    class_id int,
    # student 表中的 class_id 将参考 class 表中的 id
    FOREIGN KEY(class_id) REFERENCES class(id)
);

此时的 class 表为父表，而 student 表为子表。若我们对父表的主键字段进行删和改时，如果对应的主键在子表中被引用，操作就会失败。
假如class 和 student 这两张表已经存在，但没有设置外键约束，可以使用以下语句：

ALTER TABLE student ADD CONSTRAINT FOREIGN KEY (class_id) REFERENCES class (id);

值得注意的是外键约束必须是在 InnoDB 存储引擎中才起作用。此外，使用外键会降低数据库的速度，所以尽量不要使用。

索引

索引是存储引擎用于快速找到记录的一种数据结构，在 MySQL 中也叫做键，用来排序数据以加快搜索和排序操作的速度。这就像图书馆里的每本书都有自己的编号一样，如果图书馆里的书没有编号也没有分类，那我们要找到一本书，会花很长时间。而每本书拥有自己唯一的编号，我们只需要在对应编号的书架上查找该书。所以，索引的出现是为了提高查询速度。

索引按数据结构分类，常见的有以下三种：

• 哈希索引（HASH）：是基于哈希实现的，只有精确匹配索引的所有列的查询才有效。
• B-Tree 索引（BTREE）：是索引的值按照顺序存储。
• R-Tree 空间数据索引（RTREE）：是 MyISAM 表支持空间数据索引，可以用作地理数据存储。

常见的索引种类有以下几种：

• 普通索引（INDEX）：最基本的索引，没有任何限制，仅加速查询。
• 唯一索引（UNIQUE）：是索引列的值必须唯一，但允许有空值。
• 主键索引（PRIMARY KEY）：是一种特殊的唯一索引，它不允许有空值。
• 全文索引（FULLTEXT）：是对文本的内容进行分词，进行搜索。

普通索引

普通索引的创建方法如下所示：

# 直接创建索引
CREATE INDEX <索引名> ON <表名>(<字段名>)

假设有一张名为 class 的表(包含id字段和name字段)，我们为给表中的 name 字段创建一个普通索引：
方式1：直接创建

# 给表中的 name 字段名创建一个名为 idx_id_name 的索引
CREATE INDEX idx_id_name ON class(name);

可以使用 SHOW INDEX FROM <表名>\G 来查看一下刚才创建的索引信息：

SHOW INDEX FROM class\G;

方式2：修改表结构创建

# 修改表结构的方式添加索引
ALTER TABLE <表名> ADD INDEX <索引名> (<字段名>);

我们再向 class 表中的 id 字段名添加一个名为 idx_id 的索引：

ALTER TABLE class ADD INDEX idx_id (id);

方式3：建表时直接创建

# 建表同时创建索引
CREATE TABLE <表名>(
    <字段名> <数据类型>,
    ...
    INDEX <索引名> (<字段名>)
);

在数据库中创建一张名为 t的表，在创建表时，同时创建索引：

# 创建表时，并创建一个名为 idx_id 的索引
CREATE TABLE t(
    id int NOT NULL,
    name varchar(12),
    INDEX idx_id (id)
);
# 查看索引信息
SHOW INDEX FROM t\G;

如果索引设置错误，我们可以使用 DROP INDEX <索引名> ON <表名> 来删除索引。

例如，我们把名为 t 的表中创建的一个名为idx_id的索引给删除了：

# 删除名为 t 的索引
DROP INDEX idx_id ON t;

唯一索引

唯一索引与普通索引的区别在于，唯一索引是查到符合条件的项后就直接返回，而普通索引是查到符合条件的项后会继续查找下一项，若下一项不符合再返回。

创建唯一索引的方式：
方式1：直接创建

# 创建唯一索引
CREATE UNIQUE INDEX <索引名> ON <表名> (<列名>)

比如，在刚才创建的 t 表中创建一个名为 idx_uni_id 的唯一索引：

CREATE UNIQUE INDEX idx_uni_id ON t (id);

方式2：修改表结构创建索引

# 修改表结构创建唯一索引
ALTER TABLE <表名> ADD UNIQUE <索引名>（<列名>）

例如，在 t 表中字段名 name 上添加一个唯一索引，取索引名为 idx_uni_name：

ALTER TABLE t ADD UNIQUE idx_uni_name (name);

方式3：建表时直接创建

# 建表时,创建唯一索引
CREATE TABLE <表名>(
    <字段名> <数据类型>,
    ...
    UNIQUE <索引名> (<列名>)
);

例如，创建一张名为 t1 的表，并且在建表时建立唯一索引：

CREATE TABLE t1(
    id int,
    name varchar(12),
    UNIQUE idx_uni_id (id)
);

主键索引

主键索引必须唯一，并且非空。

创建主键索引的方式如下所示：
方式1：修改表结构创建索引

# 创建表后，添加主键索引
ALTER TABLE <表名> ADD PRIMARY KEY ( <字段名> )

例如，在 t1 表中，给字段 id 添加一个主键索引

# 在 t1 表中添加主键索引
ALTER TABLE t1 ADD PRIMARY KEY (id);
# 查看索引信息
SHOW INDEX FROM t1\G;

方式2：建表时直接创建索引

# 创建表时，建立主键索引
CREATE TABLE <表名>(
    <字段名> <数据类型>,
    ...
    PRIMARY KEY(<列名>)
);

例如，创建一张名为 t2 的表，并给字段名 id 添加一个索引：

CREATE TABLE t2(
    id int NOT NULL,
    name varchar(12),
    PRIMARY KEY(id)
);

SHOW INDEX FROM t2\G;

删除索引
例如，把 t1 表中的主键索引给删除了。

# 删除主键索引
ALTER TABLE t1 DROP PRIMARY KEY;

全文索引

全文索引有很多，例如自然语言搜索、布尔全文搜索、查询扩展搜索等。若想了解更多关于全文索引的知识，可以查看 官方文档 。

全文索引的创建方式：
方式1：建表时直接创建索引

# 创建表同时建立全文索引
CREATE TABLE <表名> (
    <字段名> <字段类型>,
    FULLTEXT (<字段名>)
);

例如，创建一个名为 books 的数据表，并在创建表时，创建全文索引：

# 创建一张名为 books 的表
CREATE TABLE books(
    id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title varchar(50),
    author varchar(30),
    # 将字段名 title 和 author 设置全文索引
    FULLTEXT (title,author)
);

同样这里可以使用 SHOW INDEX FROM books 来看一下创建的索引信息，现在向表里插入数据：

# 向表中插入一些书名和作者名
INSERT INTO books(title,author) VALUES
('Wuthering Heights','Emily Bronte'),
('Jane Eyre','Charlotte Bronte'),
('David Copperfield','Charles Dickens');

# 使用自然搜索来检索在表中含有 Bronte 的行数据
SELECT * FROM books
WHERE MATCH (title,author)
AGAINST ('Bronte' IN NATURAL LANGUAGE MODE)；

方式2：修改表结构创建索引

# 创建表后，添加全文索引
ALTER TABLE <表名> ADD FULLTEXT (<字段名>)

索引的优点主要是它可以在很大程度上减少服务器需要扫描的数据量；可以帮助服务器避免排序和临时表；可以将随机 I/O 变为顺序 I/O。

一般来说，增加新索引将会导致 INSERT、UPDATE、DELETE 等操作的速度变慢。

约束和索引的关键字很多是相同的，但是两者之间的使用性质是完全不同的。约束是为了保证数据的完整性，而索引是为了提高查询数据的效率。