Orion K Blog
MySQL数据类型与字段设计最佳实践：存储优化与性能提升
Orion K Lv6
  2023-08-28 14:15:00  

正确选择MySQL数据类型和合理设计字段结构是数据库性能优化的基础。本文将深入探讨各种数据类型的特点、使用场景和设计原则，帮助开发者做出最优的设计决策。

数值类型详解

1. 整数类型选择

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- 整数类型对比和选择
CREATE TABLE integer_types_demo (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- TINYINT: 1字节，范围 -128 到 127 (有符号) 或 0 到 255 (无符号)
    status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态：0-禁用，1-启用',
    age TINYINT UNSIGNED COMMENT '年龄，0-255足够',
    
    -- SMALLINT: 2字节，范围 -32768 到 32767 (有符号) 或 0 到 65535 (无符号)
    port SMALLINT UNSIGNED COMMENT '端口号',
    year SMALLINT COMMENT '年份',
    
    -- MEDIUMINT: 3字节，范围 -8388608 到 8388607 (有符号) 或 0 到 16777215 (无符号)
    medium_id MEDIUMINT UNSIGNED COMMENT '中等范围ID',
    
    -- INT: 4字节，范围 -2147483648 到 2147483647 (有符号) 或 0 到 4294967295 (无符号)
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    count INT UNSIGNED DEFAULT 0 COMMENT '计数',
    
    -- BIGINT: 8字节，范围很大
    timestamp_ms BIGINT UNSIGNED COMMENT '毫秒时间戳',
    large_number BIGINT COMMENT '大数值'
);

-- 查看表结构和存储空间
DESCRIBE integer_types_demo;

-- 插入测试数据
INSERT INTO integer_types_demo (status, age, port, year, user_id, timestamp_ms) VALUES
(1, 25, 3306, 2023, 1001, 1693200000000),
(0, 30, 8080, 2023, 1002, 1693200001000);

-- 查看数据存储大小
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size in MB'
FROM information_schema.tables 
WHERE table_schema = DATABASE() AND table_name = 'integer_types_demo';

-- 整数类型选择原则演示
SELECT 
    '数据类型' as 类型,
    '存储字节' as 字节数,
    '有符号范围' as 有符号范围,
    '无符号范围' as 无符号范围,
    '适用场景' as 使用场景
UNION ALL
SELECT 'TINYINT', '1', '-128 到 127', '0 到 255', '状态标志、年龄、小范围枚举'
UNION ALL
SELECT 'SMALLINT', '2', '-32768 到 32767', '0 到 65535', '端口号、年份、中小范围计数'
UNION ALL
SELECT 'MEDIUMINT', '3', '-8388608 到 8388607', '0 到 16777215', '中等范围ID、计数'
UNION ALL
SELECT 'INT', '4', '-2147483648 到 2147483647', '0 到 4294967295', '用户ID、订单ID、大部分计数'
UNION ALL
SELECT 'BIGINT', '8', '非常大的负数到正数', '0 到很大的正数', '时间戳、大数据量ID、大计数';

2. 浮点数和定点数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- 浮点数和定点数类型对比
CREATE TABLE numeric_precision_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- FLOAT: 4字节，单精度浮点数，精度约7位小数
    float_value FLOAT(7,2) COMMENT '单精度浮点数',
    
    -- DOUBLE: 8字节，双精度浮点数，精度约15位小数
    double_value DOUBLE(10,4) COMMENT '双精度浮点数',
    
    -- DECIMAL: 精确的定点数，用于金融计算
    price DECIMAL(10,2) NOT NULL COMMENT '价格，精确到分',
    amount DECIMAL(15,4) DEFAULT 0.0000 COMMENT '金额，精确到万分位',
    
    -- 实际应用场景
    product_weight FLOAT(5,2) COMMENT '商品重量(kg)',
    gps_latitude DOUBLE(10,7) COMMENT 'GPS纬度',
    gps_longitude DOUBLE(10,7) COMMENT 'GPS经度',
    account_balance DECIMAL(15,2) COMMENT '账户余额'
);

-- 插入测试数据演示精度差异
INSERT INTO numeric_precision_demo 
(float_value, double_value, price, amount, product_weight, gps_latitude, gps_longitude, account_balance) 
VALUES
(123.456, 123.456789012345, 99.99, 1234.5678, 2.5, 39.9042000, 116.4074000, 10000.50),
(999.999, 999.999999999999, 199.95, 9999.9999, 15.75, 31.2304000, 121.4737000, 50000.25);

