【MySQL】数据类型

数据类型

点赞👍👍收藏🌟🌟关注💖💖
你的支持是对我最大的鼓励，我们一起努力吧!😃😃

1.数据类型分类

就如我们学习的C/C+等语言每一种都有自己的数据类型，MySQL也有自己的数据类型，常见的如下：

2.数值类型

数值类型整体是被分成：位类型、布尔类型、整数类型、浮点数类型。

下面列出来的是主要是以整型为例：

上面这么多类型其实都叫做整数类型，根据名字它们所占字节数大小是不一样的。都是MySQL这里规定好的。默认如果我们只写了tinyint、smallint等这样的，默认是有符号类型的，取值范围也和C/C++学习的对应的整数范围是一样的。如果tinyint、samllint后面加 unsigned 就代理是无符号类型。

2.1tinyint类型

我们以tinyint类型为例，剩下的整数类型都是一样的。

下面我们先建立一张表

create table if not exists t1(
     num tinyint
     );

表现在也建立好了，我们看到tinyint后面跟一个数字4，这是什么意思，未来其他类型后面（）里面的含义我们都会说，而这个等到说约束的时候在细说，现在先不要管。

插入语句后面我们再说，现在我们先用。tinyint目前是有符号的，取值范围是-128~127，所以呢插入一些值看看情况

insert into t1 values (-128);#全列插入,不指定向那列中插

此时发现在tinyint取值范围内的数据都可以正常插入。

当我们插入取值范围外的时候，发现mysql不让我们插了。报我们超出范围了。

• 在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。
• 可以通过UNSIGNED来说明某个字段是无符号的

当前默认是有符号的，我们当然也可以建立一个无符号的啊，

create table t2(
     num tinyint unsigned
     );

此时就建立了一张表num字段的类型就是无符号类型，然后再插入一些数据看看情况。

我们同样发现一个问题，当我们插入这个类型的值超过了它的取值范围，mysql的做法是拦截，不让你插入。

如果在C/C++中向一个char类型插入比它取值范围还大的数据 ， 如char a=1234567；此时会发生截断然后放进去。

如果我们向mysql特定的类型中插入不合法的数据，MySQL一般都是直接拦截我们，不让我们做对应的操作! MySQL必须保证插入数据的完整性，一旦截断，那在MySQL中有些是成功插入的有些是截断后插入的，那作为用户来讲，他还能信任MySQL中插入的数据吗？

反过来，如果我们已经有数据被成功插入到mysql中，一定是插入的时候合法的！

所以mysql中，一般而言，数据类型本身也是一种：约束！

约束 —> 倒逼程序员尽可能进行正确插入。所以约束，约束的是使用者。另外如果你不是一个很好的使用者，mysql也能保证数据插入的合法性。

这样的话就能保证数据库中的数据是可预期，完整的。
以tinyint为例，它是有符号的，所以可预期的是未来插入的值范围一定在-128~127的。并且数据是完整的没有发生过截断或者隐式类型转化。

我们还可以发现一个细节，mysql表中建立属性列， 列名称在前 类型在后 如 num tinyint ，如果反过来就是C/C++那一套形式。

tinyint我们说完了，其他类型自己推导。

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

不过我们还是结合具体场景来使用类型。

2.2bit类型

基本语法：

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

位类型，M表示比特位的位数。比如一个比特位取值范围0-1，两个比特位取值范围0-3，四个比特位取值范围0-15

create table t3(
    id int,
    online bit(1));

用一个比特位表示这个用户是否在线

因为我们只有一个比特位，只能插0或1，插入其他的都不行超过范围了。

当我们查一下t3这个表的内容，我们发现id这里插入的整数都显示了。但是online这里插入的1，0为什么不显示？

原因就是在于当前这个online是位类型，位类型在显示的时候通常是按照ASCLL码值得形式显示的。只不过当前在online插入得值是0，1在ASCLL码值中是属于不可显示的。所以这里什么都看不到。

如果想要看见，可以让online按照16进制方案显示

select id,hex(online)from t3;

那到底是不是这样的呢，我们把这个online类型改一下，插入65，97，看到显示的确实就是ASCLL码中的A，a，而前面插入的0，1就是不会显示因为它是在ASCLL码中是不可显示的。

2.3浮点数类型

2.3.1 float

语法:

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

在定义的时候可以在列字段后面带上float类型，该列就会变成浮点数类型。而浮点数类型我们可以[ ]选择性的携带，M代表的是浮点数数字个数的总位数。d代表小数点之后数字的位数。

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

