选择合适的数据类型
我们会经常遇见的一个问题就是,在建表时如何选择合适的数据类型,通常选择合适的数据类型能够提高性能、减少不必要的麻烦,下面我们就来一起探讨一下,如何选择合适的数据类型。
CHAR 和 VARCHAR 的选择
char 和 varchar 是我们经常要用到的两个存储字符串的数据类型,char 一般存储定长的字符串,它属于固定长度的字符类型,比如下面
值 | char(5) | 存储字节 |
'' | ' ' | 5个字节 |
'cx' | 'cx ' | 5个字节 |
'cxuan' | 'cxuan' | 5个字节 |
'cxuan007' | 'cxuan' | 5个字节 |
可以看到,不管你的值写的是什么,一旦指定了 char 字符的长度,如果你的字符串长度不够指定字符的长度的话,那么就用空格来填补,如果超过字符串长度的话,只存储指定字符长度的字符。
❝这里注意一点:如果 MySQL 使用了非
❞严格模式
的话,上面表格最后一行是可以存储的。如果 MySQL 使用了严格模式
的话,那么表格上面最后一行存储会报错。
如果使用了 varchar 字符类型,我们来看一下例子
值 | varchar(5) | 存储字节 |
'' | '' | 1个字节 |
'cx' | 'cx ' | 3个字节 |
'cxuan' | 'cxuan' | 6个字节 |
'cxuan007' | 'cxuan' | 6个字节 |
可以看到,如果使用 varchar 的话,那么存储的字节将根据实际的值进行存储。你可能会疑惑为什么 varchar 的长度是 5 ,但是却需要存储 3 个字节或者 6 个字节,这是因为使用 varchar 数据类型进行存储时,默认会在最后增加一个字符串长度,占用1个字节(如果列声明的长度超过255,则使用两个字节)。varchar 不会填充空余的字符串。
一般使用 char 来存储定长的字符串,比如「身份证号、手机号、邮箱等」;使用 varchar 来存储不定长的字符串。由于 char 长度是固定的,所以它的处理速度要比 VARCHAR 快很多,但是缺点是浪费存储空间,但是随着 MySQL 版本的不断演进,varchar 数据类型的性能也在不断改进和提高,所以在许多应用中,VARCHAR 类型更多的被使用。
在 MySQL 中,不同的存储引擎对 CHAR 和 VARCHAR 的使用原则也有不同
- MyISAM:建议使用固定长度的数据列替代可变长度的数据列,也就是 CHAR
- MEMORY:使用固定长度进行处理、CHAR 和 VARCHAR 都会被当作 CHAR 处理
- InnoDB:建议使用 VARCHAR 类型
TEXT 与 BLOB
一般在保存较少的文本的时候,我们会选择 CHAR 和 VARCHAR,在保存大数据量的文本时,我们往往选择 TEXT 和 BLOB;TEXT 和 BLOB 的主要差别是 BLOB 能够保存二进制数据
;而 TEXT 只能保存字符数据
,TEXT 往下细分有
- TEXT
- MEDIUMTEXT
- LONGTEXT
BLOB 往下细分有
- BLOB
- MEDIUMBLOB
- LONGBLOB
三种,它们最主要的区别就是存储文本长度不同和存储字节不同,用户应该根据实际情况选择满足需求的最小存储类型,下面主要对 BLOB 和 TEXT 存在一些问题进行介绍
TEXT 和 BLOB 在删除数据后会存在一些性能上的问题,为了提高性能,建议使用 OPTIMIZE TABLE
功能对表进行碎片整理。
也可以使用合成索引来提高文本字段(BLOB 和 TEXT)的查询性能。合成索引就是根据大文本(BLOB 和 TEXT)字段的内容建立一个散列值,把这个值存在对应列中,这样就能够根据散列值查找到对应的数据行。一般使用散列算法比如 md5() 和 SHA1() ,如果散列算法生成的字符串带有尾部空格,就不要把它们存在 CHAR 和 VARCHAR 中,下面我们就来看一下这种使用方式
首先创建一张表,表中记录 blob 字段和 hash 值
这是合成索引的例子,如果要对 BLOB 进行模糊查询的话,就要使用前缀索引。
其他优化 BLOB 和 TEXT 的方式:
- 非必要的时候不要检索 BLOB 和 TEXT 索引
- 把 BLOB 或 TEXT 列分离到单独的表中。
浮点数和定点数的选择
浮点数指的就是含有小数的值,浮点数插入到指定列中超过指定精度后,浮点数会四舍五入,MySQL 中的浮点数指的就是 float
和 double
,定点数指的是 decimal
,定点数能够更加精确的保存和显示数据。下面通过一个示例讲解一下浮点数精确性问题
首先创建一个表 cxuan006 ,只为了测试浮点数问题,所以这里我们选择的数据类型是 float
日期类型选择
在 MySQL 中,用来表示日期类型的有 「DATE、TIME、DATETIME、TIMESTAMP」,在
这篇文中介绍过了日期类型的区别,我们这里就不再阐述了。下面主要介绍一下选择
- TIMESTAMP 和时区相关,更能反映当前时间,如果记录的日期需要让不同时区的人使用,最好使用 TIMESTAMP。
- DATE 用于表示年月日,如果实际应用值需要保存年月日的话就可以使用 DATE。
- TIME 用于表示时分秒,如果实际应用值需要保存时分秒的话就可以使用 TIME。
- YEAR 用于表示年份,YEAR 有 2 位(最好使用4位)和 4 位格式的年。默认是4位。如果实际应用只保存年份,那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间,还能提高表的操作效率。
MySQL 字符集
下面来认识一下 MySQL 字符集,简单来说字符集就是一套文字符号和编码、比较规则的集合。1960 年美国标准化组织 ANSI 发布了第一个计算机字符集,就是著名的 ASCII(American Standard Code for Information Interchange)
。自从 ASCII 编码后,每个国家、国际组织都研究了一套自己的字符集,比如 ISO-8859-1
、GBK
等。
但是每个国家都使用自己的字符集为移植性带来了很大的困难。所以,为了统一字符编码,国际标准化组织(ISO)
指定了统一的字符标准 - Unicode 编码,它容纳了几乎所有的字符编码。下面是一些常见的字符编码
字符集 | 是否定长 | 编码方式 |
ASCII | 是 | 单字节 7 位编码 |
ISO-8859-1 | 是 | 单字节 8 位编码 |
GBK | 是 | 双字节编码 |
UTF-8 | 否 | 1 - 4 字节编码 |
UTF-16 | 否 | 2 字节或 4 字节编码 |
UTF-32 | 是 | 4 字节编码 |
对数据库来说,字符集是很重要的,因为数据库存储的数据大多数都是各种文字,字符集对数据库的存储、性能、系统的移植来说都非常重要。
MySQL 支持多种字符集,可以使用 show character set;
来查看所有可用的字符集
或者使用
select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;
来查看。
使用 information_schema.character_set
来查看字符集和校对规则。
</div>