跳到主要内容

MySQL 数据类型

本章将详细介绍 MySQL 支持的各种数据类型及其使用场景。

知识速查 (常用类型)​

类型示例适用场景
INTINT整数 ID、数量
DECIMALDECIMAL(10, 2)金额、精确小数
VARCHARVARCHAR(255)姓名、地址、标题
TEXTTEXT文章内容、详细描述
DATETIMEDATETIME创建、修改时间
JSONJSON半结构化配置、属性
ENUMENUM('A', 'B')状态、性别 (固定值)

数据类型概述​

MySQL 支持以下几类数据类型：

  • 数值类型：整数、浮点数、定点数
  • 字符串类型：CHAR、VARCHAR、TEXT 等
  • 日期时间类型：DATE、TIME、DATETIME、TIMESTAMP 等
  • JSON 类型：JSON 数据
  • 其他类型：ENUM、SET、BLOB 等

数值类型​

整数类型​

类型字节有符号范围无符号范围
TINYINT1-128 ~ 1270 ~ 255
SMALLINT2-32,768 ~ 32,7670 ~ 65,535
MEDIUMINT3-8,388,608 ~ 8,388,6070 ~ 16,777,215
INT4-21亿 ~ 21亿0 ~ 42亿
BIGINT8-922亿亿 ~ 922亿亿0 ~ 1844亿亿

使用示例：

CREATE TABLE numbers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED,           -- 年龄（0-255）
    score SMALLINT,                  -- 分数（-32768~32767）
    population INT UNSIGNED,         -- 人口（无符号）
    view_count BIGINT UNSIGNED       -- 浏览量（大数字）
);

-- 插入数据
INSERT INTO numbers (age, score, population, view_count)
VALUES (25, 98, 1400000000, 9999999999999);

整数显示宽度：

-- INT(M) 中的 M 是显示宽度，不影响存储范围
-- 配合 ZEROFILL 使用时，不足位数会用 0 填充
CREATE TABLE display_width (
    id INT(6) ZEROFILL  -- 如：000001
);

-- MySQL 8.0.17+ 已弃用显示宽度

浮点类型​

类型字节说明
FLOAT4单精度浮点数
DOUBLE8双精度浮点数
CREATE TABLE floats (
    id INT PRIMARY KEY,
    price FLOAT,                 -- 单精度
    rate DOUBLE,                 -- 双精度
    precise_price FLOAT(7, 2),   -- 总共 7 位，小数 2 位
    precise_rate DOUBLE(15, 4)   -- 总共 15 位，小数 4 位
);

-- 注意：浮点数有精度问题
INSERT INTO floats VALUES (1, 0.1, 0.1, 123.45, 1234567890.1234);
SELECT price + 0.1 FROM floats WHERE id = 1;  -- 可能不是精确的 0.2

定点类型​

DECIMAL 用于存储精确的小数：

CREATE TABLE decimals (
    id INT PRIMARY KEY,
    money DECIMAL(10, 2),        -- 总共 10 位，小数 2 位
    salary DECIMAL(12, 2)        -- 适合存储金额
);

-- DECIMAL 存储精确值
INSERT INTO decimals VALUES (1, 12345678.90, 9999999999.99);

-- 金额计算必须用 DECIMAL
SELECT money + 0.01 FROM decimals;  -- 精确计算

浮点数 vs 定点数：

特性FLOAT/DOUBLEDECIMAL
存储方式二进制浮点字符串存储
精度近似值精确值
适用场景科学计算金额计算
性能较高较低

BIT 类型​

存储位字段：

CREATE TABLE bit_example (
    id INT PRIMARY KEY,
    flags BIT(8)    -- 8 位
);

INSERT INTO bit_example VALUES (1, b'10101010');

-- 读取时显示为十进制或十六进制
SELECT id, flags, flags + 0 AS decimal_value, HEX(flags) AS hex_value
FROM bit_example;

字符串类型​

CHAR 和 VARCHAR​

类型说明最大长度
CHAR(M)定长字符串255 字符
VARCHAR(M)变长字符串65,535 字节
CREATE TABLE strings (
    id INT PRIMARY KEY,
    code CHAR(10),              -- 固定 10 字符，不足补空格
    name VARCHAR(50),           -- 最多 50 字符
    description VARCHAR(1000)   -- 最多 1000 字符
);

INSERT INTO strings VALUES (1, 'ABC', '张三', '这是一段描述');

-- CHAR 适合存储固定长度的数据
-- 如：手机号、身份证号、MD5 值

CHAR vs VARCHAR：

