-- 创建表时指定整数类型
CREATE TABLE products (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 主键推荐 BIGINT UNSIGNED
    price_cents INT UNSIGNED NOT NULL,                       -- 价格用分表示
    stock      INT UNSIGNED DEFAULT 0,                       -- 库存
    status     TINYINT UNSIGNED DEFAULT 1,                   -- 状态
    view_count BIGINT UNSIGNED DEFAULT 0                     -- 访问量
);

-- UNSIGNED 将负数范围转为正数，容量翻倍
-- INT:          -2,147,483,648 ~ 2,147,483,647
-- INT UNSIGNED: 0 ~ 4,294,967,295

-- 适用：主键、计数器、金额（用分表示）等不会为负的字段
-- 不适用：温度、盈亏、坐标等可能为负的字段

-- ❌ 不要用于金额
CREATE TABLE bad_money (amount FLOAT);    -- FLOAT 精度不够
CREATE TABLE bad_money (amount DOUBLE);   -- DOUBLE 精度不够

-- ✅ 使用 DECIMAL
CREATE TABLE accounts (
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00  -- 整数部分10位，小数2位
);

-- DECIMAL 的存储方式：将数字拆分为整数存储，精确计算
-- DECIMAL(M,D)：M=总位数，D=小数位数
-- DECIMAL(12,2)：最大 9999999999.99

-- 精确计算示例
SELECT 0.1 + 0.2;                       -- FLOAT: 0.30000000000000004 ❌
SELECT CAST(0.1 AS DECIMAL(5,2)) + CAST(0.2 AS DECIMAL(5,2));  -- 0.30 ✅

-- 推荐：用 BIGINT UNSIGNED 存储分为单位的金额
CREATE TABLE payments (
    amount_cents BIGINT UNSIGNED NOT NULL COMMENT '金额（分）'
);
-- 100.50 元存为 10050
-- 显示时：SELECT amount_cents / 100.0 AS amount_yuan FROM payments;

-- CHAR 适合定长数据
phone     CHAR(11)         -- 手机号：固定11位
md5_hash  CHAR(32)         -- MD5哈希：固定32位
country   CHAR(2)          -- 国家代码：US, CN

-- VARCHAR 适合变长数据
username  VARCHAR(50)      -- 用户名：长度不定
email     VARCHAR(100)     -- 邮箱：长度不定
address   VARCHAR(500)     -- 地址：长度不定

-- TEXT 类型家族
TINYTEXT    -- 最大 255 字节
TEXT        -- 最大 65535 字节（64KB）
MEDIUMTEXT  -- 最大 16MB
LONGTEXT    -- 最大 4GB

-- 使用建议
summary     TEXT            -- 文章摘要
content     MEDIUMTEXT      -- 文章内容
bio         VARCHAR(500)    -- 个人简介

-- 通常不建议在数据库中存储文件，应存文件路径
-- 如确需存储二进制数据：
avatar_data  MEDIUMBLOB      -- 头像图片数据
document     LONGBLOB        -- 文档数据

-- 推荐方案：存文件路径
avatar_url   VARCHAR(500)    -- 头像URL，实际文件存在 OSS/S3

-- 创建测试表
CREATE TABLE time_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dt DATETIME,
    ts TIMESTAMP
);

-- 插入数据
INSERT INTO time_test (dt, ts) VALUES ('2026-05-10 14:30:00', '2026-05-10 14:30:00');

-- 修改时区
SET SESSION time_zone = '+09:00';  -- 切换到 UTC+9（东京）

SELECT * FROM time_test;
-- DATETIME 值不变：2026-05-10 14:30:00
-- TIMESTAMP 值转换：2026-05-10 15:30:00（东京比北京时间快1小时）

-- 推荐的表设计
CREATE TABLE orders (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no    VARCHAR(32) NOT NULL,
    created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted_at  DATETIME    DEFAULT NULL COMMENT '软删除时间'
);

-- 本周的订单
SELECT * FROM orders 
WHERE YEARWEEK(created_at, 1) = YEARWEEK(CURDATE(), 1);

-- 本月的订单
SELECT * FROM orders 
WHERE YEAR(created_at) = YEAR(CURDATE()) 
  AND MONTH(created_at) = MONTH(CURDATE());

-- 近 30 天的订单
SELECT * FROM orders 
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- 按天统计（最近7天）
SELECT 
    DATE(created_at) AS day,
    COUNT(*) AS order_count
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(created_at)
ORDER BY day;

-- 按小时统计（今天各时段分布）
SELECT 
    HOUR(created_at) AS hour,
    COUNT(*) AS order_count
FROM orders
WHERE DATE(created_at) = CURDATE()
GROUP BY HOUR(created_at)
ORDER BY hour;

