![f85a18f4b3a83cbde05b19d5589cce3a.gif](https://i-blog.csdnimg.cn/blog_migrate/aad1ea0dcffa009180d1d3499adbd3d5.gif)
01
数值类型
包含所有标准SQL支持的数值数据类型。![1124e79df6bc43e5683569abedac7e60.png](https://i-blog.csdnimg.cn/blog_migrate/7db7119e13477181f8349778313803ef.png)
02
日期和时间类型
![e5c55254f9ab045c74a6ce8e5fb5f128.png](https://i-blog.csdnimg.cn/blog_migrate/6940b54848ac7d711d29d88a2d21228f.png)
03
字符串类型
![4327bb13c0d8e773aa2aa412caba7d14.png](https://i-blog.csdnimg.cn/blog_migrate/fe625f1de9b3dc9602aa3606556ac160.png)
04
类型后的括号
在MySQL中创建表的时候经常能够看见在类型之后用括号的表示形式,例如int(M), 这里需要注意一个点,在MySQL中使用int(M)时的这个M并不是指存储在数据库中的具体的长度,不管我们的M设什么值,在数据库里面存储都是占4个字节,所以其能表示的最大最小值也都是一致的。唯一的区别在于当你设置zerofill才能显现,他会用0补足显示宽度。同理BIGINT也是一样的。 但是对于varchar和char来说,括号中的M代表了其长度,对于char来说,存储空间类似INT型一样都是提前分配好的,但是varchar这类可变长度类型中的M只是指代了其最大存储长度。这里也有一点需要注意,在4.0版本以下,varchar(20),指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字节),同时其最大长度上限是255,因而要存储更大长度的字符串时只能使用固定的TEXT或BLOB格式。而在5.0版本以上,varchar(20),指的是20字符,无论存放的是数字、字母还是UTF8汉字(每个汉字3字节),都可以存放20个,最大大小是65532字节 ,这也是由于MySQL中的行的定义长度不能超过65535,若定义的表长度超过这个值也将报错。![1967222e88ef789fae0b6332348f928a.png](https://i-blog.csdnimg.cn/blog_migrate/14a5685e15dd1923da3e54a0906b9fdc.png)
05
常用字符串类型的特点
在字符串类型中,每一种类型的存储方式和数据的检索方式都不一样。 常用的几种字符串数据类型的检索效率是:char > varchar > text 在空间占用方面: char:存储定长数据很方便,char字段上的索引效率级高,必须在括号里定义长度,可以有默认值,比如定义char(10),那么不论你存储的数据是否达到了10个字符,都要占去10个字符的空间(自动用空格填充),且在检索的时候后面的空格会隐藏掉,所以检索出来的数据需要记得用什么trim之类的函数去过滤空格。 varchar:存储变长数据,但存储效率没有char高,必须在括号里定义长度,可以有默认值。保存数据的时候,不进行空格自动填充,而且如果数据存在空格时,当值保存和检索时尾部的空格仍会保留。另外,varchar类型的实际长度是它的值的实际长度+1(如果长度大于255将+2),这个字节用于保存实际使用了多大的长度。 text:存储可变长度的非Unicode数据,最大长度为2^31-1个字符。text列不能有默认值,存储或检索过程中,不存在大小写转换,后面如果指定长度,不会报错误,但是这个长度是不起作用的,意思就是你插入数据的时候,超过你指定的长度还是可以正常插入。 从碎片角度进行考虑,使用char字符型时,由于存储空间都是一次性分配的。为此某个字段的内容,其都是存储在一起的。单从这个角度来讲,其不存在碎片的困扰。而可变长度的字符数据类型,其存储的长度是可变的。当其更改前后数据长度不一致时,就不可避免的会出现碎片的问题。故使用可变长度的字符型数据时,数据库管理员要时不时的对碎片进行整理。如执行数据库导出导入作业,来消除碎片。 另一点是考虑其长度的是否相近,如果某个字段其长度虽然比较长,但是其长度总是近似的,如一般在90个到100个字符之间,甚至是相同的长度。此时比较适合采用char字符类型。比较典型的应用就是MD5哈希值。当利用MD5哈希值来存储用户密码时,就非常使用采用char字符类型。因为其长度是相同的。另外,像用来存储用户的身份证号码等等,一般也建议使用char类型的数据。06
实际业务中的应用
在最近处理的一个业务需求中,需要减小大数据量级(千万行)下的MySQL存储空间,需求需要整合一个大宽表,表中只用包含一个用户id作为主键来加速查询,并需要将对应用户的100余个特征属性按列存储下来。对于短留存用户来说,其大部分属性都为空,并且数据中的短留存数据占比很大。 如果使用int,bigint,float,char等类型来存储对应的属性,我们一般会将其对应空值用一个无意义的数字来替代,就算对应用户的属性为null,其占用的空间也还是固定的,在实际测试中将大大占用存储空间。 这时我们就可以使用varchar可变存储长度的特点来缩小表所占用的空间。在存储过程中,由于除role_id列以外的其他列没有索引要求,所以将除主键外的其他列都建为varchar型。如果对应用户的属性为空,我们就以一个内容为空的字符串来赋值,这样varchar类型只用其实际长度来占用空间,这将比使用int型这些节省很大的存储资源。在测试后最终使用这种方法减少了60%的存储资源浪费。07
结论
频繁更新的字段用char
能够确认固定长度或者长度在固定区间内的用char
InnoDB中尽量用varchar
超过255字节的只能用varchar或者text
能用varchar的地方不用text
能够用数字类型的字段尽量选择数字类型而不用字符串类型的(电话号码),这会降低查询和连接的性能,并会增加存储开销。但是如果对应需求对索引要求不高并包含大量空值,可以使用变长的varchar存储来节省存储空间。
![6b1d4c8c224d287766ff7c9f0a47c28d.png](https://i-blog.csdnimg.cn/blog_migrate/58ee21db539a8e2a209a71ec78e7b1c7.png)
![eaabb15631a0e07dd27b1922870390db.png](https://i-blog.csdnimg.cn/blog_migrate/93cff219b91f337664b3b404df48099c.png)
![85bb155c813ef57f0169db92ef80a920.png](https://i-blog.csdnimg.cn/blog_migrate/66569bb0a1d8bc2dd7ed10e8f53f8384.png)
![8634b6cc9d02a32d680365880679a095.png](https://i-blog.csdnimg.cn/blog_migrate/770396f79e9ea8c06aeda4efdb301a11.png)