MySQL数据类型

MySQL支持多种数据类型，合理选择数据类型对于数据库性能和存储效率至关重要。本文档将详细介绍MySQL中的各种数据类型及其使用场景。

数据类型分类

MySQL的数据类型主要分为以下几类：

数值类型
日期和时间类型
字符串类型
JSON类型
空间数据类型

数值类型

整数类型

类型	存储空间	有符号范围	无符号范围
TINYINT	1字节	-128 到 127	0 到 255
SMALLINT	2字节	-32,768 到 32,767	0 到 65,535
MEDIUMINT	3字节	-8,388,608 到 8,388,607	0 到 16,777,215
INT/INTEGER	4字节	-2,147,483,648 到 2,147,483,647	0 到 4,294,967,295
BIGINT	8字节	-2^63 到 2^63-1	0 到 2^64-1

示例：

sql

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tiny_value TINYINT,
    small_value SMALLINT,
    medium_value MEDIUMINT,
    big_value BIGINT,
    unsigned_int INT UNSIGNED  -- 无符号整数
);

浮点数类型

FLOAT：4字节单精度浮点数

sql

CREATE TABLE measurements (
    id INT PRIMARY KEY,
    temperature FLOAT(7,2),  -- 总共7位数字，小数点后2位
    humidity FLOAT
);

DOUBLE：8字节双精度浮点数

sql

CREATE TABLE precise_data (
    id INT PRIMARY KEY,
    latitude DOUBLE(10,8),  -- 更高精度
    longitude DOUBLE
);

DECIMAL：精确的小数类型，适合存储货币等需要精确计算的数据

sql

CREATE TABLE financial_data (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),  -- 总共10位数字，小数点后2位
    tax_rate DECIMAL(5,4)  -- 税率，如0.0825
);

日期和时间类型

类型	格式	范围	存储空间
DATE	YYYY-MM-DD	1000-01-01 到 9999-12-31	3字节
TIME	HH:MM:SS	-838:59:59 到 838:59:59	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 到 9999-12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 到 2038-01-19 03:14:07	4字节
YEAR	YYYY	1901 到 2155	1字节

示例：

sql

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_date DATE,           -- 仅日期
    event_time TIME,           -- 仅时间
    event_datetime DATETIME,   -- 日期和时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 时间戳，自动设置为当前时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 自动更新时间戳
    birth_year YEAR            -- 年份
);

字符串类型

定长字符串

CHAR(n)：固定长度字符串，最大255个字符

sql

CREATE TABLE codes (
    id INT PRIMARY KEY,
    country_code CHAR(2),     -- 国家代码，如'CN'、'US'
    status_code CHAR(1)       -- 状态代码，如'Y'、'N'
);

变长字符串

VARCHAR(n)：可变长度字符串，最大65,535个字符

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),     -- 用户名，最大50个字符
    email VARCHAR(100),       -- 邮箱，最大100个字符
    bio VARCHAR(500)          -- 个人简介，最大500个字符
);

大文本类型

类型	最大长度	用途
TINYTEXT	255字节	小文本数据
TEXT	65,535字节	中等长度文本
MEDIUMTEXT	16,777,215字节	大文本数据
LONGTEXT	4,294,967,295字节	超大文本数据

sql

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    summary TEXT,             -- 文章摘要
    content LONGTEXT,         -- 文章内容
    metadata JSON             -- 元数据（MySQL 5.7+）
);

二进制类型

类型	最大长度	用途
TINYBLOB	255字节	小二进制数据
BLOB	65,535字节	中等二进制数据
MEDIUMBLOB	16,777,215字节	大二进制数据
LONGBLOB	4,294,967,295字节	超大二进制数据

sql

CREATE TABLE files (
    id INT PRIMARY KEY,
    filename VARCHAR(255),
    file_data LONGBLOB,       -- 文件内容
    thumbnail MEDIUMBLOB      -- 缩略图
);

枚举和集合类型

ENUM：枚举类型，只能从预定义的值中选择一个

sql

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium'
);

-- 使用示例
INSERT INTO orders (status, priority) VALUES ('pending', 'high');

