MySQL: MySQL数据类型详解与选择指南

-- 演示浮点精度丢失
SELECT 0.1 + 0.2;  -- 结果: 0.300000004（近似值，不精确）

-- 存入表后更明显
CREATE TABLE float_demo (price FLOAT);
INSERT INTO float_demo VALUES (199.99);
SELECT price FROM float_demo;  -- 结果: 199.99（显示正确但内部存储有微小误差）
SELECT price + 0.01 FROM float_demo;  -- 累积误差可能在运算中暴露

-- DECIMAL 精确存储，无误差
CREATE TABLE decimal_demo (price DECIMAL(10,2));
INSERT INTO decimal_demo VALUES (199.99);
SELECT price + 0.01 FROM decimal_demo;  -- 结果: 200.00（精确）

CREATE TABLE user_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age TINYINT UNSIGNED,              -- 年龄 0-255 足够
    view_count INT UNSIGNED,           -- 浏览次数
    total_orders BIGINT UNSIGNED       -- 总订单数（可能很大）
);

-- UNSIGNED 表示无符号（只有正数）
INSERT INTO user_stats (age, view_count, total_orders) 
VALUES (25, 1000000, 9999999999);

CREATE TABLE financials (
    id INT PRIMARY KEY,
    amount DECIMAL(12,2),       -- 金额，最大 9999999999.99
    rate DECIMAL(5,4),          -- 比率，最大 9.9999
    quantity DECIMAL(8,0)       -- 整数数量
);

INSERT INTO financials VALUES (1, 1234567.89, 0.0850, 1000);
SELECT * FROM financials;

+----+-------------+--------+----------+
| id | amount      | rate   | quantity |
+----+-------------+--------+----------+
|  1 | 1234567.89  | 0.0850 |     1000 |
+----+-------------+--------+----------+

-- CHAR(10) 固定长度，存储 'abc' 占 10 字节
-- VARCHAR(10) 可变长度，存储 'abc' 占 4 字节（3+1长度前缀）

CREATE TABLE phones (
    -- 手机号固定 11 位，用 CHAR 更快
    mobile CHAR(11),
    -- 用户名长度不定，用 VARCHAR 更省
    username VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending'
);

INSERT INTO orders (id, status) VALUES (1, 'paid');
INSERT INTO orders (id) VALUES (2);  -- 默认 'pending'

-- ENUM 存储的是数字索引（1,2,3...），不是字符串
SELECT status, status + 0 AS index_num FROM orders;

+---------+-----------+
| status  | index_num |
+---------+-----------+
| paid    |         2 |
| pending |         1 |
+---------+-----------+

CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO events (event_name, event_date, start_time) 
VALUES ('Tech Conference', '2026-09-15', '09:00:00');

SELECT * FROM events;

+----+----------------+------------+------------+---------------------+---------------------+
| id | event_name     | event_date | start_time | created_at          | updated_at          |
+----+----------------+------------+------------+---------------------+---------------------+
|  1 | Tech Conference| 2026-09-15 | 09:00:00   | 2026-07-03 10:00:00 | 2026-07-03 10:00:00 |
+----+----------------+------------+------------+---------------------+---------------------+

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES 
('iPhone', '{"color": "black", "storage": 128, "tags": ["phone", "apple"]}');

-- 提取 JSON 值
SELECT 
    name,
    attributes->>'$.color' AS color,
    attributes->>'$.storage' AS storage
FROM products;

-- JSON 数组查询
SELECT * FROM products WHERE JSON_CONTAINS(attributes->'$.tags', '"apple"');

+--------+-------+---------+
| name   | color | storage |
+--------+-------+---------+
| iPhone | black | 128     |
+--------+-------+---------+