MySQL（4）【数据类型 —— 数值类型】

引言

在上一篇文章里，我们学习了MySQL中如何操作表，比如怎么创建、修改和删除它们。但要想数据库真正发挥作用，还得了解怎么存储数据，这就涉及到了数据类型。数据类型决定了列里可以存什么类型的信息，比如数字、文字还是日期等。

本篇文章，我们就来聊聊MySQL中的数据类型，看看它们是怎么帮助我们把数据存得既准确又高效的。准备好，我们这就开始吧！

一、数据类型分类

分类	数据类型	说明
数值类型	BIT(I)	位类型。I指定位数，默认值1，范围1-64
数值类型	TINYINT [UNSIGNED]	带符号的范围-128到127，无符号范围0到255.默认有符号
数值类型	BOOL	使用0和1表示真和假
数值类型	SMALLINT [UNSIGNED]	带符号是-215到215-1，无符号是216-1
数值类型	INT [UNSIGNED]	带符号是-231到231-1，无符号是232-1
数值类型	BIGINT [UNSIGNED]	带符号是-263到263-1，无符号是264-1
数值类型	FLOAT[(M,D)][UNSIGNED]	指定显示长度，d指定小数位数，占用4字节
数值类型	DOUBLE[(M, D)][UNSIGNED]	表示比float精度更大的小数，占用空间8字节
数值类型	DECIMAL (M,D)[UNSIGNED]	定点数指定长度，D表示小数点的位数
文本、二进制类型	CHAR(size)	固定长度字符串，最大255
文本、二进制类型	VARCHAR(SIZE)	可变长度字符串，最大长度65535
文本、二进制类型	BLOB	二进制数据
文本、二进制类型	TEXT	大文本，不支持全文索引，不支持默认值
时间日期	日期类型	DATE（yyy-mm-dd）/DATETIME（yyyy-mm-dd hh:mm:ss）/TIMESTAMP时间戳
ENUM类型	ENUM	ENUM是一个字符串对象，其值来自表创建时在列规定中显，示枚举的一列值
SET类型	SET	SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个set成员的set，列值时各成员之间用逗号间隔开。这样set成员值本身不能包含逗号。

二、数值类型取值范围

类型	字节	最小值（带符号的）	最大值（带符号的）	最大值（无符号的）
TINYINT	1	-128	127	255
SMALLINT	2	-32768	32767	65535
MEDIUMINT	3	-8388608	8388607	16777215
INT	4	-2147483648	2147483647	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807	18446744073709551615

三、tinyint 类型

1. 💻数值越界测试

⭕有符号案例

首先，我们创建了一个名为t1的表，该表包含一个名为num的列，其数据类型为TINYINT。

mysql> create table t1(num tinyint);
Query OK, 0 rows affected (0.02 sec)

接下来，向t1表中插入了一个值1。

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

然后，尝试向t1表中插入一个值128。但是，128超出了TINYINT（有符号）的有效值域，因此MySQL抛出了一个错误。

mysql> insert into t1 values(128); -- 越界插入，报错
ERROR 1264 (22003): Out of range value for column 'num' at row 1

通过SELECT查询来查看t1表中的所有数据。由于只有第一个INSERT操作成功，所以查询结果将只包含第一个插入的值1。

mysql> select * from t1;
+-----+
| num |
+-----+
|   1 |
+-----+

🚨🚨注意：在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的，可以通过UNSIGNED来说明某个字段是无符号的。

⭕无符号案例

mysql> create table t2(num tinyint unsigned);mysql> insert into t2 values(-1); -- 无符号，范围是： 0 - 255
ERROR 1264 (22003): Out of range value for column 'num' at row 1mysql> insert into t2 values(255);
Query OK, 1 row affected (0.02 sec)mysql> select * from t2;
+------+
| num |
+------+
| 255 |
+------+
1 row in set (0.00 sec)

四、bit 类型

1. 基本语法

bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

2. 使用示例

BIT[(M)] 类型在 MySQL 中用于存储二进制数据，其中 M 表示位字段的长度，范围从 1 到 64。如果省略 M，则默认为 1。

