MySQL JSON数据查询实战：订单金额统计与分析

最新推荐文章于 2025-12-20 14:42:23 发布

原创最新推荐文章于 2025-12-20 14:42:23 发布 · 61 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #json #android #php #后端

在处理半结构化数据时，MySQL的JSON功能提供了极大的灵活性。本文将带你学习如何使用JSON_EXTRACT和其他JSON函数来统计和分析订单数据。

数据准备：订单表结构

假设我们有一个test_json表，其中包含一个json_data列，存储着订单信息的JSON数据。每条记录的JSON结构如下：

{
  "order_id": "1001",
  "order_amount": 299.99,
  "discount_amount": 30.00,
  "customer_id": "cus_12345",
  "order_date": "2023-10-01"
}

基础统计：总金额和总优惠

方法一：使用JSON_EXTRACT函数

SELECT
  SUM(JSON_EXTRACT(json_data, '$.order_amount')) AS total_order_amount,
  SUM(JSON_EXTRACT(json_data, '$.discount_amount')) AS total_discount_amount
FROM test_json;

这是最基础的JSON数据提取方法。JSON_EXTRACT函数从JSON文档中提取指定路径的值，路径使用$.field_name格式。

方法二：使用->>运算符（MySQL 8.0+）

SELECT
  SUM(json_data->>'$.order_amount') AS total_order_amount,
  AVG(json_data->>'$.order_amount') AS avg_order_amount,
  MAX(json_data->>'$.order_amount') AS max_order_amount,
  MIN(json_data->>'$.order_amount') AS min_order_amount
FROM test_json;

代码解释：

->> 运算符是MySQL 8.0引入的简写形式，等价于 JSON_UNQUOTE(JSON_EXTRACT())
可以直接对提取的值进行聚合运算
除了总和，还可以计算平均值、最大值、最小值等

数据清洗：处理无效数据

在实际业务中，数据往往不够完美，可能存在空值或格式错误的情况：

SELECT
  SUM(CASE 
        WHEN JSON_TYPE(json_data->'$.order_amount') = 'DOUBLE' 
        THEN json_data->>'$.order_amount' 
        ELSE 0 
      END) AS total_valid_amount
FROM test_json;

关键点说明：

JSON_TYPE() 函数返回JSON值的类型
使用 CASE WHEN 条件判断确保只对有效的数字类型（‘DOUBLE’）进行求和
对于非数值数据，返回0避免计算错误

进阶应用：多维度分析

按日期统计每日订单总额

SELECT
  json_data->>'$.order_date' AS order_date,
  SUM(json_data->>'$.order_amount') AS daily_total
FROM test_json
GROUP BY json_data->>'$.order_date'
ORDER BY order_date;

计算实际支付金额（订单金额-优惠金额）

SELECT
  json_data->>'$.order_id' AS order_id,
  (json_data->>'$.order_amount' - json_data->>'$.discount_amount') AS actual_amount
FROM test_json;

性能优化建议

创建生成列：对于频繁查询的JSON字段，可以创建存储生成列

ALTER TABLE test_json 
ADD COLUMN order_amount DECIMAL(10,2) 
AS (json_data->>'$.order_amount');

CREATE INDEX idx_order_amount ON test_json(order_amount);