MySQL之表的约束(上)

空属性(NULL)

实例建表

插入操作

默认值(default)

建表

插入操作

NULL与default的结合

列描述

建表

zerofill

建表

插入操作

主键

建表

插入

主键的增加与去掉

去掉

增加

复合主键

插入的影响

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

表中一定要有各种约束，通过约束，让我们未来插入数据库中的数据是符合预期的。约束本质是通过技术手段倒逼程序员插入正确的数据(不正确不会让你插入)。返回来，站在mysql视角，凡是插入的数据都是符合数据约束的。

约束的最终目标：(预先把规则设计好，让别人按照规则插入)保证数据的完整性和可预期性。

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

空属性(NULL)

在mysql中NULL代表没有(什么都没有)而‘ ’或“ ”代表空串，这个串有但是是空的(mysql中单双引号都可以表示字符串)类似于我没有银行卡与我有卡但是卡里没钱。NULL不参与计算。

两个值：null（默认的）和not null(不为空)

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

实例建表

创建一个班级表，包含班级名和班级所在的教室。

站在正常的业务逻辑中：

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

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

建表顺序：

1. 建立数据库create database school;

2. 使用当前数据库use school;

3. 在当前数据库中建表create table if not exists myclass(
-> class_name varchar(20) not null,
-> class_room varchar(20) not null,
-> other varchar(20)
-> );

查表desc myclass;

如果建表时不输入not null那么就默认是null。

NULL这一列就代表了对应这一列未来插入是否为空，no代表如果你插入空值或者没插入那么我就不让你插入，这意味着当插入新行或更新现有行时，该列必须始终包含一个值，而不能为 NULL。

实际上在mysql保存的创表操作是这个：

插入操作

insert into myclass (class_name,class_room,other) values ('高三1班','101教室','普通班');

insert into myclass (class_name,class_room) values ('高三2班','102教室');

没有向other插入数据，那么就默认NULL，没有加非空约束；

insert into myclass (class_name) values ('高三2班');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value报错信息
insert into myclass (class_name,class_room) values ('高三2班',NULL);
ERROR 1048 (23000): Column 'class_room' cannot be null不能为空
那么就能拦住任何向name和room中插入空值，则必须插入数据值；

not null就是限定你插入时必须插入合法数据不能是NULL。没有not null约束即可以插入NULL

默认值(default)

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

建表

mysql> create table if not exists t1(
-> name varchar(20) not null,
-> age tinyint unsigned default 18,
-> gender char(1) default '男'
-> );

可以看到默认情况下age为18，gender为男

插入操作

insert into t1 (name,age,gender) values ('张三','19','女');//全列插入

insert into t1 (name) values ('李四');//使用默认值

对于default来说，如果用户插入时有自己的具体数据那么就用自己的，若没有就用默认值。

NULL与default的结合

mysql> create table if not exists t2(
-> name varchar(20) not null,
-> age tinyint default 18,
-> gender char(1) not null default '男');//语法允许

那么在看具体mysql怎么解释

name只设定了not null，mysql没有自己在后面加default那么说明name列不能为空

对于insert into t2 (name,age,gender) values (NULL,20,'男');ERROR 1048 (23000): Column 'name' cannot be null来说，是非空约束了。

对于insert into t2 (age,gender) values (20,'男');ERROR 1364 (HY000): Field 'name' doesn't have a default value报错信息不一样，我在插入时忽略了name列没有插入则是default约束因为我没有设置默认default值
换句话来说如果我插入NULL，你不让我插入是NULL约束，但是我不对你插入忽略你，也没有设置default值那么就是default约束

那么对于gender来说：
对他忽略没出错因为有默认值。如果用户忽略某个列就用default值

default和not null不冲突而是相互补充的。当用户想插入的时候受not null约束(必须是合法数据不能是NULL)，当忽略这一列时受到default约束 (设置defaule值使用默认值，没有设置default值就错误)

如果自己在建表时自己没有设置default值和not null，那么MySQL会对自己的sql做优化。

show create table xxx\G可以看出优化，优化后默认带default，没有带not null。

如果用户自己设置了not null但没有设置default值，那么mysql也不会优化带default。

列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA (数据库管理员)来进行了解。类似于注释；

建表

mysql> create table if not exists t3(
-> name varchar(20) not null comment '这个是用户名',
-> age tinyint unsigned default 18 comment '这个是用户的年纪',
-> gender char(1) default '男' comment '这个是用户的性别'
-> );

