【MySQL】数据类型与表约束

目录

数据类型分类

数值类型

tinyint类型

bit类型

小数类型

字符串类型

日期和时间类型

enum和set

表的约束

空属性

默认值

列描述

zerofill

主键

自增长

唯一键

外键

---

数据类型分类

数值类型

tinyint类型

• MySQL中，整形可以是有符号和无符号的，默认有无符号的。
• 可以通过unsigned来说明某个字段是无符号的。
• 如果我们向MySQL特定的的类型中插入不合法数据，MySQL是直接拦截，不让我们做对应的操作（这一点和语言不同）。反过来，如果已经有数据被成功插入到MySQL中了，一定插入的时候是合法的。所以，MySQL中，数据类型本身也是一种约束。

bit类型

bit[(M)]，M表示每个值的位数，范围从1到64。如果忽略M，那么默认是1。

• bit字段在显示时，是按照ASCII码对应的值显示；

小数类型

float

float[(m,d)][unsigned]：m显示指定长度，d指定小数位数，占用4字节。

例如，float(4,2)表示的范围是-99.99~99.99，MySQL在保存值会进行四舍五入(99.991->99.99)。如果定义的是float(4,2)unsigned，范围是0-99.99。

decimal

decimal(m,d)[unsigned]：m显示指定长度，d指定小数位数。

• decimal(5,2)表示的范围是-999.99-999.99
• decimal(5,2)unsigned表示的范围是0-999.99
• decimal和float很像，只是精度不一样，decimal更精确。

字符串类型

char

char(L)：固定长度字符串，L是可以存储的长度，单位是字符，最长可以是255。

这里的char和语言中的char不一样，char(2)可以存放两个字符，可以是字母或汉字，但是不能超过2个，最多是255个。

varchar

varchar(L)：可变长度字符串，L表示字符长度，最长为65535个字节。

L是这个字符串长度的上限，其真实长度可能小于等于这个长度。

• varchar字节数可以指定为0-65535之间的值，但有1-3个字节用于记录数据大小，所以有效字节数是65532；
• 当表的编码是utf8时，varchar(n)的最大值是65532/3=21844（utf8中一个字符占用3个字节）。

char和varchar的比较

如何选择定长或变长字符串？

• 如果数据确定长度都一样且不变，那就使用char，比如，身份证，手机号。如果数据长度有变化，那就使用varchar，比如，名字，地址，但是要保证最长的能存进去。
• 定长的磁盘空间比较浪费，但是效率高；变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间；变长的意义是在不超过自定义范围的情况下，用多少开辟多少。

日期和时间类型

常用的日期有如下3个：

• date：日期‘yyyy-mm-dd’，占用三个字节；
• datetime时间日期格式‘yyyy-mm-dd HH:ii:ss’，占用8字节；
• timestamp：时间戳，和datetime格式完全一致，占用4字节。

当对表进行更新时，timestamp数据（如果有）自动更新。

enum和set

enum：枚举，单选类型，enum('选项1','选项2',...)。这些选项值依次对应1 2 3 4 5 6...。

set：集合，多选类型，set('选项1','选项2',...)。在插入时，如果传入多个选项，可以使用逗号分隔符传进去，也可以用数组下标（位图方式表示，这些选项的每个选项值依次对应如下数字，1 2 4 8 16 32...）。

创建表：

插入数据：

想查询所有喜欢登山的人：

这样查询不能查出所有结果。

集合查询函数find_in_set函数：

find_in_set(sub,str_list)：如果sub在str_list中，那么返回下标（非0）；如果不在，返回0。  

查询爱好登山的人：

查询爱好登山和篮球的人：

表的约束

表的约束，表中一定要有各种约束，通过约束，让我们未来插入数据库表中的数据是符合预期的，约束本质是通过技术手段，倒逼程序员，插入正确的数据。反过来，站在MySQL的角度，凡是插入进来的数据，都是符合数据约束的！

约束的最终目标：保证数据的完整性和可预期性。      

空属性

• 两个值：null（默认）和not null；

例子：创建一个表，包含班级名称和所在教室，这两个字段不能为空。

当插入数据时，如果没有给not null的字段传入值，那么会报错。

默认值

default：如果设置了，用户将来插入，有具体的数据，就用用户的，没有就用默认的。

如下图，如果我们没有明确指定一列要插入，用的是default，如果建表中，对应列没有默认的default值，则无法直接插入。 

default和not null不冲突，而是相互补充的，not null是当用户想插入的时候，约束的是，要么是NULL，要么是合法数据。而default是当用户忽略这一列的时候，使用默认值（如果设置了），如果没有设置，直接报错！

列描述

comment，没有实际含义，会根据表创建语句保存，用来给程序员看的进行了解。

通过show语句可以查看comment：

zerofill

 

我们看到int(10)，这个10代表什么？实际上，如果没有zerofill这个属性的话，括号内的数字毫无意义。

如果没有设置zerofill，那么显示结果是：

如果设置了zerofill属性，那么显示结果是：

这就是说，如果宽度小于设定的宽度，自动填充0。这只是最后显示的结果，实际存储的还是200，我们可以通过hex函数证明：

主键

主键：primary key用来唯一约束该字段里的数据，不能重复，不能为空（自动设置为not null），一张表中最多有1个主键，主键所在的列通常是整数类型。

案例：

• 创建表的时候直接在字段上指定主键；

• 主键中的字段不能重复，一旦重复就会失败；

•  删除主键

• 当表创建好但是还没主键的时候，可以再次追加主键；

• 复合主键

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

自增长

auto_increment：对应的字段，不给值，会自动被系统更新，系统从当前字段中已有的最大值+1，得到一个新的不同的值，通常和主键搭配使用，作为逻辑主键。

自增长有如下特点：

• 任何一个字段要做自增长，前提本身是一个索引。
• 自增长字段必须是整数。
• 一张表最多只能有一个自增长。

如果插入主键值，那下次从这个值开始自增长。

通过last_insert_id()查询上次插入的键值。

唯一键

一张表中往往有多个字段需要唯一性，数据不能重复，但是一张表中只能有一个主键；唯一键就可以解决表中有多个字段需要唯一性约束的问题。唯一键的本质和主键差不多，但唯一键允许为空，且可以多个为空，空字段不做唯一性比较。

唯一键和主键的区别：主键更多的是标识唯一性的，而唯一键更多是保证在业务上，不要和别的信息出现重复。比如在员工管理系统，可以将员工身份证号作为主键，同时为了保证员工号不出现输入重复错误，可以将员工号作为唯一键。

一般把和业务无关的字段设为主键，这样当业务调整的时候，就不会对主键做过大的调整。

外键

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

语法：

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

创建主表：

创建从表：