MySQL数据类型

MySQL数据类型分类

MySQL和C++语言一样，也具有自己的数据类型。

MySQL一共有四大类数据类型：数值类型、文本类型、时间类型、string类型。程序员可以根据需要使用不同类型。

MySQL数值类型

类型名	字节	最小值（有符号/无符号）	最大值（有符号/无符号）
tinyint	1	-128/0	127/255
smallint	2	-32768/0	32767/65535
mediumint	3	-8388608/0	8388607/16777215
int	4	-2147483648/0	2147483647/4294967295
bigint	8	-2^63/0	2^63-1/2^64

tinyint类型

知道数值类型的种类，我们再来直接使用一下这几种类型。

我们先创建一个只包含一个 tinyint 类型成员的表，然后向它插入数据。

 我们发现只有在tinyint类型范围中的数据才能够插入到表中，否则mysql会直接报错；

这一点和我们的C++之类的语言不同，我们给一个变量超过数据范围的值时，会报一个警告，但是并不会报错，而mysql这种直接报错的行为也能够有效禁止程序员进行错误的操作。

mysql这种行为也是一种对程序员的约束，保证程序员插入数据的正确性。

而我们也可以创建一个无符号tinyint类型的表。

这样就可以插入0~255的值了。

 bit类型

语法：bit(m) 一般m在1 ~ 64之间，如果没有设置数字，默认为1.

该类型就是按bit来给定大小，m为多少，这个类型就有多少bit位。

这里我创建一个大小为1bit的类型，发现只能插入0和1.

并且 bit 类型的显示根据不同mysql的版本会有不同。

比如我这里是十六进制的，如果想要查看十进制，那么命令需要修改。

select id, hex(flag) from bit_test;

这是一种约束，具体细节以后了解。

float类型

语法：float (m,d) [unsigned] : 其中m指的是数字的总长度，d表示小数的长度，占用4字节 

 

比如我这里创建一个数字长度2为4，小数长度为2的类型，它可存储的类型就是(-99.99~99.99)之间，超过的会直接报错。

并且浮点数默认按照四舍五入来存储数据，若是四舍五入后数据也超过m长度，也会报错。

但是如果你四舍五入后没有依旧在范围内，那么就不会报错。 

不过显示出来的依旧是按照(4,2)的格式，小数点后面依旧只有两位数据。 

而如果float类型是unsigned的话，那么范围就会变成 0 ~ 99.99，直接减半。 

而float后面没指明长度的话，一般小数点后面也只能保存两位，此外，当数据很大时，float保存的数据就不会特别准确，需要注意。

decimal类型

上面说过，float类型会有精度丢失的问题，而decimal类型作用和float类型一样，但是它的精度更高。

语法 ： decimal (m,n) [unsigned]; m:数字的长度， n：小数的长度

 我们通过创建一个表，分别是 float 类型和 decimal 类型，它们的精度都设置成 (10,8)，然后插入相同的数据，我们发现精度较高时，float会出现偏差，但是decimal不会。

一般float的精度是7位。

decimal整数最大是65位，小数最大位数是30位，若n被省略，默认为0，m被省略，默认为10.

因此如果追究更高的精度我们一般采用decimal类型。

MySQL字符串类型

char类型

语法：char(L); 固定字符串，L是字符的长度，单位为字符，最长为255

 这里我创建一个表，它的变量name字符长度为2，我们发现我们插入的数据的是字母还是汉字，只要是长度在2以内，都可以插入。

而一个汉字的字节长度为3，这说明对于MySQL而言，这个char的字符长度只看字符的个数，而不是占的字节数。

varchar类型

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

这里我创建一个长度为10字符的变量，发现插入数据如果超过10，就会报错。 

从目前看来，varchar似乎和char类似，那么varchar为什么称为可变字符串呢？

首先我们需要了解varchar的len值是用来干嘛的。

上面说过，varchar最长为65535个字节，那么varchar能否为65535个字节长度呢？

我们发现，它最长只能为21845，而21845正好是65535/3的长度。

而我们目前采用的字符集是utf8，面向中文的，一个字符占3个字节，因此len的长度会根据编码集不同而不同。

接着我们将长度改为21845时，发现依旧出错了，这是为什么呢？

 这就是varchar的变长字符串的特性了。

因为varchar是变长字符串，因此在65535个字节中，有1~3个字节是用来记录数据大小的，因此实际上可用的字节是65532个字节，而65532个字节长度为21844个字符（utf8环境下)，因此我们这里也只能是21844个字符长度。

varchar的长度 

• 有1~3个字节用来记录数据长度，因此有效的字节数只有65532个字节
• 根据不同的字符集环境，长度可能不同，utf8环境下一个字符占3个字节，因此varchar长21844个字符，而gbk下一个字符占2个字节，varchar最长为32766字符