-- 查看精度保存情况
SELECT * FROM numeric_precision_demo;

-- 数值类型选择建议
SELECT 
    '场景' as 使用场景,
    '推荐类型' as 数据类型,
    '原因' as 选择原因
UNION ALL
SELECT '金融金额', 'DECIMAL', '精确计算，避免浮点误差'
UNION ALL
SELECT '商品价格', 'DECIMAL(10,2)', '精确到分，满足货币计算'
UNION ALL
SELECT '科学计算', 'DOUBLE', '高精度浮点运算'
UNION ALL
SELECT '商品重量', 'FLOAT', '一般精度足够，节省存储'
UNION ALL
SELECT 'GPS坐标', 'DOUBLE', '需要高精度位置信息'
UNION ALL
SELECT '统计比率', 'FLOAT', '近似值计算，存储效率高';

-- 金融计算示例
CREATE TABLE financial_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    transaction_amount DECIMAL(15,2) NOT NULL COMMENT '交易金额',
    fee_rate DECIMAL(5,4) NOT NULL COMMENT '手续费率',
    calculated_fee DECIMAL(15,2) AS (transaction_amount * fee_rate) STORED COMMENT '计算的手续费',
    final_amount DECIMAL(15,2) AS (transaction_amount - calculated_fee) STORED COMMENT '最终金额'
);

INSERT INTO financial_demo (user_id, transaction_amount, fee_rate) VALUES
(1001, 1000.00, 0.0025),  -- 0.25% 手续费
(1002, 5000.50, 0.0030);  -- 0.30% 手续费

SELECT * FROM financial_demo;

字符串类型优化

1. CHAR vs VARCHAR 选择

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- 字符串类型对比和选择
CREATE TABLE string_types_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- CHAR: 固定长度，适合长度固定的数据
    country_code CHAR(2) NOT NULL COMMENT '国家代码，固定2位',
    currency_code CHAR(3) NOT NULL COMMENT '货币代码，固定3位',
    md5_hash CHAR(32) COMMENT 'MD5哈希值，固定32位',
    
    -- VARCHAR: 可变长度，适合长度不固定的数据
    username VARCHAR(50) NOT NULL COMMENT '用户名，最长50字符',
    email VARCHAR(100) NOT NULL COMMENT '邮箱地址',
    description VARCHAR(500) COMMENT '描述信息',
    
    -- TEXT类型：大文本存储
    content TEXT COMMENT '文章内容',
    
    -- 创建时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引演示
CREATE INDEX idx_username ON string_types_demo(username);
CREATE INDEX idx_email ON string_types_demo(email);
-- 对于TEXT类型，需要指定前缀长度
CREATE INDEX idx_content_prefix ON string_types_demo(content(100));

-- 插入测试数据
INSERT INTO string_types_demo 
(country_code, currency_code, md5_hash, username, email, description, content) VALUES
('CN', 'CNY', MD5('test1'), 'zhangsan', 'zhangsan@example.com', '这是一个测试用户', '这是一篇很长的文章内容...'),
('US', 'USD', MD5('test2'), 'john_doe', 'john@example.com', 'Test user from US', 'This is a long article content...');

-- 查看存储空间使用
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'string_types_demo'
ORDER BY ORDINAL_POSITION;

-- 字符串类型选择指南
CREATE TEMPORARY TABLE string_type_guide AS
SELECT 
    '数据类型' as 类型,
    '存储特点' as 特点,
    '适用场景' as 场景,
    '性能特点' as 性能
UNION ALL
SELECT 'CHAR(n)', '固定长度，不足补空格', '国家代码、货币代码、哈希值', '查询快，但可能浪费空间'
UNION ALL
SELECT 'VARCHAR(n)', '可变长度，按实际长度存储', '用户名、邮箱、标题', '节省空间，查询效率高'
UNION ALL
SELECT 'TEXT', '大文本，最大65535字节', '文章内容、长描述', '不能设置默认值，索引需指定长度'
UNION ALL
SELECT 'MEDIUMTEXT', '中等文本，最大16MB', '较长文档内容', '适合中等长度文本'
UNION ALL
SELECT 'LONGTEXT', '长文本，最大4GB', '超长文档、日志', '适合大文本存储';

SELECT * FROM string_type_guide;