因为目前float后面我们没带unsigned，所以既可以插入正数也可以插入负数。而且float M为4表示插入浮点数总位数是4，d为2表示精度位数是2个。

float(4,2)表示的范围是-99.99 ~ 99.99

下面插入的数都不行，为什么-100.0也不行呢？因为这里要求精度要求是2位，这里不够就补0，后面2位加前面3位总长度就超过了4了，也是不行的。

插入-10.0，虽然后面会补一个0，然后补过之后总长度是4，插入也是可以的。

再看当我们插入的数，虽然小数位都超过2位了，但是插入依然没问题，并且23.456 插入后变成 23.46了 。23.454 变成 23.45。

这说明实际上在进行浮点数存储的时候，如果要求人家是2位精度，但是你传入了更多的位的精度，MySQL在保存值十会进行四舍五入 将数据进行存储。

虽然MySQL保存值时会进行四舍五入，但是就像99.995 四舍五入之后 变成100.00 但总长度超过了当前的m=4.也是不能插入的。

最后总结一下，当精度不够时补0，精度超了会四舍五入但超过对应浮点数位数范围也不能插入。

上面我们定义的float是有符号的，接下来float定义成无符号的看看效果，

create table t5(
     id int,
     salary float(4,2) unsigned
     );

float(4，2)取值范围 -99.99~99.99 那无符号呢?

虽然我们这里给的是0，但是依然很坚强的把精度补齐

虽然是无符号的但是我们发现这里上限还是99.99，并且插入负数根本不行

换句话说将float定义成无符号类型，直接把负数全部砍掉。直接变成了0-上限。同时也有精度不够补0，够了也要做四舍五入。

最后一点浮点数在存的时候是有精度损失的

alter table t5 modify salary float;#更改float使用默认的精度

可以看到我要存的和实际上存的不一样，从这就能看出来浮点数在存的时候是有可能精度损失的。

还有一个double大家自行验证。

2.3.2 decimal

语法：

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

decima也是MySQL当中的一个浮动数类型，也可以指定长度和小数点的位数和float和double一样。总之它的使用和float一模一样，也有精度不够补0，够了四舍五入。

那decima有什么用呢？

float经过我们刚才的验证我们发现它确实是有精度损失的。只不过decima可以有效的规定这一点。

float默认表示的精度大约是7位。
这里我们把float精度改成8看看能不能把数据按照精度8位去存。

可以对比发现虽然float精度设为8但是实际上存的时候已经和插入数据不一样了。float类型往往在精度过大或者整体数字过大时会自作聪明帮我们做一些优化策略。但是decimal不会，它能够完完全全让数据怎么存就怎么取。

所以未来你的场景对精度有要求就用decimal，精度要求并不高就用float。

decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。

3.字符串类型

3.1 char

语法：

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

当前name char(2) ，char类型可以存字符串并且是固定长度的，这里长度为2表示最多存两个字符。

这里证明确实最多可以存2个字符，超过就不让插了

如果我插入的是中文会是什么样子的？

为什么一个汉字可以插，两个汉字也可以插，但是插入三个中文的时候就不让我插了？

首先要说的是在utf8编码中一个汉字占3个字节的。像gbk编码一个汉字占2个字节。

这里中国是占了6个字节依旧可以表里面做插入，而char类型中长度单位为字符。 这里要特别说明一下，MySQL中的字符和C/C++中的字符概念是不一样的，以前语言上的字符，一个字符对应一个字节，而在MySQL中的字符真的代表一种符号，要么是1234、要么abcd、要么就是中文汉字，一个汉字就是一个字符。

char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个， 最多只能是255，超过不让你插，连表都不让你建！

总结一下：char 后面括号里面填的就是固定长度字符串的上限，一旦定义好之后该给你多少空间就给你申请好，你用多少是你的事 ，我给你多少由L决定。另外如果插入字符超过L那就不给你插入。最后这个L不能超过255。

3.2 varchar

语法：

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

varchar是一个变长字符串，下面我们看看它这个类型对应边界特性以及约束体现在哪里

当前我们定义varchar括号里面是6，并且允许我们进行字符式的插入，每一个汉字都是一个字符。我们最多可以插入6个字符，然后多插一个字符就不让插了。就这就varchar 后面括号带数字的意义。

但是这个和char有什么区别呢，区别看着不大啊，括号里面定义的数字依旧表示字符串最大长度啊，和刚刚的char是一样的啊。到底有什么区别呢？等会揭晓！

