mysql 字段类型选择

mysql 字段类型介绍

整数类型：

类型	占用字节	数值范围
tinyint	1	-128~127/0~255
smallint	2	-32768~32767/0~65535
mediumint	3	-8388608~8388607/0~16777215
int/integer	4	-2147483648~2147483647/0~4294967295
bigint	8	-9223372036854775808~9223372036854775807/0~18446744073709551615

整数的宽度指示符M必须与zerofill配合使用，当数值长度小于M时，会在数值前面加上足够的0使得长度达到M。当长度大于等于M时，保持原样。但M的大小不会影响类型的数值范围，即int(4)与int(5)的数值范围大小是一样的。只是保存1245时，int(4)会1245，int(5)会01245。

定点数和浮点数

类型	占用字节
float	4
double	8
decimal/numeric	M+2

浮点数的宽度指示符M与整数的不一样，它表示数字总的个数。浮点数和定点数都可以设置小数点的位数D（D<=M）。所以（M，D）表示的数的范围为：总长度为M，小数点后保留D位，超过D位进行四舍五入处理。decimal比float更加准确，占用空间也比较少，建议使用decimal。

字符类型

类型	占用长度
char	1<=M<=255
varchar	L+1,L<=M,1<=M<=255
tinytext	L+1,L<2^8
text	L+2,L<2^16
mediumtext	L+3,L<2^24
longtext	L+4,L<2^32
tinyblob	L+1,L<2^8
blob	L+2,L<2^16
mediumblob	L+3,L<2^24
longblob	L+4,L<2^32
enum	1或2字节，依赖与枚举数量（最多2^8-1)
set	1，2，3，4或8个字节, 取决于集合成员的数量(最多64个成员）

char（M）：定长字符串，适用于长度相对固定的字符串。长度不满M的字符串，会在后面补空格，使得长度达到M。所以原字符串后面不能有空格，否则在查询出来时会被删除。

varchar（M）：变长字符串，长度L不满M时，不会添加空格，它会再用一个字节来保存长度。所有字符串后的空格会被保留下来。由于变长，所以性能比char差。

XXtext（M）：大文本字符串，不区分大小写，不能用来保存图片。在非严格模式下，字符串长度超过限制时，会进行截断处理。如果截断不是空格，则会出现警告信息。在严格模型下，则会直接报错。实际的长度还会与编码字符集有关。

XXblob（M）：二进制字符串，区分大小写，没有字符集，排序和比较基于列值字节的数值值。在非严格模式下，字符串长度超过限制时，会进行截断处理。如果截断不是空格，则会出现警告信息。在严格模型下，则会直接报错。

enum：在设计表的时候就预先定义枚举的值（值后面的空格会被删除），每次只能从其中选取一个来作为该字段的指，或者设置该字段值为null。这些枚举值会被存储为数字，并且从 1 开始用数字做索引，数字0表示空字符串。使用order by对该列进行排序时，默认会按照索引排序（null最前面，然后到空白字符，最后才是定义的值）。要设置按照ascii排序，必须使用GROUP BY CAST(col AS CHAR)或GROUP BY CONCAT(col)。

set：在设计表就预定义set的值，每次可以从其中选取1个以上的值，也可以设置为null或空白字符串。mysql会用数字保存set值，所保存值的低阶位对应第1个set成员。如果在数值上下文中检索一个值，检索的值的位设置对应组成列值的set成员。例如{‘a’,’b’,’c’}，’a’对应二进制数字001，’b’对应010，’c’对应100.所以输入十进制数字5，会组合成’a’,’c’。可以FIND_IN_SET(要搜索的值，set所在的列)函数或LIKE操作符搜索SET值

日期类型

类型	格式化	范围
time	‘HH:mm:ss’	‘-838:59:59’~’838:59:59’
year	‘yyyy’	‘1901’~’2155’
date	‘yyyy:MM:dd’	‘1000:01:01’~’9999:12:31’
datetime	‘yyyy:MM:dd HH:mm:ss’	‘1000-01-01 00:00:00’~’9999-12-31 23:59:59’
timestamp	无	‘1970-01-01 00:00:01’ UTC ~’2038-01-19 03:14:07’ UTC

time：TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔。没有冒号时，MySQL解释值时假定最右边的两位表示秒。如’1822’表示’00:18:22’。无效TIME值被转换为’00:00:00’。

year：非法值转为0000.
- 四位字符串：范围从’1901’~’2155’
- 四位数字：范围从1901~2155
- 两位字符串：范围从’00’：’99’。’00’~’69转化为’2000’~’2069’，’70’~’99’转化为’1970’~’1999’
- 两位数字：范围从1~99.1~69转化为2001~2069，70~99转为1970~1999.

选择原则

• 优先选择数字类型，其次是日期，再次是字符串
• 优先选择占用空间少的类型
• 避免使用null