2. 字符集和排序规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- 字符集和排序规则演示
CREATE TABLE charset_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- UTF8MB4: 支持完整的UTF-8字符集，包括emoji
    name_utf8mb4 VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '支持emoji的姓名',
    
    -- UTF8: 传统UTF-8，不支持4字节字符
    name_utf8 VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci COMMENT '传统UTF-8姓名',
    
    -- 不同排序规则对比
    text_ci VARCHAR(100) COLLATE utf8mb4_unicode_ci COMMENT '不区分大小写',
    text_cs VARCHAR(100) COLLATE utf8mb4_bin COMMENT '区分大小写',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入测试数据，包含emoji和不同大小写
INSERT INTO charset_demo (name_utf8mb4, name_utf8, text_ci, text_cs) VALUES
('张三 😊', '张三', 'Hello', 'Hello'),
('李四 🎉', '李四', 'hello', 'hello'),
('王五 ❤️', '王五', 'HELLO', 'HELLO');

-- 查看数据
SELECT * FROM charset_demo;

-- 排序规则对比
SELECT '不区分大小写排序:' as 说明;
SELECT text_ci FROM charset_demo ORDER BY text_ci;

SELECT '区分大小写排序:' as 说明;
SELECT text_cs FROM charset_demo ORDER BY text_cs;

-- 查询对比
SELECT '不区分大小写查询:' as 说明;
SELECT * FROM charset_demo WHERE text_ci = 'hello';

SELECT '区分大小写查询:' as 说明;
SELECT * FROM charset_demo WHERE text_cs = 'hello';

-- 字符集选择建议
SELECT 
    '字符集' as charset,
    '特点' as 特点,
    '适用场景' as 场景,
    '注意事项' as 注意
UNION ALL
SELECT 'utf8mb4', '完整UTF-8支持，包括emoji', '现代应用，国际化需求', '存储空间稍大'
UNION ALL
SELECT 'utf8', '传统UTF-8，3字节字符', '老系统兼容', '不支持emoji等4字节字符'
UNION ALL
SELECT 'latin1', '单字节字符集', '纯英文环境', '不支持中文等多字节字符'
UNION ALL
SELECT 'ascii', 'ASCII字符集', '纯英文数字', '最节省空间，功能有限';

日期时间类型

1. 日期时间类型选择

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
-- 日期时间类型对比
CREATE TABLE datetime_types_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- DATE: 日期，格式 YYYY-MM-DD，范围 1000-01-01 到 9999-12-31
    birth_date DATE COMMENT '出生日期',
    
    -- TIME: 时间，格式 HH:MM:SS，范围 -838:59:59 到 838:59:59
    work_time TIME COMMENT '工作时间',
    
    -- DATETIME: 日期时间，格式 YYYY-MM-DD HH:MM:SS，范围 1000-01-01 00:00:00 到 9999-12-31 23:59:59
    created_datetime DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_datetime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- TIMESTAMP: 时间戳，格式同DATETIME，但范围 1970-01-01 00:00:01 到 2038-01-19 03:14:07
    login_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间戳',
    
    -- YEAR: 年份，格式 YYYY，范围 1901 到 2155
    graduation_year YEAR COMMENT '毕业年份'
);

-- 插入测试数据
INSERT INTO datetime_types_demo 
(birth_date, work_time, graduation_year) VALUES
('1990-05-15', '09:00:00', 2012),
('1985-12-20', '14:30:00', 2008),
('1995-03-08', '18:45:00', 2017);

-- 查看数据
SELECT * FROM datetime_types_demo;

-- 日期时间函数应用
SELECT 
    id,
    birth_date,
    YEAR(birth_date) as birth_year,
    MONTH(birth_date) as birth_month,
    DAY(birth_date) as birth_day,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) as age,
    DATE_FORMAT(created_datetime, '%Y年%m月%d日 %H:%i:%s') as formatted_datetime
FROM datetime_types_demo;

-- 时区处理示例
CREATE TABLE timezone_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    
    -- 使用DATETIME存储本地时间
    local_datetime DATETIME COMMENT '本地时间',
    
    -- 使用TIMESTAMP存储UTC时间（自动转换）
    utc_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'UTC时间戳',
    
    -- 存储时区信息
    timezone VARCHAR(50) DEFAULT 'Asia/Shanghai' COMMENT '时区'
);