现在我们改一下表，因为刚才看到最大长度是65535，那到底能不能设置成65535呢？

上面说的是长度太大了，name （max=21845），不对啊，上面不是65535吗。但是这里是21845啊。如果你足够仔细你看发现65535后面跟的是字节

MySQL在存储字符类型的时候，认为utf8编码，单个字符是三个字节，

换句话说varchar保存最大长度是65535个字节，但字符数是21845！

可是虽然是在这样，但是varchar和char有什么区别呢？char不也是括号里面带数字吗，它也是字符啊，只不过范围比varchar小罢了，char最大是255，varchar最大是21845。有什么区别呢？

此处目前来看没有什么区别，但实际上是有区别的。
char是固定长度字符串，类似于C/C++中定义一个数组，比如定义一个char(6) 就代表字符个数是6个字符也就是18个字节。至于当前你用了多少我不管。反正你要6我就给你6个字符的空间，有可能你只用了1个，但我依旧给你6个。也就是不管你用没用到那么多空间，只要你要了我都给你那么大的空间。

但是varchar不一样，这个L你定义多大，这个数字表示的是该字符串空间的上限！比如说varchar(6)，L是6，实际上当我们在正常使用的时候，你可能只用了一个，那varchar只会给你分配保存一个字符的空间。换句话说你用多少给你多少，但是上限是L。 换言之，我们字符的上限和我们有多少用多少并不冲突。

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

• varchar字节长度为0到65535之间，但这个变长字符串里面一定要有一点空间用来保存实际用了多少字符，所以有1 - 3 个字节用于记录实际用了多少字符的数据大小，varchar尽可能节省空间，所以它会根据实际字符的个数来动态调整用几个字节来表示有效字符的个数。假设未来会把varchar字符数写满，所以最终一定要三个字节来记录数据大小。所以说有效字节数是65532。
• 当我们的表的编码是utf8时，varchar(n)的参数n最大值65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）

当前是len是21845，是不让我们建表的，换成21844就可以了。这就证明了varchar字符个数不能超过21845，只能在21844包括21844以内。

前面我们改表的时候，它提醒max=21845，但是我们换成21845还是不行。再换成21844也还是不行。原因在于t8里面有一个id。它报错原因是一行大小太大了，其实MySQL这个65535这个值也代表一行MySQL当中的数据。只能再改小一点才行。

但这些不重要 ，重要的是我们发现这里定义出来的21845，但实际我们算出来是21844，这里是21845主要原因是因为它也到那三个字节计数数据的也带上来了。所以是21845，但实际只有21844，这里21844成功，是因为MySQL这一行都给你了。

所以表是一行一行存的，建表时有其他字段存在，varchar(21844)也是不行的。

最后总结一下：varchar有自己长度上限，在上限范围内用多少给多少，做法就是在申请的众多字节中有1~3个字节用来表示有效字符长度，通过这种方式来确定实际字符是多少来实现变长。varchar最大字节数65535，但65535一定要包含1-3个记录数据长度的字段。在utf8保存在大字符个数是21844。如果这个表很干净一行内没用其他字段。varchar能到21844，但是有其他字段那这个值就会变小。

3.3char和varchar比较

共同点：都能保存字符串 ，都有上限。但char是定长的，varchar是变长的，char一次把空间分配好，varchar用多少给多少，因为里面有字节保存有效字符个数。

这里只用一个字节就能保存有效字符个数，用不到三个。

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

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

4.日期和时间类型

常用的日期有如下三个：

• 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是时间戳，时间戳会自动变化。不需要我们自己做任何操作。也就是说这个表中只要有时间戳，只要对这一条记录插入或者更改时，这个时间戳会被自动更新到最新时间。

update t10 set t1='1999-1-1';

当我们把表中t1更新了，没用改t3，但这个t3也会被自动跟新

timestamp 时间戳有什么用呢？
比如说你在博客上面给别人评论的时候，用到的就是这。只要对评论更改或者插入，这个时间戳就会被更新到最新时间。

datetime就是要存储一个固定时间，如记录你入职的时间。

要结合具体的场景选择时间。

5.enum和set

语法：

• enum：枚举，“单选”类型； enum(‘选项1’,‘选项2’,‘选项3’,…);

MySQL允许我们设置表结构，在表结构中设置枚举类型。enum后面可以带一些特定的选项。这些选项一定是多选一，单选！，就好比做问卷调查有一个选项问你是男还是女，你只能选一个。在数据库中也支持在插入时进行多选一的选项，我们称为enum。

