数据类型的选择
- 更小的通常更好
- 简单就好
- 尽量避免NULL
整数类型
- tinyint 占空间8位
- smallint 占空间16位
- mediumint 占空间24位
- int 占空间32位
- bigint 占空间64位
它们可以储存值的范围为-2n-1 ~ 2n-1-1
实数类型
- float 4个字节
- double 8个字节
- decimal decimal(18,9),每4个字节存储9个数字,小数点占一个字节,共9个字节
float和double通常使用比decimal更少的空间,但是容易丢失精度,所以在考虑精确计算是应使用decimal,其他情况可考虑用float或double来实现更少的空间占有
字符串类型
- varchar 用于存储可变长字符串,会使用1-2额外字节记录,它比定长类型更节省空间。需要1或2个额外记录字符串长度,varchar节省了存储空间,对性能有一定的帮助,但是由于行是变长的,在update的时候,可能使行变得比原来更长,需要做额外的工作,InnoDB需要分裂页来使行可以放进页内,
- char 类型是定长的,存储char值时,mysql会删除所有的末尾空格char值会根据需要采取空格进行填充以方便比较
对于固定长度,经常变更的数据,char会更适合
对于只有少数字符较长的情况或者数据长度不一的数据,varchar会更合适。
与char和varchar类似的类型还有binary和varbinary,它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似,但是二进制字符串存储的是字节码而不是字符,填充也不一样,mysql填充binary采用的是\0而不是空格
避免乱分配varchar的长度
使用varchar(5)和varchar(200)存储’hello’的空间开销是一样的,但是在使用内存临时表进行排序或操作时,更长的列会消耗更多的空间
BLOB和TEXT类型
- TEXT tinytext,smalltext,text,mediumtext,longtext
- BLOB tinyblob, smallblob,blob,mediumblob,longblob
存储方式:mysql把每个blob和text值当作一个独立对象处理。当blob和text值太大时,InnoDB会使用专门的“外部存储区域”来进行存储,此时每个值在行内需要1-4个字节存储一个指针,然后在外部存储区域存储实际的值
排序:blob和text列进行排序与其他类型时不同的:它只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面的一小部分字符串,则可以减少max_sort_length的配置,或者使用order by sustring(column,length)
日期和时间类型
datetime
这个类型能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。
TIMESTAMP
TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数,它和unix时间戳。timestamp只使用4个字节的存储空间,因此它的范围比datetime小很多,只能表示从1970年到2038年。MySQL提供了from_unixtime() 函数把unix时间戳转换为日期,并提供了unix-timestamp()函数把日期转换为Unix时间戳。
-
除了特殊行为之外,通常也应该尽量使用TIMESTAMP,因为它比DATETIME空间效率更高。有时候人们会将Unix时间戳存储为整数值,但这不会带来任何收益。用整数保存时间戳的格式通常不方便处理,所以我们不推荐这样做。
-
如果要存储不比秒更小的细粒度的日期和时间值,可以用BIGINT类型存储微秒级别的时间戳,或者使用DOUBLE存储秒之后的小数部分
标识符
为标识列选择合适的数据类型非常重要。一般来说很有可能用标识列与其他值比较,或者通过标识列寻找其他列。标志列也可能在另外的表作为外键使用。一旦选定了一种类型,要确保所有关联表中都使用同样的类型,这样可避免隐式转换带来的开销