[MySQL] — 数据类型和表的约束

目录

数据类型

数据类型分类

数值类型

  tinyint类型

  bit类型

  小数类型

 float

 decimal

  字符串类型

 char

 varchar

 char和varchar的区别

日期和时间类型

enum 和 set

表的约束

空属性

默认值 

列描述

zeorfill

主键

创建表时在字段上指定主键

删除主键：

追加主键属性

复合主键

自增长

唯一键

外键

---

 

数据类型

 数据类型分类

数值类型

  tinyint类型

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。字段类型加了UNSIGNED来说明该字段是无符号的。

以tinyint举例，可以看到tinyint类型是有取值范围的，如果插入的数超过了取值范围会出现什么情况呢？

 其他的整数类型也基本如此。

注意：加不加unsigned也要分场景，如有些数(年龄)不能为负数的场景就可以加，但是加不加都行的场景下尽量不加unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。但是这里并不是说无脑选最大，而是根据场景去选择类型，如果无脑选最大，每条数据都浪费一点空间，在数据量超大的情况下，存储空间就会不够用。

正常在C/C++语言中往char类型中插入一个很大的数，往往会发生数据截断或类型提升。而在MySQL这里插入超范围的值会插入失败且直接报错。反过来讲，也就是说MySQL这里只要插入成功，那么数据一定是合法的。所以在MySQL中数据类型本身一是一种约束，这个约束主要是约束使用者，倒逼着使用者进行正确的插入，如果使用者是小白，也能很好的保证数据的合法性。

为什么这里不像C/C++那样呢？因为如果发生数据截断，那么数据库中可能会因为截断而导致两个数据相同，那么此时如何辨别这两条数据呢？辨别不了，所以为了保证数据插入时的合法性，所设置的一个约束。

  bit类型

bit[(M)] : 位字段类型，M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

使用：

 当然创建表时bit的位数也不能超过最大值，超过会创建失败。

   小数类型

      float

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

 如果不指定显示长度和小数位数，默认的精度会很大。

float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

使用： 

如果加上unsigned， 这时，因为把它指定为无符号的数，它的范围是 0 ~ 99.99 。

 decimal

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数。

decimal和float很像，表示范围基本相同，但是他两有区别：float和decimal表示的精度不一样。如果需要较高的精度，推荐使用decimal。

使用： 

float表示的精度大约是7位，decimal整数最大位数m为65，支持小数最大位数d是30。如果d被省略，默认为0，如果m被省略，默认是10。这个默认值可能会随着MySQL的版本不同而不同。
 

  字符串类型

   char

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

创建表时，char类型的长度超过255，就会创建失败。

char(L)表示可以存放L个字符，可以是字母或汉字，但是不能超过L个， 最多只能是255。

使用： 

    varchar

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

使用：

 这里我们发现最大长度不是65535吗，怎么这里变成21845了？

65535是字节，这里表示的是最大长度的字符。

 

关于varchar(len)，len到底是多大，这个len值，和表的编码密切相关：

这个varchar，就像是C中的变长数组，你用多少，就给你分配多少，但是最长不超过你设定的长度。varchar字节长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。

当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占
用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符
占用2字节）。

  char和varchar的区别

 如何选择char和varchar？

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

日期和时间类型

常用的日期有如下三个：

• date：日期 'yyyy-mm-dd' ，占用三字节；
• datetime： 时间日期 格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节；
• timestamp：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节。

使用： 

enum 和 set

enum：枚举，“单选”类型；enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,....最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

set：集合，“多选”类型；set('选项值1','选项值2','选项值3', ...);

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，.... 最多64个。

使用：

  我们想要从数据中进行查找：所有去过北京的人

 这里并不能查找出所有去过北京的人。

集合查询推荐使用find_ in_ set函数：

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；str_list 用逗号分隔的字符串。

 

表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。本质还是通过技术手段，约束使用者插入正确的数据。

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

空属性

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

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

使用非空约束：

