MySQL数据类型

数据类型的作用：

• 决定了存储数据时应该开辟的空间大小。
• 决定了如何识别一个特定的二进制序列。
• 决定了数据的取值范围。

数据类型分类

说明一下：

MySQL本身是不支持bool类型的，当把一个数据设置成bool类型时，数据库会自动将其转换成tinyint(1)的数据类型，其实这个就是变相的bool类型，因为tinyint(1)只有1和0两种取值，可以分别对应bool类型的true和false。

数值类型

tinyint类型

我们创建一个表，数据类型为tinyint类型：

tinyint类型占用1字节，因此有符号tinyint的取值范围为-128~127，插入该范围内的数据时都能成功插入。

如果插入的数据不在-128 ~ 127范围内，此时插入就会报错；

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的，可以通过UNSIGNED来说明某个字段是无符号的，接下来我们来看一下无符号类型tinyint测试。

tinyint类型只占用一个字节，因此无符号的tinyint类型的数据范围为0 ~ 255，插入该范围内的数据就可以插入成功；

如果插入的数据不在0 ~ 255范围内，此时插入就会报错；

注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

bit类型

基本语法：

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

创建一个表，表当中包含一个int类型的id列和一个8位bit类型的unknown列；

我们此时在表中插入一条记录，记录中指定id和a的值均为10，插入记录后查看表会发现unknown的值显示的并不是10。

原因是bit在显示时，是根据ASCII码值进行显示的，10对应的ASCII码值是控制字符LF，表示换行的意思。如果我们此时向表中插入的值为97的话，我们就会看见unknown列显示的值就是a，因为a对应的ASCII码值为97。

bit范围测试

创建一个表，表当中包含用户名name和用户性别gender，其中gender的类型可以指定为1位bit类型，因为性别只有男和女两种取值，使用1个比特位来表示用户的性别就可以节省空间。

如果规定gender列插入0表示男，插入1表示女，那么在插入用户信息时就可以通过插入0和1来指定用户的性别。

如果插入gender列的数据不是0或1，那么插入数据时就会产生报错。

注意：

• 虽然MySQL提供了位类型bit，但一般不建议将数据类型设置成位类型，除非将来这个数据本身就只是给程序看的，并且数据本身非常占用资源。
• 因为查询位类型数据时，默认会按照ASCII码对应的值进行显示，这对于将来数据库管理员维护数据库或程序员调试程序都是不太方便的。

float类型

基本语法：

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

有符号float范围测试

创建一个表，表当中包含一个float(4,2)类型的列，默认其为有符号类型。

由于float(4,2)的取值范围为-99.99~99.99，因此插入该范围内的数据都能成功插入。

由于MySQL在保存值时会进行四舍五入，因此实际可插入float(4,2)的范围为-99.994~99.994，如果插入的数据不在该范围内，那么插入数据时就会产生报错。

有符号float范围测试

创建一个表，表当中包含一个float(4,2)类型的列，并指定其为无符号类型。

作为无符号float类型，float(4,2)的取值范围为0 ~ 99.99，实际可插入的范围是0 ~ 99.994。

decimal类型

基本语法：

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

• decimal(5,2) 表示的范围是 -999.99 ~ 999.99；
• decimal(5,2) unsigned 表示的范围 0 ~ 999.99。

decimal和float很像，但是有区别:

• float和decimal表示的精度不一样。

创建一个表，表当中分别包含一个float(10,8)的列和一个decimal(10,8)的列。

向表当中插入一条记录，指定float和decimal的值均为12.12345678，但最终查表时会发现decimal保持了数据的原貌，而float则会存在一定的精度损失。

说明：

• float表示的精度大约是7位，decimal整数最大位数m为65，支持小数最大位数d是30，如果d被省略，默认为0，如果m被省略，默认是10。
• 如果希望小数的精度高，推荐使用decimal。

字符串类型

char类型

基本语法：

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

创建一个表，表当中包含一个char(6)的name列。

