mysql 数据类型

更新: 10/9/2025字数: 0 字时长: 0 分钟

MySQL 数据类型主要分为三大类：数值类型、字符串类型和日期时间类型。

一、数值类型

用于存储数字，分为整数和浮点数。

1. 整数类型

类型	字节	有符号范围	无符号范围	用例
`TINYINT`	1	-128 到 127	0 到 255	状态码（0/1）、年龄
`SMALLINT`	2	-32768 到 32767	0 到 65535	中小型数值
`MEDIUMINT`	3	-8388608 到 8388607	0 到 16777215
`INT`	4	约 -21 亿到 21 亿	约 0 到 42 亿	最常用的整数类型，如用户 ID、数量
`BIGINT`	8	非常大	非常大	天文数字、雪花 ID

案例：

sql

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 无符号自增主键
    age TINYINT UNSIGNED, -- 年龄，无符号小整数
    status TINYINT(1) DEFAULT 1 -- 状态标志，0或1
);

CREATE TABLE large_data (
    snowflake_id BIGINT UNSIGNED PRIMARY KEY -- 雪花算法生成的巨大ID
);

2. 浮点数类型

用于存储近似数值，计算可能有精度误差。

类型	字节	用途
`FLOAT(M, D)`	4	单精度浮点数
`DOUBLE(M, D)`	8	双精度浮点数

M：总位数（整数+小数）
D：小数位数

案例：

sql

CREATE TABLE products (
    id INT PRIMARY KEY,
    price FLOAT(8, 2), -- 总长8位，小数2位，如 999999.99
    temperature DOUBLE(5, 2) -- 温度，如 -40.50
);

3. 定点数类型

用于存储精确数值，如金额。

类型	描述
`DECIMAL(M, D)`	精确的定点数，不会丢失精度

案例：

sql

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    -- 金额必须精确，使用DECIMAL。总长10位，小数2位，最大 99999999.99
    total_amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders VALUES (1, 99.99);
INSERT INTO orders VALUES (2, 12345678.91); -- 精确存储，无误差

二、字符串类型

用于存储文本和二进制数据。

1. 短文本字符串

类型	最大长度	描述	用例
`CHAR(n)`	255 字符	固定长度，不足补空格	定长数据（MD5、邮编、性别）
`VARCHAR(n)`	65535 字节	可变长度，按实际长度存储	最常用，如用户名、标题、地址

案例：

sql

CREATE TABLE user_profile (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL, -- 用户名，可变长度，最长50字符
    gender CHAR(1), -- 性别，固定1字符，如 'M', 'F'
    postcode CHAR(6), -- 邮编，固定6字符
    password_md5 CHAR(32) -- MD5哈希值，固定32字符
);

-- CHAR 和 VARCHAR 的区别演示
INSERT INTO user_profile VALUES (1, 'Tom', 'M', '100000', 'e10adc3949ba59abbe56e057f20f883e');
-- 'Tom' 在 VARCHAR(50) 中占 3 字节
-- 'M' 在 CHAR(1) 中占 1 字节，即使存 '' 也占 1 字节

2. 长文本字符串

类型	最大长度	描述
`TINYTEXT`	255 字节	短文本
`TEXT`	64KB	常规文本，如文章内容、评论
`MEDIUMTEXT`	16MB	中等长度文本
`LONGTEXT`	4GB	超长文本

案例：

sql

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200), -- 标题用 VARCHAR
    content TEXT, -- 文章内容可能很长，用 TEXT
    summary TINYTEXT -- 摘要较短，用 TINYTEXT
);

3. 二进制数据

类型	描述
`BLOB`	存储二进制数据，如图片、文件
`LONGBLOB`	存储大型二进制数据

注意：现在更常见的做法是将文件存储在磁盘或对象存储（如 AWS S3）中，只在数据库存文件路径（字符串）。

三、日期与时间类型

