Mysql支持多种数据类型,我们选择数据类型时应遵循以下几条原则:
- 选择可以正确存储数据的最小数据类型。更小的数据类型意味着更少的硬盘储存空间,更少的内存以及cpu。
- 选择更简单的数据类型,操作简单的数据类型一般需要更少的CPU时间。例如:操作整数比字符串代价更低,因为字符串的字符集以及排序规则比整数更复杂。
- 尽量避免null,可为null的列使索引,索引统计和值比较都变得更加复杂。而且可为null的列会使用更多的存储空间,在mysql中需要特殊处理。
- 数据类型介绍
1. 整数类型
TINYINT | SMALLINT | MEDIUMINT | INT | BIGINT | |
储存大小 | 8 | 16 | 24 | 32 | 62 |
范围 | -2(n-1)次方--2(n-1)次方 |
对于整数有unsigned(无符号)属性,可以使正数的上限提高一倍。
例:TINYINT的范围是(-128至128),TINYINT UNSIGNED的范围是(0至256)
2. 实数类型(带有小数部分的数字)
(1) DECIMAL:用于存储精确的小数,并且支持精准计算,由于该类型不是CPU支持的计算类型,所以精准计算由mysql实现,因此会比默认的浮点运算慢一点。DECIMAL使用每4字节存储9位数字。
例:DECIMAL(18,9) 该类型可以存储小数点前九位,以及小数点后九位。该类型共使用9个字节。小数点前占4字节,小数点后占4字节。小数点占1字节。
(2)FLOAT和DOUBLE,浮点数在储存同样范围的值时,比decimal更节省空间,其中float占4字节,double占8字节。
3. 字符串类型
(1)VARCHAR和CHAR
VARCHAR存储可变长字符串,在储存时只使用必要的空间,但是需要1至2额外字节记录字符串长度,如果列的最大长度小于等于255则使用1字节否则使用2字节。
例:VARCHAR(10) 需要11字节,VARCHAR(1000)需要1002字节。
因为VARCHAR是变长的所以UPDATE时有可能使得行变得更长,也就需要额外的工作。也可能变得更短出现碎片。
VARCHAR适用情景:
1. 字符串的最大长度比平均长度大很多。
2.使用类似于utf8这样的变长字符集。
3. 列很少更新。
当我们为VARCHAR分配空间时应该尽量分配够用的最小空间,虽然在硬盘中存储是一样的,但是在内存中却不一样,Mysql会分配固定大小的内存块来保存内部值。
CHAR是定长类型,Mysql总是根据定义的字符串长度分配足够的空间。CHAR会删除字符串结尾的空格。
类似于CHAR和VARCHAR的还有BINARY和VARBINARY。只不过他们两个存储的是二进制字符串。
(2)BOLB和TEXT
这两种数据类型是为了储存很大的数据而设计的字符串数据类型。
Mysql把每个blob和text值当做独立的对象处理,存储引擎在储存时通常会作特殊处理,当值太大时,innodb会使用专门的“外部”存储区域来进行存储,此时每个值只需要一个指针即可。
排序:这俩种数据类型的排序只对每个列得最前max_sort_length个字符进行排序,。
(3)枚举
Mysql也支持枚举类型,使用该类型可以将一些不重复的字符串储存在一个预定的集合中。其在存储时会被压缩到一个或两个字节中。
4. 日期和时间类型
(1)DATATIME
可以保存1001年到9999年的时间,精度为秒,与时区无关,使用8个字节存储,格式为:YYYYMMDDHHMMSS。
(2)TIMESTAMP
该类型保存的是19700101000000以来的秒数,与unix时间戳相同,只是用4字节存储。范围是1970年到2038年。该类型在不同的时区访问行为不同。需要注意。
除了与时区有关,timestamp空间更小,效率更高,所以应该尽量使用该类型。
5. 位数据类型
BIT: Mysql把bit当做字符串类型,而不是数字类型。当上下文为数字时,结果将转换为数字。