MySQL数据类型

本文使用的MySQL 8.0.34

int 类型

查看官方文档

【总结】

1.int占用4字节空间,存储空间大小固定
2.有符号可存储的最大值 2147483647
3.int(5) 中的5不影响存储空间大小,只有在设置 ZEROFILL 才有意义,即不足长度5的情况下前面填充0
4.建表时可以不用再指定长度了

use db0;
CREATE TABLE `t_1` (
   `id` bigint NOT NULL auto_increment,
   `t_name` char(32) not null,
   `t_address` varchar(32) DEFAULT NULL,
   `t_age` int(5) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 collate utf8mb4_0900_ai_ci;

int占用4字节空间
1.有符号最大值(2^31)-1=2147483647

如果插入的数据超过2147483647 则报错Error Code: 1264. Out of range value for column ‘t_age’ at row 1

mysql> insert into db0.t_1 values(null,'Libai','chengdu',2147483648);
ERROR 1264 (22003): Out of range value for column 't_age' at row 1

2.指定的长度5只有在设置 ZEROFILL 才有意义,即不足5位长度的前面填充0

use db0;
CREATE TABLE `t_2` (
   `id` bigint NOT NULL auto_increment,
   `t_name` char(32) not null,
   `t_address` varchar(32) DEFAULT NULL,
   `t_age` int(5) zerofill DEFAULT NULL, -- 指定 zerofill 
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 collate utf8mb4_0900_ai_ci;

insert into db0.t_2 values(null,'Libai','chengdu',23);
insert into db0.t_2 values(null,'Libai','chengdu',7);
insert into db0.t_2 values(null,'Libai','chengdu',102931);

mysql> select id,t_age FROM db0.t_2;
+----+--------+
| id | t_age  |
+----+--------+
|  1 |  00023 | -- 不足5位长度的前面填充0
|  2 |  00007 | -- 不足5位长度的前面填充0
|  3 | 102931 |
+----+--------+

3.其实在创建表完成之后, 提示了一个警告 Integer display width is deprecated and will be removed in a future release

因此在指定字段类型时, 不用指定宽度 t_age int(5) , 而是使用 t_age int .

char 类型

查看官方文档

【总结】

1.存储上限255个字符, 即char(n)中n最大255
2.char(32) 表示存储32个字符(英文或中文字符),不足32个字符会用空格在其右侧填充.
3.char(n)占用存储空间字节大小 = n个字符在字符集里占用的空间之和

use db0;
CREATE TABLE `t_1` (
   `id` bigint NOT NULL auto_increment,
   `t_name` char(32) not null,
   `t_address` varchar(32) DEFAULT NULL,
   `t_age` int(5) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 collate utf8mb4_0900_ai_ci;

char(32) 如果插入的数据长度超过32 则报错ERROR 1406 (22001): Data too long for column ‘t_name’ at row 1

-- 插入的 t_name 值长度35
mysql> insert into db0.t_1 values(null,'LibaiHanyuLibaiHanyuLibaiHanyuLibai','chengdu',23);
ERROR 1406 (22001): Data too long for column 't_name' at row 1

-- 插入的 t_name 值长度33
mysql> insert into db0.t_1 values(null,'零一二三四五六七八九零一二三四五六七八九零一二三四五六七八九零一二','chengdu',23);
ERROR 1406 (22001): Data too long for column 't_name' at row 1

查看 字符个数 和 占用存储空间

英文字符在 utf8mb4 中占用1个字节
中文字符在 utf8mb4 中占用3个字节(不严谨)

varchar 类型

【总结】

1.varchar(n) 最多存储n个字符
2.字段值超过n个字符会被截断
3.65535的噩梦

use db0;
CREATE TABLE `t_1` (
   `id` bigint NOT NULL auto_increment,
   `t_name` char(32) not null,
   `t_address` varchar(32) DEFAULT NULL,
   `t_age` int(5) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 collate utf8mb4_0900_ai_ci;

varchar(32) 如果插入的数据长度超过32 则截断字段值超过字符个数的部分

-- 插入的 t_name 值长度40
mysql> insert into db0.t_1 values(null,'Libai','成都的风吹进你的心里成都的风吹进你的心里成都的风吹进你的心里成都的风吹进你的心里',7);
Query OK, 1 row affected, 1 warning (0.02 sec)   -- 提示1个警告

mysql> show warnings; -- 查看警告
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 't_address' at row 1 |  -- 提示数据被截断
+---------+------+------------------------------------------------+

关于65535的解读如下

1.单列情况

mysql> CREATE TABLE `t_1` (
   `id` bigint NOT NULL auto_increment,
   `t_name` char(32) not null,
   `t_address` varchar(16384) DEFAULT NULL,
   `t_age` int DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Note  | 1246 | Converting column 't_address' from VARCHAR to TEXT |
+-------+------+----------------------------------------------------+

解读 varchar(16384) :
单列字段长度字节 = 字符个数 * 一个字符在字符集里占用的空间 = 16384 * 4 = 65536 > 65535 会将 varchar类型转成TEXT类型 .

再次查看表结构验证说法

mysql> show create table t_1;
CREATE TABLE `t_1` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `t_name` char(32) NOT NULL,
  `t_address` mediumtext,   -- 类型TEXT
  `t_age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

当把 varchar(16384) 改成 varchar(16350) 变少了,再次重新创建表

mysql> CREATE TABLE `t_1` (
   `id` bigint NOT NULL auto_increment,
   `t_name` char(32) not null,
   `t_address` varchar(16350) DEFAULT NULL,
   `t_age` int DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

报错了.

计算单行记录长度字节 = 8(id) + 324(t_name) + 163504(t_address) + 4(t_age) = 8 + 128 + 65400 + 4 = 65540 > 65535 建表失败

单列字段长度字节 < 65535 OK
单列字段长度字节 > 65535 转换成TEXT  OK
单列字段长度字节 = 字符个数 * 一个字符在字符集里占用的空间

单行记录长度字节(排除TEXT BLOB等) < 65535 可以建表 OK
单行记录长度字节(排除TEXT BLOB等) > 65535 建表失败 ERROR