类型	格式	范围	用例
`DATE`	`YYYY-MM-DD`	1000-01-01 到 9999-12-31	生日、发布日期
`TIME`	`HH:MM:SS`	-838:59:59 到 838:59:59	持续时间
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	1000-01-01 00:00:00 到 9999-12-31 23:59:59	创建时间、更新时间（常用）
`TIMESTAMP`	`YYYY-MM-DD HH:MM:SS`	1970-01-01 00:00:01 到 2038-01-19 03:14:07	自动时间戳、时区相关时间
`YEAR`	`YYYY`	1901 到 2155	年份

案例：

sql

CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    -- 发布日期，只需要日期部分
    publish_date DATE,
    -- 创建时间，需要精确到秒，范围大，用 DATETIME
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    -- 更新时间，自动更新，并且需要处理时区，用 TIMESTAMP
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO posts (id, title, publish_date)
VALUES (1, 'MySQL教程', '2023-10-25');
-- created_at 和 updated_at 会自动设置为当前时间

四、枚举与集合类型

类型	描述	用例
`ENUM('val1', 'val2', ...)`	枚举，只能从预定义列表选一个	状态、类型
`SET('val1', 'val2', ...)`	集合，可以从预定义列表选多个	标签、兴趣爱好

案例：

sql

CREATE TABLE tasks (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    -- 状态只能是以下几种之一，比VARCHAR更高效
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    -- 标签可以多选
    tags SET('urgent', 'feature', 'bug', 'optimization')
);

INSERT INTO tasks VALUES
(1, '修复登录BUG', 'completed', 'bug,urgent'),
(2, '开发新功能', 'in_progress', 'feature');

综合案例：博客系统表结构

sql

CREATE DATABASE my_blog;
USE my_blog;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password CHAR(60) NOT NULL, -- BCrypt 哈希密码，固定60字符
    display_name VARCHAR(50),
    bio TINYTEXT,
    avatar_url VARCHAR(255),
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    description VARCHAR(200)
);

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    excerpt VARCHAR(500), -- 摘要
    content TEXT NOT NULL, -- 正文
    cover_image VARCHAR(255), -- 封面图路径
    category_id TINYINT UNSIGNED,
    author_id INT UNSIGNED,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    -- 金额相关的演示
    word_count MEDIUMINT UNSIGNED,
    reading_time TINYINT UNSIGNED,
    -- 时间相关
    published_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (author_id) REFERENCES users(id)
);

-- 插入示例数据
INSERT INTO users (username, email, password, display_name)
VALUES ('zhangsan', 'zhangsan@example.com', 'hashed_password', '张三');

INSERT INTO categories (name, description)
VALUES ('技术教程', '编程与技术相关文章');

INSERT INTO articles (title, content, category_id, author_id, status, published_at)
VALUES ('MySQL数据类型详解', '这里是详细的文章内容...', 1, 1, 'published', NOW());

选择数据类型的最佳实践总结

宁小勿大：在满足需求的前提下，选择占用空间最小的类型。
简单就好：整数比字符串处理速度更快。
避免 NULL：尽量设置字段为 NOT NULL 并给出默认值。
精确金额用 DECIMAL：永远不要用 FLOAT/DOUBLE 存储金额。
主键用 INT UNSIGNED AUTO_INCREMENT：这是最通用和高效的做法。
变长字符串用 VARCHAR，定长编码用 CHAR。
DATETIME 和 TIMESTAMP：根据是否需要时区支持和范围要求来选择。

mysql 数据类型 ​

一、数值类型 ​

1. 整数类型 ​

2. 浮点数类型 ​

3. 定点数类型 ​

二、字符串类型 ​

1. 短文本字符串 ​

2. 长文本字符串 ​

3. 二进制数据 ​

三、日期与时间类型 ​

四、枚举与集合类型 ​

综合案例：博客系统表结构 ​

选择数据类型的最佳实践总结 ​