-- 设置时区并插入数据
SET time_zone = '+08:00';
INSERT INTO timezone_demo (event_name, local_datetime) VALUES
('北京会议', '2023-08-28 14:00:00'),
('上海培训', '2023-08-29 09:30:00');

-- 查看不同时区下的时间
SELECT 
    event_name,
    local_datetime,
    utc_timestamp,
    CONVERT_TZ(local_datetime, '+08:00', '+00:00') as utc_datetime,
    CONVERT_TZ(local_datetime, '+08:00', '-05:00') as ny_datetime
FROM timezone_demo;

-- 日期时间类型选择指南
SELECT 
    '类型' as 数据类型,
    '存储字节' as 字节,
    '范围' as 时间范围,
    '适用场景' as 场景
UNION ALL
SELECT 'DATE', '3', '1000-01-01 到 9999-12-31', '生日、纪念日等纯日期'
UNION ALL
SELECT 'TIME', '3', '-838:59:59 到 838:59:59', '工作时间、持续时间'
UNION ALL
SELECT 'DATETIME', '8', '1000年到9999年', '不需要时区转换的时间'
UNION ALL
SELECT 'TIMESTAMP', '4', '1970年到2038年', '需要时区转换的时间戳'
UNION ALL
SELECT 'YEAR', '1', '1901到2155', '年份信息';

2. 时间戳设计最佳实践

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- 时间戳设计最佳实践
CREATE TABLE timestamp_best_practices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 标准时间戳字段
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 软删除时间戳
    deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间，NULL表示未删除',
    
    -- 业务相关时间戳
    published_at TIMESTAMP NULL COMMENT '发布时间',
    expired_at TIMESTAMP NULL COMMENT '过期时间',
    
    -- 毫秒时间戳（使用BIGINT存储）
    created_ms BIGINT UNSIGNED DEFAULT (UNIX_TIMESTAMP(NOW(3)) * 1000) COMMENT '毫秒时间戳',
    
    -- 其他字段
    title VARCHAR(200) NOT NULL,
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态：1-正常，0-删除',
    
    -- 索引设计
    INDEX idx_created_at (created_at),
    INDEX idx_updated_at (updated_at),
    INDEX idx_deleted_at (deleted_at),
    INDEX idx_status_created (status, created_at)
);

-- 插入测试数据
INSERT INTO timestamp_best_practices (title, published_at, expired_at) VALUES
('文章1', NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY)),
('文章2', NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY)),
('草稿文章', NULL, NULL);

-- 软删除操作
UPDATE timestamp_best_practices 
SET deleted_at = NOW(), status = 0 
WHERE id = 2;

-- 查询未删除的记录
SELECT * FROM timestamp_best_practices 
WHERE deleted_at IS NULL AND status = 1;

-- 查询即将过期的记录
SELECT * FROM timestamp_best_practices 
WHERE expired_at IS NOT NULL 
    AND expired_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 3 DAY)
    AND deleted_at IS NULL;

-- 时间戳查询优化示例
-- 使用索引友好的查询方式
EXPLAIN SELECT * FROM timestamp_best_practices 
WHERE created_at >= '2023-08-01' 
    AND created_at < '2023-09-01'
    AND status = 1;

-- 避免在时间字段上使用函数
-- 不好的查询方式
-- SELECT * FROM timestamp_best_practices WHERE DATE(created_at) = '2023-08-28';

-- 好的查询方式
SELECT * FROM timestamp_best_practices 
WHERE created_at >= '2023-08-28 00:00:00' 
    AND created_at < '2023-08-29 00:00:00';

JSON和二进制类型

1. JSON类型应用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
-- JSON类型应用示例 (MySQL 5.7+)
CREATE TABLE json_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    
    -- JSON字段存储复杂数据
    profile JSON COMMENT '用户配置信息',
    settings JSON COMMENT '用户设置',
    metadata JSON COMMENT '元数据',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 为JSON字段创建虚拟列和索引
    email VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.email'))) STORED,
    age INT AS (JSON_EXTRACT(profile, '$.age')) VIRTUAL,
    
    INDEX idx_email (email),
    INDEX idx_user_id (user_id)
);

