mysql进阶数据类型一篇详解

2025-03-10 约 730 字预计阅读 2 分钟

https://bing.ee123.net/img/rand?artid=146113999

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"');


	记得点赞、关注、收藏哦！