数据类型

一、整数类型

1.1 整数类型列表

1.1.1 类型对比

1.1.2 存储需求

计算公式：

• TINYINT：1字节 = 8位
• SMALLINT：2字节 = 16位
• MEDIUMINT：3字节 = 24位
• INT：4字节 = 32位
• BIGINT：8字节 = 64位

1.2 使用示例

1.2.1 创建表

CREATE TABLE integer_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age TINYINT UNSIGNED,
    score SMALLINT,
    population MEDIUMINT,
    world_population BIGINT
);

1.2.2 插入数据

INSERT INTO integer_types (age, score, population, world_population)
VALUES (25, 95, 500000, 7900000000);

1.2.3 查询数据

SELECT * FROM integer_types;

1.3 显示宽度与填充

1.3.1 显示宽度

语法格式：

INT(M)

参数说明：

• M：显示宽度，不限制数值范围
• 配合ZEROFILL使用

示例：

CREATE TABLE display_width (
    id INT(5) ZEROFILL,
    num INT(10) ZEROFILL
);

INSERT INTO display_width VALUES (123, 456);

SELECT * FROM display_width;

输出：

+-------+------------+
| id    | num        |
+-------+------------+
| 00123 | 0000000456 |
+-------+------------+

1.4 AUTO_INCREMENT

1.4.1 自增属性

特点：

• 自动生成唯一值
• 通常用于主键
• 每个表只能有一个自增列

示例：

CREATE TABLE auto_increment_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

INSERT INTO auto_increment_test (name) VALUES ('张三');
INSERT INTO auto_increment_test (name) VALUES ('李四');
INSERT INTO auto_increment_test (name) VALUES ('王五');

SELECT * FROM auto_increment_test;

输出：

+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+

1.4.2 设置起始值

CREATE TABLE auto_increment_start (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
) AUTO_INCREMENT = 1000;

INSERT INTO auto_increment_start (name) VALUES ('测试');

SELECT * FROM auto_increment_start;

输出：

+------+--------+
| id   | name   |
+------+--------+
| 1000 | 测试   |
+------+--------+

二、浮点类型

2.1 浮点数类型

2.1.1 类型对比

2.1.2 精度对比

FLOAT：

• 精度：约7位有效数字
• 存储范围：-3.402823466E+38 ~ -1.175494351E-38

DOUBLE：

• 精度：约15位有效数字
• 存储范围：-1.7976931348623157E+308 ~ -2.2250738585072014E-308

DECIMAL：

• 精度：可精确指定
• 存储范围：取决于M和D

2.2 DECIMAL类型

2.2.1 语法格式

DECIMAL(M, D)

参数说明：

• M：总位数（精度），最大65
• D：小数位数（标度），最大30

示例：

CREATE TABLE decimal_test (
    id INT PRIMARY KEY,
    price DECIMAL(10, 2),
    rate DECIMAL(5, 3)
);

INSERT INTO decimal_test VALUES (1, 12345.67, 0.125);

SELECT * FROM decimal_test;

输出：

+----+----------+-------+
| id | price    | rate  |
+----+----------+-------+
|  1 | 12345.67 | 0.125 |
+----+----------+-------+

2.2.2 精确计算

使用DECIMAL：

CREATE TABLE money_test (
    id INT PRIMARY KEY,
    amount DECIMAL(10, 2)
);

INSERT INTO money_test VALUES (1, 0.1), (2, 0.2);

SELECT SUM(amount) FROM money_test;

输出：

+-------------+
| SUM(amount) |
+-------------+
|        0.30 |
+-------------+

使用FLOAT（精度丢失）：

CREATE TABLE float_test (
    id INT PRIMARY KEY,
    amount FLOAT
);

INSERT INTO float_test VALUES (1, 0.1), (2, 0.2);

SELECT SUM(amount) FROM float_test;

输出：

+-------------+
| SUM(amount) |
+-------------+
|   0.3000000 |
+-------------+

2.3 类型选择

选择建议：

• 科学计算：FLOAT或DOUBLE
• 货币金额：DECIMAL
• 百分比：DECIMAL
• 需要精确计算：DECIMAL

三、字符串类型

3.1 CHAR与VARCHAR

3.1.1 类型对比

3.1.2 CHAR类型

特点：

• 定长存储
• 最大255字符
• 自动补空格
• 查询效率高

示例：

CREATE TABLE char_test (
    id INT PRIMARY KEY,
    code CHAR(10)
);

INSERT INTO char_test VALUES (1, 'ABC');

SELECT id, code, LENGTH(code), CHAR_LENGTH(code) FROM char_test;

输出：

+----+------------+--------------+-------------------+
| id | code       | LENGTH(code) | CHAR_LENGTH(code) |
+----+------------+--------------+-------------------+
|  1 | ABC        |           10 |                10 |
+----+------------+--------------+-------------------+