-- 创建 JSON 字段
CREATE TABLE user_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    profile JSON,
    settings JSON
);

-- 插入 JSON 数据
INSERT INTO user_profiles (username, profile, settings) VALUES
('zhangsan', 
 '{"age": 25, "city": "Beijing", "hobbies": ["reading", "coding"]}',
 '{"theme": "dark", "language": "zh-CN", "notifications": true}');

-- 查询 JSON 字段
SELECT username, 
       profile->'$.city' AS city,           -- 返回带引号: "Beijing"
       profile->>'$.city' AS city_clean,    -- 返回不带引号: Beijing
       JSON_EXTRACT(profile, '$.hobbies') AS hobbies
FROM user_profiles;

-- JSON 数组查询
SELECT username, profile->>'$.hobbies[0]' AS first_hobby
FROM user_profiles;

-- 添加字段
UPDATE user_profiles 
SET profile = JSON_SET(profile, '$.email', '[email protected]')
WHERE username = 'zhangsan';

-- 替换字段（不存在则不操作）
UPDATE user_profiles 
SET profile = JSON_REPLACE(profile, '$.age', 26)
WHERE username = 'zhangsan';

-- 删除字段
UPDATE user_profiles 
SET profile = JSON_REMOVE(profile, '$.hobbies')
WHERE username = 'zhangsan';

-- 数组追加
UPDATE user_profiles 
SET profile = JSON_ARRAY_APPEND(profile, '$.hobbies', 'travel')
WHERE username = 'zhangsan';

-- JSON_MERGE_PATCH：合并 JSON（后者覆盖前者）
SELECT JSON_MERGE_PATCH('{"a":1,"b":2}', '{"b":3,"c":4}');
-- {"a": 1, "b": 3, "c": 4}

-- JSON_MERGE_PRESERVE：合并 JSON（保留所有值，数组合并）
SELECT JSON_MERGE_PRESERVE('{"a":[1]}', '{"a":[2]}');
-- {"a": [1, 2]}

-- JSON 字段不能直接建索引，需要通过生成列（Generated Column）
ALTER TABLE user_profiles
ADD COLUMN city VARCHAR(100) GENERATED ALWAYS AS (profile->>'$.city') VIRTUAL,
ADD INDEX idx_city (city);

-- 查询会自动使用索引
SELECT * FROM user_profiles WHERE city = 'Beijing';

-- JSON_TABLE：将 JSON 数组展开为行
SELECT jt.*
FROM user_profiles,
JSON_TABLE(profile, '$.hobbies[*]' COLUMNS (
    hobby VARCHAR(50) PATH '$'
)) AS jt
WHERE username = 'zhangsan';
-- 结果：
-- reading
-- coding

-- ENUM：枚举类型，只能取预定义值之一
CREATE TABLE articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft'
);

INSERT INTO articles (title, status) VALUES ('MySQL Guide', 'published');

-- ENUM 内部存储为整数（1, 2, 3...），空间效率高
SELECT status, status + 0 AS internal_value FROM articles;
-- published → 2

-- SET：集合类型，可以取多个预定义值
CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    features SET('waterproof', 'bluetooth', 'wifi', 'gps', 'fingerprint')
);

INSERT INTO products (name, features) 
VALUES ('Smart Watch', 'waterproof,bluetooth,gps');

-- 查询包含特定特性的产品
SELECT * FROM products WHERE FIND_IN_SET('bluetooth', features) > 0;

-- 方案 A：JSON（适合属性不确定的商品）
CREATE TABLE products_json (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    attributes JSON COMMENT '商品属性，如颜色、尺寸等'
);

INSERT INTO products_json (name, price, attributes) VALUES
('T恤', 99.00, '{"color":"红色","size":"M","material":"棉"}'),
('手机', 3999.00, '{"brand":"Apple","storage":"128GB","color":"黑色"}');

-- 方案 B：EAV 模式（适合属性非常灵活的场景）
CREATE TABLE product_attributes (
    product_id BIGINT UNSIGNED NOT NULL,
    attr_name VARCHAR(50) NOT NULL,
    attr_value VARCHAR(500),
    PRIMARY KEY (product_id, attr_name)
);

-- 存储坐标
CREATE TABLE locations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    lat DECIMAL(10,7) NOT NULL COMMENT '纬度',
    lng DECIMAL(10,7) NOT NULL COMMENT '经度',
    SPATIAL INDEX idx_geo (POINT(lat, lng))  -- 空间索引（需要 POINT 类型）
);

-- 使用 POINT 类型（更推荐）
CREATE TABLE locations_geo (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position POINT NOT NULL SRID 4326,
    SPATIAL INDEX idx_position (position)
);