MySQL 数据类型

MySQL 数据类型

---

前提条件

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set

---

整型数据类型

数据类型	占用字节	表示范围（有符号）	表示范围（无符号）	默认宽度	MyBatis 中映射po类的数据类型
tinyint	1	-128 ~ 127	0 ~ 256	3	tinyint(1) –> Boolean ，其他 Byte
smallint	2	-2^15 (-32,768) ~ 2^15 - 1 (32,767)	0 ~ 65535	5	Integer
mediumint	3	-8388608 ~ 8388607	0 ~ 16777215	8	Integer
int	4	-2^31 (-2,147,483,648) ~ 2^31 - 1 (2,147,483,647)	0 ~ 4294967295	10	Integer
bigint	8	-2^63 (-9223372036854775808) ~ 2^63-1 (9223372036854775807)	0 ~ 18446744073709551615	20	Long

• 最大宽度：对应建表语句中的数据类型的长度，即建表语句中的int(n) 其中 n 表示宽度；
  
  • 对于mysql存储的数值没有任何影响，即不影响存储数值的表示范围
  • 如果输入内容大于预设宽度，不影响存储；如果小于预设宽度，则左侧补0
  • 作用，需要与zerofill配合使用，如果启用了zerofill，就是当数值的位数

// 建表语句
CREATE TABLE `a` (
  `c10` int(10) unsigned zerofill DEFAULT NULL,
  `c11` int(11) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
// zerofill ，插入表中的数据位数不足n位时左侧补零
// 插入数据
INSERT INTO A VALUES (99,99);
// 在 Navicat for MySQL 工具中看不到效果

// 在命令行窗口中查看  （Windows +R 键打开 CMD 命令窗口）
// 查看表中数据

mysql> select * from a ;
+------------+-------------+
| c10        | c11         |
+------------+-------------+
| 0000000099 | 00000000099 |
+------------+-------------+
1 row in set (0.00 sec)   

• MyBatis 中 po 与 table 映射时，tinyint 映射为 Byte ，但 tinyint(1) 映射为 Boolean

// MySQL 中不支持 boolean 数据类型，但可执行如下SQL创建表，查看表结构时发现 MySQL 将其转为 tinyint(1) 数据类型
mysql> create table xs
(
id int primary key,
bl boolean
);
Query OK, 0 rows affected

mysql> desc xs ;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| bl    | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set

• 主键键值数据类型为int时，注意表中存放数据的数量，要小于int的最大上限
• 整型数据类型在使用时，若不需要存取负值，加上unsigned

---

浮点型数据类型

• float 占 4 字节
• double 占 8 字节
• decimal(M,D) 占 M+2 字节（+2是指 符号位、小数点）；DECIMAL 实际是以串存放的
• M是数字的最大数（精度），其范围为1～65，M 的默认值是10
• M是总位数，整数部分+小数部分；整数部分不能超过M-D，否则数据插入失败
• D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M
• D是小数部分，若数值小于D，则右侧补0；若数值大于D，则截断指定的小数位

decimal	表示范围（有符号）	表示范围（无符号）
DECIMAL(4, 1)	-999.9 ~ 999.9	0 ~ 999.9
DECIMAL(5, 1)	-9999.9 ~ 9999.9	0 ~ 9999.9
DECIMAL(6, 1)	-99999.9 ~ 99999.9	0 ~ 99999.9
DECIMAL(6, 2)	-9999.99 ~ 9999.99	0 ~ 9999.99
DECIMAL(6, 3)	-999.999 ~ 999.999	0 ~ 999.999

- 示例：DECIMAL(5, 2) 存储时需要 5+2 个字节，此处的2不是小数的精度2
- 表示的数据范围：负数（符号位占 1 字节、小数点占 1 字节，小数部分占 2 字节，整数部分占 3 字节） - 999.99 ； 正数（符号位占 1 字节，小数点 占 1 字节，小数部分占2字节，正数部分占用 4 字节 ） 999.99

// 创建数据表
CREATE TABLE `t_decimal_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `money` decimal(4,2) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
// 插入数据 999.99 异常，正数部分超过指定位数 M - D = 2  
mysql> insert into t_decimal_test values (2,999.99);
1264 -

---

字符型数据类型

数据类型	char(n)	varchar(n)
列长度	固定，效率高	可变，效率偏低
表示范围	0~255 字符	0~65535 字节（64k，同时也是行数据的最大长度）
长度(n)范围	0~255	由表结构、编码格式决定
所占字节数	UTF-8编码：n*3；GBK编码：n*2	UTF-8编码：n*3；GBK编码：n*2
存储	如果字符数没有达到定义的位数，会在后面用空格补全存入数据库中	如果字符没有达到定义的位数，不会在后面补空格
取值	把后面的空格全部丢弃掉	尾部空格会保留

• char(n)、varchar(n) 用于存放字符串，n 为存放字符的长度，实际存放内容超过 n，会截取字符串
• 字符与字节的关系

编码格式/所占字节数	英文	半角标点	汉字	全角标点
GBK	1	1	2	2
UTF-8	1	1	3	3

• varchar(n) ：存储可变字符串内容，存放内容为多少字符，就占用相应编码格式下的多少字节，需额外存放此时字符串的长度，n 在 0 ~ 255 范围内，需额外 1 字节（2的8次幂），n 大于 255 则需要2个字节存放字符串的长度；char(n) ：存储固定字符串内容，如果存放的字符串的字符数量少于 n ，仍然按照 n 个字符，相应编码格式下的

• varchar(n)：n 的取值范围；varchar 的最大表示范围是 0~65535

  • varchar(n) 中 n 设置多少，就能存放多少字符；n 的取值受到如下限制：
    
    • 存储限制：varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（n 在 0 ~ 255 范围内，占用 1 字节 ； n > 255 时 占用 2 字节 ； 1 字节的表示范围 0 ~ 2的8次幂-1 ）
    • 编码长度限制：不同编码格式，不同的字符类型所占字节数不同
      
      • 字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766;
      • 字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。
      • 如果被varchar超过上述的b规则，被强转成text类型，则每个字段占用定义长度为11字节；并产生warning
    • 行长度限制： MySQL 表中每行数据的最大字节数 65535，如果存储长度超过最大限制，会报异常 （异常信息描述如下），即如果表中含有 varchar 数据类型的列，在设置 varchar(n) n 的取值时需要考虑表中其他字段的数据类型占用的字节：
      ERROR 1118 (42000): Row size too large. The maximum row size for
the used table type, not counting BLOBs, is 65535. You have to change
some columns to TEXT or BLOBs。
    • 非空标识 not null ，占用 1 位

  • 示例：

    • create table t4(c varchar(n)) charset=gbk; n 的取值上限： ( 65535 - 1 - 2 ) / 2 ；减1的原因是实际行存储从第二个字节开始，减2的原因是varchar头部的2个字节表示长度（此处由 n 的取值范围决定，减去 1 还是 2），除2的原因是字符编码是gbk（每个汉字占用两个字节）
    • create table t4(c int, c2 char(30), c3 varchar(n)) charset=utf8; n 的取值上限：( 65535 - 1 - 2 - 4 - 30 *3 ) / 3 ； 减4的原因是int类型的c占4个字节；减30*3的原因是char(30)占用90个字节，编码是utf8（每个汉字占用3字节）

  • 实际存储的内容可能为汉字标点的混合内容，所占用的字节数也需要按照不同的编码格式和字符的类型进行计算；上述为估算，取上限值，按照全为汉字计算

• 在MySQL中用来判断是否需要进行对据列类型转换的规则

  • 在一个数据表里，如果每一个数据列的长度都是固定的，那么每一个数据行的长度也将是固定的．
  • 只要数据表里有一个数据列的长度的可变的，那么各数据行的长度都是可变的．
  • 如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型．例外：长度小于4个字符的char数据列不会被转换varchar类型

---

时间数据类型

---

参考资料

• mysql中bigint、int、mediumint、smallint 和 tinyint的取值范围
• MySQL和Java数据类型对应
• mysql中int(10)与int(11)有什么区别吗？
• MySQL中char与varchar区别，varchar最大长度是多少？
• MySQL char与varchar的差异
• MySQL中的char与varchar详解
• MySQL DECIMAL数据类型
• Mysql数据类型DECIMAL(M,D)用法
• MySQL数据类型-decimal详解