跳到主要内容

数据类型

问题​

数据库中有哪些常用的数据类型？怎么选择合适的类型？

答案​

数据类型定义了表中每一列能存储什么样的数据。选择合适的数据类型对于存储效率、查询性能和数据完整性都至关重要。

以下以 MySQL 为主介绍常用数据类型，PostgreSQL 也会补充说明差异。

数值类型​

整数类型​

类型存储空间范围（有符号）范围（无符号 UNSIGNED）使用场景
TINYINT1 字节-128 ~ 1270 ~ 255布尔值、状态码
SMALLINT2 字节-32768 ~ 327670 ~ 65535年龄、小范围计数
MEDIUMINT3 字节-8388608 ~ 83886070 ~ 16777215中等范围
INT4 字节≈ ±21 亿0 ~ ≈42 亿最常用，主键、计数
BIGINT8 字节≈ ±922 京0 ~ ≈1844 京雪花 ID、大型系统主键
选择建议
  • 主键：单机用 INT，高并发/分布式用 BIGINT
  • 布尔值：MySQL 没有真正的布尔类型，TINYINT(1) 就是 BOOLEAN
  • 不确定就用 INT，因为空间差异很小，过小的类型将来可能需要迁移

浮点数与精确数值​

类型存储精度使用场景
FLOAT4 字节约 7 位有效数字科学计算
DOUBLE8 字节约 15 位有效数字科学计算
DECIMAL(M,D)变长精确计算金额、价格
-- FLOAT 有精度问题（0.1 + 0.2 ≠ 0.3）
SELECT CAST(0.1 + 0.2 AS FLOAT);  -- 可能是 0.30000001

-- DECIMAL 精确计算，金额必须用 DECIMAL
CREATE TABLE orders (
    id INT PRIMARY KEY,
    amount DECIMAL(10, 2)  -- 最多 10 位数字，其中 2 位小数
    -- 范围：-99999999.99 ~ 99999999.99
);
金额绝对不能用 FLOAT/DOUBLE！

浮点数有精度损失，0.1 + 0.2 可能不等于 0.3。金额类字段必须使用 DECIMAL 类型。

字符串类型​

类型最大长度存储方式使用场景
CHAR(n)255 字节固定长度MD5 哈希、国家代码、性别
VARCHAR(n)65535 字节可变长度最常用，姓名、邮箱、地址
TEXT65535 字节变长大文本文章正文、评论
MEDIUMTEXT16 MB变长大文本长文章
LONGTEXT4 GB变长大文本超大文本
ENUM1~2 字节枚举值状态、性别等有限选项
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,       -- 标题，可变长
    slug CHAR(32),                     -- URL slug，固定长度
    content TEXT,                      -- 正文，大文本
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft'
);
VARCHAR 长度设置

VARCHAR(n) 中的 n 表示最大字符数（不是字节数）。UTF-8 下一个中文字符占 3 字节。建议：

  • 不要随意设置 VARCHAR(255)，根据实际需要设置
  • 但也不要太小，后续扩展不方便

日期和时间类型​

类型格式范围存储使用场景
DATEYYYY-MM-DD1000 ~ 9999 年3 字节生日、日期
TIMEHH:MM:SS-838:59:59 ~ 838:59:593 字节时间段
DATETIMEYYYY-MM-DD HH:MM:SS1000 ~ 9999 年8 字节创建时间、更新时间
TIMESTAMPYYYY-MM-DD HH:MM:SS1970 ~ 2038 年4 字节创建时间、更新时间
YEARYYYY1901 ~ 21551 字节年份

DATETIME vs TIMESTAMP​

对比DATETIMETIMESTAMP
存储8 字节4 字节
范围1000-01-01 ~ 9999-12-311970-01-01 ~ 2038-01-19
时区存储原始值，不转换时区存储 UTC，自动转换时区
默认值无可设为 CURRENT_TIMESTAMP
CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- TIMESTAMP 自动追踪创建和更新时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
2038 年问题

