选择数据类型的原则
MySQL支持多种数据类型,选择合适的数据类型存储数据对MySQL存储引擎来说至关重要,下面的一些原则可以在选择数据类型的时候做出更合适的选择。
选择最小数据类型
通常情况下,选择可以正确存储数据的最小数据类型。因为最小数据类型占用的磁盘、内存和缓存更少,执行的更快。在选择合适最小数据类型的时候,选择你认为不会超出范围的最小类型。
选择简单数据类型
简单数据类型的各种操作通常需要更少的CPU周期。
避免列值为NULL
除非非常有必要,通常情况下,需要将列值设置为NOT NULL。NULL对索引、索引统计和存储来说是比较复杂的,NULL对查询是很难优化的。
数据类型
整数类型
可以使用以下几种整数类型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分别使用8、16、24、32、64位存储空间,它们可以存储的值范围是-2^(N-1)到2^(N-1)-1,其中N是存储空间的位数。MySQL可以为整数类型指定宽度,如INT(11),但是对大多数应用是没意义的,因为它并不会限制值的范围,只是规定了MySQL的一些交互工具显示字符的个数,对于存储和计算来说,INT(1)和INT(11)是相同的。
实数类型
实数类型不仅用于存储带小数的数据,还可以用DECIMAL存储比BIGINT还大的整数。FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算,DECIMAL用于存储精确的小数。FLOAT使用4个字节存储数据,DOUBLE使用8个字节存储数据,MySQL5.0和更高的版本中DECIMAL允许最多65个数字。
字符串类型
VARCHAR用于存储可变长字符串,它比定长类型更节省空间,因为它仅使用必要的空间。它需要额外的空间存储字节长度,如果列最大长度小于或等于255使用一个字节,否则使用两个字节。
CHAR是定长的,长度不够会以空格填充,适用于存储较短的数据,更适用于所有列值都接近同一个长度的情况。
BLOB使用二进制方式存储很大数据量的字符串。
TEXT使用字符方式存储很大数据量的字符串。
日期和时间类型
DATETIME能保存的范围值从1001年到9999年,精度为秒,它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关,使用8个字节的存储空间。
TIMESTAMP保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它使用4个字节的存储空间,因此时间范围比DATETIME小得多,只能表示从1970年到2038年,并且它显示的值依赖于时区,所以在使用这个类型的时候,需要正确设置机器时区。它的列值默认是非空的,并且默认情况下具有自动更新的特性,之前也写过分析这个特性的内容,有兴趣的可以翻看历史文章。
位数据类型
在MySQL5.0之前,BIT和TINYINT是同义的,但是在5.0及更高的版本中,它们是完全不同的数据类型。BIT(1)定义了一个包含单个位的字段,BIT(2)存储两个位,BIT最大长度为64个位。MySQL把BIT当做字符串类型,而不是数字类型,将检索BIT(1)的值时,结果是一个包含二进制0或1值的字符串,而不是ASCII码的“0”或“1”。