-- 插入JSON数据
INSERT INTO json_demo (user_id, profile, settings, metadata) VALUES
(1001, 
 JSON_OBJECT('name', '张三', 'email', 'zhangsan@example.com', 'age', 28, 'city', '北京'),
 JSON_OBJECT('theme', 'dark', 'language', 'zh-CN', 'notifications', true),
 JSON_OBJECT('source', 'web', 'device', 'desktop', 'ip', '192.168.1.100')
),
(1002,
 JSON_OBJECT('name', '李四', 'email', 'lisi@example.com', 'age', 32, 'city', '上海', 'hobbies', JSON_ARRAY('reading', 'swimming')),
 JSON_OBJECT('theme', 'light', 'language', 'en-US', 'notifications', false),
 JSON_OBJECT('source', 'mobile', 'device', 'iPhone', 'ip', '192.168.1.101')
);

-- JSON查询操作
-- 查询JSON字段中的特定值
SELECT 
    user_id,
    JSON_EXTRACT(profile, '$.name') as name,
    JSON_EXTRACT(profile, '$.email') as email,
    JSON_EXTRACT(profile, '$.age') as age
FROM json_demo;

-- 使用JSON_UNQUOTE去除引号
SELECT 
    user_id,
    JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) as name,
    JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city')) as city
FROM json_demo;

-- 使用->和->>操作符 (MySQL 5.7+)
SELECT 
    user_id,
    profile->'$.name' as name_with_quotes,
    profile->>'$.name' as name_without_quotes,
    profile->>'$.age' as age
FROM json_demo;

-- JSON数组查询
SELECT 
    user_id,
    profile->>'$.name' as name,
    JSON_EXTRACT(profile, '$.hobbies') as hobbies,
    JSON_LENGTH(profile, '$.hobbies') as hobbies_count
FROM json_demo
WHERE JSON_EXTRACT(profile, '$.hobbies') IS NOT NULL;

-- JSON条件查询
SELECT * FROM json_demo 
WHERE profile->>'$.city' = '北京';

SELECT * FROM json_demo 
WHERE JSON_EXTRACT(settings, '$.theme') = 'dark';

-- JSON更新操作
-- 更新JSON字段中的特定值
UPDATE json_demo 
SET profile = JSON_SET(profile, '$.age', 29)
WHERE user_id = 1001;

-- 添加新的JSON属性
UPDATE json_demo 
SET profile = JSON_SET(profile, '$.phone', '13800138000')
WHERE user_id = 1001;

-- 删除JSON属性
UPDATE json_demo 
SET profile = JSON_REMOVE(profile, '$.phone')
WHERE user_id = 1001;

-- JSON聚合查询
SELECT 
    JSON_EXTRACT(settings, '$.theme') as theme,
    COUNT(*) as user_count
FROM json_demo
GROUP BY JSON_EXTRACT(settings, '$.theme');

-- JSON类型使用建议
SELECT 
    '使用场景' as 场景,
    '优点' as 优点,
    '缺点' as 缺点,
    '建议' as 建议
UNION ALL
SELECT '用户配置', '灵活存储复杂数据', '查询性能相对较低', '为常用字段创建虚拟列和索引'
UNION ALL
SELECT '产品属性', '适合变化的属性结构', '不支持外键约束', '重要字段建议单独存储'
UNION ALL
SELECT '日志数据', '便于存储结构化日志', '占用存储空间较大', '定期清理和归档'
UNION ALL
SELECT '配置信息', '避免频繁修改表结构', '数据一致性检查复杂', '制定JSON结构规范';

2. 二进制类型应用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
-- 二进制类型应用示例
CREATE TABLE binary_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- BINARY: 固定长度二进制数据
    hash_binary BINARY(16) COMMENT 'MD5哈希值（16字节）',
    
    -- VARBINARY: 可变长度二进制数据
    token VARBINARY(255) COMMENT '访问令牌',
    
    -- BLOB类型：二进制大对象
    small_file BLOB COMMENT '小文件（最大65KB）',
    medium_file MEDIUMBLOB COMMENT '中等文件（最大16MB）',
    large_file LONGBLOB COMMENT '大文件（最大4GB）',
    
    -- 文件信息
    filename VARCHAR(255) COMMENT '文件名',
    file_type VARCHAR(50) COMMENT '文件类型',
    file_size INT UNSIGNED COMMENT '文件大小（字节）',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入二进制数据示例
INSERT INTO binary_demo (hash_binary, token, filename, file_type) VALUES
(UNHEX(MD5('test_file_1')), 
 UNHEX('a1b2c3d4e5f6'), 
 'test.txt', 
 'text/plain'),
