数据类型

整数类型

类型	字节	范围 (有符号)	范围 (无符号)	应用场景
TINYINT	1	-128 ~ 127	0 ~ 255	性别、状态位、布尔值
SMALLINT	2	-32,768 ~ 32,767	0 ~ 65,535	年龄、年份、地区编号
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215	大一点的 ID 编号
INT / INTEGER	4	-21亿 ~ 21亿	0 ~ 42亿	最常用 ID、自增主键
BIGINT	8	-2^63 ~ 2^63-1	0 ~ 2^64-1	金额、大用户量 ID

👉 注意

• 可以加 UNSIGNED 让范围翻倍（去掉负数部分）。
• INT(11) 中的数字 11 不是长度，只是 显示宽度（已废弃）。

INT(M) 的含义：

• INT 是整数类型，固定占用 4 个字节。
• INT(11) 中的 (11) 并不是表示该整数可以存储的最大位数，也不是长度。
• 它本质上是 显示宽度（display width），用于ZEROFILL时控制输出格式。

CREATE TABLE test_width (
    id INT(11) ZEROFILL,
    score INT(5) ZEROFILL
);

INSERT INTO test_width (id, score) VALUES (1, 45);
SELECT * FROM test_width;

结果为；

id	score
00000000001	00045

• 可以看到，INT(11) 的意思是显示 11 位宽度，不足的左侧用 0 填充（因为用了 ZEROFILL）。
• 如果没有 ZEROFILL，INT(11) 输出和普通 INT 一样，没有区别

注意事项

1. 不影响存储范围
   • INT 占用 4 字节，范围 -2147483648 ~ 2147483647（有符号）
   • (11) 不会改变存储大小或范围
2. 已废弃
   • 从 MySQL 8.0.17 开始，INT(M) 的显示宽度功能已经 废弃，即使写了 (11) 也没有效果。
   • 推荐直接写 INT，如果需要格式化输出，用 LPAD() 或应用层处理。

bit类型

BIT(M) 表示存储 M 位二进制数，M 最大 64。

存储布尔值或标志位：

CREATE TABLE flags (
    id INT,
    is_active BIT(1),  -- 0 或 1
    permissions BIT(4) -- 例如 1010
);

-- 二进制插入表示 b'1010' 二进制表示
INSERT INTO bit_test (flags) VALUES (b'1010'), (b'0110');

-- 使用十进制表示
INSERT INTO bit_test (flags) VALUES (10);  -- 10 的二进制就是 1010

查询显示问题

-- 默认显示
SELECT * FROM bit_test;
-- 结果可能会显示为 二进制字符或乱码
-- MySQL 将 BIT 类型存储为 二进制字符串，查询时如果客户端使用字符集显示，会显示乱码

转为可读整数

-- 使用 +0 或 BIN()、CAST() 显示

-- 转十进制
SELECT flags+0 AS flags_dec FROM bit_test;

-- 转二进制字符串
SELECT BIN(flags+0) AS flags_bin FROM bit_test;

flags_dec	flags_bin
10	1010
6	0110

小数类型

类型	存储方式	特点
FLOAT	4字节，单精度	约7位精度，可能有误差
DOUBLE	8字节，双精度	约15位精度，科学计算用
DECIMAL(M,D)	使用字符串存储	精确小数，适合金钱

示例：

CREATE TABLE t_double (
    d1 double, -- 默认
    d2 double(5,2) -- 指定总长度5位，小数部分2位
);

insert into t_double value(2.5,2.5);

CREATE TABLE t_float_double_decimal (
    f1 FLOAT,          -- 单精度浮点
    f2 FLOAT(7,4),     -- 7位总长度，小数4位（MySQL旧版本支持）
    d1 DOUBLE,         -- 双精度浮点
    d2 DOUBLE(10,4),   -- 总长度10，小数4位
    dec1 DECIMAL(10,2) -- 精确小数，总共10位，其中小数2位
);

INSERT INTO t_float_double_decimal (f1, f2, d1, d2, dec1)
VALUES
(123.456789, 123.4567, 123.4567890123, 1234567.1234, 1234567.12),
(0.1, 0.1, 0.1, 0.1, 0.1),
(9999999.9999, 999.9999, 999999999.9999, 9999999.9999, 9999999.99);

SELECT * FROM t_float_double_decimal;

字符串类型

类型	n 取值范围	默认长度	存储特点	适用场景
CHAR(n)	1 ~ 255	1	固定长度，右填空格	身份证号、手机号、性别
VARCHAR(n)	1 ~ 65535 (受行大小限制)	无默认	可变长度 + 长度信息	用户名、地址、邮箱、备注