此时，我们的表中varchar长度是21844个字符，然后我们向表中新增新的属性，却发现报错了！ 

看报错，是说一行的长度太长了，这是由于varchar一个属性就已经到了最长的65535字节，一行再也容不下其他属性了，因此出现报错，如果还想插入其他属性，需要将varchar变小一点。

varchar和char对比

从上面看，我们依旧看不出varchar和char的不同，只是知道varchar可能需要有几个字节来记录长度，但是其他的地方二者并无不同。

因此我们来直观的对比一下二者的不同（一下都是utf8环境下的测试)

实际存储	char(4)	varchar(4)	char占用字节	varchar占用字节
abcd	abcd	abcd	4*3=12字节	4*3+1=13字节
a	a	a	4*3=12字节	1*3 + 1=4字节
abcde	x	x	数据过长	数据过长

我们发现，char类型存储数据时，不管长度多长，始终都是char类型多长，存储的长度多长。

可以看到char类型存储4个字符和存储1个字符时，它们占用的字节都是12；

而varchar存储4个字符和存储1个字符时，占用的字节不同。

因此我们可以得出，其实varchar和string差不多，只要存储的数据不到上限。有多少字符就存储多少字符，因而我们根据不同的情况，需要使用不同的类型。

如何选择varchar和char

• 如果数据长度都一样，可以使用char类型，比如手机号，身份证之类的
• 如果数据长度不一样，就采用varchar类型，但要保证能够将最长的数据存储进去
• char类型浪费磁盘空间，但是效率高
• varchar节省空间，但是效率低
• char类型直接开辟空间
• varchar在不超过范围的情况下，用多少开辟多少

MySQL日期和时间类型

date: 格式：'yyy-mm-dd',占用三个字节

datetime:格式：'yyy-mm-dd hh:ii:ss',表示范围从1000到9999，共八个字节

timestamp:时间戳，从1970年到'yyy-mm-dd hh:ii:ss',格式和datetime一致，占用4个字节

其中，timestamp后续跟上DEFAULT CURRENT_TIMESTAMP和 ON UPDATE CURRENT_TIMESTAMP就可以使该值默认为当前时间戳，并且启动自动更新。

有的mysql版本可以不用设置，有的需要设置，大家可以自行测试一下。

然后通过insert into time_test(t1,t2) values('2000-10-1','2000-10-1 10:1:1');后查看表中数据。 

发现t3自动更新了。

 enum和set类型

enum:枚举类型，‘单选'类型；

语法：enum (’选项1‘，'选项2',....);

该类型虽然可以有多个选项，但是实际上只存储了一个值，并且这个值实际上是数字，类似于数组的下标，不过下标从1开始。

set:集合，’多选类型‘

语法：set(’选项1‘，’选项2‘...);

该类型只是提供了若干个选项的值，其值并不是下标，而是需要转换成位图一样的值，比如1表示选择第一个选项，2表示选择第二个选项，3表示前两个选项都保存了。

我们直接看看示例；

我们先创建一个表，包含名字，性别和爱好。

 其中插入数据我们可以这样插入：

也可以这样插入，我们发现，enum的下标一定是从1开始，插入0会报错。 

 而set类型也可以这样，只不过规则是将数字转换为二进制，来看看包含哪些选项。

 

enum\set类型查找

如果我们想查找某一条件下的数据应该怎么做到呢？

 enum查找

如果是enum查找，因为enum只有一个选项，因此可以很简单的查找。

可以直接使用select * from enum_set_test where sex='男'; 来查找。

 也可以下标查找。

set查找

因为set的选项可以有多个，比如爱好可以有羽毛球，游泳之类的，如果像enum查找一样使用 where hobby='xxx'； 的话，只能寻找到只有xxx这一个爱好的数据。

 

当然我们也可以学着插入时用数字来查找，但是也是绝对查找。

 

但是我们也许只是想查找含有羽毛球这一兴趣的数据，而非只有羽毛球这一兴趣的人。

因此mysql有一个函数：find_in_set；供我们使用。

它可以在表中查找含有某一数据的数据，我们直接使用一下。

语法：find_in_set(sub,str_list); 

如果sub在str_list中，返回下标（从1开始),否则返回0；

因此我们查找set可以使用该函数查找。

使用select * from enum_set_test where find_in_set('羽毛球',hoby); 即可找到所有含有羽毛球兴趣的数据。

 

总结

以上就是mysql一些重要的类型以及讲解。

我们不仅了解了整形类型，还有字符串类型以及时间类型，枚举类型和set类型。

并且分别讲解了不同类型的长度，用法以及特殊之处，希望对各位有所帮助。