【MYSQL】MYSQL学习的一大重点：MYSQL数据类型
🎬 个人主页：艾莉丝努力练剑
❄专栏传送门：《C语言》《数据结构与算法》《C/C++干货分享&学习过程记录》
《Linux操作系统编程详解》《笔试/面试常见算法：从基础到进阶》《Python干货分享》
⭐️为天地立心，为生民立命，为往圣继绝学，为万世开太平
🎬 艾莉丝的简介：
文章目录
0 ~> int：最佳实践
整数后面的圆括号里的内容我们暂时还不清楚，但是bit后面的圆括号我们知道了，就是：你想带几个bit位。
mysql> show tables; Empty set(0.00 sec) mysql>select database(); +------------+ | database()| +------------+ | d4 | +------------+ 1 row inset(0.00 sec) mysql> create table if not exists t1( ->id int, -> online bit(65) ->); ERROR 1439(42000): Display width out of range forcolumn'online'(max =64) mysql> create table if not exists t1(id int, online bit(64)); Query OK, 0 rows affected (0.04 sec) mysql> desc t1; +--------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ |id| int | YES || NULL ||| online | bit(64)| YES || NULL || +--------+---------+------+-----+---------+-------+ 2 rows inset(0.00 sec) mysql> drop table t1; Query OK, 0 rows affected (0.02 sec) mysql> create table if not exists t1(id int, online bit ); Query OK, 0 rows affected (0.03 sec) mysql> desc t1; +--------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------+------+-----+---------+-------+ |id| int | YES || NULL ||| online | bit(1)| YES || NULL || +--------+--------+------+-----+---------+-------+ 2 rows inset(0.01 sec)1 ~> 数据类型分类
1.1 数值类型
| 数据类型 | 说明 |
|---|---|
| BIT(M) | 位类型。M指定位数，默认值1，范围1-64 |
| TINYINT [UNSIGNED] | 带符号的范围-128127，无符号范围0255。默认有符号 |
| BOOL | 使用0和1表示真和假 |
| SMALLINT [UNSIGNED] | 带符号是-2^15次方 到 215-1，无符号是216-1 |
| INT [UNSIGNED] | 带符号是-2^31次方 到 231-1，无符号是232-1 |
| BIGINT [UNSIGNED] | 带符号是-2^63次方 到 263-1，无符号是264-1 |
| FLOAT [(M, D)] [UNSIGNED] | M指定显示长度，d指定小数位数，占用4字节 |
| DOUBLE [(M, D)] [UNSIGNED] | 表示比float精度更大的小数，占用空间8字节 |
| DECIMAL (M, D) [UNSIGNED] | 定点数M指定长度，D表示小数点的位数 |
1.2 文本、二进制类型
| 数据类型 | 说明 |
|---|---|
| CHAR (size) | 固定长度字符串，最大255 |
| VARCHAR (SIZE) | 可变长度字符串，最大长度65535 |
| BLOB | 二进制数据 |
| TEXT | 大文本，不支持全文索引，不支持默认值 |
1.3 时间日期
| 数据类型 | 说明 |
|---|---|
| DATE/DATETIME/TIMESTAMP | 日期类型(yyyy-mm-dd) (yyyy-mm-dd hh:mm:ss) timestamp时间戳 |
1.4 String类型
| 数据类型 | 说明 |
|---|---|
| ENUM类型 | ENUM是一个字符串对象，其值来自表创建时在列规定中显示枚举的一列值 |
| SET类型 | SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个set成员的set列值时各成员之间用逗号隔开。这样set成员值本身不能包含逗号。 |
2 ~> 数值类型
| 类型 | 字节 | 最小值（有符号/无符号） | 最大值（有符号/无符号） |
|---|---|---|---|
| TINYINT | 1 | -128 / 0 | 127 / 255 |
| SMALLINT | 2 | -32768 / 0 | 32767 / 65535 |
| MEDIUMINT | 3 | -8388608 / 0 | 8388607 / 16777215 |
| INT | 4 | -2147483648 / 0 | 2147483647 / 4294967295 |
| BIGINT | 8 | -9223372036854775808 / 0 | 9223372036854775807 / 18446744073709551615 |
2.1 TINYINT 类型
默认有符号，可通过 UNSIGNED 指定无符号。
示例：
-- 有符号 mysql>CREATETABLE ttl (num TINYINT); mysql>INSERTINTO ttl VALUES(1); mysql>INSERTINTO ttl VALUES(128);-- 越界，报错 ERROR 1264(22003): Outof range valueforcolumn'num' at row1-- 无符号 mysql>CREATETABLE tt2 (num TINYINTUNSIGNED); mysql>INSERTINTO tt2 VALUES(-1);-- 越界，报错 ERROR 1264(22003): Outof range valueforcolumn'num' at row1 mysql>INSERTINTO tt2 VALUES(255);-- 成功注意：尽量不要使用 UNSIGNED，因为可能仍然存不下较大数值，不如直接使用更大的类型（如 BIGINT）。
2.2 BIT 类型
语法：BIT[(M)]，M 范围 1～64，默认 1。
存储为二进制值，查询时默认按 ASCII 码显示。
mysql>CREATETABLE tt4 (id INT, a BIT(8)); mysql>INSERTINTO tt4 VALUES(10,10); mysql>SELECT*FROM tt4;+------+------+| id | a |+------+------+|10||-- 10 对应 ASCII 不可见字符，所以显示为空+------+------+ mysql>INSERTINTO tt4 VALUES(65,65); mysql>SELECT*FROM tt4;+------+------+| id | a |+------+------+|10|||65| A |-- 65 对应 ASCII 'A'+------+------+如果只存放 0 或 1，可定义为 BIT(1) 以节省空间。
mysql>CREATETABLE tt5 (gender BIT(1)); mysql>INSERTINTO tt5 VALUES(0),(1); mysql>INSERTINTO tt5 VALUES(2);-- 越界，报错 ERROR 1406(22001): Data too long forcolumn'gender' at row12.3 小数类型
2.3.1 FLOAT
- 语法：
FLOAT[(M,D)] [UNSIGNED]，M 为显示长度，D 为小数位数，占用 4 字节。 - 保存时会四舍五入。
mysql>CREATETABLE tt6 (id INT, salary FLOAT(4,2));-- 范围 -99.99 ～ 99.99 mysql>INSERTINTO tt6 VALUES(100,-99.99); mysql>INSERTINTO tt6 VALUES(101,-99.991);-- 四舍五入为 -99.99 mysql>SELECT*FROM tt6;+------+--------+| id | salary |+------+--------+|100|-99.99||101|-99.99|+------+--------+- 无符号
FLOAT(4,2) UNSIGNED范围 0 ～ 99.99。
mysql>CREATETABLE tt7 (id INT, salary FLOAT(4,2)UNSIGNED); mysql>INSERTINTO tt7 VALUES(100,-0.1);-- 警告，实际可能变为 0 mysql>INSERTINTO tt7 VALUES(100,0); mysql>INSERTINTO tt7 VALUES(100,99.99);2.3.2 DECIMAL
- 语法：
DECIMAL(M,D) [UNSIGNED]，定点数，M 为总位数，D 为小数位数。 - 例如：
DECIMAL(5,2)范围 -999.99 ～ 999.99，无符号时 0 ～ 999.99。 - 精度比 FLOAT 高，适合需要精确计算的场景（如金额）。
mysql>CREATETABLE tt8 (id INT, salary FLOAT(10,8), salary2 DECIMAL(10,8)); mysql>INSERTINTO tt8 VALUES(100,23.12345612,23.12345612); mysql>SELECT*FROM tt8;+------+-------------+-------------+| id | salary | salary2 |+------+-------------+-------------+|100|23.12345695|23.12345612|-- float 出现精度误差，decimal 精确+------+-------------+-------------+FLOAT 精度约为 7 位有效数字，DOUBLE 约为 15 位。
DECIMAL 最大位数 M 为 65，小数位数 D 最大为 30。若省略 D，默认为 0；若省略 M，默认为 10。
- 建议： 对精度要求高的数据使用 DECIMAL。
3 ~> 字符串类型
3.1 CHAR
语法：CHAR(L)，L 为字符数，最大 255。
固定长度，存储时右补空格到指定长度，检索时会去掉末尾空格（但严格模式可能保留）。
mysql>CREATETABLE tt9 (id INT, name CHAR(2)); mysql>INSERTINTO tt9 VALUES(100,'ab'); mysql>INSERTINTO tt9 VALUES(101,'中国');-- 一个汉字算一个字符 mysql>SELECT*FROM tt9;+------+------+| id | name |+------+------+|100| ab ||101| 中国 |+------+------+ mysql>CREATETABLE tt10 (id INT, name CHAR(256));-- 错误，超过最大长度 ERROR 1074(42000): column length too big forcolumn'name'(max =255);useBLOBorTEXT instead 3.2 VARCHAR
语法：VARCHAR(L)，L 为字符数，但实际最大长度受字节数限制（最多 65535 字节）。
需要 1～3 字节额外空间记录数据长度，因此可用字节数为 65532。
不同字符集下最大字符数不同：
utf8mb4：每个字符最多 4 字节，最大字符数 ≈ 65532 / 4 = 16383。utf8（即utf8mb3）：每个字符最多 3 字节，最大字符数 ≈ 65532 / 3 = 21844。gbk：每个字符 2 字节，最大字符数 ≈ 65532 / 2 = 32766。
-- utf8 编码下，最大可定义 21844 个字符 mysql>CREATETABLE ttl (name VARCHAR(21844))CHARSET=utf8; Query OK,0rows affected (0.01 sec)-- 21845 超出限制 mysql>CREATETABLE ttl (name VARCHAR(21845))CHARSET=utf8; ERROR 1118(42000): Row size too large....3.3 CHAR 与 VARCHAR 对比
假设使用 utf8 字符集（每个字符最多 3 字节），CHAR(4)和VARCHAR(4)对比如下：
| 实际存储内容 | CHAR(4) 存储方式（固定占用 4 字节） | VARCHAR(4) 存储方式（可变，+1 字节记录长度） |
|---|---|---|
| ‘abcd’ | 4 个字符（12 字节） | 4 个字符 + 1 字节长度 = 13 字节 |
| ‘A’ | 1 个字符右补 3 个空格（4 字节） | 1 个字符 + 1 字节长度 = 4 字节 |
| ‘Abcde’ | 超出长度，插入失败 | 超出长度，插入失败 |
4 ~> 日期和时间类型
| 类型 | 格式 | 占用字节 | 说明 |
|---|---|---|---|
| DATE | ‘yyyy-mm-dd’ | 3 | 仅日期 |
| DATETIME | ‘yyyy-mm-dd HH:ii:ss’ | 8 | 日期和时间，范围 1000-01-01 ~ 9999-12-31 |
| TIMESTAMP | ‘yyyy-mm-dd HH:ii:ss’ | 4 | 时间戳，范围 1970-01-01 ~ 2038-01-19 |
-- 创建表 mysql>CREATETABLE birthday (t1 DATE, t2 DATETIME, t3 TIMESTAMP);-- 插入数据（t3 自动填充当前时间） mysql>INSERTINTO birthday (t1, t2)VALUES('1997-7-1','2008-8-8 12:1:1'); mysql>SELECT*FROM birthday;+------------+---------------------+---------------------+| t1 | t2 | t3 |+------------+---------------------+---------------------+|1997-07-01|2008-08-0812:01:01|2023-01-0112:34:56|+------------+---------------------+---------------------+-- 更新数据时，TIMESTAMP 自动更新为当前时间 mysql>UPDATE birthday SET t1 ='2000-1-1'; mysql>SELECT*FROM birthday;+------------+---------------------+---------------------+| t1 | t2 | t3 |+------------+---------------------+---------------------+|2000-01-01|2008-08-0812:01:01|2023-01-0112:35:10|+------------+---------------------+---------------------+注意： TIMESTAMP 的行为（自动初始化和自动更新）可以通过建表时的 DEFAULT 和 ON UPDATE 子句控制。
5 ~> ENUM 和 SET
5.1 ENUM（枚举）
单选类型，格式：ENUM('值1', '值2', ...)。
实际存储为数字索引（从 1 开始），最多 65535 个选项。
插入时可以使用值本身或数字索引。
5.2 SET（集合）
多选类型，格式：SET('值1', '值2', ...)。
实际存储为位图（每个选项对应一个比特位：1,2,4,8,…），最多 64 个选项。
插入时用逗号分隔多个值，如'登山,游泳'。
5.3 示例：调查表
CREATETABLE votes ( username VARCHAR(30), hobby SET('登山','游泳','篮球','武术'),-- 多选 gender ENUM('男','女')-- 单选);-- 插入数据INSERTINTO votes VALUES('雷锋','登山,武术','男');INSERTINTO votes VALUES('Juse','登山,武术',2);-- 2 对应 '女'INSERTINTO votes VALUES('LiLei','登山','男');INSERTINTO votes VALUES('LiLei','篮球','男');INSERTINTO votes VALUES('HanMeiMei','游泳','女');5.3.1 查询技巧
直接使用=查询 SET 列只能匹配完全相同的值，无法查找包含某一项的行。
应使用FIND_IN_SET()函数：FIND_IN_SET('登山', hobby)返回非 0 表示存在。
-- 错误：只能查出 hobby 正好等于 '登山' 的行SELECT*FROM votes WHERE hobby ='登山';-- 正确：查出所有包含 '登山' 的行SELECT*FROM votes WHERE FIND_IN_SET('登山', hobby);- 注意： 不建议在插入时直接使用数字编号，因为不利于阅读和维护。
结尾
uu们，本文的内容到这里就全部结束了，艾莉丝在这里再次感谢您的阅读！
结语：希望对学习MYSQL相关内容的uu有所帮助，不要忘记给博主“一键四连”哦！
往期回顾：
🗡博主在这里放了一只小狗，大家看完了摸摸小狗放松一下吧！🗡૮₍ ˶ ˊ ᴥ ˋ˶₎ა