CHAR(n)

• 存储：固定长度，长度不足时会用空格填充。
• n 的取值范围：1 ~ 255
• 默认值：如果不指定 n，MySQL 默认长度是 1。

示例：

CREATE TABLE char_example (
    code CHAR(10) -- 定长10个字符
);

INSERT INTO char_example (code) VALUES ('abc');
SELECT CONCAT('[', code, ']') FROM char_example;

-- 结果
-- 特点：适合长度固定的数据，如身份证号、手机号、性别（单字符）等。
[abc       ]  -- 左对齐，右边补空格

VARCHAR(n)

• 存储：变长，实际长度 + 1 或 2 字节长度信息（用来存放自身的长度）
  • 1 字节存储长度：当 n ≤ 255
  • 2 字节存储长度：当 n > 255
• n 的取值范围：1 ~ 65535（受行大小限制）
• 默认值：MySQL 不会自动默认长度，需要明确指定。

CREATE TABLE varchar_example (
    nickname VARCHAR(50) -- 最多50个字符
);

INSERT INTO varchar_example (nickname) VALUES ('小明');
SELECT nickname, LENGTH(nickname) FROM varchar_example;

-- 结果
-- 特点：节省空间，适合长度不固定的文本，比如用户名、地址、邮箱等
nickname | LENGTH(nickname)
小明       | 2

ENUM

枚举类型

类型	取值范围 / 限制	默认值	存储特点	适用场景
ENUM	最多 65535 个选项	第一个值	单选，内部按数字存储	性别、状态、类别

CREATE TABLE enum_example (
    gender ENUM('男','女','未知')
);

INSERT INTO enum_example VALUES ('男'),('女'),('未知');
SELECT gender FROM enum_example;

Set 类型

Set集合类型

• SET 是 MySQL 提供的一种 字符串集合类型。
• 可以存储 一个或多个选项，从定义的固定集合里选择。
• 内部 按位存储，即每个选项对应一个二进制位。

类型	取值范围 / 限制	默认值	存储特点	适用场景
SET	最多 64 个选项	空集合	多选，内部按位存储	兴趣爱好、多标签选择

CREATE TABLE set_example (
    hobbies SET('足球','篮球','羽毛球','乒乓球')
);

-- 插入单选
INSERT INTO set_example (hobbies) VALUES ('足球');

-- 插入多选
INSERT INTO set_example (hobbies) VALUES ('篮球,乒乓球');

-- 插入空集合
INSERT INTO set_example (hobbies) VALUES ('');

-- 查询
SELECT *, HEX(hobbies) AS hex_val FROM set_example;

插入非法值

INSERT INTO set_example VALUES('游泳'); -- 会插入空字符串 ''

• 只有定义的集合值才有效

排序显示

• 返回结果总是按 定义顺序 显示，而不是插入顺序

位操作

• 可以用整数做位运算来判断某个选项是否存在，例如：

SELECT hobbies, hobbies & 2 AS has_basketball FROM set_example;

BINARY和VARBINARY

类型	n 取值范围	默认长度	存储特点	适用场景
BINARY(n)	1 ~ 255	1	固定长度二进制	密码哈希、加密值
VARBINARY(n)	1 ~ 65535	无默认	可变长度二进制	文件数据、二进制存储

示例

CREATE TABLE binary_example (
    bin_col BINARY(4),
    varbin_col VARBINARY(10)
);

INSERT INTO binary_example VALUES ('ABCD', '12345');
SELECT bin_col+0 AS bin_int, varbin_col FROM binary_example;

BINARY(n)

• 存储特点：固定长度二进制字符串
• n 的取值范围：1 ~ 255
• 含义：表示 二进制数据的长度（字节数）
• 空间大小：占用 n 字节，不随实际存储数据长度变化

也就是说，BINARY(4) 就固定占 4 字节

不足长度时会 右填零字节

CREATE TABLE bin_example (
    bin_col BINARY(4)
);

INSERT INTO bin_example (bin_col) VALUES ('AB');
SELECT HEX(bin_col) FROM bin_example;

VARBINARY(n)

• 存储特点：变长二进制字符串
• n 的取值范围：1 ~ 65535（受行大小限制）
• 含义：表示 最大可存储二进制数据的长度（字节数）
• 空间大小：

- 实际占用 数据长度 + 1 或 2 字节
  - n ≤ 255 → 1 字节存储长度信息
  - n > 255 → 2 字节存储长度信息
