MySQL-数据类型

一、总览

（图源来自网络）

之后将会对常用的类型进行细分。

二、数值类型 

整形类型

 （图源来自网络）

在我们使用数值类型的时候，是默认带有符号的，如果我们想指明无符号，需要在后面加上unsigned。

我们创建了一张测试表，用来测试tinyint，发现插入127的时候是成功的，但是插入128就会报错，说明如果插入的值超过了该类型的取值范围，mysql就会报错，我们再带上unsigned看看

再创建一张表t2

create table t2(
    -> num tinyint unsigned
    -> );

 插入

发现可以插入255，但是不能插入256。

以此类推，我们再使用其他数值类型的时候，要注意其取值范围，这也是mysql倒逼程序员强制规范插入数据的逻辑。

 bit类型

基本语法：

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

我们创建一个表，其中包含了一个bit类型，并指明了8字节

尝试进行全列插入，因为默认是有符号的，那么在插入256的时候就因为超出范围而报错，255就可以插入。

可见，不合法的数据插入，mysql会直接拦截报错，不像有些语言会给你进行截断或者隐式转化，它保证了插入的数据在数据的大小范围内一定是合法的，所以数据类型本身也是一种约束。

这张表之前也插入过一些数据，我们查看一下

发现它的显示要么是空白的，要么就是乱码。这是因为bit类型它 默认是按照ascii码的方式显示的，

我们可以在查的时候，加入hex()，让这一列以十六进制方式显示出来。

select id,hex(a) from t4;

这里我们也可以验证一下它确实是以ASCII码的方式显示的，我们可以插入字符a和97

发现确实显示的都是字符a。 

浮点类型 （小数类型）

 1.float

基本语法：
float[(m, d)] [unsigned] : m指定显示长度（也就是数字位数的总个数），d指定小数位数，占用空间4个字节

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

 实验：
创建一张薪资表，其中薪水用float类型

create table if not exists t1( id int, salary float(4,2) );

接着，我们主要针对薪水分别插入12.345和12.344

发现都插入成功了

发现插入的结果上，还是保留了两位精度，给我们的数据进行四舍五入了。

另外我们知道它的范围是-99.99~99.99。那么我们插入

 

99.996发现插入失败，

99.994发现插入成功

结果也是四舍五入了。 

decimal

语法：

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

这里看起来和float很像，但是他俩表示的精度的范围不一样。

比如我们再创建一个表

create table t2 ( id int, salary float(10,8), salary2 decimal(10,8) );

再插入

insert into t2 values(100,23.12345612, 23.12345612);

我们发现float类型的数据有偏差，但是decimal的没有，

说明float表示的精度范围最大是七位。

一般来说decimal最大整数位m为65，支持小数最大位数d是30。如果d被省略，默认是0，如果m被省略，默认是10。但是这些只做参考，还是得以实验得出的数据为主。

所以如果对精度的要求很高，应该使用decimal类型。 

字符串类型

 1.char

语法：

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

创建一个测试表

create table t3 (
    -> id int,
    -> name char(2)
    -> );

 主要针对name列插入 ab，和 你好

 发现都插入成功了。但是我们需要注意，在utf8编码中，汉字是要占3个字节的。gbk是占2字节。

也就是插入 你好 这其实是6个字节了。说明mysql它的字符跟C++这样的语言的字符是不一样的。

2.varchar 

语法：

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

圆括号里表示的跟char一样，但是它的最大长度是以字节限制的，这里要注意有些字符所占的字节数是不一样的。

如果我们尝试创建这样一个表

create table t1 ( id int, name varchar(65535) );

它是会报错的

它提示我们varchar里面能存的最大值是21845，说明是65535 / 3。它是以utf8的标准来设计的。

varchar的字节大小是0到65535，但是它有1~3个字节用来记录数据长度。

因此当编码是utf8时，它的最大值就是65532 / 3 = 21844，如果是gbk那就是 65532 / 2 = 32766

另外如果我们这张表除了varchar还有其他字段，比如id int这样的，那么它的最大值会变得更小。 

关于char和varchar之间的区别 

varchar也叫变长字符串，它varchar（len），这里面的len表示的是这个字符串的上限，假如len = 3，但是我们只用了2个字符长度，那么它就会只开辟两个字符长度的空间。

