5.MySQL表的约束上文章目录5.MySQL表的约束上一、为什么需要约束约束是什么把写数据从自由变成有规则约束总览二、空属性约束null 与 not null三、默认值约束defaultdefault 与 not null不冲突各管一段四、列描述comment五、zerofill数字显示宽度的补零六、主键primary key单列主键的用法表建好之后追加或删除主键复合主键总结上一篇笔记确立了一个贯穿全库的认识列的类型本身就是约束越界的数据进不来。但类型约束只管值符不符合类型格式管不了这个值合不合业务规则——学号列可以存下 QQ 号email 列可以出现两份相同的邮箱数据类型对此一概不拦。本篇正式展开约束体系先讲清楚约束为什么存在再逐个讲解空属性null/not null、默认值default、列描述comment、zerofill、主键含复合主键五种表级约束。自增长auto_increment、唯一键unique key与外键foreign key属于下一篇本篇结尾只作预告。一、为什么需要约束上篇验证过类型即约束tinyint 列插 128 报 ERROR 1264char(2) 存 3 个字符报 Data too long——数据类型拦截了格式非法与范围越界的数据。但数据类型给出的约束很单一它只能保证存得下、格式对保证不了数据的业务合法性。学号是 varchar 列QQ 号、邮箱字符串一样存得进去性别是 varchar 列身高、体重、籍贯的文本同样来者不拒。把数据写成什么、是不是业务上允许的值类型约束看不到需要额外的表级约束来把关。约束是什么把写数据从自由变成有规则类比写代码编译器会在语法层拦住写错的代码语法不过就不允许编译通过倒逼程序员写出语法正确的程序。数据库的表约束扮演同样的角色——约束的本质是 MySQL 通过技术手段限定某一列允许出现的数据让不合规则的数据根本插不进去约束的对象是想插入数据的人。用户插数据时要么插合法的数据要么插非法数据约束保证非法数据一律被拦截只有合法数据能进表。约束的效果是可预期性。声明了 tinyint unsigned 的列未来插进库里的数据一定落在 0 到 255 之间声明了主键的列未来一定不会出现重复值。表结构设计者可以在任何数据插入之前先定好规则之后入库的每一行都必然符合这些规则。库里的数据因此完整、可信。约束总览MySQL 常见表级约束共八种本篇讲解前五种本篇MySQL 表的约束上空属性约束 null / not null默认值 default列描述 commentzerofill 显示宽度补零主键 primary key含复合主键下篇表的约束下预告自增长 auto_increment唯一键 unique key外键 foreign key二、空属性约束null 与 not null先厘清 NULL 在 MySQL 中的含义。在 C/C 里 null 表示零或空指针在 MySQL 里 NULL 表示没有、不存在它与空字符串是两回事——空串 ‘’ 是一个长度为 0 的字符串属于真实存在的数据而 NULL 是什么都没有。MySQL 里字符串用单引号或双引号都可以习惯上写单引号就是合法的空串数据。NULL 一般不参与运算任何数据与 NULL 运算的结果还是 NULLmysql select null; ------ | NULL | ------ | NULL | ------ mysql select 1null; -------- | 1null | -------- | NULL | --------数据库默认允许字段为空Nullable但实际开发时尽可能让字段 not null因为数据为空就无法参与运算业务上难以处理。空属性约束就是给某一列声明允许为空null默认“或不允许为空not null”不写约束默认允许为空插入时可以省略该列或显式插 NULL。声明 not null 后该列必须给出实际数据省略或插 NULL 都会被拦。典型的业务场景班级表里班级名称与教室都不该为空——班级没名字就不知道自己在哪个班教室为空就不知道去哪上课这两列必须建表时就锁死myclass 表mysql create table myclass( - class_name varchar(20) not null, - class_room varchar(10) not null); Query OK, 0 rows affected (0.02 sec) mysql desc myclass; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | class_name | varchar(20) | NO | | NULL | | | class_room | varchar(10) | NO | | NULL | | ----------------------------------------------------desc 输出中Null 一栏就是空属性的直接体现NO 表示该列不允许为空。插入时只给班级名、省略教室这一列MySQL 直接拒绝mysql insert into myclass(class_name) values(class1); ERROR 1364 (HY000): Field class_room doesnt have a default value注意这条报错的口径是没有默认值而不是不能为空——省略列与显式插 NULL 是两种不同的违规报错也不同下一节专门对比。若显式插入 NULL拦截报错则是 cannot be null 一类ERROR 1048。只要声明了 not null任何人向这两列插入空值的企图都会被 MySQL 拦住想成功插入就必须把班级名称和教室都填上。三、默认值约束defaultdefault 约束给某一列指定一个默认值插入时用户给了值就用用户的值省略该列就用默认值填充。它解决的是某个值经常固定出现、不该每次重复输入的问题——比如注册表单里性别默认设为男用户填了就按用户填的存没填就存默认值。案例age 默认 0sex 默认 ‘男’只有 name 是必填的mysql create table tt10 ( - name varchar(20) not null, - age tinyint unsigned default 0, - sex char(2) default 男 - ); Query OK, 0 rows affected (0.00 sec) mysql insert into tt10(name) values(zhangsan); -- 只给 name省略 age 与 sex Query OK, 1 row affected (0.00 sec) mysql select * from tt10; ---------------------- | name | age | sex | ---------------------- | zhangsan | 0 | 男 | ---------------------- mysql show create table tt10\G; *************************** 1. row *************************** Table: tt10 Create Table: CREATE TABLE tt10 ( name varchar(20) NOT NULL, age tinyint unsigned DEFAULT 0, sex char(2) DEFAULT 男 ) ENGINEInnoDB DEFAULT CHARSETutf8mb3 1 row in set (0.00 sec)省略某列时MySQL 看的是这一列有没有可用的默认值设置了 default 的列省略时自动用默认值补上没设 default 但可空的列MySQL 会自动补上 DEFAULT NULL省略时存 NULL、不报错。反过来没有 default 又声明了 not null 的列上节的 class_room省略即报 ERROR 1364。default 与 not null不冲突各管一段用户对某一列的插入只有两种形态显式给出值或省略该列。两种约束分别看守这两种形态不冲突、互相补充not null 管显式给值用户给的值不能是 NULL必须是合法数据哪怕是空串 ‘’ 也算合法数据因为空串是真实存在的值。default 管省略该列准确说管的是省略时有没有可用的默认值设置了 default省略时用默认值填充没设 default 的可空列被 MySQL 自动补上 DEFAULT NULL省略时存 NULL、不报错只有 not null 且无 default 的列省略才报 ERROR 1364Field doesn’t have a default value。两种约束可以同时设置显式插入时 NULL 照样被 not null 拦下省略该列时 default 值不为空插入照常成功。建表时只写 not null 不写 defaultMySQL 不会替该列补 default——会被自动补 DEFAULT NULL 的只有可空列。注以上报错行为以 MySQL 默认的严格模式sql_mode 含 STRICT_TRANS_TABLES5.7/8.0 默认开启为前提若关闭严格模式not null 且无 default 的列被省略或显式插 NULL 都不报错而是回退为该类型的隐式默认值数值列补 0、字符串列补空串 ‘’并产生一条 warning。回到上一节的报错就豁然开朗myclass 的 class_room 是 not null 且无 default用户省略该列落到了 default 的管辖范围——没有默认值可用报错doesn’t have a default value若用户显式插 NULL则落到 not null 的管辖范围报错cannot be null。两种报错对应两种不同的违规看报错文案即可区分是哪个约束在拦截。四、列描述commentcomment 是加在列定义后面的注释文字用来描述这一列的语义专供 DBA(Database Administrator数据库管理员) 与维护表的程序员阅读。它不参与任何数据校验——数据不符合 comment 描述的语义也不会被拦截因此被称为软约束。作用相当于代码注释性别列写上 comment 后后来人一看便知这里只能放男或女。tt12 案例建表时给每列挂上注释mysql create table tt12 ( - name varchar(20) not null comment 姓名, - age tinyint unsigned default 0 comment 年龄, - sex char(2) default 男 comment 性别 - ); Query OK, 0 rows affected (0.00 sec)comment 不会出现在 desc 输出里desc 只看得到列名、类型、是否为空这些结构信息mysql desc tt12; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | 0 | | | sex | char(2) | YES | | 男 | | ----------------------------------------------------注释信息保存在建表语句里要用 show create table 才能看到mysql show create table tt12\G *************************** 1. row *************************** Table: tt12 Create Table: CREATE TABLE tt12 ( name varchar(20) NOT NULL COMMENT 姓名, age tinyint(3) unsigned DEFAULT 0 COMMENT 年龄, sex char(2) DEFAULT 男 COMMENT 性别 ) -- 其余建表参数从略建表时给每个字段写一句 comment 是好习惯表结构越复杂、字段含义越不直观注释的价值越大desc 看不见它但表结构导出、迁移、交接时它都跟着走。五、zerofill数字显示宽度的补零上篇遗留过一个疑问desc 与 show create table 里整型列显示成 int(11)、int(10) 一类带圆括号的样式这个数字是什么先看事实——建表时不写这个数字MySQL 会自动补一个tt3 表a、b 都是无符号 intmysql create table tt3 (a int unsigned, b int unsigned); Query OK, 0 rows affected (0.00 sec) mysql show create table tt3\G *************************** 1. row *************************** Create Table: CREATE TABLE tt3 ( a int(10) unsigned DEFAULT NULL, b int(10) unsigned DEFAULT NULL ) -- 其余建表参数从略 mysql insert into tt3 values(1, 2); mysql select * from tt3; ------------ | a | b | ------------ | 1 | 2 | ------------圆括号里的数字是显示宽度与取值范围无关——int 就是 4 字节、范围由有无符号决定括号里的 10 不改变存储。没有 zerofill 属性时这个宽度数字毫无意义显示的只是数据本身。默认宽度是这样定的int 能表示的最大值十进制是 10 位无符号 4294967295有符号还要为负号预留 1 位所以无符号默认补 int(10)有符号默认补 int(11)。给列加上 zerofill 属性后显示宽度才开始起作用把 a 改为宽度 5 的无符号 zerofill 列mysql alter table tt3 change a a int(5) unsigned zerofill; Query OK, 0 rows affected (0.00 sec) mysql show create table tt3\G Create Table: CREATE TABLE tt3 ( a int(5) unsigned zerofill DEFAULT NULL, b int(10) unsigned DEFAULT NULL ) mysql select * from tt3; ------------- | a | b | ------------- | 00001 | 2 | -------------zerofill 的含义显示时若数值位数不足声明宽度前面自动补 0 到满宽位数超过声明宽度则按实际数值原样显示。原来的 1 变成了 00001。这一约束的典型场景是编号列全校 100 个班要显示成三位编号就声明 int(3) zerofill让 001、002 一路排到 100。必须强调补零只发生在显示层存储与计算的值不变。证明方法是用 hex 函数把列值按十六进制打出来mysql select a, hex(a) from tt3; --------------- | a | hex(a) | --------------- | 00001 | 1 | ---------------内部存的还是 100001 只是格式化输出同样用where b200这类数值条件筛选 zerofill 列比较的仍是数值本身。zerofill 的显示是等宽的代价仅是展示层格式化不影响任何运算与查询。六、主键primary key主键是表中用来唯一标识一行记录的列它的值不能重复、不能为空。生活中对应的概念是学号——每个学生一个学号靠学号能定位到这个学生的全部信息学号不会与任何人冲突。主键所在列通常是整数类型方便后续配合自增长使用下篇内容。数据库场景里主键的意义与 C 关联容器中 key 的意义一致key 唯一按 key 可以快速定位到对应的记录便于对某一行做精准的增删查改具体可对照 C 关联式容器 map、set 详解 中 map 的按键查找理解。单列主键的用法建表时直接在列定义后面跟 primary key 关键字tt13 表id 即主键mysql create table tt13 ( - id int unsigned primary key comment 学号不能为空, - name varchar(20) not null); Query OK, 0 rows affected (0.00 sec) mysql desc tt13; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | ----------------------------------------------------desc 输出里Key 一栏的 PRI 标明主键列。注意 id 明明只写了 primary key、没写 not nullNull 一栏却是 NO——主键约束隐含不能为空MySQL 会自动为主键列补上 not null。插入数据验证主键的唯一性mysql insert into tt13 values(1, aaa); Query OK, 1 row affected (0.00 sec) mysql insert into tt13 values(1, aaa); -- 主键值 1 已存在重复插入 ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY主键冲突时 MySQL 直接拦截报 ERROR 1062 Duplicate entry。重复的主键数据永远进不了表库中每一行的主键值必然互不相同。表建好之后追加或删除主键主键不一定要在建表时指定之后仍可调整追加主键alter table 表名 add primary key(字段列表)括号里写明要把哪一列设为主键。前提是这一列现有数据没有重复——若表里已有重复值追加时报同样的 Duplicate entry 错误必须先清理重复数据再设主键。删除主键alter table 表名 drop primary key。因为一张表只能有一个主键删除时不用告诉 MySQL 删哪一列写 drop primary key 即可。主键最好在建表时就定好。表使用一段时间、塞满了数据之后才想起来加主键若目标列存在重复记录就面临删哪一行都不合适的取舍删除任何用户数据都是代价。复合主键注意措辞区分一张表只能有一个主键不代表主键只能由一列构成多列合起来充当一个主键时称为复合主键。选课场景是复合主键的典型一张选课记录表保存哪个学生选了哪门课、考了多少分同一个学生可以选多门课、同一门课可以被多个学生选但(学生, 课程)这个组合不能重复出现——同一个学生把同一门课选两次就是重复记录。这种单列允许重复、组合必须唯一的约束单列主键做不到复合主键正合适tt14 表id 与 course 合起来当主键mysql create table tt14( - id int unsigned, - course char(10) comment 课程代码, - score tinyint unsigned default 60 comment 成绩, - primary key(id, course) -- id 和 course 构成复合主键 - ); Query OK, 0 rows affected (0.00 sec) mysql desc tt14; -------------------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | | | course | char(10) | NO | PRI | NULL | | | score | tinyint(3) unsigned | YES | | 60 | | --------------------------------------------------------desc 输出里 id 与 course 的 Key 栏都是 PRI两个 PRI 并不代表两个主键而是两者都是同一个主键的组成部分。验证约束行为mysql insert into tt14 (id, course) values(1, 123); Query OK, 1 row affected (0.02 sec) mysql insert into tt14 (id, course) values(1, 123); -- 组合 (1, 123) 已存在 ERROR 1062 (23000): Duplicate entry 1-123 for key PRIMARY -- 报错把组合值连成整体显示复合主键把多列值视为一个整体来比较唯一性只要组合与历史记录不完全相同就能插入组合整体与历史记录完全相同时才触发主键冲突。总结本篇回答了三个层次的问题。为什么需要约束类型约束只保证格式合法业务合法性要靠表级约束把关数据库是数据入库前的最后一道防线约束越严格库中数据越完整、可预期。单个列的插入规则not null 看守显式插入的值NULL 进不来报 cannot be nulldefault 看守省略的列有默认值就填充无默认值时可空列省略存 NULLnot null 列省略才报 doesn’t have a default value两者各管一段、互不冲突comment 不拦数据只是写给 DBA 和维护者看的列说明zerofill 只改显示不改存储声明宽度不足补零、超过原样默认宽度无符号为 10、有符号为 11预留负号位。行级别的约束主键唯一标识一行隐含非空冲突报 ERROR 1062一张表至多一个主键但可以由多列合成复合主键把组合值当整体比较唯一性适合单列可重复、组合必须唯一的业务。下一篇继续约束体系的后半程自增长 auto_increment主键的子话题让主键值自动递增、唯一键 unique key允许为空的多列唯一约束与外键 foreign key表与表之间的引用关系。