- 数据越短，占用空间越小

CREATE TABLE varbin_example (
    varbin_col VARBINARY(10)
);

INSERT INTO varbin_example (varbin_col) VALUES ('AB');
SELECT HEX(varbin_col), LENGTH(varbin_col) FROM varbin_example;

• 'AB' 实际只占用 2 字节 + 1 字节长度信息
• 没有补零

总结：

• n 不仅是“长度”，也是空间占用上限
• BINARY 固定长度 → 总是占 n 字节
• VARBINARY 变长 → 占用实际数据长度 + 额外长度字节

BLOB类型和TEXT类型

BLOB类型

类型	最大长度	默认值	存储特点	适用场景
TINYBLOB	255 B	无	小二进制数据	小文件、图标
BLOB	64 KB	无	普通二进制数据	图片、音频片段
MEDIUMBLOB	16 MB	无	中等大小二进制数据	视频片段、日志文件
LONGBLOB	4 GB	无	大型二进制数据	视频、文件存储

CREATE TABLE blob_example (
    tiny_blob TINYBLOB,
    normal_blob BLOB,
    medium_blob MEDIUMBLOB,
    long_blob LONGBLOB
);

INSERT INTO blob_example VALUES
('abc', '123456', REPEAT('A',1000), REPEAT('B',1000000));

SELECT LENGTH(tiny_blob), LENGTH(normal_blob), LENGTH(medium_blob), LENGTH(long_blob) FROM blob_example;

TEXT类型

类型	最大长度	默认值	存储特点	适用场景
TINYTEXT	255 字节	无	小文本数据	简短描述、备注
TEXT	64 KB	无	普通文本数据	博客内容、文章
MEDIUMTEXT	16 MB	无	中等长度文本	长篇文章、小说
LONGTEXT	4 GB	无	大型文本	小说、日志、大数据

示例：

CREATE TABLE text_example (
    tiny_text TINYTEXT,
    normal_text TEXT,
    medium_text MEDIUMTEXT,
    long_text LONGTEXT
);

INSERT INTO text_example VALUES
('短', '这是一段普通文本', REPEAT('中等长度文本',1000), REPEAT('长文本',1000000));

SELECT LENGTH(tiny_text), LENGTH(normal_text), LENGTH(medium_text), LENGTH(long_text) FROM text_example;

💡 总结区别

类型	存储对象	长度范围	适用场景	注意事项
BLOB	二进制数据	255B ~ 4GB	图片、音频、文件	可用 HEX() 查看内容
TEXT	文本数据	255B ~ 4GB	文本内容	字符集影响实际字节长度

日期时间类型

DATE

• 格式：YYYY-MM-DD
• 存储大小：3 字节
• 范围：1000-01-01 ~ 9999-12-31
• 用途：只存储日期，不存储时间
• 示例：

CREATE TABLE date_example (
    birth DATE
);

INSERT INTO date_example VALUES ('1990-03-02');
SELECT * FROM date_example;

---

TIME

• 格式：HH:MM:SS
• 存储大小：3 字节
• 范围：-838:59:59 ~ 838:59:59
• 用途：只存储时间或时间间隔
• 示例：

CREATE TABLE time_example (
    work_duration TIME
);

INSERT INTO time_example VALUES ('08:30:00');
SELECT * FROM time_example;

Tip：可以表示负时间，用于计算时间差。

---

DATETIME

• 格式：YYYY-MM-DD HH:MM:SS
• 存储大小：8 字节
• 范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• 用途：存储完整的日期和时间
• 示例：

CREATE TABLE datetime_example (
    created_at DATETIME
);

INSERT INTO datetime_example VALUES ('2025-08-20 16:00:00');
SELECT * FROM datetime_example;

---

TIMESTAMP

• 格式：YYYY-MM-DD HH:MM:SS
• 存储大小：4 字节
• 范围：1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC
• 用途：通常用于记录数据修改时间，可自动更新
• 特性：
  • 默认可自动设置为当前时间：DEFAULT CURRENT_TIMESTAMP
  • 可自动在更新时修改：ON UPDATE CURRENT_TIMESTAMP
• 示例：

CREATE TABLE timestamp_example (
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO timestamp_example VALUES (NULL);
SELECT * FROM timestamp_example;

---

YEAR

• 格式：YYYY
• 存储大小：1 字节
• 范围：1901 ~ 2155
• 用途：只存储年份
• 示例：

CREATE TABLE year_example (
    join_year YEAR
);

INSERT INTO year_example VALUES (2025);
SELECT * FROM year_example;