MySQL数据类型选择
一 .选择原则
更小的通常更好:一般情况下选择可以正确存储数据的最小数据类型。越小的数据类型通常更快,占用磁盘,内存和CPU缓存更小。
简单就好:简单的数据类型的操作通常需要更少的CPU周期。例如:整型比字符操作代价要小得多,因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
尽量避免NULL:尽量制定列为NOT NULL,除非真的需要NULL类型的值。因为可能为NULL列使得索引,索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。
二.选择具体数据类型
在选择列的数据类型时需要先选定合适的大类型,如:数字,字符串,时间等。
整数类型
有两种类型的数字:整数和实数。如果需要存储整数,可以选择一下集中数据类型:
数据类型 | 长度/字节 | 范围 | 说明 |
---|---|---|---|
TINYINT | 1 | -2^7—2^7-1 | 整数类型可选UNSIGND属性 |
SMALLINT | 2 | -2^15—2^15-1 | 表示不允许为负值 |
MEDIUMINT | 3 | -2^23—2^23-1 | 这大致可以使整数上限提高一倍 |
INT | 4 | -2^31—2^31-1 | 例如UNSIGNED可以使TINYINT |
BIGINT | 8 | -2^63—2^63-1 | 存储的范围是0—255 |
*实数类型
实数是带有小数部分的数字。然而它们不只是存储小数部分;也可以是DECIMAL存储比BIGINT还大的整数。MYSQL即支持精确类型,也支持非精确存储。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。具体计算规则取决于所使用平台的实现。
数据类型 | 长度 | 范围 | 说明 |
---|---|---|---|
FLOATR | 4字节 | — | 浮点数 |
DOUBLE | 8字节 | — | 浮点数 |
DECIMAL | 最多65数字(5.0以上版本) | — | 定点数 |
MySQL使用DOUBLE作为内部浮点计算的类型。注意,DECIMAL需要额外的空间和计算开销,所以只要要求尽量对小数进行精确时才使用DECIMAL,例如在存储财务数据时,可以用BIGINT来替代它来实现,只需要将数据乘以相应的倍数即可。
*字符串类型
MySQL支持多种字符串类型。从4.1版本开始,每个字符串列可以定义自己的字符集和校对规则(排序规则),这些东西会在很大程度上影响性能。
VARCHAR和CHAR类型
VARCHAR和CHAR时最主要的两种字符串类型。
VARCHAR:适用于存储变长的字符串,是最常见的字符串数据类型。它比定常更加节省空间,因为它仅需要使用必要的额外空间(如果MySQL表使用ROW_FORMAT=FIXED创建的话,会使用定长)。
适合使用该类型的情况:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了UTF-8这样复杂的字符集,每个字符的使用不同字节数进行存储。
注:5.0以上版本会保留末尾空格,更早的版本会删除末尾空格。InnoDB存储引擎会把过长的VARCHAR转换为BLOB类型。
CHAR:CHAR是定长字符串类型,MySQL总是根据定义的类型进行分配足够的空间。当存储CHAR时MySQL会默认删除行尾空格。因为CHAR值通常需要用空格进行填充以方便比较。
CHAR适合存储很短的字符串,或者所有长度都接近一个长度。
对于经常变更的数据CHAR比较VARCHAR要好,因为定长的CHAR类型不容易产生碎片。
对于更短的列CHAR比VARCHAR要更加节省空间,因为VARCHAR总是需要一个额外的字节来存储长度。
BLOB和TEXT类型
BLOB和TEXT是用来存储很大的数据来设计的字符串数据类型,分别采用二进制和字符方式进行存储。
实际上他们时属于两种不同数据类型家族:字符类型的时TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT;对应的二进制类型是TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB。BLOB是SMALLBLOGB的同义词,TEXT是SMALLTEXT的同义词。
与其他数据类型不同,它们被当作单独的对象进行处理。当BLOB和TEXT值太大时InnoDB会使用专门的外部区域来进行存储,此时每个值只需要1—4个字节在行内存储一个指针即可,然后在外部存储区域存储实际的值。
BLOB和TEXT的仅有的区别在于BLOB类型存储的时二进制数据,没有排序规则或字符集,而TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列进行排序时与其他类型时不同的,它只对每个列的最前max_sort_length字节而不是整个字符串做排序。
MySQL不能将BLOB和TEXT列全部长度进行字符串进行索引,也不能使用这些索引消除排序。
日期和时间类型
MySQL可以使用很多类型来保存日期和时间值,例如YEAR和DATE.MySQL能存储最小粒度为秒。但是可以使用微秒级的力度进行运算。
MySQL提供了两种相似的数据类型:
DATETIME和TIMESTAMP
DATETIME
能保存1001到9999年,精度为秒。格式为YYYY-MM-DD HH:MM:SS与时区无关,使用八个字节的存储空间。
TIMESTAMP
时间戳,正如名字一样。它能保存从1970年1月1号午夜(格林尼治标准时间)。它只使用四个字节的存储空间只能表示1970到2038年。
TIMESTAMP显示的值依赖于时区。MYSQL服务器,操作系统,以及客户端连接都有时区设置。因此存储值为0时在不同的时区显示值会有差别。
注:通常情况下应尽量使用TIMESTAMP,因为它比DATETIME效率更高。如果需要存储更小粒度的时间,可以用BIGINGT或者转换成DOUBLE类型来进行存储。
ENUM数据类型
枚举数据类型在内存中非常紧凑,会根据列表值的数量压缩到一个或两个字节。MySQL自身会维护一个查找表。例如:
CREATE TABLE enum_test(
gender ENUM("男","女","其他") NOT NULL
);
INSERT INTO enum_test values("男","女","其他");
在数字上下文环境检索时可以看到
SELECT gender FROM enum_test;
gender+0 |
---|
1 |
2 |
3 |
注意:枚举时按照内部的数字而不是定义的字符串进行排序的。
mysql> SELECT gender FROM enum_test ORDER BY gender;
gender |
---|
男 |
女 |
其他 |
在很多情况下我们可以使用ENUM类型来替代字符串类型。但是如果不宜使用数字类型作为枚举常量,这种双重性容易导致混乱。枚举最不好的地方就是,字符串列表是固定的,添加或删除字符串必须使用ALTER TABLE。
特殊数据类型
某些类型的数据不直接与内置类型一直。如用BIGINT和DOUBLE来存储低于秒级精度的时间戳。还有就是用一个32位无符号整数存储IP地址。MySQL提供了INET_ATON()和INET_NTOA函数进行两种表示方式之间转换。
INET_ATON(3232235521) ——> 192.168.0.1
INET_NTOA(192.168.0.1)——>3232235521
三.总结
以上只是常见的数据类型,其他类型就不一一赘述。本人个人博客站点:www.mycookies.cn(适合初级JAVA学习者学), 欢迎大家来指点指点哦。源码地址:https://github.com/liqianggh/blog