(UNHEX(MD5('test_file_2')), 
 UNHEX('f6e5d4c3b2a1'), 
 'image.jpg', 
 'image/jpeg');

-- 查询二进制数据
SELECT 
    id,
    HEX(hash_binary) as hash_hex,
    HEX(token) as token_hex,
    filename,
    file_type
FROM binary_demo;

-- 二进制数据比较
SELECT * FROM binary_demo 
WHERE hash_binary = UNHEX(MD5('test_file_1'));

-- 文件存储最佳实践示例
CREATE TABLE file_storage_best_practice (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 文件基本信息
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_extension VARCHAR(10) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size BIGINT UNSIGNED NOT NULL,
    
    -- 文件哈希值（用于去重和完整性检查）
    md5_hash BINARY(16) NOT NULL,
    sha256_hash BINARY(32) COMMENT 'SHA256哈希值',
    
    -- 存储路径（推荐存储路径而不是文件内容）
    storage_path VARCHAR(500) NOT NULL COMMENT '文件存储路径',
    storage_type ENUM('local', 'oss', 's3', 'cdn') DEFAULT 'local',
    
    -- 文件状态
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '1-正常，0-删除',
    
    -- 时间戳
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 索引
    UNIQUE KEY uk_md5_hash (md5_hash),
    INDEX idx_filename (filename),
    INDEX idx_file_extension (file_extension),
    INDEX idx_created_at (created_at)
);

-- 二进制类型选择指南
SELECT 
    '类型' as 数据类型,
    '最大长度' as 最大长度,
    '适用场景' as 场景,
    '建议' as 使用建议
UNION ALL
SELECT 'BINARY(n)', '255字节', '固定长度哈希值、密钥', '长度固定时使用，如MD5、UUID'
UNION ALL
SELECT 'VARBINARY(n)', '65535字节', '可变长度二进制数据', '令牌、小型二进制文件'
UNION ALL
SELECT 'BLOB', '65KB', '小型文件、图片缩略图', '建议存储路径而非文件内容'
UNION ALL
SELECT 'MEDIUMBLOB', '16MB', '中等大小文件', '谨慎使用，影响查询性能'
UNION ALL
SELECT 'LONGBLOB', '4GB', '大型文件', '不推荐，建议使用文件系统';

字段设计最佳实践

