[Mysql连载二]mysql支持的数据类型及最佳实践

Mysql支持的数据类型及各个类型的特点应用场景,及各类型使用注意事项,最后会提供数据类型使用的最佳实践。

数值

类型 大小 范围-有符号 范围-无符号 用途
tinyint 1字节 (-128, 127) (0, 255) 小整数
smallint 2字节 (-32768, 32767) (0, 65535) 大整数值
mediumint 3字节 (-8388608, 8388607) (0, 16777215) 大整数值
int 4字节 (-2147483648, 2147483647) (0, 4294967295) 大整数值
bigint 8字节 (-9233372036854775808, 9223372036854775807) (0, 18446744073709551615) 极大整数值
float 4字节 单精度浮点数值
double 8字节 双精度浮点数
decimal 对decimal(M,D)如M>D为M+2否则D+2 定点小数
  • Mysql可以显示宽度指示器的形式对SQL标准进行扩展如int(6),6即宽度指示器,该宽度并不影响int列存储字段的大小也就是超过6位也不会截取依然后存储;此处宽度指示器的作用在于该字段是否有zerofill如果有就未满6位部分用0补充。
  • 浮点数存在误差问题,对货币等精度敏感的数据应该使用定点数表示或存储

字符串

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535字节 变长字符串
TINYBLOB 0-255字节 不超过255个字符串的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65535字节 二进制形式长文本数据
TEXT 0-65535字节 长文本数据
MEDIUMBLOB 16M 二进制形式中长度文本
MEDIUMTEXT 16M 中等长度文本数据
LOGNGBLOB 4G 二进制极大数据
LONGTEXT 4G 极大文本
  • 字段值定长时使用CHAR使性能高于VARCHAR
  • 字段值不定长使用VARCHAR在空间暂用性能能上由于CHAR
  • CHAR与VARCHAR不同在于MYSQL处理指示器的方式不同,CHAR把它视为值大小,长度不足空格补齐。VARCHAR处理把它视为最大值,使用时只使用字符串的实际长度VARCHAR类型总会多占一个字节,如varchar(4)存满数据会占用5个字节。
  • 非严格模式下超过指示器的数据会被截断,严格模式下不予存储。
  • BLOB和TEXT值在执行大量删除更新时会产生空洞,以后填入的长度不同,为提高性能定期使用 optimize table 对数据表进行碎片整理
  • 利用合成索引对BLOB或TEXT精确检索,为提高性能对字段做md5、sha1等散列后存储,然后基于散列值检索。
  • 避免查询检索BLOB或TEXT,为了不浪费网络流量减少select * 操作
  • BLOB与TEXT尽可能拆表存储,以避免select * 操作或方便optimize table操作

时间类型

类型 大小(字节) 范围 格式 用途
DATE 3 1000-01-01-9999012031 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00-9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 日期和时间
TIMESTAMP 8 1970-01-01 00:00:00/2037 YYYYMMDD HHMMSS 时间或日期或时间戳

最佳实践

  • 常用类型varchar(256) char(32) text及有符号tinyint(4) int(11)无符号下tinyint(3)int(10)
  • 符合类型使用tinyint代替
  • 手机号推荐使用char(11)在查询效率上更高
  • 最佳实践name char(20);price decimal(7, 3);no smallint(5) unsigned;content text;pass char(32);time int(10) unsigned;email char(32);
  • 数据类型选择合理范围内最小的,可以大大减少磁盘空间及IO读写,减少内存占用,减少cpu占用
  • 选择最简单的数据类型,如时间戳不用timestamp而是用int
  • 不使用null因为MYSQL对null字段索引优化不加增加计算难度,可以使用0或空字符串替换