MySQL 数据类型

Begindev 编辑部 March 30, 2026

在 MySQL 中创建表时，为每一列定义正确的数据类型是至关重要的。数据类型决定了一列可以存储什么样的数据（例如：数字、文本、日期），这些数据将占用多少存储空间，以及 MySQL 将如何处理这些数据相关的操作。选择合适的数据类型不仅能保证数据的完整性，还能优化存储空间并提升查询性能。

1. 数字数据类型

数字数据类型用于存储数值。MySQL 提供了多种数字类型来满足不同的范围和精度需求，主要分为整数类型和定点数/浮点数类型。

1.1 整数类型

整数类型用于存储没有小数部分的完整数字（正数或负数）。每种整数类型都有其特定的可存储范围。在设计表结构时，一个常见的优化策略就是：使用能够容纳预期数值范围的最小数据类型。

TINYINT：存储非常小的整数。

有符号范围：-128 到 127
无符号范围：0 到 255
示例：存储产品评论的星级（1-5星）。

SMALLINT：存储小整数。

有符号范围：-32,768 到 32,767
无符号范围：0 到 65,535
示例：存储小型订单中的商品数量。

MEDIUMINT：存储中等大小的整数。

有符号范围：-8,388,608 到 8,388,607
无符号范围：0 到 16,777,215
示例：存储一个小城市的人口数量。

INT (或 INTEGER)：最常用的整数类型。

有符号范围：-2,147,483,648 到 2,147,483,647
无符号范围：0 到 4,294,967,295
示例：存储客户或产品的 ID。适用于不需要用到 BIGINT 的通用整数场景。

BIGINT：存储极大的整数。

有符号范围：-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
无符号范围：0 到 18,446,744,073,709,551,615
示例：在高并发金融系统中存储唯一的交易 ID，或者热门视频的超大播放量。

提示：在定义整数类型时，你可以选择性地添加 UNSIGNED（无符号）关键字，以禁止输入负数并扩大正数的存储范围。例如，INT UNSIGNED 意味着该列只能存储非负整数（0 到 4,294,967,295），这相当于把有符号 INT 的正数范围扩大了一倍。

1.2 定点数类型

定点数类型用于存储精确的数值，非常适合对精度要求极高且不能容忍舍入误差的财务数据。

DECIMAL(M, D) (或 NUMERIC(M, D))：存储精确的数值。M 代表总位数（精度），D 代表小数点后的位数（标度）。

M 的最大值是 65，D 的最大值是 30。
示例：DECIMAL(10, 2) 可以存储最大 99999999.99 的数字（小数点前 8 位，小数点后 2 位）。这非常适合表示像 $19.99 这样的货币金额，或者需要精确表示的科学测量值。
场景假设：一家加密货币交易所需要高精度地存储交易金额。使用 DECIMAL(20, 8) 可以精准存储像 0.00000001 BTC 这样的数值。
真实案例：在电商数据库中存储商品价格，价格必须是精确的，比如用 DECIMAL(7, 2) 存储高达 99999.99 的价格。另一个例子是计算销售税，税率和最终金额都需要极其精确。

1.3 浮点数类型

浮点数类型用于存储近似数值。它们并非绝对精确，可能会出现精度丢失的问题，因此通常不适合用于财务计算。但对于可以接受近似值的科学或工程数据来说，它们非常实用。

FLOAT(M, D)：单精度浮点数。M 是总位数，D 是小数点后的位数。具体的范围和精度取决于硬件。

示例：FLOAT(7, 4) 可以存储像 123.4567 这样的值。适用于对精度要求不高、允许存在一定误差的测量数据。

DOUBLE(M, D) (或 REAL)：双精度浮点数。提供比 FLOAT 更高的精度。

示例：DOUBLE(10, 6) 可以存储像 1234.567890 这样的值。
真实案例：存储 GPS 坐标（经纬度），这类数据通常需要较高的精度，但可以容忍浮点数的近似性质。比如在地图应用中使用 DOUBLE 存储经纬度。另一个用例是存储传感器读数或物理测量值等科学数据，因为测量本身固有的误差通常超过了浮点数的精度问题。

2. 字符串数据类型

字符串数据类型用于存储文本数据。MySQL 提供了多种选项，每种选项具有不同的存储特性和最大长度。

CHAR(L)：固定长度字符串。L 代表要存储的精确字符数（范围 0 到 255）。

