MySQL - 拆分分隔的左右值

Question

我有一种情况，我将获得产品的输入及其值，并用分隔的特殊字符分隔。使用此字符串，我需要将产品及其值分隔到 MySQL 行，如下所示。

输入:

{"1301":29.00,"1302":25.01,"1306":50.09,"1678":100.00}

输出:

Product ID      Value
1301             29.00
1302             25.01
1306             50.09
1678             100.00

这里的产品Id计数是动态的，我们每次都可以得到n个计数。请帮助我在 MySQL 中获取上述输出。

Answer 1

MySQL 唯一具有 JSON 函数的解决方案。

查询

 SELECT 
  TRIM(REPLACE(
    SUBSTRING_INDEX(
       SUBSTRING_INDEX(json_parsed, ',', number_generator.number)
       , ','
       , -1
     )
     , '"'
     , ''
   ))  AS 'Product ID'
 , JSON_EXTRACT(json, CONCAT('$.', SUBSTRING_INDEX(
     SUBSTRING_INDEX(json_parsed, ',', number_generator.number)
     , ','
     , -1
   ))) AS 'Value'
FROM (

  SELECT 
   @row := @row + 1 AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2
  CROSS JOIN (
    SELECT @row := 0 
  ) init_user_params 
) AS number_generator
CROSS JOIN (

    SELECT  
        SUBSTRING(json_keys, 2, json_keys_length - 2) AS json_parsed
      , json_keys
      , json
      , JSON_LENGTH(json_keys) AS json_array_length                       
    FROM (
       SELECT 
            JSON_KEYS(record.json) AS json_keys
          , json
          , LENGTH(JSON_KEYS(record.json)) AS json_keys_length
       FROM (
          SELECT 
             '{"1301":29.00,"1302":25.01,"1306":50.09,"1678":100.00}' AS json
          FROM  
            DUAL  
       ) AS record                     
    ) AS json_information  
  ) AS json_init
WHERE
 number_generator.number BETWEEN 0 AND json_array_length

结果

| Product ID | Value |
| ---------- | ----- |
| 1301       | 29.0  |
| 1302       | 25.01 |
| 1306       | 50.09 |
| 1678       | 100.0 |

参见demo