• set：集合，“多选”类型； set(‘选项值1’,‘选项值2’,‘选项值3’, …);

set也是一样，允许在建表的时候创建集合类型，集合类型中可以设定特定的n多个选项值，这些选项值在设定的时候一定是多选也可以单选，如还是问卷调查问你的爱好给你好几个选项，你可以选一个也可以选多个。

关于enum和set更多细节的东西，我们建一个问卷调查表，把enum和set用起来在说。

我们插入的只能是属于enum枚举类型给我提供约束，换句话说插入时只能插入枚举的类型，不允许插入除该枚举类型外其他任何字符。

下面可以看到插入数字1、2可以插入，其他的都不能插入了。换句话说，当enum枚举类型在插入的时候，可以直接写这个枚举限定的常量，也可以写对应常量的下标，注意这个数字下标从1开始，分别代表第一个枚举值
第二个枚举值等。有几个就只能到几，超过不行

下面在看看set集合我们发现，set不允许插入不存在的类型。插入时可以单插入，也可以以逗号为分隔符多插入。这就是集合给我带来的约束。

当不向enum和set中插入时，也是可以的，默认为NULL，后面说。

当前我们向set中插入一个0，什么都没有显示，其实这里并不是没用。
NULL 和 ’ ’ 是不一样的。NULL真的表示什么都没有， ’ ‘ 表示有东西只不过这东西是空串，所以它们俩个是不一样的。

但是向里面插入一个1，这里显示的是代码，这里不就和枚举类型一样的了吗？

插入2，果然就是羽毛球。但真的是这样吗？

插入3应该是乒乓球了。但是这里并不是。没有按照我们的要求插入。

更为夸张的是，我插入的是7，应该是不存在的，为什么插入成功，而且把前三个显示出来了

很显然在set以数字形式插入绝对不是下标！

目前这里有5个爱好，在集合中我们把它想象成5个比特位 00000 ，这里我们从右到左表示从低比特位到高比特位。

插入1的时候 00000 -> 00001 ，这个比特位的位置代表是代码这个爱好，这个比特位的内容为0为1代表也没有这个爱好，1代表有代码这个爱好，0就代表没有。这就是我们刚才插入1的时候，显示的是代码的爱好

插入2的时候 00000 -> 00010 ，第二个比特位置为1表示有羽毛球这个爱好

比如插入的是7 00000 -> 00111 ，表示当前表中低三个爱好都有，所以会显示出前三个爱好

所以，当我们在向一个set集合中插入的时候，这个数字代表的是位图，集合中有几个类型就有几个比特位，比特位从低向高依次代表set类型中从左向右。比特位的位置代表是那个类型，比特位为0为1代表是否是有这个类型。

所以要注意enum枚举中插入数字代表的是下标，set集合中插入数字代表的是位图。

enum和set的查找如何做呢？

先看在枚举中怎么筛选。

在enum中既可以用常量，也可以用常量对应的下标进行筛选

select*from votes where gender='女';select*from votes where gender=2;

那set这里怎么筛选呢？先试试和enum一样的做法

此时确实能把爱好是羽毛球的人筛选出来了，但是呢有的人有多种爱好里面也有羽毛球，但是这里筛选的时候把爱好只有羽毛球的筛选出来了。我想要的不是爱好只有羽毛球的，我要的是爱好有羽毛球的。

set也可以用位图筛选

像这些不管是用位图，还是常量，是多选还是单选，这些都是严格去匹配的。也就是说这样去筛选必须要有而且是仅有。

那想只要有就给我筛选出来怎么做呢？

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

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

select 可以执行表达式，同理函数也可以执行

在就返回下标，从1开始，不在返回0，也就是说非0表示真，0表示假。

我们发现一次查多个元素是不行的，换句话说find_in_set仅查一个元素是否在集合里面。

find_in_set能够在一个集合中查找某一个元素是否存在，存在就返回对应的下标。其次它查找的过程是判断一个元素是否在集合中而不是判断相等的。

set这里查找爱好是羽毛球用严格匹配。爱好有羽毛球就可以用find_in_set

那现在要求爱好里有代码和羽毛球怎么做呢？

我们where条件筛选和C/C++一样，相当于一种条件判断，里面可以有一个函数所对应的得到的结果，也可以把多种函数对应的结果级联起来，要求两个结果同时满足，就可以如下做，这里的and就相当于逻辑与

select*from votes where find_in_set('羽毛球',hobby) and find_in_set('代码',hobby);