如果存入的字符串比 L 短，系统会用空格填充至指定长度。读取数据时，除非开启了 PAD_CHAR_TO_FULL_LENGTH SQL 模式，否则尾部的空格会被自动去除。
示例：CHAR(3) 存储 'abc' 占用 3 个字符。CHAR(3) 存储 'ab' 也占用 3 个字符（被填充为 'ab '）。
何时使用：当某一列的所有值长度都完全一致时使用 CHAR，比如两字母的省份/州份缩写（CHAR(2)）或固定长度的商品代码。
真实案例：存储国家代码如 'US'、'GB'、'CN' 等，它们始终是两个字符。

VARCHAR(L)：可变长度字符串。L 代表可存储的最大字符数（范围 0 到 65,535）。

它只存储实际输入的字符，外加 1 到 2 个字节来记录字符串的长度。
示例：VARCHAR(255) 存储 'Hello' 占用 5 个字符加上记录长度的字节。
何时使用：用于大多数长度变化较大的通用字符串存储，如姓名、地址或产品描述。
真实案例：存储客户的名字（VARCHAR(50)）或电子邮件地址（VARCHAR(255)）。这些值的长度各不相同，使用 VARCHAR 能够节省空间。

TINYTEXT：最大长度为 255 个字符的文本列。

示例：简短的备注或评论。

TEXT：最大长度为 65,535 个字符的文本列。

示例：适合较长的文本，如文章正文或详细的产品说明。

MEDIUMTEXT：最大长度为 16,777,215 个字符的文本列。

示例：存储整篇博客文章或书的某一个章节。

LONGTEXT：最大长度为 4,294,967,295 个字符的文本列。

示例：用于极其庞大的文本数据，如一整本书的内容或海量的日志文件。

ENUM：枚举类型。它的值必须是从创建表时指定的一组允许值列表中选择的一个。

示例：产品尺寸使用 ENUM('Small', 'Medium', 'Large')。
优势：由于它在底层存储的是整数索引而不是字符串本身，因此效率很高。
真实案例：存储订单状态（'Pending'（待处理）, 'Processing'（处理中）, 'Shipped'（已发货）, 'Delivered'（已送达））。

SET：集合类型。它可以包含零个或多个值，每个值必须从创建表时指定的列表中选择。

示例：产品关联的颜色使用 SET('Red', 'Green', 'Blue')。一个产品可以是 'Red'、'Green'，或者同时包含 'Red,Blue'。
底层机制：以位掩码（bitmask）的形式存储多个选项。
场景假设：用户的通知接收偏好设置，例如 SET('Email', 'SMS', 'Push Notification')。用户可以同时勾选 'Email,SMS'。

3. 日期和时间数据类型

日期和时间数据类型用于存储时间维度的值。MySQL 提供了专门用于存储日期、时间或两者组合的类型。

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'。
示例：订单生成的精确时间戳或某个事件发生的具体时刻。
真实案例：存储用户账号创建的时间，例如 2023-10-26 14:30:00。

TIMESTAMP：同样存储日期和时间的组合，类似于 DATETIME，但它的范围较小，通常用于追踪记录的变更。它存储的是自 Unix 纪元（1970年1月1日，UTC 时间）以来的秒数。

范围：'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。
特性：如果在 UPDATE 操作时没有明确设置它的值，它会自动更新为当前时间，这使得它非常适合用作 last_updated（最后更新时间）列。
存储时，它会将当前时区的时间转换为 UTC 时间；读取时，再从 UTC 时间转换回当前时区的时间。
示例：追踪一条记录最后被修改的时间。
真实案例：products（产品）表中的一个 last_modified（最后修改）列，每当产品详细信息被修改时，该列会自动更新为当前时间戳。

YEAR：以 'YYYY' 格式存储年份。

范围：1901 到 2155，或 0000。
示例：一本书的出版年份。

4. 实战演示与代码示例

让我们通过一家在线书店的假设场景，来演示这些数据类型的具体用法。我们将设计一个名为 books（图书）的表。

