MySql 数据类型

选择优化的数据类型

• 更小的通常更好。更小的数据类型通常更快、占用更少的磁盘、内存和CPU缓存
• 简单就好。简单的数据类型的操作通常需要更少的CPU周期，例如整形比字符串操作代价更低
• 尽量避免NULL。通常最好指定列为NOT NULL，因为NULL是列的默认属性。如果查询中包含可为NULL的列，对MySql来说更难优化

整数类型

名称	占用内存(字节)
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT	4
BIGINT	8

• 整数类型具有可选的UNSIGNED属性，表示不允许负数。示例:INT UNSIGNED
• MySql可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的:这并不会限制值的合法范围，只是规定了MySql的一些交互工具用来显示字符的个数，对于存储于计算来说，INT(1)和INT(20)是一样的

实数类型

名称	占用内存(字节)
DECIMAL	[1, 65]
FLOAT	4
DOUBLE	8

• DECIMAL用于存储精确的小数，运算速度没有浮点运算快
• DECIMAL具有UNSIGNED属性
• DECIMAL(P,D)
  P是表示有效数字数的精度。 P范围为[1, 65]，默认为10
  D是表示小数点后的位数。 D的范围是[0, 30]。MySQL要求D小于等于P。
  例:DECIMAL(6,2)最多可以存储6位数字，小数位数为2位; 因此范围是从-9999.99到9999.99。
• DOUBLE、FLOAT也可以指定精度，但是不建议

字符串类型

名称	备注
VARCHAR	vachar(N) 用来存储非二进制字符串，插入时，对于少于N个字符的不填补空格，查询时，尾部的空格不会被丢弃掉
CHAR	char(N)用来存储非二进制字符串，插入时，对于少于N个字符的会自动在尾部加空格，查询时，尾部的空格就会被丢弃掉
BINARY	binary(N)存储二进制字符串，插入进，少于N个字节的会自动在尾部加0x00，取出时，所有的字节都保留，返回定义长度的字节长度
VARBINARY	varbinary在插入不会去填补0x00字节，查询的时候也不会丢弃任何字节
BLOB	用于存储大型二进制数据。TINYBLOB(255)，BLOB(65535)，MEDIUMBLOB(16MB-1)，LONGBLOB(4GB-1)
TEXT	用于存储大型字符串。TINYTEXT(255)，TEXT(65535)，MEDIUMTEXT(16MB-1)，LONGTEXT(4GB-1)

• MySQL单行最大限制为65535，不包括TEXT、BLOB。此条规定间接限制了VARCHAR的最大长度
• 最好避免使用BLOB与TEXT

使用枚举代替字符串类型

• 枚举列可以把一些不重复的字符串存储成一个预定义的集合，在内部会将每个值保存为整数，且排序方式也是按照整数的规则进行的
• 枚举最不好的地方是字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE
• 由于MySql把每个枚举值保存为整数，并且必须进行查找才能转换为字符串，所以枚举列有一些开销

create table sznTable(v enum('banana', 'apple'));
insert into sznTable(v) values('apple'),('banana');

select * from sznTable	select v + 1 from sznTable	select * from sznTable order by v
apple	2	banana
banana	3	apple

日期与时间类型

名称	备注
DATATIME	此类型能保存大范围的值，从1001年到9999年，精度为秒 把时间封装到YYYYMMDDHHMMSS的整数中 与时区无关 使用8字节的存储空间 默认情况下，MySql以一种可排序、无歧义的格式显示此类型的值，例如:"2018-09-10 20:29:00"
TIMESTAMP	保存了从 1970年1月1日 0点0分0秒 以来的秒计数，和UNIX时间戳相同 使用4字节的存储空间 提供FROM_UNIXTIME()、UNIX_TIMESTAMP，进行UNXI时间戳与日期的转换 与时区相关

TIMESTAMP时间戳在创建的时候可以有多重不同的特性:

• 在创建新记录和修改现有记录的时候都对这个数据列刷新：
  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
• 在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新TIMESTAMP DEFAULT CURRENT_TIMESTAMP
• 在创建新记录的时候把这个字段设置为0，以后修改时刷新它：
  TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
• 在创建新记录的时候把这个字段设置为给定值，以后修改时刷新它：
  TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP
• 默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySql会设置这个列的值为当前时间，更新时也是如此

create table sznTest
(
    t0 timestamp,
    t1 timestamp default current_timestamp on update current_timestamp,
    t2 timestamp default current_timestamp,
    t3 timestamp on update current_timestamp,
    t4 timestamp default '2018-09-10 20:54:00' on update current_timestamp
);

位数据类型

名称	备注
BIT	可以使用BIT列存储一个或多个true/false值 BIT(1)定义一个包含单个位的字段，以此类推，最大长度是64 MySql将BIT当做字符串类型而非数据类型，然而在数字上下文中会将其转为数字
SET	最多保存64个元素的集合 在MySql中是以一系列打包的位的集合来表示的，一般无法在SET列中通过索引查找 主要缺点:改变列的定义时需要用ALTER TABLE，代价可能会很高 1～8成员的集合，占1个字节 9～16成员的集合，占2个字节 17～24成员的集合，占3个字节 25～32成员的集合，占4个字节 33～64成员的集合，占8个字节

create table t (v bit(8));
insert into t (v) values(b'00111001');

select * from t	select v + 0 as value from t
9	57

• 不同于枚举，枚举列的值只能是预设值中的一个，而SET列的值可以是预设值中的多个的组合

create table t (v set('szn', 'slz', 'szy'));
insert into t(v) value('szn');
insert into t(v) values('szn,slz');
insert into t(v) values('szn,szy');

select * from t	select v + 0 as value from t
szn	1
szn,slz	3
szn,szy	5