特性CHARVARCHAR
存储定长，不足补空格变长，按实际存储
最大长度255 字符65,535 字节
性能查询较快空间效率高
适用固定长度数据变长数据

TEXT 类型​

类型最大长度
TINYTEXT255 字节
TEXT65,535 字节 (64KB)
MEDIUMTEXT16MB
LONGTEXT4GB
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    summary TEXT,               -- 文章摘要
    content MEDIUMTEXT          -- 文章内容
);

-- TEXT 类型不能有默认值
-- TEXT 类型在排序时只使用前 max_sort_length 字节

BINARY 和 VARBINARY​

存储二进制字符串：

CREATE TABLE binary_data (
    id INT PRIMARY KEY,
    hash BINARY(32),            -- 存储 MD5 哈希
    uuid BINARY(16)             -- 存储 UUID
);

-- 查询时使用 HEX() 函数
SELECT id, HEX(hash) AS hash_hex FROM binary_data;

BLOB 类型​

存储大二进制数据：

类型最大长度
TINYBLOB255 字节
BLOB65,535 字节 (64KB)
MEDIUMBLOB16MB
LONGBLOB4GB
CREATE TABLE files (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    mime_type VARCHAR(100),
    data MEDIUMBLOB             -- 存储文件内容
);

-- 不建议在数据库中存储大文件
-- 更好的做法是存储文件路径，文件存储在文件系统或对象存储中

日期时间类型​

类型概览​

类型格式范围
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-31
TIMEHH:MM:SS-838:59:59 ~ 838:59:59
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 ~ 2038-01-19 03:14:07
YEARYYYY1901 ~ 2155

DATE 类型​

CREATE TABLE dates (
    id INT PRIMARY KEY,
    birth_date DATE,            -- 生日
    hire_date DATE              -- 入职日期
);

INSERT INTO dates VALUES (1, '1990-05-15', '2024-01-01');

-- 日期函数
SELECT 
    birth_date,
    YEAR(birth_date) AS year,
    MONTH(birth_date) AS month,
    DAY(birth_date) AS day,
    DAYOFWEEK(birth_date) AS weekday
FROM dates;

TIME 类型​

CREATE TABLE times (
    id INT PRIMARY KEY,
    start_time TIME,            -- 开始时间
    duration TIME               -- 持续时间
);

INSERT INTO times VALUES (1, '09:00:00', '02:30:00');

-- 时间函数
SELECT 
    start_time,
    HOUR(start_time) AS hour,
    MINUTE(start_time) AS minute,
    SECOND(start_time) AS second
FROM times;

DATETIME 类型​

CREATE TABLE datetimes (
    id INT PRIMARY KEY,
    created_at DATETIME,        -- 创建时间
    updated_at DATETIME         -- 更新时间
);

-- 插入当前时间
INSERT INTO datetimes VALUES (1, NOW(), NOW());

-- MySQL 5.6.5+ 支持自动更新
CREATE TABLE auto_datetime (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

TIMESTAMP 类型​

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

-- TIMESTAMP 自动转换为 UTC 存储，查询时转换为当前时区
-- DATETIME 不做时区转换

DATETIME vs TIMESTAMP：

特性DATETIMETIMESTAMP
范围1000-9999 年1970-2038 年
存储8 字节4 字节
时区不转换自动转换
默认值支持函数默认支持自动更新

YEAR 类型​

CREATE TABLE years (
    id INT PRIMARY KEY,
    year_value YEAR
);

INSERT INTO years VALUES (1, 2024);

-- 两位数年份的转换规则
-- 00-69 → 2000-2069
-- 70-99 → 1970-1999
INSERT INTO years VALUES (2, 24);  -- 2024
INSERT INTO years VALUES (3, 99);  -- 1999

JSON 类型​

MySQL 5.7.8+ 支持 JSON 类型：

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

-- 插入 JSON 数据
INSERT INTO json_data VALUES (
    1,
    '{"name": "张三", "age": 25, "hobbies": ["阅读", "游泳"]}',
    '{"theme": "dark", "language": "zh-CN"}'
);

-- JSON 函数
SELECT 
    data->>'$.name' AS name,
    data->'$.age' AS age,
    data->'$.hobbies[0]' AS first_hobby
FROM json_data;

-- JSON 路径表达式
-- $    根元素
-- $.name  对象属性
-- $[0]    数组元素
-- $.hobbies[*]  所有数组元素

-- 常用 JSON 函数
SELECT 
    JSON_EXTRACT(data, '$.name') AS name,           -- 提取值
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name_unquoted,  -- 去引号
    JSON_TYPE(data) AS type,                         -- 类型
    JSON_CONTAINS(data, '"张三"', '$.name') AS contains,  -- 是否包含
    JSON_SEARCH(data, 'one', '张三') AS search       -- 搜索路径
FROM json_data;

-- 修改 JSON 数据
UPDATE json_data 
SET data = JSON_SET(data, '$.age', 26)
WHERE id = 1;

UPDATE json_data 
SET data = JSON_ARRAY_APPEND(data, '$.hobbies', '编程')
WHERE id = 1;

JSON 函数一览​

函数说明
JSON_EXTRACT(json, path)提取值
JSON_UNQUOTE(json)去除引号
JSON_TYPE(json)返回类型
JSON_ARRAY(...)创建数组
JSON_OBJECT(...)创建对象
JSON_MERGE_PRESERVE(json1, json2)合并 JSON
JSON_SET(json, path, value)设置值
JSON_INSERT(json, path, value)插入值
JSON_REPLACE(json, path, value)替换值
JSON_REMOVE(json, path)删除值
JSON_CONTAINS(json, value, path)是否包含
JSON_SEARCH(json, mode, value)搜索

ENUM 和 SET 类型​

ENUM 类型​

枚举类型，只能选择列出的值之一：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    gender ENUM('male', 'female', 'other')
);