TIMESTAMP 使用 32 位整数存储 Unix 时间戳，最大只能到 2038-01-19 03:14:07 UTC。如果你的数据需要存储 2038 年以后的日期（如合同到期日），请使用 DATETIME。MySQL 8.0.28+ 已支持 64 位 TIMESTAMP 来解决这个问题。

JSON 类型​

MySQL 5.7+ 和 PostgreSQL 都支持 JSON 类型，适合存储半结构化数据：

CREATE TABLE user_settings (
    user_id INT PRIMARY KEY,
    -- JSON 类型存储用户设置
    preferences JSON
);

-- 插入 JSON 数据
INSERT INTO user_settings VALUES (1, '{"theme": "dark", "lang": "zh-CN", "notifications": {"email": true, "sms": false}}');

-- 查询 JSON 字段（MySQL）
SELECT 
    user_id,
    preferences->>'$.theme' AS theme,           -- 提取值
    preferences->>'$.notifications.email' AS email_notify
FROM user_settings;

-- 查询 JSON 字段（PostgreSQL，使用 JSONB 更推荐）
SELECT 
    user_id,
    preferences->>'theme' AS theme,
    preferences->'notifications'->>'email' AS email_notify
FROM user_settings;
JSON vs 单独建表

JSON 适合：

  • 配置项、用户偏好等不常查询的数据
  • Schema 经常变化的数据
  • 不需要在该字段上建索引的数据

不适合存储核心业务数据（如订单金额），因为 JSON 查询性能不如普通列。

二进制类型​

类型最大长度使用场景
BINARY(n)255 字节固定长度二进制
VARBINARY(n)65535 字节可变长度二进制
BLOB65535 字节二进制大对象（图片、文件）
不建议在数据库中存储文件

图片、视频等大文件应该存储在对象存储（如 S3、OSS） 中，数据库只存 URL。在数据库中存储 BLOB 会导致表膨胀、备份困难、查询变慢。

PostgreSQL 特有类型​

PostgreSQL 提供了很多强大的数据类型，在某些场景下非常有用：

类型说明使用场景
JSONB二进制 JSON，支持索引配置数据、半结构化数据
UUID通用唯一标识符分布式系统主键
ARRAY数组类型标签、权限列表
INET / CIDRIP 地址 / 网段网络管理
INTERVAL时间间隔INTERVAL '3 days'
SERIAL / BIGSERIAL自增序列主键
TSRANGE / DATERANGE范围类型预订时间段、价格区间
TSVECTOR全文搜索站内搜索
-- PostgreSQL 数组类型
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    tags TEXT[] DEFAULT '{}'  -- 字符串数组
);

INSERT INTO articles (title, tags) VALUES 
    ('学习 PostgreSQL', ARRAY['database', 'postgresql', 'tutorial']);

-- 查询包含 'database' 标签的文章
SELECT * FROM articles WHERE 'database' = ANY(tags);

-- PostgreSQL 范围类型：酒店房间预订
CREATE TABLE hotel_bookings (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    stay DATERANGE NOT NULL,  -- 入住日期范围
    -- 同一房间的预订不能重叠
    EXCLUDE USING gist (room_id WITH =, stay WITH &&)
);

类型转换​

隐式转换（自动）​

-- MySQL 会自动将字符串转为数字进行比较
SELECT * FROM products WHERE price > '100';  -- '100' 被转为 100

-- ⚠️ 隐式转换可能导致索引失效！
SELECT * FROM users WHERE phone = 13800138000;  
-- phone 是 VARCHAR，但查询用了数字！MySQL 会把 phone 列逐行转为数字比较 → 全表扫描

显式转换​