3.1.3 VARCHAR类型

特点：

• 变长存储
• 最大65535字节
• 不补空格
• 节省空间

示例：

CREATE TABLE varchar_test (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    description VARCHAR(255)
);

INSERT INTO varchar_test VALUES (1, '张三', '这是一个测试描述');

SELECT id, name, LENGTH(name), CHAR_LENGTH(name) FROM varchar_test;

输出：

+----+--------+--------------+-------------------+
| id | name   | LENGTH(name) | CHAR_LENGTH(name) |
+----+--------+--------------+-------------------+
|  1 | 张三   |            6 |                 2 |
+----+--------+--------------+-------------------+

3.1.4 存储空间

VARCHAR存储需求：

• 长度 ≤ 255：1字节存储长度
• 长度 > 255：2字节存储长度

示例：

• VARCHAR(10)：最多11字节（10字节数据+1字节长度）
• VARCHAR(1000)：最多1002字节（1000字节数据+2字节长度）

3.2 TEXT类型

3.2.1 类型对比

3.2.2 使用示例

CREATE TABLE text_test (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    article MEDIUMTEXT
);

INSERT INTO text_test (id, title, content) 
VALUES (1, '文章标题', '这是一段较长的文本内容...');

3.3 ENUM类型

3.3.1 枚举类型

语法格式：

ENUM('值1', '值2', '值3', ...)

特点：

• 只能选择列表中的值
• 存储为整数
• 节省空间
• 最多65535个值

示例：

CREATE TABLE enum_test (
    id INT PRIMARY KEY,
    status ENUM('待审核', '已发布', '已下线'),
    gender ENUM('男', '女', '保密')
);

INSERT INTO enum_test VALUES (1, '待审核', '男');

SELECT id, status, status+0 FROM enum_test;

输出：

+----+-----------+----------+
| id | status    | status+0 |
+----+-----------+----------+
|  1 | 待审核    |        1 |
+----+-----------+----------+

3.4 SET类型

3.4.1 集合类型

语法格式：

SET('值1', '值2', '值3', ...)

特点：

• 可选择多个值
• 存储为位图
• 最多64个成员

示例：

CREATE TABLE set_test (
    id INT PRIMARY KEY,
    hobbies SET('阅读', '运动', '音乐', '旅行', '游戏')
);

INSERT INTO set_test VALUES (1, '阅读,运动,音乐');

SELECT id, hobbies FROM set_test;

输出：

+----+-------------------+
| id | hobbies           |
+----+-------------------+
|  1 | 阅读,运动,音乐    |
+----+-------------------+

四、时间日期类型

4.1 类型对比

4.2 DATE类型

4.2.1 日期格式

标准格式：YYYY-MM-DD

示例：

CREATE TABLE date_test (
    id INT PRIMARY KEY,
    birth_date DATE,
    hire_date DATE
);

INSERT INTO date_test VALUES (1, '1990-05-15', '2020-03-20');

SELECT * FROM date_test;

4.2.2 日期函数

-- 当前日期
SELECT CURDATE();

-- 日期计算
SELECT DATE_ADD('2024-01-01', INTERVAL 7 DAY);
SELECT DATE_SUB('2024-01-01', INTERVAL 1 MONTH);

-- 日期差值
SELECT DATEDIFF('2024-12-31', '2024-01-01');

-- 提取日期部分
SELECT YEAR('2024-05-20');
SELECT MONTH('2024-05-20');
SELECT DAY('2024-05-20');

4.3 TIME类型

4.3.1 时间格式

标准格式：HH:MM:SS

示例：

CREATE TABLE time_test (
    id INT PRIMARY KEY,
    start_time TIME,
    duration TIME
);

INSERT INTO time_test VALUES (1, '09:00:00', '08:30:00');

SELECT * FROM time_test;

4.3.2 时间函数

-- 当前时间
SELECT CURTIME();

-- 时间计算
SELECT TIME_ADD('10:00:00', INTERVAL 30 MINUTE);
SELECT TIME_SUB('10:00:00', INTERVAL 1 HOUR);

-- 时间差值
SELECT TIMEDIFF('18:00:00', '09:00:00');

-- 提取时间部分
SELECT HOUR('10:30:45');
SELECT MINUTE('10:30:45');
SELECT SECOND('10:30:45');

4.4 DATETIME类型

4.4.1 日期时间格式

标准格式：YYYY-MM-DD HH:MM:SS

示例：

CREATE TABLE datetime_test (
    id INT PRIMARY KEY,
    create_time DATETIME,
    update_time DATETIME
);

INSERT INTO datetime_test VALUES (1, '2024-05-20 10:30:00', '2024-05-20 15:45:00');

SELECT * FROM datetime_test;

4.4.2 日期时间函数

