数据类型与 JSON - MySQL 教程

讲解

选类型的第一条原则是「够用就好，别贪大」。整数家族里：TINYINT 占 1 字节（常用来做布尔，写成 TINYINT(1)）、INT 占 4 字节（约 21 亿上限，绝大多数主键够用）、BIGINT 占 8 字节；后面加 UNSIGNED 可以把范围翻一倍（只允许非负）。主键预估会超过 21 亿的表（日志、流水）直接用 BIGINT，事后再改主键类型是大手术。INT(11) 里的括号只是显示宽度，不影响存储和取值范围，8.0.17 起已废弃这个写法，直接写 INT。

字符串用 VARCHAR(n)，n 是字符数不是字节数；CHAR 是定长，只有长度严格固定的场景（如 md5 存 CHAR(32)）才用；TEXT 存长文本。最要紧的纪律是金额绝不用 FLOAT/DOUBLE——浮点数是近似存储，0.1 + 0.2 不等于 0.3，对账时就是事故；金额、价格一律 DECIMAL(M,N)。日期时间常用 DATETIME（存字面时间，8 字节）和 TIMESTAMP（带时区换算，4 字节，范围到 2038 年）；业务时间字段推荐 DATETIME，配 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 自动维护创建/更新时间。

MySQL 5.7 起支持原生 JSON 类型：存入时校验合法性、以二进制格式存储、可以用 JSON_EXTRACT 和 ->> 运算符直接查询内部字段，还能对虚拟列建索引。JSON 列适合存储结构不固定的扩展属性（如商品的规格参数），但它不是垃圾桶——有固定查询条件的字段应该落成普通列，否则失去约束、索引和类型检查。

示例

一张体现了常见类型选择的商品表：UNSIGNED 主键、DECIMAL 金额、TINYINT 布尔、自动维护的时间戳：

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(32) NOT NULL,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  stock INT NOT NULL DEFAULT 0,
  is_on_sale TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO products (sku, name, price, stock, is_on_sale) VALUES ('KB-001', '机械键盘', 299.00, 50, 1);

SHOW CREATE TABLE products;

浮点与定点对比：同样 0.1 + 0.2，FLOAT 给出近似值，DECIMAL 给出精确值——这就是金额必须用 DECIMAL 的原因：

SELECT 0.1 + 0.2 AS float_sum, CAST('0.1' AS DECIMAL(10,4)) + CAST('0.2' AS DECIMAL(10,4)) AS decimal_sum;

JSON 类型：存一个规格对象，用 ->> 取出字段（>> 表示取出后去掉 JSON 引号），JSON_CONTAINS_PATH 检查路径是否存在：

CREATE TABLE product_attrs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  attrs JSON NOT NULL
);

INSERT INTO product_attrs (attrs) VALUES ('{"color": "曜石黑", "tags": ["办公", "游戏"], "spec": {"weight": "1.2kg"}}');

SELECT attrs->>'$.color' AS color, attrs->>'$.spec.weight' AS weight FROM product_attrs;

SELECT JSON_CONTAINS_PATH(attrs, 'one', '$.tags') AS has_tags FROM product_attrs;

常见坑

金额用 FLOAT/DOUBLE：浮点误差在对账、退款场景是资损事故。金额一律 DECIMAL，或者在应用层用「分」为单位的 BIGINT。
varchar 长度随手写 255：长度定义影响内存临时表的分配和索引前缀长度，按真实业务上限定义，不是越大越好。
DATETIME 和 TIMESTAMP 乱选：TIMESTAMP 会随时区设置换算且 2038 年溢出，存业务发生时间用 DATETIME 更省心；记录行的更新时间用 TIMESTAMP 也可以。
把 JSON 当万能扩展字段：所有查询条件都塞进 JSON 后，每条查询都要 JSON_EXTRACT，索引用不上，性能崩塌。固定筛选条件的字段必须是普通列。

小结

整数按量级选 INT/BIGINT，金额用 DECIMAL，时间用 DATETIME 配自动默认值，JSON 只放结构不固定的扩展属性。类型选完，下一章进入更高一层的话题：多张表之间的关系怎么设计。