MySQL数据类型

---

一、数据类型分类

数据类型	说明
BIT(M)	位类型：M指定位数，默认值为1，范围为1-64
BOOL	布尔类型：使用1表示真，使用0表示假
TINYINT [UNSIGNED]	占用1字节，默认为有符号
SMALLINT [UNSIGNED]	占用2字节，默认为有符号
MEDIUMINT [UNSIGNED]	占用3字节，默认为有符号
INT [UNSIGNED]	占用4字节，默认为有符号
BIGINT [UNSIGNED]	占用8字节，默认为有符号
FLOAT[(M,D)] [UNSIGNED]	M指定显示长度，D指定小数位数，占用4字节
DOUBLE[(M,D)] [UNSIGNED]	M指定显示长度，D指定小数位数，占用8字节
DECIMAL(M,D) [UNSIGNED]	M指定显示长度，D指定小数位数，每4个字节表示9个数字，小数点占用1字节
CHAR(L)	固定长度字符串：L指定字符串长度，最大为255
VARCHAR(L)	可变长度字符串：L指定字符串长度上限，最多占用65535字节
BLOB	用于存储二进制数据
TEXT	用于存储大文本数据
DATE / DATETIME	日期类型：YYYY-MM-DD格式 / YYYY-MM-DD HH:MM:SS格式
TIMESTAMP	时间戳：以YYYY-MM-DD HH:MM:SS格式进行显示
ENUM	枚举类型：ENUM类型的取值范围需要在定义字段时进行指定，设置字段值时只允许从成员中选取单个值，其所需的存储空间由定义ENUM类型时指定的成员个数决定
SET	集合类型：SET类型的取值范围需要在定义字段时进行指定，设置字段值时可以从成员中选取一个或多个值，其所需的存储空间由定义SET类型时指定的成员个数决定

二、 数值类型

1. tinyint类型

在MySQL中整型有好几种，这里我们以tinyint为例进行介绍，其他的用法和tinyint一模一样的，唯一的区别就是范围不同罢了。

tinyint是1个字节，八个比特位，可以表示的范围是-128~+127，如果是无符号类型的那就是0~255。
我们在进行插入数据的时候必须在规定的范围内，否则就会插入失败。

这里我们创建了一个t1表然后往里面插入了三个数据分别是127、0、-128。我们可以看到插入成功了。

这里我们在试试插入几个超过范围的数字，来看看什么情况。
这里我们插入了一个128，一个-129都报错说超过范围了，因此tinyint只能插入范围内的数据。

无符号的tinyint

无符号的tinyint范围是0~255，我们这里创建了一个t2表，然后插入两个数据0和255，都插入成功。

但如果插入不在范围内的数据那就会报错，比如这里我们插入一个-1和256.

smallint、mediumint、 int、 bigint和tinyint类似只不过是表示的范围不同罢了，大家可以自己试试，这里就不多介绍了。

2. bit类型

bit类型是一个位类型，在进行使用的时候可以在bit后面加个括号，用来指定几个比特位，不指定的话默认是1，指定的范围是1~64。

一个比特位只能表示0和1嘛，指定八个比特位那就是一个字节嘛。

这里我们可以创建一个表t3，里面包含一个int类型的id和bit（8）类型的a。、

这里我们都插入个10试试什么样子。
我们发现id这一列显示10，但是a这一列却啥都没有，这是什么原因？
原来是bit类型显示的时候是按ASCLII编码进行显示的。在ASCII编码表中10是换行符，所以这里就会显示图中的样子。

既然如此，我们可以都插入一个65试试，在ASCII编码表中65代表的是大写字母A，我们看看到底是不是按ASCII编码表显示的呢？

可以看到是符合我们预期的。

bit类型的范围

bit类型的范围主要看你指定的bit位数是多少，比如我们这里可以建立一张表，里面有个性别，我们就可以使用一个bit位来表示男和女。虽然现在一个比特位好像已经不够表示所有性别了吧，比如美国承认的性别类型就有五十多种。

这里只能插入0或者1来表示男和女，不是这俩数字就会报错，因为超出范围了。

建议

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

3. bool类型

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

所以操作上就不多介绍了，大家可以手动试试

4. float类型

FLOAT[(M,D)] [UNSIGNED]
M指定显示长度，D指定小数位数，占用4字节
float所能表示的范围不仅和字节数有关，还和你指定的M和D有关，比如说这里我们零M=4，D=2。即显示长度为4位，小数部分占两位，那表示的范围就是-99.99~99.99。小数一定占两位，显示长度占四位，那整数部分就只能占两位了，所以整数最大就是99。（后面说到四舍五入的时候可以知道实际范围是-99.994~99.994 ）

这里创建了一个有float（4，2）的表，向里面插入个99.99和-99.99发现插入成功了。

我们再去插入一个超过范围的数试试，比如说100和-100。可以发现都插入失败了。

关于四舍五入

float的小数是存在四舍五入的。还是上面内个例子，我们插入99.994是可以插入的，因为更具四舍五入原则，直接把最后的4舍弃了，所以插入的就是99.99.

但是如果这里插入的是99.995的话，那就插入失败了，因为5会进一位，就导致整数部分成100了，这显然就超过范围了，就会报错，不让插入。

这里我们多插入几个看看效果，比如插入一个19.995，会发现实际上插入了一个20.00.插入一个19.994，会发现实际上插入了一个19.99，原理呢就是四舍五入。

关于无符号类型的float

如果将float设置成unsigned的话，float会直接将负数内部分抛弃，只剩下0和正数。比如 float（4，2）范围是-99.994~99.994，
但是如果写成float（4，2）unsigned的话，那表示的范围就是0~99.994了。

可以看到0和99.994可以插入，但是-1直接报错了，因为超范围了。

5. decimal类型

decimal的使用方式和float的使用方式相同，就是decimal的精度更高一些。
这里插入相同的数据之后发现decimal保持不变，但float就不准了。

三、 字符串类型

1. char类型

CHAR(L) 固定长度字符串：L指定字符串长度，最大为255
这里的L表示字符长度，比如说L=3，那就是3个字符，值得注意的是这里的字符的意思是只要是字符就行一个英文字母一个汉字都算是一个字符。

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

这里我们建立一张表，里面有个name char（5）。表示插入的时候需要插入五个以内的字符。

由于这里我们写的是char（5），所以插入的字符个数不能超过5个，超过的话就会报错，插入失败。

2. varchar类型

建立一张有一列是varchar类型的表。

这里也是和char一样，只要字符的个数少于6就都可以插入进入，只要超过6那就报错。


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

• varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。

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

• 如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符
  占用2字节）。
  

3. 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三种时间日期类型的列。

我们这里只插入了t1列和t2列，不插入t3列，它会自动插入当前的时间戳。

如果更新了某一个数据，那么与之相应的timestamp也会自动修改为修改的时间。

五、enum和set类型

1. enum类型和set类型

enum语法：

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

set语法：

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

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。

插入数据的时候hobby可以多选，而gender只能选一个。



集合查询使用find_ in_ set函数：
find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；str_list 用逗号分隔的字符串。
查询爱好登山的人：