-- MySQL
SELECT CAST('123' AS SIGNED);    -- 字符串 → 整数
SELECT CAST(123 AS CHAR);        -- 整数 → 字符串
SELECT CAST('2024-01-01' AS DATE); -- 字符串 → 日期
SELECT CONVERT('123', SIGNED);   -- 另一种语法

-- PostgreSQL（使用 :: 语法更简洁）
SELECT '123'::INTEGER;
SELECT 123::TEXT;
SELECT '2024-01-01'::DATE;
隐式类型转换是性能杀手

当 WHERE 条件中的数据类型与列类型不匹配时，MySQL 会做隐式类型转换，导致索引失效。例如 VARCHAR 列用数字查询、日期列用字符串比较等。面试中经常考这个问题。

类型选择最佳实践​

实用选型口诀：

  • 整数用 INT，大数用 BIGINT
  • 金额用 DECIMAL，绝不用 FLOAT
  • 短文本用 VARCHAR，长文本用 TEXT
  • 时间用 DATETIME 或 TIMESTAMP
  • 布尔用 TINYINT(1)
  • 灵活数据用 JSON

常见面试问题​

Q1: DECIMAL 和 FLOAT/DOUBLE 的区别？为什么金额要用 DECIMAL？​

答案：

FLOAT/DOUBLE 使用 IEEE 754 标准的浮点数表示，存在精度损失：

-- FLOAT 精度问题演示
SELECT CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT);
-- 结果可能是 0.30000001192092896，不是 0.3

-- DECIMAL 精确计算
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));
-- 结果精确是 0.30

DECIMAL 使用字符串形式存储，内部按照十进制精确计算，不会有精度丢失。金额、价格、利率等涉及钱的数据必须使用 DECIMAL。

Q2: VARCHAR(100) 和 VARCHAR(200) 存同样的数据，有区别吗？​

答案：

存储空间上没有差异，都只占实际长度 + 1~2 字节。但有一些间接影响：

  1. 内存占用：MySQL 在排序（ORDER BY）、创建临时表时，会按最大长度分配内存。VARCHAR(200) 会比 VARCHAR(100) 多分配内存
  2. 约束效果：VARCHAR(100) 会阻止插入超过 100 个字符的数据，起到数据校验作用
  3. 设计规范：精确的长度定义能更好地表达业务语义

建议：根据业务实际需要设置合理长度，不要一味用最大值。

Q3: DATETIME 和 TIMESTAMP 怎么选？​

答案：

场景推荐原因
记录创建/更新时间TIMESTAMP自动管理、节省空间、时区转换
存储未来日期（如合同到期）DATETIME避免 2038 年问题
跨时区系统TIMESTAMP自动 UTC ↔ 本地时区转换
纯粹记录日期时间DATETIME不需要时区转换，所见即所得

Q4: 如何存储 IP 地址？​

答案：

有两种常见方式：

-- 方式 1：VARCHAR（简单直观，但浪费空间）
ip_address VARCHAR(45)  -- 兼容 IPv6

-- 方式 2：INT UNSIGNED（省空间，查询快，但仅限 IPv4）
ip_address INT UNSIGNED

-- 转换函数
SELECT INET_ATON('192.168.1.1');     -- 3232235777（字符串 → 整数）
SELECT INET_NTOA(3232235777);         -- '192.168.1.1'（整数 → 字符串）

-- MySQL 5.6+ 支持 IPv6
SELECT HEX(INET6_ATON('::1'));

推荐：需要支持 IPv6 用 VARCHAR(45)，纯 IPv4 且追求性能用 INT UNSIGNED。

Q5: ENUM 类型有什么优缺点？​

答案：

优点：

  • 存储空间小（1~2 字节），比 VARCHAR 节省
  • 限制取值范围，保证数据合法性

缺点：

  • 修改枚举值需要 ALTER TABLE，在大表上很慢
  • 不利于国际化
  • 移植性差（不同数据库实现不同）

替代方案：使用 TINYINT + 应用层映射，或创建字典表。

相关链接​