-- 这条语句会在 'books' 表不存在的情况下创建它。
CREATE TABLE IF NOT EXISTS books (
    -- 'book_id' 是一个整数，作为主键。
    -- INT UNSIGNED 确保它是一个正整数，作为 ID 非常高效。
    -- AUTO_INCREMENT 会为每本新书自动分配一个唯一且递增的数字。
    -- PRIMARY KEY 将此列指定为每一行的唯一标识符。
    book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 'title' 存储书名。VARCHAR(255) 非常灵活，适合不同长度的书名。
    -- NOT NULL 表示一本书必须要有书名，不能为空。
    title VARCHAR(255) NOT NULL,
    
    -- 'author_first_name' 和 'author_last_name' 存储作者姓名。
    -- VARCHAR(100) 允许合理的姓名长度。
    author_first_name VARCHAR(100),
    author_last_name VARCHAR(100) NOT NULL,
    
    -- 'publication_year' 使用 YEAR 数据类型，专门用于存储年份。
    publication_year YEAR,
    
    -- 'price' 使用 DECIMAL(6, 2) 来保证精确的货币数值。
    -- 总共 6 位数，小数点后 2 位（例如，最高可达 9999.99）。
    -- NOT NULL 意味着每本书都必须有价格。
    price DECIMAL(6, 2) NOT NULL,
    
    -- 'stock_quantity' 追踪库存中的图书数量。
    -- SMALLINT UNSIGNED 非常适合最大为 65535 的数量。
    stock_quantity SMALLINT UNSIGNED DEFAULT 0, -- DEFAULT 0 将未指定时的初始库存设为 0。
    
    -- 'genre' 使用 ENUM 限定一套固定的分类，优化存储空间并确保输入有效。
    genre ENUM('Fiction', 'Non-Fiction', 'Science', 'Fantasy', 'Mystery', 'Biography') DEFAULT 'Fiction',
    
    -- 'is_available_for_preorder' 使用 TINYINT(1)（在某些系统中也叫 BOOLEAN）表示真/假。
    -- 0 通常表示假（false），1 表示真（true）。
    is_available_for_preorder TINYINT(1) DEFAULT 0,
    
    -- 'last_updated_timestamp' 使用 TIMESTAMP 类型。
    -- DEFAULT CURRENT_TIMESTAMP 会在插入数据时将默认值设为当前时间。
    -- ON UPDATE CURRENT_TIMESTAMP 会在该行数据被修改时自动更新此列的值。
    last_updated_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 'description' 存储关于这本书的较长文本介绍。
    -- TEXT 适合用于处理可能包含大量文字的内容。
    description TEXT
);

现在，让我们插入一些数据，看看这些数据类型是如何处理实际数值的：

-- 向 'books' 表中插入一本新书。
INSERT INTO books (
    title,
    author_first_name,
    author_last_name,
    publication_year,
    price,
    stock_quantity,
    genre,
    is_available_for_preorder,
    description
) VALUES (
    'The Hitchhiker''s Guide to the Galaxy',  -- 书名
    'Douglas',                               -- 作者名
    'Adams',                                 -- 作者姓氏
    1979,                                    -- 出版年份
    12.99,                                   -- 价格 (DECIMAL(6,2))
    500,                                     -- 库存数量 (SMALLINT UNSIGNED)
    'Fiction',                               -- 类别 (ENUM)
    0,                                       -- 不可预购 (TINYINT(1) 表示布尔值)
    'A comedic science fiction series. A must-read for fans of absurd humor and space travel.' -- 描述 (TEXT)
);

-- 插入另一本书，用于演示默认值的作用。
-- stock_quantity 会默认设为 0，genre 默认为 'Fiction'，is_available_for_preorder 默认为 0。
-- last_updated_timestamp 会自动设定为插入时的当前时间。
INSERT INTO books (
    title,
    author_first_name,
    author_last_name,
    publication_year,
    price
) VALUES (
    'Sapiens: A Brief History of Humankind',
    'Yuval Noah',
    'Harari',
    2011,
    18.50
);

-- 插入一本目前仅供预购的书。
INSERT INTO books (
    title,
    author_first_name,
    author_last_name,
    publication_year,
    price,
    stock_quantity,
    is_available_for_preorder,
    genre
) VALUES (
    'The Lord of the Rings',
    'J.R.R.',
    'Tolkien',
    1954,
    25.00,
    0, -- 库存为 0，但可以预购
    1, -- 1 表示 is_available_for_preorder 为真
    'Fantasy'
);

-- 更新 'The Hitchhiker''s Guide to the Galaxy' 的库存数量。
-- 这一操作将自动更新 'last_updated_timestamp' 列的时间。
UPDATE books
SET stock_quantity = 490
WHERE title = 'The Hitchhiker''s Guide to the Galaxy';