1. 主键设计策略

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 主键设计对比
-- 1. 自增整数主键（推荐）
CREATE TABLE users_auto_increment (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. UUID主键
CREATE TABLE users_uuid (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 主键选择建议
SELECT 
    '主键类型' as 类型,
    '优点' as 优点,
    '缺点' as 缺点,
    '适用场景' as 场景
UNION ALL
SELECT '自增整数', '性能好、存储小、有序', '单机限制、可预测', '单机应用、高性能要求'
UNION ALL
SELECT 'UUID', '全局唯一、分布式友好', '存储大、无序、性能较低', '分布式系统、数据合并';

2. 索引字段设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 索引字段设计最佳实践
CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    sku VARCHAR(50) NOT NULL UNIQUE COMMENT '商品SKU',
    category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
    price DECIMAL(10,2) NOT NULL COMMENT '价格',
    status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：1-上架，0-下架',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 索引设计
    INDEX idx_category_status (category_id, status),
    INDEX idx_price_status (price, status),
    INDEX idx_created_at (created_at)
);

3. 字段约束和默认值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 字段约束和默认值设计
CREATE TABLE user_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    nickname VARCHAR(50) NOT NULL COMMENT '昵称',
    gender ENUM('male', 'female', 'unknown') NOT NULL DEFAULT 'unknown' COMMENT '性别',
    phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
    email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
    is_verified TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否认证：1-是，0-否',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 约束
    CONSTRAINT chk_phone CHECK (phone IS NULL OR phone REGEXP '^[0-9+\\-\\s()]+$'),
    CONSTRAINT chk_email CHECK (email IS NULL OR email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
    
    -- 索引
    INDEX idx_user_id (user_id),
    INDEX idx_phone (phone),
    INDEX idx_email (email)
);

性能优化建议

1. 存储空间优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 存储空间优化示例
CREATE TABLE storage_optimization_demo (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：1-2-3',
    age TINYINT UNSIGNED COMMENT '年龄：0-255',
    price DECIMAL(8,2) NOT NULL COMMENT '价格：精确到分',
    name VARCHAR(50) NOT NULL COMMENT '姓名：实际很少超过50字符',
    gender ENUM('M', 'F', 'U') DEFAULT 'U' COMMENT '性别：M-男，F-女，U-未知',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 计算存储空间节省
SELECT 
    '优化项' as 项目,
    '优化前字节' as 优化前,
    '优化后字节' as 优化后,
    '节省比例' as 比例
UNION ALL
SELECT 'status字段', '4', '1', '75%'
UNION ALL
SELECT 'age字段', '4', '1', '75%'
UNION ALL
SELECT 'price字段', '8', '5', '37.5%'
UNION ALL
SELECT 'name字段', '255*4', '50*4', '80%'
UNION ALL
SELECT 'gender字段', '20', '1', '95%';

2. 查询性能优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询性能优化字段设计
CREATE TABLE performance_optimized_table (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    user_name VARCHAR(50) NOT NULL COMMENT '冗余用户名，避免JOIN用户表',
    order_total DECIMAL(10,2) NOT NULL,
    status TINYINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_date DATE AS (DATE(created_at)) STORED COMMENT '日期字段，便于按日期查询',
    
    -- 索引设计
    INDEX idx_user_status (user_id, status),
    INDEX idx_created_date (created_date)
);

最佳实践总结

1. 字段设计检查清单

  • 数据类型选择：选择能满足需求的最小数据类型
  • 字段长度：根据实际需求设置合适长度
  • NULL值处理：明确字段是否允许NULL，设置合理默认值
  • 默认值设置：为字段设置合理默认值
  • 字段注释：每个字段都有清晰注释
  • 索引设计：为查询字段创建合适索引
  • 约束设置：添加必要的数据约束

2. 常见设计问题

  • 过度使用VARCHAR(255)：浪费存储空间，影响性能
  • 滥用TEXT类型：无法设置默认值，影响查询
  • 不设置字段注释：维护困难，理解成本高
  • 忽略字符集设置：可能出现乱码问题
  • 主键设计不当：影响查询和存储性能
  • 索引设计不合理：查询性能差

3. 设计规范建议

  1. 数据类型选择：选择能满足需求的最小数据类型
  2. 字段命名：使用有意义的英文名称，遵循命名规范
  3. NULL值处理：明确字段是否允许NULL，设置合理默认值
  4. 字符集统一：统一使用utf8mb4字符集和排序规则
  5. 索引设计：根据查询模式设计合适的索引
  6. 约束设置：添加必要的数据完整性约束
  7. 注释完整：每个字段和表都要有清晰的注释说明

通过合理的数据类型选择和字段设计，可以显著提升MySQL数据库的存储效率和查询性能。记住，好的字段设计是数据库性能优化的基础，需要在项目初期就认真考虑和规划。

  1. 数值类型详解
    1. 1. 整数类型选择
    2. 2. 浮点数和定点数
  2. 字符串类型优化
    1. 1. CHAR vs VARCHAR 选择
    2. 2. 字符集和排序规则
  3. 日期时间类型
    1. 1. 日期时间类型选择
    2. 2. 时间戳设计最佳实践
  4. JSON和二进制类型
    1. 1. JSON类型应用
    2. 2. 二进制类型应用
  5. 字段设计最佳实践
    1. 1. 主键设计策略
    2. 2. 索引字段设计
    3. 3. 字段约束和默认值
  6. 性能优化建议
    1. 1. 存储空间优化
    2. 2. 查询性能优化
  7. 最佳实践总结
    1. 1. 字段设计检查清单
    2. 2. 常见设计问题
    3. 3. 设计规范建议
© 2025    Orion K
本站由 提供部署服务
  1. 数值类型详解
    1. 1. 整数类型选择
    2. 2. 浮点数和定点数
  2. 字符串类型优化
    1. 1. CHAR vs VARCHAR 选择
    2. 2. 字符集和排序规则
  3. 日期时间类型
    1. 1. 日期时间类型选择
    2. 2. 时间戳设计最佳实践
  4. JSON和二进制类型
    1. 1. JSON类型应用
    2. 2. 二进制类型应用
  5. 字段设计最佳实践
    1. 1. 主键设计策略
    2. 2. 索引字段设计
    3. 3. 字段约束和默认值
  6. 性能优化建议
    1. 1. 存储空间优化
    2. 2. 查询性能优化
  7. 最佳实践总结
    1. 1. 字段设计检查清单
    2. 2. 常见设计问题
    3. 3. 设计规范建议