SET：集合类型，可以从预定义的值中选择多个

sql

CREATE TABLE user_permissions (
    id INT PRIMARY KEY,
    permissions SET('read', 'write', 'delete', 'admin')
);

-- 使用示例
INSERT INTO user_permissions (permissions) VALUES ('read,write');
INSERT INTO user_permissions (permissions) VALUES ('read,write,admin');

JSON类型

MySQL 5.7+引入了原生JSON类型，提供更好的JSON数据处理能力：

sql

CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    user_data JSON,
    preferences JSON
);

-- 插入JSON数据
INSERT INTO user_profiles (user_data) VALUES (
    '{"name": "John", "age": 30, "hobbies": ["reading", "swimming"]}'
);

-- 查询JSON数据
SELECT JSON_EXTRACT(user_data, '$.name') FROM user_profiles;
SELECT user_data->'$.name' FROM user_profiles;  -- 简写语法

数据类型选择最佳实践

数值类型选择

选择合适范围：选择能容纳预期数据范围的最小类型
- 使用TINYINT存储状态值（0-1）
- 使用SMALLINT存储年龄、评分等
- 使用INT存储ID、计数等
- 使用BIGINT存储大ID、时间戳等
整数vs浮点数：
- 需要精确计算时使用DECIMAL（如货币）
- 一般计算使用FLOAT/DOUBLE
- 不需要小数时使用整数类型

字符串类型选择

CHAR vs VARCHAR：
- 长度固定时使用CHAR（如国家代码）
- 长度变化较大时使用VARCHAR
VARCHAR长度：
- 根据实际需求设置长度
- 过长会浪费空间，过短会截断数据
TEXT类型使用：
- 存储大量文本时使用TEXT类型
- 注意TEXT类型不能有默认值
- 考虑全文搜索需求

日期时间类型选择

DATETIME vs TIMESTAMP：
- 需要时区转换时使用TIMESTAMP
- 不需要时区转换时使用DATETIME
- TIMESTAMP范围较小（到2038年）
存储时间戳：
- 使用TIMESTAMP自动记录创建/更新时间
- 使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP

性能考虑

空间效率：选择最小的数据类型以节省存储空间
索引效率：较小的数据类型索引效率更高
计算效率：整数运算比浮点数运算更快
字符集：选择合适的字符集（utf8mb4 vs latin1）

特殊数据类型

位类型

BIT：位字段类型，用于存储位值

sql

CREATE TABLE flags (
    id INT PRIMARY KEY,
    permissions BIT(8),  -- 8位，可以存储0-255的值
    status_flags BIT(4)  -- 4位，可以存储0-15的值
);

空间数据类型

MySQL支持空间数据类型，用于地理信息存储：

sql

CREATE TABLE locations (
    id INT PRIMARY KEY,
    point_data POINT,        -- 点
    line_data LINESTRING,    -- 线
    polygon_data POLYGON     -- 多边形
);

数据类型转换

MySQL支持隐式和显式类型转换：

sql

-- 隐式转换
SELECT * FROM users WHERE id = '123';  -- 字符串'123'被转换为整数

-- 显式转换
SELECT CAST('123' AS SIGNED);  -- 转换为整数
SELECT CONVERT('123', SIGNED); -- 转换为整数
SELECT CAST(price AS DECIMAL(10,2)); -- 转换为DECIMAL

总结

合理选择MySQL数据类型对数据库性能和存储效率至关重要。在设计表结构时，应根据数据的实际特点和使用场景选择合适的数据类型。正确的数据类型选择不仅能节省存储空间，还能提高查询性能和数据完整性。

MySQL数据类型 #

数据类型分类 #

数值类型 #

整数类型 #

浮点数类型 #

日期和时间类型 #

字符串类型 #

定长字符串 #

变长字符串 #

大文本类型 #

二进制类型 #

枚举和集合类型 #

JSON类型 #

数据类型选择最佳实践 #

数值类型选择 #

字符串类型选择 #

日期时间类型选择 #

性能考虑 #

特殊数据类型 #

位类型 #

空间数据类型 #

数据类型转换 #

总结 #