mysql

数据类型与表设计

By AI-Writer 6 min read

数据类型与表设计

正确选择数据类型和约束是数据库设计的第一步。选型不当会导致存储空间浪费、查询性能下降、数据完整性受损，甚至引发线上故障。本文系统梳理 MySQL 8.x 的核心数据类型，并讲解六种约束的含义、作用场景与设计原则。

数值类型

MySQL 提供精确数值和近似数值两大类。

精确数值：INT 系列

sql
-- TINYINT：1 字节，-128 ~ 127（或 0~255 无符号）
CREATE TABLE t1 (age TINYINT UNSIGNED);

-- SMALLINT：2 字节，-32768 ~ 32767
CREATE TABLE t2 (score SMALLINT);

-- INT / INTEGER：4 字节，-21 亿 ~ 21 亿（日常使用最频繁）
CREATE TABLE t3 (user_id INT, view_count INT);

-- BIGINT：8 字节，适用超出 INT 范围的场景（如访问量、金额精确到分的乘积）
CREATE TABLE t4 (order_amount BIGINT);  -- 存储单位：分

原则：在满足范围的前提下，优先使用更小的类型。每节省 1 字节，索引体积更小，内存扫描更快。

精确数值：DECIMAL

用于存储精确小数（货币金额、科学计算），避免浮点误差：

sql
-- DECIMAL(M, D)：M = 总位数（含小数），D = 小数位数
-- 示例：DECIMAL(10,2) 表示最多 10 位数字，其中 2 位小数
-- 范围：-9999999.99 ~ 9999999.99
CREATE TABLE accounts (
    balance    DECIMAL(15, 2),    -- 存储余额，最多 13 位整数 + 2 位小数
    interest   DECIMAL(5, 4),     -- 利率，如 0.0355
    exchange_rate DECIMAL(10, 6)  -- 汇率，6 位小数保证精度
);

近似数值：FLOAT 与 DOUBLE

sql
-- FLOAT：4 字节，单精度浮点（不推荐存储精确值）
CREATE TABLE sensor_data (temperature FLOAT);

-- DOUBLE：8 字节，双精度浮点
CREATE TABLE math_constants (value DOUBLE);

原则：永远不要用 FLOAT/DOUBLE 存储货币。它们是近似值，0.1 + 0.2 = 0.30000000000000004 在二进制浮点中是真实存在的误差。

无符号属性 UNSIGNED

sql
-- 年龄不可能为负，使用 UNSIGNED
CREATE TABLE users (age TINYINT UNSIGNED);

-- 点赞数、访问量
CREATE TABLE articles (likes INT UNSIGNED, views BIGINT UNSIGNED);

字符类型

CHAR 与 VARCHAR

这是最容易选错的类型组合：

sql
-- CHAR(N)：固定长度，不足部分用空格补齐。最大 255 字符。
-- 适用：国家代码（CN/USA）、性别（M/F）、状态码等**长度固定的字段**
CREATE TABLE countries (code CHAR(2), name VARCHAR(100));

-- VARCHAR(N)：可变长度，只存储实际字符。最大 65535 字节（实际受行宽限制约 21653 字符）
-- 适用：用户名、邮箱、地址等**长度不确定的字段**
CREATE TABLE users (username VARCHAR(50), bio VARCHAR(500));
特性CHARVARCHAR
存储长度固定 N实际字符数 + 1~2 字节长度前缀
性能写入/读取略快（无需计算长度）略低（需解析长度前缀）
空间空白浪费（不足 N 时填充空格）紧凑
最大长度255~21653（字符集为 utf8mb4 时约 8000）

字符集：utf8mb4（必须掌握）

MySQL 的 utf8 字符集仅支持 3 字节，无法存储 Emoji（需要 4 字节）。现代应用必须使用 utf8mb4：