desc表发现跟以前的没区别：通过desc查看不到注释信息

insert into t3 values ('张三',19,'女');//发现插入也没区别

那么我们想看到的话就show create table t3\G 可以看到列信息的描述字段

zerofill

建表

mysql> create table if not exists t4(
-> a int unsigned not null,
-> b int unsigned not null
-> );

但是我们查表时发现自己带了(10)，自己建表时没有设置数据大小。

这是因为实际执行时sql会自己补充优化带(10)

可以看到int(10),这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？其实没有zerofill这个属性，括号内的数字是毫无意义的

插入操作

insert into t4 values (1,2);//全列插入

更改表属性操作： alter table t4 modify b int unsigned zerofill not null;//对b列添加zerofill属性

发现数据变了，b多了很多前缀0；

继续插入：insert into t4 values (100,200);

那么zerofill就是给特定的列添加zerofill属性，他的结果就是我们在进行显示的时候如果宽度小于设定的宽度(就是int(x)圆括号里的数字)时自动填充0，如果够宽度了那么就不用管该是什么长度就是什么长度不用加0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。这样未来在显示时是等宽的。

可以看到最终在mysql中存储的是还是2和200(以16进制显示hex())；

alter table t4 modify a int not null;

alter table t4 modify b int unsigned not null;

可以看到a比b多一个是因为有符号位，又为什么默认是10呢？因为一个整数四个字节32bit位，有符号取值范围为 (-(2^{31})) 到 (2^{31} - 1)，无符号整数，所有32位都用来表示数值本身，范围是 (0) 到 (2^{32} - 1)；无论是2^32(42亿多)还是2^31(21亿多)最终转化为十进制都是十位，十位足够把整数保存下来。

所以zerofill并不影响存储，他只是影响我们后续的格式化显示(zerofill约束)；

主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键(要么没有要么就只有一个)；主键所在的列通常是整数类型。

建表

mysql> create table t5 (
-> id int unsigned primary key comment '学号',
-> name varchar(20) not null);

可以发现有属性列key，key列代表当前列是否为主键列那么有pri说明就是主键。

同时带主键了那么就自动默认添加not null约束了(如果自己没写not null约束)。

插入

insert into t5 values (1,'张飞');

insert into t5 values (1,'刘备');//再次插入就出错了

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'//主键冲突，所以相同的key不让插入

所以只能插入不同的主键值

那么主键约束带来的就是未来程序员插入时不能主键冲突，键对应的字段中不能重复，一旦重复，操作失败。所以我们就能根据主键拿出绝对唯一的值。
同时也能根据主键修改值：update t5 set name='王老板' where id=2;

主键的增加与去掉

主键可以在建表时添加，也可以在建表后添加。

去掉

当前id是主键，那么怎么去掉呢?

更改一张表alter table t5 drop primary key;因为主键在一张表里只有一个，所以不用说在哪一列。
那么这时就可以冲突插入了，insert into t5 values (2,'刘备');//没有主键约束了

这时候表的结构也发生变化了，没有主键显示了。

增加

alter table t5 add primary key(id);//向id列增加主键
但是报错了ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'//主键冲突
为什么呢？
因为我们之前在相同的id插入不同的name使冲突。所以要想把id列增加主键就要id列要么没有数据要么就一个不冲突的数据。

现在只能删除多的数据：delete from t5 where name='刘备';
增加主键：alter table t5 add primary key(id);

最终可以看到主键被添加了。

复合主键

虽然表中最多只有一个主键，但不意味着一个表中的主键只能添加给一列(一个主键可以添加到一列或者多列上)；

我们把多列上的主键叫做复合主键。

mysql> create table t6(
-> id int unsigned default 0,
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> primary key(id,course)
-> );

id和course合并充当主键(他俩合起来才称为一个主键)。

插入的影响

insert into t6 values(1234,'语文',90);

insert into t6 values(1235,'语文',85);

那为什么会是这样呢？id和course都是primary key，为什么course一样了mysql并没有拦截？
insert into t6 values(1234,'数学',90);同样也允许这样。

而再次插入就不允许了。我们可以看到mysql把1234-数学作为一个整体了。

所以我们可以用多个主键，但是当作为主键的同时跟历史数据冲突了才不让插入，报错。

分开彼此之间可以重复，但是合并就不能重复。