char(6)中最多可存储6个字符，因此只要插入的字符个数不超过6个都是能够成功插入的。

需要注意的是，这里所说的字符并不只是指一个英文字母，一个汉字也是一个字符，因此只要插入的汉字个数不超过6个也是可以插入的。

如果插入的字符个数超过了6个，那么在插入数据时就会产生报错。

好处：

• 在不同编码中，一个字符所占的字节个数是不同的，比如utf8中一个字符占3个字节，而gbk中一个字符占2个字节。MySQL限定字符的概念不是字节，这样用户就不用关心复杂的编码细节了。

varchar类型

基本语法：

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

创建一个表，表当中包含一个varchar(6)的name列。

由于varchar(6)中最多可存储6个字符，因此只要插入的字符个数不超过6都是能够成功插入的，超过6就会插入失败。

varchar类型可指定的字符个数上限

varchar类型最多占用65535字节，其中有1~2字节用来表示实际数据长度，还有1字节来存储其他控制信息，因此varchar类型的有效字节数最多是65532字节。

而varchar类型可指定的字符个数上限，与表的编码格式有关：

• 对于utf8编码来说，一个字符占用三个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 3 = 21844。
• 对于gbk编码来说，一个字符占用两个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 2 = 32766。

因此在定义编码格式为utf8的表时，varchar(L)中的L如果超过了21844，则会产生报错。如下：

而在定义编码格式为gbk的表时，varchar(L)中的L如果超过了32766，则会产生报错。

char和varchar比较

char与varchar的区别如下：

• char可存储的字符上限是255，varchar类型可存储字符上限与表的编码格式有关。
• char(L)定义后，无论存储的字符串长度是否到达L，都会开辟用于存储L个字符的定长空间，如果存储的字符串长度超过L则会报错。
• varchar(L)定义后，会根据存储字符串的长度按需开辟空间，并且需要使用1-3字节的空间用于表示存储字符串的长度以及其他控制信息，如果存储的字符串长度超过L则会报错。

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

• 如果数据确定长度都一样，就使用定长（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 完全一致，占用四字节。

创建一个表，表当中包含date、datetime和timestamp三种时间日期类型的列。

查看表结构可以看到，timestamp类型的t3列是不允许为空的，它的默认值为CURRENT_TIMESTAMP。

因此如果插入数据时不插入t3列，那么就会自动插入当前的时间戳。

enum和set

基本语法：

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

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

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

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

示例：

有一个调查表votes，需要调查人的喜好， 比如（登山，游泳，篮球，武术）中去选择(可以多选)，（男，女）[单选]；

向表中插入记录时，被调查人的性别只能从男和女中进行二选一，被调查人的爱好可以从提供的若干个选项中进行多选一或多选，多个爱好之间需要通过英文逗号隔开。

通过数字设置enum

在插入记录时，除了通过指明男女来设置性别，还可以通过插入数字1和2来设置性别。

虽然enum和set可以通过数字的方式进行设置，但严重不推荐这种做法，因为这样的SQL可读性太差，导致后期维护成本变高。

enum和set查找

如果想要筛选出调查表中所有女同志的信息，那么直接在筛选时指明gender='女’即可，因为enum类型的值只能多选一。

但如果要筛选出调查表中爱好包含武术的人的信息就比较麻烦了，如果继续使用上述方式，那么最终筛选出来的是爱好仅为武术的人的信息。

这时需要借助find_in_set(str,strlist)函数，该函数的作用是查询strlist中是否包含str，如果包含则返回str在strlist中的位置（从1开始），否则返回0。

通过select可以对find_in_set函数进行验证，依次查找集合a,b,c中是否包含字符a、b、d，这时在查找字符a和b时就会得到其在集合中的下标，而在查找字符d时就会得到0值。

这时就可以通过select搭配find_in_set函数，来筛选出爱好包含武术的人的信息。

此外，我们还可以继续添加筛选条件，比如筛选出爱好中包含武术，但爱好又不仅仅是武术的人的信息。