而char也交定长字符串，char（len），我们给出的len是多少，它就开多少空间。

因此，varchar它相对于char更能节省空间，但是它插入的效率比char要低。

相反，char插入的效率比varchar要高，但是它不能节省空间。 

关于如何选择char和varchar？

如果确定数据是定长的，比如身份证，手机号，md5，那么就可以用char。

如果数据不是定长的，比如姓名，地址，就可以用varchar，但是要保证它的上限。

另外varchar会拿出1~3字节来存数据的长度。

日期和时间类型

1.date 

 日期，‘yyyy-mm-dd’，占三个字节。一般用作生日日期这种对具体时间没有太多要求的场景。

2.datetime

时间日期格式 ‘yyyy-mm-dd HH：ii：ss’，表示范围从1000到9999，占八个字节。可以用作某评论的发表时间，它需要用户手动输入时间。

3.timestamp

时间戳，从1970年开始的， ‘yyyy-mm-dd HH：ii：ss’格式和datatime完全一致，占四字节。它跟datatime不同，它是会自动变化的。

我们创建一个表来测试一下

create table t2 ( t1 date, t2 datetime, t3 datestamp );

desc一下

发现有个Extra列，说明timestamp不需要用户插入，在我们向表中插入数据时，会自动更新。

那么我们插入一些数据

insert into t2 (t1,t2) values ('2004-02-01','2004-02-01 08:00:00');

 过会再插入一条数据

insert into t2 (t1,t2) values ('2003-02-01','2003-02-01 08:00:00');

然后查看

发现t3时间戳这一列确实是向表插入数据时自动更新的，但其实不只是插入数据，对表的哪一行做更新时，它哪一行的时间戳列也会自动更新的。

enum和set 

enum 

enum('选项1','选项2','选项3',...);

在插入的时候，我们只能多选一

set

set('选项值1','选项值2','选项值3', ...);

它可以选择若干个值。

enum和set它默认是允许为空的。

另外我们插入NULL和插入空是不一样的，这里需要注意。

接下来测试

create table votes(
    -> username varchar(30),
    -> hobby set('game','ball','rap','dance'),
    -> gender enum('男','女')
    -> );

desc votes\G;

或者

 

insert into votes values('张三','game','男');

 这个数据是可以插入的

另外，我们还可以用数字来代替选项

insert into votes values('李四','game','1');
insert into votes values('李四','game','2');

这两条都插入成功，选项如果是用数字代替的话，是从1开始的，超过范围就会报错。

这里主要是展示了enum的使用，而在set这里，对于使用数字代替选项是需要注意一下的

先看看表结构

比如我们插入

insert into votes values('里昂','2','男');

那么再插入

insert into votes values('里昂','3','男');

 

这里我们就发现规律了，set使用数字插入的时候，它有点像linux文件的权限那样，使用类似位图的设计，因为3的二进制是11，结果就是图上两项。如果插入4，就是rap了

 

这里有四个爱好选项，那么就是有4个比特位，按顺序分别对应爱好。

所以总计就是枚举enum数字对应的就是下标，而set数字对应的是位图。 

enum和set类型的查询

查询的时候，我们一样可以用数字来代替选项，这点enum没什么好说的。

但是当我们查set类型的时候，比如查1

select * from votes where hobby=1;

 

确实爱好都是game，但是

 

还有一个人的爱好是game和ball，它就没有查询出来，说明这样子查询是严格匹配的，但是大部分情况下，我们查的都是包含了game这个爱好的，而不是只有game爱好的结果。

在这里介绍mysql的一个函数，find_in_set，它是专门用来查询集合中包含的元素的函数。

select find_in_set('a','a,b,c');

 

 

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；

使用这个函数再来查询

select * from votes where find_in_set('game',hobby);

 

这样就能查询出来了。但是这里就不能用数字代替选项了。

但是需要注意的是，where其实有点像我们C语言里面的条件判断，它可以判断多个函数，因此如果我们想查询比如包含game和ball爱好的结果，应该这样查询

select * from votes  where find_in_set('game',hobby) and find_in_set('ball',hobby);

 中间加上一个and

总结一下：

数据类型天然就是mysql中的一种约束。