创建一个班级表，表内包含班级的名字、班级所在的教室和班级说明。

正常看来一个班级不能没有名字，班级也不能没有教室。

所以在设计时表明班级名和班级教室不能为空，为空就不能插入，这就是“约束”。

默认值 

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

使用：

default和 NOT NULL 这两个并不冲突，而是互相补充的。

• NOT NULL 约束的是用户插入时，只有插入合法数据也就是不为空的数据。
• default 约束的是用户忽略某一项时，如果有默认值就使用默认值，如果没有默认值直接报错。

那有人会说不对呀，我使用的时候，我也没指定某一项不能为空，也没有指定默认值，但是我插入时忽略该项也没有报错呀。如下图

那是因为在创建表的时候，MySQL默认给我们添加了default 为NULL的信息 。就像上图查看表信息的时候defaul类型就会有一个NULL，而如果你指定了某一项不能为空的信息，MySQL则就不会添加default 为NULL的信息了。

列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。说白了就是注释，属于一种软性约束，给使用者看的，让使用者自发的根据描述去插入数据。

zeorfill

其实没有 zerofill 这个属性， 括号内的数字是毫无意义的，你正常插入数字，他也会正常显示。

但是添加了 zerofill 属性 ，显示结果就不一样了，这里我们修改表内b的属性。

可以看到b的值在前面填充了0，这就是zerofill的属性，如果宽度小于设定的宽度，自动填充0，如果宽度大于设定值则正常显示。

要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是正常的值，只是设置了zerofill属性后的一种格式化输出而已，查找的话也没问题。

这里还有一个细节需要注意：int类型创建时默认是11位，而加了unsigned的int却是10位为什么呢？

我们知道 有符号 int 的值区间是 -21亿多 ~ 21亿多，而无符号 int 是0 ~ 42亿多，这两个的值10位数就可以全部覆盖，而有符号 int 有负号所以多加1位。

 主键

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

使用：

创建表时在字段上指定主键

主键不能重复，重复会插入失败。

删除主键：

 删除掉主键属性后，id就可以重复了。

 追加主键属性

主键属性最好是创建表的时候就指定，或者刚开始使用的时候指定，别等用了很久以后才指定，这个时候，你说你删那条数据呢？

 复合主键

前面说过主键一个表最多只能有一个，这不意味着一个表的主键只能添加给一个字段，一个主键可以给一个字段添加，也可以给多个字段添加，这样的我们称为复合主键。

自增长

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

自增长的特点:

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

使用：

自增默认从1开始。

 自己指定插入也可以，但是自增会从最新的位置开始。

如果添加自增属性的不是主键会报错。

也可以自己指定从哪里开始自增。 

索引：

• 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
• 索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

唯一键

unique key：一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，那么唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多都是保证唯一性，但是唯一键允许为空，而且可以多个为空，因为空字段不做唯一性比较。

关于唯一键和主键的区别：
我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。它们两个是不冲突的，是属于互补的。

 就像上面的例子，主键保证学号的唯一性，因为名字可能会重复，所以保证不为空就行，而电话基本每个人单独有自己的，且电话号码并不会重复，所以电话号码就需要保证唯一性，否则如果粗心让两个人的电话相同，那么在查询时，你能否知道这个电话号码是谁的吗? 并不知道，那么有人会说电话都可以为空了，这没有问题吗？当然你也可以在创建时将电话那列的属性加上NOT NULL属性，此时唯一键的功能就和主键一样了，但是他们两负责的内容是不同的，主键负责记录的唯一性，唯一键负责业务上，不要出现信息重复。

 一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。

外键

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

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

 使用：

 

如何理解外键约束？

        首先我们承认，这个世界是数据很多都是相关性的。理论上，上面的例子，我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。但是，在实际使用的时候，可能会出现什么问题？有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中？

        比如某个学校高三只开了101班，102班，但是在上课的学生里面竟然有高三103班的学生(这个班目前并不存在)，这很明显是有问题的。因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。

        解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。