MySQL表的约束

在数据库中，数据约束是一种强制性规则，它可以确保数据的完整性和一致性。MySQL提供了多种约束类型，例如：主键约束、唯一键约束、外键约束和检查约束等。它们可以用于限制表中某些列的取值范围或者规定表之间的关系。接下来讲介绍 MySQL 中常用的约束类型及其使用方法。

表的约束

真正约束字段的是数据类型，但是数据了些约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。

表的约束有很多，这里主要介绍这几个： null/not null,default, comment, zerofill，primary key， auto_increment，unique key

空属性

在 MySQL 中，空属性数值某个列中没有任何值。与其它数据库管理系统不同的是，MySQL 中的空属性可以被认为是一种特殊的值，称为 NULL。

数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

可以通过 select 查看 null 的值：

示例：创建一个班级表，包含班级名和班级所在的教室。站在正常的业务逻辑中：

• 如果班级没有名字，你不知道你在哪个班级
• 如果教室名字可以为空，就不知道在哪上课

所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中。这就是“约束”。

向 class 表中插入数据，如下所示，这两列都不能为空，因为被约束了：

默认值

在 MySQL 中，默认约束是指在插入新行时，如果没有为某个列指定值，则该列将自动赋值为默认值。MySQL 支持多种类型的默认约束，包括列级约束和表级约束。

默认值：某一种数据经常性的出现某个具体的值，可以一开始就指定好，用户可以灵活性的选择是否使用默认值。

示例：列级约束是指单个列定义的约束，可以在列定义时指定默认值。如下所示，将年龄 age 的默认值设置为 18：

向 info 表中插入数据：

默认约束可以大大简化数据插入操作。但是，在设计表结构时，应该谨慎使用默认约束，以确保默认值符合实际需求，并且不会影响数据的完整性和一致性。

列描述

在 MySQL 中，comment 是用来为数据库、表、列等对象添加注释的语句。注释可以帮助开发人员更好的理解与维护数据库结构，提高开发效率。

示例：

创建完表结构之后，可以通过 show create table 表名 可以查看创建表的相关细节：

zerofill

在 MySQL 中，zerofill 是一种用于数值类型的属性，它可以在数字前面填充0，使其达到指定的位数。zerofill 只能用于整数类型（如：int、bigint等），不适用于浮点数或其它非整数类型。

在创建表时，可以使用 zerofill 属性来设置整数类型的字段。如果使用 zerofill 属性，必须将字段定义为 unsigned 类型，否则可能会出现错误。如下所示：

在上面示例中，将 num2 字段定义为 int(5) unsigned zerofill 类型，表示 num2 字段是一个无符号整数类型，长度为 5 位，如果不足 5 位，则前面使用 0 来填充。

向 t1 表中插入数据，如下所示：

总的来说，zerofill 属性可以提高查询结果的可读性和可排序性，但需要注意其使用场景和数据类型限制。

主键

在 MySQL 中，主键（primary key）是一种特殊的索引，用于标识表中的唯一记录。主键必须满足以下条件：

• 值唯一：每个记录必须具有唯一的主键值。
• 非空：主键值不能为空值。

主键可以由一个或多个列组成，这些类可以是任何数据类型，包括 BLOB、TEXT 等大型数据类型。一般情况下，主键由一个自增长的整数列组成，这样可以确保每一行数据都有一个唯一的标识，且可以方便的进行排序和查询。

示例：

创建表时直接在字段上指定主键： 如下所示，在 primary key 属性下，是不能允许为空的。

主键约束，主键对应的字段不能重复，必须是表中唯一的。一旦数据重复，则插入数据失败：

创建的表中没有定义主键，可以创建完表之后进行追加主键：

ALTER TABLE table_name PRIMARY KEY(字段列表);

删除表中的主键约束：

ALTER TABLE table_name DROP PRIMARY KEY;

当表中没有 auto_increment 属性约束的时候，可以删除表中的主键。但是，若 primary key 和 auto_increment 属性约束一个列，则该列的主键删除时会报错，如上所示。

复合主键，在创建表的时候，在所有字段之后，使用 primary key 来创建主键，若有多个字段作为主键，可以使用复合主键。

向表中插入数据，若两个主键同时冲突，则插入数据失败：

注意：更改主键可能会影响表中的所有和外键等依赖关系，因此需要谨慎操作。