INSERT INTO users (name, status, gender) VALUES ('张三', 'active', 'male');

-- ENUM 存储为整数（从 1 开始）
SELECT status, status + 0 AS status_int FROM users;

-- 使用数值插入
INSERT INTO users (name, status) VALUES ('李四', 1);  -- 'active'

-- 查询枚举值列表
SHOW COLUMNS FROM users LIKE 'status';

ENUM 优缺点：

优点：

  • 存储紧凑（1-2 字节）
  • 数据验证

缺点：

  • 修改需要 ALTER TABLE
  • 查询结果为数字需要转换

SET 类型​

集合类型，可以选择多个值：

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    tags SET('技术', '生活', '旅行', '美食', '编程')
);

INSERT INTO articles (title, tags) VALUES ('MySQL 教程', '技术,编程');
INSERT INTO articles (title, tags) VALUES ('周末游记', '生活,旅行');

-- 查询包含特定标签的文章
SELECT * FROM articles WHERE FIND_IN_SET('技术', tags) > 0;

-- 使用位运算查询
SELECT * FROM articles WHERE tags & 1;  -- '技术' 是第 1 位

数据类型选择原则​

整数选择​

-- 根据数值范围选择最小类型
age TINYINT UNSIGNED        -- 0-255，足够存储年龄
year SMALLINT UNSIGNED      -- 0-65535，足够存储年份
view_count INT UNSIGNED     -- 0-42亿，适合访问量
big_id BIGINT UNSIGNED      -- 超大数字

字符串选择​

-- 固定长度用 CHAR
phone CHAR(11)              -- 手机号
id_card CHAR(18)            -- 身份证号
md5 CHAR(32)                -- MD5 哈希

-- 变长用 VARCHAR
name VARCHAR(50)            -- 姓名
email VARCHAR(100)          -- 邮箱
title VARCHAR(200)          -- 标题

-- 长文本用 TEXT
content TEXT                -- 文章内容

日期时间选择​

-- 只需日期
birth_date DATE

-- 需要日期和时间
created_at DATETIME         -- 范围大，不受时区影响

-- 需要时区支持
event_time TIMESTAMP        -- 自动时区转换

-- 只需年份
year_value YEAR

小数选择​

-- 金额计算（必须精确）
price DECIMAL(10, 2)
salary DECIMAL(12, 2)

-- 科学计算（允许近似）
rate DOUBLE
ratio FLOAT

小结​

本章我们学习了：

  1. 数值类型：整数、浮点数、定点数、BIT
  2. 字符串类型：CHAR、VARCHAR、TEXT、BLOB
  3. 日期时间类型：DATE、TIME、DATETIME、TIMESTAMP、YEAR
  4. JSON 类型：JSON 数据存储和操作函数
  5. ENUM 和 SET：枚举和集合类型
  6. 类型选择原则：根据实际需求选择合适的类型

练习​

  1. 创建一个用户表，选择合适的数据类型存储各种用户信息
  2. 设计一个订单表，正确处理金额和时间字段
  3. 使用 JSON 类型存储用户配置信息
  4. 比较 DATETIME 和 TIMESTAMP 在不同时区下的行为

参考资源​