-- 当前日期时间
SELECT NOW();
SELECT SYSDATE();

-- 日期时间计算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- 格式化
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s');

4.5 TIMESTAMP类型

4.5.1 时间戳格式

特点：

• 存储为Unix时间戳
• 自动转换为本地时区
• 自动更新

示例：

CREATE TABLE timestamp_test (
    id INT PRIMARY KEY,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO timestamp_test (id) VALUES (1);

SELECT * FROM timestamp_test;

4.5.2 自动更新

-- 插入数据时自动设置
INSERT INTO timestamp_test (id) VALUES (2);

-- 更新数据时自动更新
UPDATE timestamp_test SET id = 3 WHERE id = 2;

4.6 YEAR类型

4.6.1 年份格式

格式：YYYY

示例：

CREATE TABLE year_test (
    id INT PRIMARY KEY,
    birth_year YEAR
);

INSERT INTO year_test VALUES (1, 1990);

SELECT * FROM year_test;

五、JSON类型

5.1 JSON类型特点

5.1.1 优势

• 原生支持JSON格式
• 自动验证JSON有效性
• 高效存储和查询
• 支持索引（MySQL 8.0+）

5.2 JSON操作

5.2.1 创建JSON列

CREATE TABLE json_test (
    id INT PRIMARY KEY,
    data JSON
);

INSERT INTO json_test VALUES 
(1, '{"name": "张三", "age": 25, "hobbies": ["阅读", "运动"]}'),
(2, '{"name": "李四", "age": 30, "city": "北京"}');

5.2.2 提取JSON值

-- 提取字段
SELECT id, data->>'$.name' AS name, data->>'$.age' AS age
FROM json_test;

-- 提取数组元素
SELECT id, data->'$.hobbies[0]' AS first_hobby
FROM json_test;

5.2.3 JSON函数

-- 创建JSON
SELECT JSON_OBJECT('name', '王五', 'age', 28);

-- 合并JSON
SELECT JSON_MERGE_PRESERVE(
    '{"name": "张三"}',
    '{"age": 25}'
);

-- 设置值
SELECT JSON_SET('{"name": "张三"}', '$.age', 25);

-- 删除值
SELECT JSON_REMOVE('{"name": "张三", "age": 25}', '$.age');

-- 查找路径
SELECT JSON_CONTAINS_PATH('{"name": "张三"}', 'one', '$.name');

六、二进制类型

6.1 类型对比

6.2 使用示例

6.2.1 存储二进制数据

CREATE TABLE blob_test (
    id INT PRIMARY KEY,
    file_name VARCHAR(100),
    file_data MEDIUMBLOB
);

-- 插入二进制数据
INSERT INTO blob_test (id, file_name, file_data)
VALUES (1, 'test.jpg', LOAD_FILE('/path/to/test.jpg'));

6.2.2 读取二进制数据

SELECT id, file_name, LENGTH(file_data) AS file_size
FROM blob_test;

七、类型选择原则

7.1 选择建议

7.1.1 整数类型

• 小范围整数：TINYINT
• 常规整数：INT
• 大整数：BIGINT
• 主键：INT或BIGINT

7.1.2 浮点类型

• 科学计算：FLOAT或DOUBLE
• 货币金额：DECIMAL
• 百分比：DECIMAL

7.1.3 字符串类型

• 固定长度：CHAR
• 变长字符串：VARCHAR
• 长文本：TEXT
• 枚举值：ENUM
• 多选值：SET

7.1.4 时间类型

• 仅日期：DATE
• 仅时间：TIME
• 日期时间：DATETIME
• 自动更新：TIMESTAMP
• 仅年份：YEAR

7.2 性能优化

7.2.1 使用合适类型

错误示例：

CREATE TABLE bad_design (
    id VARCHAR(50) PRIMARY KEY,
    age VARCHAR(10),
    price VARCHAR(20)
);

正确示例：

CREATE TABLE good_design (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age TINYINT UNSIGNED,
    price DECIMAL(10, 2)
);

7.2.2 避免过度设计

过度设计：

CREATE TABLE over_design (
    id BIGINT PRIMARY KEY,
    name VARCHAR(1000),
    description LONGTEXT
);

合理设计：

CREATE TABLE reasonable_design (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    description TEXT
);

八、本章小结

8.1 核心要点

✅ 整数类型根据范围选择 ✅ 货币金额使用DECIMAL ✅ 字符串根据长度选择CHAR或VARCHAR ✅ 时间类型根据需求选择 ✅ JSON类型支持原生JSON操作

8.2 验证清单

完成本章学习后，请确认您能够：

• 选择合适的整数类型
• 使用DECIMAL处理精确计算
• 区分CHAR和VARCHAR
• 选择合适的时间类型
• 使用JSON类型存储JSON数据