MySQL常用数据类型详解

MySQL 提供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。

数值类型

数值类型：tinyint、smallint、mediumint、int/integer、bigint、float、double、decimal

并增加了 bit 类型，用来存放位数据。

注意：

1. 在整数类型中，如果保存的数值超出类型范围，会发生 Out of range 错误提示。为了避免此类问题发生，在选择数据类型时要根据应用的实际情况确定其取值范围。
2. 对于整型数据，MySQL 还支持在类型名称后面的小括号内指定显示宽度，例如 int(5) 表示当数值宽度小于 5 位的时候在数字前面填满宽度，如果不显示指定宽度则默认为 int(11)。一般配合 zerofill 使用，顾名思义，zerofill 就是用 “0” 填充的意思，也就是在数字位数不够的空间用字符 “0” 填满。如：alert table test modify id int zerofill;。如果这时插入大于宽度限制的值，会发生截断或者插不进去报错吗？答案是：不会对插入数据造成任何影响，还是按照类型的实际精度进行保存，只不过这时宽度已经任何意义，左边不填充0而已。
3. 所有的整数类型都有一个可选属性 UNSIGNED (无符号)，如果需要在字段里保存非负数或者需要较大上限值的时候，可以选用此选项，它的下限取 0 ，上限取原值的 2 倍。如果字段被指定为 zerofill ，则自动为该列添加 UNSIGNED 属性。
4. 整数类型还有另一个属性：AUTO_INCREMENT。在需要产生唯一标识或顺序值时，可利用此属性，这个属性只用于整数类型。一个表最多只能有一个 AUTO_INCREMENT 列，对于任何想要使用 AUTO_INCREMENT的列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键。
5. 浮点数（float、double）和定点数（decimal）都可以用类型名称后加 (M,D)的方式来进行表示，(M,D)表示该值一共显示M位数字（整数位+小数位），其中D位位于小数点后面，M和D又称为精度和标度。
6. 如果浮点数不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值 decimal(10,0) 来进行操作，并且如果数据超越了精度和标度值，系统则会报错。
7. bit 类型，用于存放位字段值，bit(m) 可以用来存放多位二进制数，m 范围从 1~ 64，如果不写则默认为1位(即只能保存0或者1)。数据插入时bit型字段时，首先转换为二进制，如果位数允许，将成功插入；如果位数小于实际定义的位数，则插入失败。对于位字段，直接使用 select 命令将不会看到结果，可以用 bin() （显示为二进制格式）或者 hex() （显示为十六进制格式）函数进行读取。

日期时间类型

日期时间类型：datetime、date、timestamp、time、year
每个时间类型有一个有效值范围和一个零值，当指定不合法的mysql不能表示的值时，使用零值

数据类型	零值表示
DATE	0000-00-00
TIME	00:00:00
YEAR	0000
DATETIME	0000-00-00 00:00:00
TIMESTAMP	00000000000000

注意：

1. YEAR 有 2 位或 4 位格式的年，默认是 4 位格式。在 2 位格式中，允许的值是 70 ~ 69 ，即 1970 ~ 2069年（MySQL 5.5.27以后就不支持了）
2. 如果需要经常插入或者更新日期为当前系统时间，则通常使用 TIMESTAMP 来表示。TIMESTAMP 类型有专有的自动更新特性。MySQL 会给表中第一个 TIMESTAMP 字段创建 CURRENT_TIMESTAMP (当前系统日期)，如果有第二个 TIMESTAMP 字段，则创建默认值为 0 值。TIMESTAMP 还有一个重要特点，就是和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面读取时，也同样需要将日期转换为本地时区后显示。这样，两个不同时区的用户看到的同一个日期可能是不一样的。
3. 默认情况下，当MySQL遇到超出范围的日期/时间类型的值或该类型的其他无效值时，它会将该值转换为“零”值。

字符串类型

字符串类型：char、varchar、blob、text、binary、varbinary、enum、set

注意：

1. char 存储定长字符串，存储时会使用空格补全右侧，读取时会截断末尾空格，最大长度为255个字符；varchar 存储可变长字符串，存储时需要加1个额外的字节存储长度，读取时保留末尾空格，最大长度为65532个字符；字符不区分中英文、数字、特殊字符；如果采用 gbk 字符集，1个字符占两个字节；如果采用 utf-8 字符集，1个字符占三个字节；如果插入值的长度超过设置的长度，将会报错Data too long for column '*** at row *(MySQL 5.7及以上)

2. binary 和 varbinary 类似于 char 和 varchar，不同的是它们只包含二进制字符串，而不包含非二进制字符串；

3. enum 叫枚举类型，它的值范围需要在创建表时通过枚举方式显示指定，对 1~255 个成员的枚举需要1个字节存储；对于255~65535个成员，需要2个字节存储。插入数据时，enum 类型时忽略大小写的，但是不能插入空字符串和不在枚举集合里面的值，可以插入null（MySQL 5.7及以后）

4. set 类型和 enum 类型非常类似，也是一个字符串对象，里面可以包含 0~64个成员。根据成员不同，存储上也不同。

   • 1~8成员的集合，占1个字节；
   • 9~16成员的集合，占2个字节；
   • 17~24成员的集合，占3个字节；
   • 25~32成员的集合，占4个字节；
   • 33~64成员的集合，占8个字节；

   set 类型和 enum 类型除了存储外，最主要的区别在于 set 类型 一次可以选取多个成员，而 enum 则只能选一个。对于 set 类型，只要插入的值是在允许的集合内，都可以正确插入；对于包含重复值的，则去重后插入；对于超出范围的则不能插入。

不推荐存储到数据库的数据类型

1. 二进制多媒体数据

   将二进制多媒体数据存放在数据库中，一个问题是数据库空间资源耗用非常严重，另一个问题是这些数据的存储很消耗数据库主机的CPU 资源。这种数据主要包括图片，音频、视频和其他一些相关的二进制文件。 这些数据的处理本不是数据的优势，如果我们硬要将他们塞入数据库，肯定会造成数据库的处理资源消耗严重。

   对于图片，音频、视频的存储，如果说是特殊情况可以使用BLOB，但是通常来说跟推荐使用 varchar 存储资源的路径，而资源放在静态服务器中的文件夹中。

2. 超大文本数据

   对于 5.0.3 之前的 MySQL 版本，VARCHAR 类型的数据最长只能存放 255 个字节，如果需要存储更长的文本数据到一个字段，我们就必须使用 TEXT 类型（最大 可存放 64KB）的字段，甚至是更大的LONGTEXT 类型 （最大 4GB）。而 TEXT 类型数据的处理性能要远比 VARCHAR 类型数据的处理性能低下很多。从 5.0.3 版 本开始 ，VARCHAR 类型的最大长度被调整到 64KB 了，但是当实际数据小于 255Bytes 的时候，实际存储空间和实际的数据长度一样，可一旦长度超过 255 Bytes 之后，所占用的存储空间就是实际数据长度的两倍。

   对于超大文本数据，如网络小说，一般的方案是将文本放在 txt 中，然后库里存放对应 txt 的地址。

参考文章