MySQL数据类型详解

1.MySQL数据类型

MySQL 支持多种类型，可以分为三类：数值、日期/时间和字符串(字符)类型。

1.1 数值类型

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

1.2 日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

1.3 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。 

2.MySQL各数据类型括号内数字含义

2.1 数值类型括号内数字含义

对于数值类型长度是固定的，定义类型的时候就已经确认了长度范围，而数据类型（m）中的m不是表示的数据长度，而是表示数据在显示时显示的最小长度。
tinyint(1) 这里的1表示的是 最短显示一个字符。tinyint(2) 这里的2表示的是 最短显示两个字符。

当字符长度（m）超过对应数据类型的最大表示范围时，相当于啥都没发生；
当字符长度（m）小于对应数据类型的表示范围时，就需要指定拿某个字符来填充，比如zerofill（表示用0填充）。
设置tinyint(2) zerofill 你插入1时他会显示01；设置tinyint(4) zerofill 你插入1时他会显示0001。
即使你建表时，不指定括号数字，mysql会自动分配长度：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。

2.2 字符类型括号内数字含义

char的列长度是固定的,char的长度可选范围在0-255字符之间。也就是char最大能存储255个字符.
varchar的列长度是可变的,在mysql5.0.3之前varchar的长度范围为0-255字符，mysql5.0.3之后varchar的长度范围为0-65535个字节.

CHAR(M)定义的列的长度为固定的，M取值可以为0-255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字符，都要占去10个字符的空间,不足的自动用空格填充。

VARCHAR(M)定义的列的长度为可变长字符串，VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。varchar存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么"+1"呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

varchar(m)里面表示的是可存储的长度，例如：varchar（5）表示最大可存储5个中文或5个英文字母。

不同字符集所占内存不同
字符和字节不存在绝对的关系，只是在不同的编码格式里，对应的比值不一样。比如：
1.UTF-8编码中，一个英文字符等于一个字节，一个中文（含繁体）字符等于三个字节。
2.Unicode编码中，一个英文等于两个字节，一个中文（含繁体）字符等于两个字节。
符号：英文标点占一个字节，中文标点占两个字节。举例：英文句号“.”占1个字节的大小，中文句号“。”占2个字节的大小。

3. 案例验证

CREATE TABLE IF NOT EXISTS `test_data`(
   `bigida` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
   `tinya` TINYINT(4) NOT NULL COMMENT 'tinyint4',
   `inta` int(4) NOT NULL COMMENT 'int4',
   `zerofilla` int(5) ZEROFILL NOT NULL COMMENT 'zerofill5',
   `vchar` VARCHAR(3) NOT NULL COMMENT '字符类型',
   PRIMARY KEY ( `bigida` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据类型测试表';


INSERT INTO `test`.`test_data`(`bigida`, `tinya`, `inta`, `zerofilla`, `vchar`) VALUES (1, 1, 1, 00001, '字符串');
INSERT INTO `test`.`test_data`(`bigida`, `tinya`, `inta`, `zerofilla`, `vchar`) VALUES (2, 6, 128, 00012, '阿萨德');
INSERT INTO `test`.`test_data`(`bigida`, `tinya`, `inta`, `zerofilla`, `vchar`) VALUES (3, 127, 2147483647, 00123, '阿斯顿');
INSERT INTO `test`.`test_data`(`bigida`, `tinya`, `inta`, `zerofilla`, `vchar`) VALUES (4, 127, 123, 01234, '点对点');

通过验证也能够看出来：
数值类型int(m)，tinyint(m)，smalint(m)，mediumint(m)，bigint(m)中m表示显示长度，与数据类型是否定义zerofill有关，实际数值长度大于m不做任何处理，小于m左侧补零，如果不是zerofill类型则不补零，大于小于都不做任何处理。

字符类型，char(m)是长度固定的，0-255，m不足末尾补空格。
varchar(m)是变长的，实际长度是最大行存储数据长度与字符集决定的，最长是65535。