✅创建表并插入数据

假设我们有一个表，用于存储用户的ID和他们的某些二进制设置（例如，是否启用了某个功能）。

create table user_settings (user_id INT AUTO_INCREMENT PRIMARY KEY,feature_enabled BIT(1) NOT NULL DEFAULT b'0'
);-- 插入数据
INSERT INTO user_settings (feature_enabled) VALUES (b'1'); -- 启用功能
INSERT INTO user_settings (feature_enabled) VALUES (b'0'); -- 禁用功能

在这个例子中，feature_enabled 列是一个 BIT(1) 类型的字段，用于存储单个位的值（0 或 1），表示功能是否启用。

✅使用 BIT 存储多个设置

如果你需要存储多个二进制设置，可以使用更大的 BIT 字段。

create table user_profiles (user_id INT AUTO_INCREMENT PRIMARY KEY,settings BIT(8) NOT NULL DEFAULT b'00000000'
);-- 假设 settings 的每一位代表一个不同的设置
-- 例如，第1位是邮件通知，第2位是短信通知，依此类推
INSERT INTO user_profiles (settings) VALUES (b'10101010');

✅查询和格式化 BIT 数据

当你从 BIT 类型的列中检索数据时，你可能想要以更易于理解的方式查看这些数据。

-- 查询并格式化 BIT 数据
SELECT user_id, BIN(settings) AS binary_settings FROM user_profiles;-- 如果你只对某些位感兴趣，可以使用位运算
-- 例如，检查邮件通知是否启用（假设它是第1位）
SELECT user_id, (settings & b'00000001') AS mail_notifications FROM user_profiles;

✅更新 BIT 数据

你可以使用位运算符来更新 BIT 字段中的值。

-- 假设我们要启用用户ID为1的用户的邮件通知（第1位）
UPDATE user_profiles
SET settings = settings | b'00000001'
WHERE user_id = 1;-- 禁用短信通知（假设它是第2位）
UPDATE user_profiles
SET settings = settings & ~b'00000010'
WHERE user_id = 1;

五、小数类型

1. float

🎯基本语法

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

🎯使用示例

⭕有符号案例

小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入

mysql> create table t6(id int, salary float(4,2));
Query OK, 0 rows affected (0.01 sec)mysql> insert into t6 values(100, -99.99);
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values(101, -99.991); #多的这一点被拿掉了
Query OK, 1 row affected (0.00 sec)mysql> select * from t6;
+------+--------+
| id | salary |
+------+--------+
| 100 | -99.99 |
| 101 | -99.99 |
+------+--------+
2 rows in set (0.00 sec)

⭕无符号案例

如果定义的是float(4,2) unsigned 这时，因为把它指定为无符号的数，范围是 0 ~ 99.99

mysql> create table t7(id int, salary float(4,2) unsigned);
Query OK, 0 rows affected (0.01 sec)mysql> insert into t7 values(100, -0.1);
Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'salary' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)mysql> insert into t7 values(100, -0);
Query OK, 1 row affected (0.00 sec)mysql> insert into t7 values(100, 99.99);
Query OK, 1 row affected (0.00 sec)

2. decimal

🎯基本语法

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

• decimal(5,2) 表示的范围是 -999.99 ~ 999.99
• decimal(5,2) unsigned 表示的范围 0 ~ 999.99
• decimal和float很像，但是有区别：float和decimal表示的精度不一样

🎯使用示例

mysql> create table t8 ( id int, salary float(10,8), salary2
decimal(10,8));mysql> insert into t8 values(100,23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)mysql> select * from t8;
+------+-------------+-------------+
| id | salary | salary2 |
+------+-------------+-------------+
| 100 | 23.12345695 | 23.12345612 | # 发现decimal的精度更准确，因此如果我们希望某个数据表示高精度，选择decimal
+------+-------------+-------------+

📌经过上面的例子可以发现decimal的精度更准确，因此如果我们希望某个数据表示高精度就选择decimal。