MariaDB教程FG026-MariaDB JSON数据类型操作与业务实战

内容简介：本文风哥教程参考MariaDB官方文档MariaDB Server、JSON Functions等章节，详细讲解MariaDB JSON数据类型的操作与业务应用，包括JSON的创建、查询、更新和性能优化。

Part01-基础概念与理论知识

1.1 JSON数据类型基本概念

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，MariaDB从10.2.7版本开始支持JSON数据类型。JSON数据类型允许存储和查询半结构化数据，非常适合存储复杂的数据结构。

1.2 JSON函数与操作符

JSON创建函数：
- JSON_OBJECT()：创建JSON对象
- JSON_ARRAY()：创建JSON数组
- JSON_QUOTE()：将字符串转换为JSON字符串
JSON查询函数：
- JSON_EXTRACT()：提取JSON值
- JSON_VALUE()：提取标量值
- JSON_CONTAINS()：检查JSON是否包含指定值
- JSON_SEARCH()：搜索JSON中的值
JSON更新函数：
- JSON_SET()：设置JSON值
- JSON_INSERT()：插入JSON值
- JSON_REPLACE()：替换JSON值
- JSON_REMOVE()：删除JSON值

1.3 JSON优势与适用场景

优势：
- 灵活的数据结构
- 支持复杂的嵌套结构
- 易于与现代应用集成
- 减少表结构变更
适用场景：
- 配置数据存储
- 用户偏好设置
- 半结构化数据
- API响应数据
- 日志数据存储

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 JSON数据模型设计原则

风哥提示：合理的JSON数据模型设计可以提高查询性能和数据管理效率。

避免过度嵌套：深层嵌套会影响查询性能
合理使用数组：数组适合存储同类型的多个值
使用标准化结构：保持JSON结构的一致性
考虑查询需求：根据查询模式设计JSON结构
平衡灵活性与性能：在灵活性和性能之间找到平衡

2.2 性能优化建议

使用JSON索引：为频繁查询的JSON字段创建索引
合理使用JSON函数：避免在WHERE子句中使用复杂的JSON函数
限制JSON大小：避免存储过大的JSON文档
使用适当的JSON函数：选择最适合的JSON函数
定期优化表：对包含JSON字段的表进行定期优化

2.3 生产环境最佳实践

在开发环境充分测试JSON操作
监控JSON字段的使用情况
设置合理的JSON字段大小限制
使用事务确保数据一致性
定期备份包含JSON数据的表

学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 JSON数据类型创建与管理

更多学习教程公众号风哥教程itpux_com

# 创建包含JSON字段的表
MariaDB [fgedudb]> CREATE TABLE fgedu_json_test (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50),
-> data JSON,
-> created_at TIMESTAMP DEFAULT NOW()
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
# 插入JSON数据
MariaDB [fgedudb]> INSERT INTO fgedu_json_test (name, data)
-> VALUES (
-> ‘Test 1’,
-> JSON_OBJECT(
-> ‘age’, 30,
-> ’email’, ‘test@fgedu.net.cn’,
-> ‘address’, JSON_OBJECT(
-> ‘street’, ‘123 Main St’,
-> ‘city’, ‘New York’,
-> ‘zip’, ‘10001’
-> ),
-> ‘hobbies’, JSON_ARRAY(‘reading’, ‘hiking’, ‘coding’)
-> )
-> );
Query OK, 1 row affected (0.00 sec)
# 查看JSON数据
MariaDB [fgedudb]> SELECT * FROM fgedu_json_test;
+—-+——-+——————————————————————————————————————————————————————————————-+
| id | name | data |
+—-+——-+——————————————————————————————————————————————————————————————-+
| 1 | Test 1 | {“address”: {“city”: “New York”, “street”: “123 Main St”, “zip”: “10001”}, “age”: 30, “email”: “test@fgedu.net.cn”, “hobbies”: [“reading”, “hiking”, “coding”]} |
+—-+——-+——————————————————————————————————————————————————————————————-+
# 更新JSON数据
MariaDB [fgedudb]> UPDATE fgedu_json_test
-> SET data = JSON_SET(data, ‘$.age’, 31, ‘$.email’, ‘updated@fgedu.net.cn’)
-> WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 查看更新后的JSON数据
MariaDB [fgedudb]> SELECT * FROM fgedu_json_test;
+—-+——-+————————————————————————————————————————————————————————————————+
| id | name | data |
+—-+——-+————————————————————————————————————————————————————————————————+
| 1 | Test 1 | {“address”: {“city”: “New York”, “street”: “123 Main St”, “zip”: “10001”}, “age”: 31, “email”: “updated@fgedu.net.cn”, “hobbies”: [“reading”, “hiking”, “coding”]} |
+—-+——-+————————————————————————————————————————————————————————————————+