sql
-- 建库时指定字符集
CREATE DATABASE myapp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 建表时指定
CREATE TABLE users (
    username VARCHAR(50) CHARACTER SET utf8mb4,
    bio TEXT CHARACTER SET utf8mb4
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

建议：在 my.cnf 中将默认字符集设为 utf8mb4，一劳永逸。

TEXT 系列

用于存储超长文本，TEXT 不参与行大小计算（独立存储）：

sql
CREATE TABLE articles (
    title   VARCHAR(200),   -- 标题有限长，用 VARCHAR
    content TEXT,           -- 文章正文，不限制长度
    summary MEDIUMTEXT,     -- 中等长度摘要（约 16MB）
    raw_data LONGTEXT       -- 超大文本，如 JSON、日志
);

日期时间类型

sql
CREATE TABLE events (
    -- DATE：日期（1000-01-01 ~ 9999-12-31）
    birthday    DATE,

    -- TIME：时间（-838:59:59 ~ 838:59:59）
    work_hours  TIME,

    -- DATETIME：日期+时间，不受时区影响（存什么读什么）
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,

    -- TIMESTAMP：日期+时间，受时区影响（存 UTC，MySQL 自动转换）
    -- 2038 年问题：最大值 2038-01-19 03:14:07
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- YEAR：年份（1901~2155 或 00~69/70~99）
    birth_year  YEAR
);

原则：

  • 用户注册时间、订单时间等业务时间，用 DATETIME
  • 自动记录变更时间（created_at / updated_at）用 TIMESTAMP
  • 不存具体时间只存日期用 DATE（如生日）
  • 有国际化需求时注意 DATETIME vs TIMESTAMP 的时区行为差异

约束（Constraint）

主键 PRIMARY KEY

唯一标识表中每一行的列（或列组合），不允许为空：

sql
-- 单列主键
CREATE TABLE users (
    id   INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 多列复合主键（联合主键）
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)  -- 两列共同唯一
);

自增 AUTO_INCREMENT

常与主键配合，自动生成连续整数 ID：

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

INSERT INTO users (name) VALUES ('alice');
INSERT INTO users (name) VALUES ('bob');
-- id 自动生成：1, 2, ...

NOT NULL 非空约束

sql
-- 用户名不能为空
CREATE TABLE users (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,   -- 必填
    email    VARCHAR(100) NOT NULL
);

UNIQUE 唯一约束

sql
-- 邮箱必须唯一，但允许为空（空值可以有多个）
CREATE TABLE users (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE
);

-- 多列组合唯一
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE (user_id, role_id)  -- 同一用户不能有重复角色
);

DEFAULT 默认值

sql
CREATE TABLE articles (
    title     VARCHAR(200) NOT NULL,
    published BOOLEAN DEFAULT FALSE,      -- 默认为草稿
    views     INT DEFAULT 0,              -- 默认阅读量为 0
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

外键 FOREIGN KEY

sql
CREATE TABLE orders (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT NOT NULL,
    total      DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE   -- 级联删除：删除用户时其订单一并删除
        ON UPDATE CASCADE   -- 级联更新：更新用户 ID 时同步更新订单
);

外键约束的代价：每次 INSERT/UPDATE/DELETE 外键表时，MySQL 需要检查参照完整性，对高并发写入场景会产生额外锁竞争。现代架构中，也有不少团队选择在应用层（代码中）保证外键逻辑。

完整建表示例

整合以上知识点：

sql
CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET=utf8mb4;

USE shop;

CREATE TABLE users (
    id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username   VARCHAR(50)  NOT NULL,
    email      VARCHAR(100) NOT NULL,
    password_hash CHAR(60)  NOT NULL,    -- bcrypt 哈希固定 60 字符
    age        TINYINT UNSIGNED,
    status     TINYINT UNSIGNED DEFAULT 1,  -- 1=活跃 0=禁用
    created_at DATETIME      DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE (email),
    INDEX (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ENGINE=InnoDB：MySQL 8.x 的默认存储引擎，支持事务、行级锁、外键，是生产环境的必选。

总结

数据类型选择的核心原则：

  • 数值：能用 TINYINT 不必用 INT；货币必用 DECIMAL
  • 字符串：长度固定用 CHAR，不确定用 VARCHAR，超长用 TEXT；始终使用 utf8mb4
  • 日期：业务时间用 DATETIME，自动时间戳用 TIMESTAMP
  • 约束：主键必填、外键慎用、非空和唯一按业务需求添加

下一篇文章我们将学习 条件过滤与聚合查询，掌握 WHERE、LIKE、GROUP BY、HAVING 等数据处理核心技巧。

#mysql #sql #数据类型 #表设计 #约束

评论

A

Written by

AI-Writer

Related Articles

mysql
#5

条件过滤与聚合查询

掌握 WHERE、LIKE、IN、BETWEEN、ORDER BY、LIMIT、GROUP BY、HAVING 等子句，实现精确数据筛选、分组统计与结果排序

Read More
mysql
#3

数据类型与表设计

深入理解 MySQL 8.x 的数值、字符、日期类型，以及主键、外键、NOT NULL、UNIQUE、DEFAULT 等约束的含义与实战选择

Read More
mysql
#3

MySQL 安装与配置

在 macOS、Linux、Windows 上安装 MySQL 8.x、启动服务、配置连接，并介绍常用客户端工具 MySQL Workbench、DBeaver 与命令行 CLI 的使用方法

Read More
Back to All Articles