自增长

在 MySQL 中，auto_increment 是一个用于定义自增长字段的关键字。当不给带有 auto_increment 字段赋值时，会自动被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

auto_increment 的特点：

• 任何一个字段要作为自增长，前提本身是一个索引（key 一栏有值）。
• 自增长字段必须为整数。
• 一张表最多只能有一个自增长值。

示例：id 列被指定为自增长字段。

向表中插入数据：

说明：auto_increment 属性可以提高数据的插入和查询效率，并确保每一行数据都有一个唯一的标识符。因此，在设计 MySQL 数据库时，通常会考虑为每个表添加一个 auto_increment 列作为主键。需要注意的是，自增字段必须是表的主键或唯一键，否则 MySQL 将无法保证自增长字段的唯一性。

唯一键

在 MySQL 中，唯一键（unique key）是一种用于保证表格中某个字段的唯一性的约束条件。与主键（primary key）类似，唯一键也是一种索引，不同之处在于唯一键允许有空值，而主键不允许有空值。

一张表中往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，唯一键就可以解决表中有多个字段需要唯一约束的问题，例如：一个邮件地址，一个手机号码等。如果插入或更新数据时违反了唯一键约束条件，MySQL 将会返回错误信息，阻止对数据的操作。

示例，创建表结构时指定特定列使用唯一键约束：

向表中插入数据：

需要注意的是，唯一键可能会对数据的插入和更新效率产生一定的影响。在高并发情况下，唯一键可能会导致性能问题，因为每次插入数据或更新数据时都需要检查数据的唯一性。因此，在设计表结构时需要慎重考虑是否需要唯一键，并且需要进行充分的测试和性能优化。

外键

在 MySQL 中，外键（foreign key）是一种用于建立表格之间关联关系的约束条件。外键是指一个表格中的一列或多列，它们的值必须对应另外一个表格中的一列或多列的值。通常情况下，外键用于建立主表与从表之间的关系。

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须主键约束或唯一索引约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为 null 。

语法：

foreign key(字段名) references 主表(列);

示例：创建一个班级表作为主表，表当中包含：classid（班级号），classname（对应的班级名称）。如下所示：

然后创建一个学生表作为从表，表中包含：id（学号），classid（班级号），name（姓名）。然后将 classid 设置为外键，关联到 class 表，如下所示：

向两个表中插入数据：

插入一个班级号为 223 的学生，由于没有这个班级，所以插入失败：

插入班级id为null，比如来了一个学生，目前还没有分配班级：

需要注意的是，外键约束条件会自动创建索引，因此在查询数据时，外键可以提高查询效率。但是，外键也会对插入、更新和删除数据操作产生一定的开销。在使用外键时需要权衡查询效率和数据操作开销，并进行充分的测试和性能优化。

综合案例

有一个商店的数据，记录客户及购物情况，有以下三个表组成：

• 商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)
• 客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)
• 购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)

要求：

• 每个表的主外键
• 客户的姓名不能为空值
• 邮箱不能重复
• 客户的性别(男，女)

SQL语句如下：

创建和使用数据库：

create database if not exists test_db;
use test_db;

创建商品的数据库表：

mysql> create table if not exists goods (
    -> goods_id int primary key auto_increment comment '商品编号',
    -> goods_name varchar(30) not null comment '商品名称',
    -> unitprice int not null default 0 comment '单价(分)',
    -> category varchar(15) comment '商品类别',
    -> provider varchar(64) not null comment '供应商名称'
    -> );

创建客户的数据库表：

mysql> create table if not exists customer (
    -> customer_id int primary key auto_increment comment '客户号',
    -> name varchar(20) not null comment '姓名',
    -> address varchar(256) comment '地址',
    -> email varchar(32) unique comment '邮箱',
    -> sex enum('男','女') not null comment '性别',
    -> card_id char(18) unique comment '身份证'
    -> );

创建购买的数据库表：

mysql> create table if not exists purchase (
    -> order_id int primary key auto_increment comment '购买订单号',
    -> customer_id int comment '客户号',
    -> goods_id int comment '商品号',
    -> nums int default 0 comment '购买数量',
    -> foreign key(customer_id) references customer(customer_id),
    -> foreign key(goods_id) references goods(goods_id)
    -> );