3.2 JSON数据操作与查询

# 提取JSON值
MariaDB [fgedudb]> SELECT
-> id,
-> name,
-> JSON_EXTRACT(data, ‘$.age’) AS age,
-> JSON_EXTRACT(data, ‘$.email’) AS email,
-> JSON_EXTRACT(data, ‘$.address.city’) AS city
-> FROM fgedu_json_test;
+—-+——-+——+———————+———-+
| id | name | age | email | city |
+—-+——-+——+———————+———-+
| 1 | Test 1 | 31 | “updated@fgedu.net.cn” | “New York” |
+—-+——-+——+———————+———-+
# 使用简化语法提取JSON值
MariaDB [fgedudb]> SELECT
-> id,
-> name,
-> data->’$.age’ AS age,
-> data->’$.email’ AS email,
-> data->’$.address.city’ AS city
-> FROM fgedu_json_test;
+—-+——-+——+———————+———-+
| id | name | age | email | city |
+—-+——-+——+———————+———-+
| 1 | Test 1 | 31 | “updated@fgedu.net.cn” | “New York” |
+—-+——-+——+———————+———-+
# 提取标量值（去除引号）
MariaDB [fgedudb]> SELECT
-> id,
-> name,
-> data->>’$.age’ AS age,
-> data->>’$.email’ AS email,
-> data->>’$.address.city’ AS city
-> FROM fgedu_json_test;
+—-+——-+——+———————+———-+
| id | name | age | email | city |
+—-+——-+——+———————+———-+
| 1 | Test 1 | 31 | updated@fgedu.net.cn | New York |
+—-+——-+——+———————+———-+
# 条件查询
MariaDB [fgedudb]> SELECT * FROM fgedu_json_test
-> WHERE JSON_EXTRACT(data, ‘$.age’) > 30;
+—-+——-+————————————————————————————————————————————————————————————————+
| id | name | data |
+—-+——-+————————————————————————————————————————————————————————————————+
| 1 | Test 1 | {“address”: {“city”: “New York”, “street”: “123 Main St”, “zip”: “10001”}, “age”: 31, “email”: “updated@fgedu.net.cn”, “hobbies”: [“reading”, “hiking”, “coding”]} |
+—-+——-+————————————————————————————————————————————————————————————————+
# 搜索JSON数组
MariaDB [fgedudb]> SELECT * FROM fgedu_json_test
-> WHERE JSON_CONTAINS(data->’$.hobbies’, JSON_ARRAY(‘coding’));
+—-+——-+————————————————————————————————————————————————————————————————+
| id | name | data |
+—-+——-+————————————————————————————————————————————————————————————————+
| 1 | Test 1 | {“address”: {“city”: “New York”, “street”: “123 Main St”, “zip”: “10001”}, “age”: 31, “email”: “updated@fgedu.net.cn”, “hobbies”: [“reading”, “hiking”, “coding”]} |
+—-+——-+————————————————————————————————————————————————————————————————+

3.3 JSON索引与性能优化

学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 基础JSON操作示例

