MySQL的数据类型

概述

MySQL支持的数据分为3部分：

• 数值类型
• 日期时间类型
• 字符串类型

先扔一张大图做总结：

数值类型

mysql支持所有标准的SQL中的数值类型

1.整型数值

整数类型字节数最小值 ~ 最大值tinyint1-128~127 或 0-255smallint2-32768~32767 或 0~65535mediumint3-8388608~8388607 或 0~1677215int4-2147483648~2147483647 或 0~4294967295bigint8-9223372036854775808~9223372036854775807 或 0~18446744073709551615

• 对于整型数值，mysql支持在类型名称后面的小括号内指定显示宽度，例如int(5)表示当数值宽度小于5位时候在数值前面填满宽度，一般配合zerofill属性使用。
• 如果一个列指定为zerofill,则MySQL自动为该列添加unsigned属性。
• 整型的auto_increment用于产生唯一标识符或顺序值。

2.浮点数类型

浮点数类型字节数最小值~最大值float4±1.175494351E-38 ~ ± 3.402823466E+38double8±2.2250738585072014E-308 ~ ±1.7976931348623157E+308

3.定点数类型

定点数类型字节数描述dec(m,d)m+2最大取值范围与double相同，给定decimal的有效值取值范围由m和d决定

4.位类型

位类型字节数最小值~最大值bit(m)1~8bit(1) ~ bit(64)

• 小数点表示（浮点数和定点数），定点数（dec）在MySQL内部以字符串形式存放，比浮动数更加精确，适合用来表示货币等精度高的数据。
• 浮点数和定点数可以用在类型名后面加“(m,d)”来表示，其意义为一共显示m位数字，其中d位位于小数点后面，m和d又称为精度和标度。
• 浮点数不写m,d，会自动四舍五入；定点数不写m,d，按照默认值dec(10,0)来操作。
• bit 直接select看不到结果的，可以用bin()或hex()进行读取。
• 在数据量较大时、建议把实数类型转为整数类型。原因很简单：1. 浮点不精确；2.定点计算代价昂贵。例如：要存放财务数据精确到万分之一、则可以把所有金额乘以一百万、然后存在BIGINT下。
• 图片：

日期时间类型

时间日期类型字节数最小值~最大值date41000-01-01 ~ 9999-12-31datetime81000-01-01 00:00:00 ~ 9999-12-31 23:59:59timestamp419700101080001 ~ 2038年某个时刻time3-838:59:59 ~ 838:59:59year11901 ~ 2155

• mysql中用now()写入当前时间。

字符串类型

字符串类型字节数描述char(m)mm为0 ~ 255之间的整数varchar(m)值长度+1m为0~65535之间的整数tinytext值长度+2允许长度0~255字节text值长度+2允许长度0~65535字节mediumtext值长度+3允许长度0~167772150字节longtext值长度+4允许长度0~4294967295字节   binary(m)m允许0~m个字节定长的字符串varbinary(m)值长度+1允许0~m个字节变长的字符串tinyblob值长度+1允许长度0~255字节blob值长度+2允许长度0~65535字节mediumblob值长度+3允许长度0~167772150字节longblob值长度+4允许长度0~4294967295字节   enum1或21~255个成员需要1个字节存；255~65535个成员，2个字节存set1/2/3/4/8类似enum,set一次可以选取多个成员，而enum只能一个

• char会删除字符串尾部的空格，varchar不会。
• binary类似于char,binary只能保存二进制字符串。
• enum类型忽略大小写。
• text与blob区别：blob保存二进制数据；text保存字符数据，有字符集。
• text和blob不能有默认值。
• 图片：

选择合适的数据类型

1. char是固定长度，所以它的处理速度比varchar快得多，但缺点是浪费存储空间，不能在行尾保存空格。在MySQL中，MyISAM建议使用固定长度代替可变长度列；InnoDB建议使用varchar类型，因为在InnoDB中，内部行存储格式没有区分固定长度和可变长度。
2. text与blob主要区别是text用来保存字符数据（如文章，日记等），blob用来保存二进制数据（如照片等）。blob与text在执行了大量删除操作时候，有性能问题（产生大量的“空洞“），为提高性能建议定期optimize table 对这类表进行碎片整理。
3. 定点与浮点数，对于精度要求较高的地方，使用定点数。
4. 日期选择原则是根据实际需求选择满足应用的最小存储日期类型。