mysql进阶——数据类型一篇详解

        本文将详细介绍MySQL各类数据类型的特点，包括介绍数值、字符串、日期时间、JSON 等类型的特点及注意事项

一、数值类型

1. 整数类型

整数类型有不同的字节长度和取值范围，如 TINYINT（1 字节，-128~127）、SMALLINT（2 字节）、INT（4 字节）、BIGINT（8 字节）。使用 UNSIGNED 关键字可使范围翻倍，例如 TINYINT UNSIGNED 的范围是 0~255。显示宽度（如 INT(5)）仅影响显示格式，不影响存储。

CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    product_name VARCHAR(100) NOT NULL,
    stock SMALLINT UNSIGNED DEFAULT 0,    
    price DECIMAL(10, 2) NOT NULL         
);

2. 浮点与定点数

FLOAT（4 字节）和 DOUBLE（8 字节）是近似存储，可能丢失精度；DECIMAL(M,D) 是精确类型，适合财务数据，M 为总位数，D 为小数位数。

CREATE TABLE orders (
    order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    total_amount DECIMAL(12, 2) NOT NULL, 
    user_id INT UNSIGNED NOT NULL
);

使用时要注意避免隐式类型转换导致索引失效

二、字符串类型

1. 定长与变长

CHAR(N) 是定长字符串，最多 255 字符，适合短且固定长度的数据；VARCHAR(N) 是变长字符串，最多 65535 字节，适合长度变化的数据。

CREATE TABLE product_details (
    product_id INT UNSIGNED PRIMARY KEY,
    title VARCHAR(255) NOT NULL,       
    description TEXT,                  
    sku_code CHAR(10)                  
);

2. 二进制类型

BINARY/VARBINARY 用于存储二进制数据，区分大小写。

3. TEXT 与 BLOB

TEXT 用于存储大文本，BLOB 用于存储二进制大对象。对它们创建索引时需指定前缀长度。

4. ENUM 与 SET

ENUM 是单选值，存储为整数索引，插入非法值会报错或存为 ''；SET 是多选值，用位掩码存储，查询需用 FIND_IN_SET()。

CREATE TABLE orders (
    order_id BIGINT UNSIGNED PRIMARY KEY,
    status ENUM('pending', 'paid', 'shipped', 'completed') NOT NULL
);

三、日期与时间类型

1. 类型对比

DATE 存储日期（YYYY-MM-DD），TIME 存储时间（HH:MM:SS），DATETIME 存储日期 + 时间（YYYY-MM-DD HH:MM:SS），范围大且无时区转换，TIMESTAMP 存储时间戳（范围 1970 - 2038 年），自动转换为 UTC 存储，支持自动更新。

CREATE TABLE orders (
    order_id BIGINT UNSIGNED PRIMARY KEY,
    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6), 
    paid_at TIMESTAMP NULL 
);

2. 微秒支持

MySQL 5.6.4+ 支持微秒，如 DATETIME(6)。

四、JSON 类型

JSON 类型可存储有效 JSON 文档，支持路径查询和部分更新。

CREATE TABLE products (
    product_id INT UNSIGNED PRIMARY KEY,
    attributes JSON NOT NULL 
);

INSERT INTO products VALUES (1, '{
    "color": ["red", "blue"],
    "size": {"S": 10, "M": 5},
    "specs": {"weight": "1.2kg", "material": "metal"}
}');

-- 查询红色商品
SELECT product_id 
FROM products 
WHERE JSON_CONTAINS(attributes->'$.color', '"red"');