# 示例1：存储用户配置
MariaDB [fgedudb]> CREATE TABLE fgedu_user_config (
-> user_id INT PRIMARY KEY,
-> config JSON,
-> updated_at TIMESTAMP DEFAULT NOW() ON UPDATE NOW()
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
# 插入用户配置
MariaDB [fgedudb]> INSERT INTO fgedu_user_config (user_id, config)
-> VALUES (
-> 1,
-> JSON_OBJECT(
-> ‘theme’, ‘dark’,
-> ‘notifications’, JSON_OBJECT(
-> ’email’, true,
-> ‘sms’, false,
-> ‘push’, true
-> ),
-> ‘preferences’, JSON_OBJECT(
-> ‘language’, ‘zh-CN’,
-> ‘timezone’, ‘Asia/Shanghai’,
-> ‘currency’, ‘CNY’
-> )
-> )
-> );
Query OK, 1 row affected (0.00 sec)
# 更新用户配置
MariaDB [fgedudb]> UPDATE fgedu_user_config
-> SET config = JSON_SET(config, ‘$.theme’, ‘light’, ‘$.preferences.language’, ‘en-US’)
-> WHERE user_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 查看用户配置
MariaDB [fgedudb]> SELECT * FROM fgedu_user_config;
+———+——————————————————————————————————————————————————————+
| user_id | config |
+———+——————————————————————————————————————————————————————+
| 1 | {“notifications”: {“email”: true, “push”: true, “sms”: false}, “preferences”: {“currency”: “CNY”, “language”: “en-US”, “timezone”: “Asia/Shanghai”}, “theme”: “light”} |
+———+——————————————————————————————————————————————————————+
# 示例2：存储产品属性
MariaDB [fgedudb]> CREATE TABLE fgedu_products (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(100),
-> price DECIMAL(10,2),
-> attributes JSON,
-> created_at TIMESTAMP DEFAULT NOW()
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
# 插入产品数据
MariaDB [fgedudb]> INSERT INTO fgedu_products (name, price, attributes)
-> VALUES (
-> ‘Smartphone’,
-> 5999.99,
-> JSON_OBJECT(
-> ‘brand’, ‘Apple’,
-> ‘model’, ‘iPhone 14’,
-> ‘specs’, JSON_OBJECT(
-> ‘display’, ‘6.1 inches’,
-> ‘storage’, ‘128GB’,
-> ‘camera’, ’12MP + 12MP’,
-> ‘battery’, ‘4000mAh’
-> ),
-> ‘colors’, JSON_ARRAY(‘black’, ‘white’, ‘blue’, ‘red’)
-> )
-> );
Query OK, 1 row affected (0.00 sec)
# 查询产品属性
MariaDB [fgedudb]> SELECT
-> id,
-> name,
-> price,
-> attributes->>’$.brand’ AS brand,
-> attributes->>’$.model’ AS model,
-> attributes->>’$.specs.storage’ AS storage,
-> attributes->>’$.colors’ AS colors
-> FROM fgedu_products;
+—-+———–+———+——-+———+———+—————————+
| id | name | price | brand | model | storage | colors |
+—-+———–+———+——-+———+———+—————————+
| 1 | Smartphone | 5999.99 | Apple | iPhone 14 | 128GB | [“black”, “white”, “blue”, “red”] |
+—-+———–+———+——-+———+———+—————————+

4.2 业务场景实战

# 场景：电商系统订单数据
MariaDB [fgedudb]> CREATE TABLE fgedu_orders (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> user_id INT,
-> total_amount DECIMAL(10,2),
-> status VARCHAR(20),
-> order_data JSON,
-> created_at TIMESTAMP DEFAULT NOW()
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
# 插入订单数据
MariaDB [fgedudb]> INSERT INTO fgedu_orders (user_id, total_amount, status, order_data)
-> VALUES (
-> 1,
-> 12999.98,
-> ‘pending’,
-> JSON_OBJECT(
-> ‘items’, JSON_ARRAY(
-> JSON_OBJECT(
-> ‘product_id’, 1,
-> ‘name’, ‘Smartphone’,
-> ‘quantity’, 2,
-> ‘price’, 5999.99
-> ),
-> JSON_OBJECT(
-> ‘product_id’, 2,
-> ‘name’, ‘Headphones’,
-> ‘quantity’, 1,
-> ‘price’, 999.99
-> )
-> ),
-> ‘shipping’, JSON_OBJECT(
-> ‘address’, ‘123 Main St, New York, 10001’,
-> ‘method’, ‘express’,
-> ‘fee’, 0
-> ),
-> ‘payment’, JSON_OBJECT(
-> ‘method’, ‘credit_card’,
-> ‘card_last4’, ‘1234’,
-> ‘status’, ‘pending’
-> )
-> )
-> );
Query OK, 1 row affected (0.00 sec)
# 查询订单详情
MariaDB [fgedudb]> SELECT
-> id,
-> user_id,
-> total_amount,
-> status,
-> order_data->>’$.shipping.address’ AS shipping_address,
-> order_data->>’$.payment.method’ AS payment_method
-> FROM fgedu_orders;
+—-+———+————–+———+—————————–+—————+
| id | user_id | total_amount | status | shipping_address | payment_method |
+—-+———+————–+———+—————————–+—————+
| 1 | 1 | 12999.98 | pending | 123 Main St, New York, 10001 | credit_card |
+—-+———+————–+———+—————————–+—————+
# 查询订单商品
MariaDB [fgedudb]> SELECT
-> id,
-> JSON_EXTRACT(order_data, ‘$.items[*].name’) AS product_names,
-> JSON_EXTRACT(order_data, ‘$.items[*].quantity’) AS quantities,
-> JSON_EXTRACT(order_data, ‘$.items[*].price’) AS prices
-> FROM fgedu_orders;
+—-+—————————————-+————+————————–+
| id | product_names | quantities | prices |
+—-+—————————————-+————+————————–+
| 1 | [“Smartphone”, “Headphones”] | [2, 1] | [5999.99, 999.99] |
+—-+—————————————-+————+————————–+
# 更新订单状态
MariaDB [fgedudb]> UPDATE fgedu_orders
-> SET
-> status = ‘paid’,
-> order_data = JSON_SET(order_data, ‘$.payment.status’, ‘completed’)
-> WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 查看更新后的订单
MariaDB [fgedudb]> SELECT
-> id,
-> status,
-> order_data->>’$.payment.status’ AS payment_status
-> FROM fgedu_orders;
+—-+——-+—————-+—————+
| id | status | payment_status |
+—-+——-+—————-+—————+
| 1 | paid | completed |
+—-+——-+—————-+—————+

4.3 JSON性能优化实战

# 测试JSON性能
MariaDB [fgedudb]> CREATE TABLE fgedu_json_perf (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> data JSON,
-> created_at TIMESTAMP DEFAULT NOW()
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
# 插入测试数据
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE PROCEDURE fgedu_insert_json_data(IN p_count INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= p_count DO
INSERT INTO fgedu_json_perf (data)
VALUES (
JSON_OBJECT(
‘id’, i,
‘name’, CONCAT(‘user_’, i),
’email’, CONCAT(‘user_’, i, ‘@fgedu.net.cn’),
‘age’, FLOOR(RAND() * 50) + 20,
‘address’, JSON_OBJECT(
‘street’, CONCAT(i, ‘ Main St’),
‘city’, ‘City’,
‘zip’, CONCAT(‘1000’, i % 10)
),
‘hobbies’, JSON_ARRAY(‘reading’, ‘hiking’, ‘coding’)
)
);
SET i = i + 1;
END WHILE;
END //
DELIMITER;
Query OK, 0 rows affected (0.00 sec)
# 插入10000条测试数据
MariaDB [fgedudb]> CALL fgedu_insert_json_data(10000);
Query OK, 1 row affected (1.23 sec)
# 测试无索引查询
MariaDB [fgedudb]> EXPLAIN SELECT * FROM fgedu_json_perf
-> WHERE JSON_EXTRACT(data, ‘$.age’) > 40\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fgedu_json_perf
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10000
filtered: 33.33
Extra: Using where
# 测试无索引查询性能
MariaDB [fgedudb]> SELECT COUNT(*) FROM fgedu_json_perf
-> WHERE JSON_EXTRACT(data, ‘$.age’) > 40;
+———-+
| COUNT(*) |
+———-+
| 2015 |
+———-+
1 row in set (0.05 sec)
# 创建JSON索引
MariaDB [fgedudb]> CREATE INDEX idx_json_age_perf ON fgedu_json_perf ((CAST(data->’$.age’ AS UNSIGNED)));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 测试有索引查询
MariaDB [fgedudb]> EXPLAIN SELECT * FROM fgedu_json_perf
-> WHERE CAST(data->’$.age’ AS UNSIGNED) > 40\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fgedu_json_perf
partitions: NULL
type: range
possible_keys: idx_json_age_perf
key: idx_json_age_perf
key_len: 9
ref: NULL
rows: 2015
filtered: 100.00
Extra: Using where
# 测试有索引查询性能
MariaDB [fgedudb]> SELECT COUNT(*) FROM fgedu_json_perf
-> WHERE CAST(data->’$.age’ AS UNSIGNED) > 40;
+———-+
| COUNT(*) |
+———-+
| 2015 |
+———-+
1 row in set (0.01 sec)
# 测试JSON更新性能
MariaDB [fgedudb]> UPDATE fgedu_json_perf
-> SET data = JSON_SET(data, ‘$.age’, 30)
-> WHERE CAST(data->’$.age’ AS UNSIGNED) > 40;
Query OK, 2015 rows affected (0.12 sec)
Rows matched: 2015 Changed: 2015 Warnings: 0

风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 JSON使用经验

在实际生产环境中，JSON数据类型的使用需要注意以下几点：

合理设计JSON结构，避免过度嵌套
为频繁查询的JSON字段创建索引
使用适当的JSON函数，提高查询效率
限制JSON文档大小，避免存储过大的数据
定期优化包含JSON字段的表

5.2 常见问题与解决方案

性能问题：
- 问题：JSON查询速度慢
- 解决方案：创建JSON索引，优化查询语句
索引问题：
- 问题：JSON索引创建失败
- 解决方案：确保JSON路径正确，使用适当的CAST函数
数据一致性：
- 问题：JSON数据结构不一致
- 解决方案：建立JSON结构验证机制，使用标准化的JSON格式

5.3 生产故障案例分析

某电商系统在使用JSON存储订单数据时出现性能问题，经过分析发现：

问题原因：
- JSON文档过大，包含大量冗余数据
- 没有为频繁查询的JSON字段创建索引
- JSON结构设计不合理，过度嵌套
解决方案：
- 优化JSON结构，减少嵌套层级
- 为频繁查询的字段创建JSON索引
- 分离热点数据，将频繁查询的字段单独存储
- 定期清理JSON中的冗余数据
效果：查询性能提升10倍，存储空间减少30%

from MariaDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

MariaDB教程FG026-MariaDB JSON数据类型操作与业务实战

Part01-基础概念与理论知识

1.1 JSON数据类型基本概念

1.2 JSON函数与操作符

1.3 JSON优势与适用场景

Part02-生产环境规划与建议

2.1 JSON数据模型设计原则

2.2 性能优化建议

2.3 生产环境最佳实践

Part03-生产环境项目实施方案

3.1 JSON数据类型创建与管理

3.2 JSON数据操作与查询

3.3 JSON索引与性能优化

Part04-生产案例与实战讲解

4.1 基础JSON操作示例

4.2 业务场景实战

4.3 JSON性能优化实战

Part05-风哥经验总结与分享

5.1 JSON使用经验

5.2 常见问题与解决方案

5.3 生产故障案例分析

相关推荐

联系我们