MYSQL解析JSON字符串 + 联表UPDATE操作

一、MYSQL解析JSON字符串

1、5.7版本以上：使用 JSON_EXTRACT和REPLACE 内置函数。

 

实例：解析出JSON字符串{"contactName":"段XX","contactJobTitle":"待确认","contactMobile":"131XXXXXXX"}中contactName字段的属性值。

SQL：

SELECT 

  REPLACE(

    JSON_EXTRACT (

      '{"contactName":"段XX","contactJobTitle":"待确认","contactMobile":"131XXXXXXX"}',

      '$.contactName'

    ),

    '"',

    ''

  ) 

FROM

  DUAL ;

 

2、5.7版本以下：使用REPLACE、SUBSTRING和LOCATE内置函数。

实例：解析出 nns_buy_order 数据表前10条数据， nns_request_data 字段中 nns_product_id 的属性值。

如：nns_request_data  属性值：{"contactName":"段XX","nns_product_id":"mgtv_yb","contactMobile":"131XXXXXXX"}

SQL:

SELECT 

      REPLACE(

        SUBSTRING(

          REPLACE(nns_request_data, '}', ','),

          LOCATE(

            'nns_product_id":',

            REPLACE(nns_request_data, '}', ',')

          ) + CHAR_LENGTH('nns_product_id":'),

          LOCATE(

            ',',

            REPLACE(nns_request_data, '}', ','),

            LOCATE(

              'nns_product_id":',

              REPLACE(nns_request_data, '}', ',')

            ) + CHAR_LENGTH('nns_product_id":')

          ) - (

            LOCATE(

              'nns_product_id":',

              REPLACE(nns_request_data, '}', ',')

            ) + CHAR_LENGTH('nns_product_id":')

          )

        ),

        '"',

        ''

      ) AS nns_product_id 

    FROM

      nns_buy_order 

    WHERE 1=1 LIMIT 10;

 

二、联表UPDATE操作

1、不同表：实现从A表中某字段的属性值 更新至B数据表某字段。（ 思路：联接A、B表）

格式：UPDATE B b JOIN A a ON a.`id` = b.`a_id` SET b.`xxx` = a.`yyy` WHERE 1=1 ;

实例：更细nns_product_fee表中的nns_product_fee_id字段属性值到nns_buy_order表的nns_product_fee_id字段，nns_product_fee和nns_buy_order关联字段为nns_product_id。

SQL：

UPDATE 

  nns_buy_order o 

  JOIN nns_product_fee f 

    ON f.`nns_product_id` = o.`nns_product_id` SET o.`nns_product_fee_id` = f.`nns_product_fee_id` 

WHERE 1=1;

 

2、相同表：更新A表中x字段的属性值到A表的y字段，且x字段的属性值为JSON字符串，需要更新JSON字符串中z参数值给y。（思路：构建临时表T + 联接A、临时表）

格式：

UPDATE 

  A a 

  JOIN 

    (SELECT 

      id,

      REPLACE(

        SUBSTRING(

          REPLACE(y, '}', ','),

          LOCATE(

            'z":',

            REPLACE(y, '}', ',')

          ) + CHAR_LENGTH('z":'),

          LOCATE(

            ',',

            REPLACE(y, '}', ','),

            LOCATE(

              'z":',

              REPLACE(y, '}', ',')

            ) + CHAR_LENGTH('z":')

          ) - (

            LOCATE(

              'z":',

              REPLACE(y, '}', ',')

            ) + CHAR_LENGTH('z":')

          )

        ),

        '"',

        ''

      ) AS z 

    FROM

      A

    WHERE 1=1) T 

    ON T.id = a.id SET a.x = T.z

WHERE 1=1;

 

实例：更细nns_buy_order表中nns_request_data字段的属性值 nns_product_id 到nns_buy_order表的nns_product_id字段。

如：nns_request_data  属性值：{"contactName":"段XX","nns_product_id":"mgtv_yb","contactMobile":"131XXXXXXX"}

 

UPDATE 

  nns_buy_order o 

  JOIN 

    (SELECT 

      nns_id,

      REPLACE(

        SUBSTRING(

          REPLACE(nns_request_data, '}', ','),

          LOCATE(

            'nns_product_id":',

            REPLACE(nns_request_data, '}', ',')

          ) + CHAR_LENGTH('nns_product_id":'),

          LOCATE(

            ',',

            REPLACE(nns_request_data, '}', ','),

            LOCATE(

              'nns_product_id":',

              REPLACE(nns_request_data, '}', ',')

            ) + CHAR_LENGTH('nns_product_id":')

          ) - (

            LOCATE(

              'nns_product_id":',

              REPLACE(nns_request_data, '}', ',')

            ) + CHAR_LENGTH('nns_product_id":')

          )

        ),

        '"',

        ''

      ) AS nns_product_id 

    FROM

      nns_buy_order 

    WHERE nns_product_id = '' 

      OR nns_product_id IS NULL) T 

    ON T.nns_id = o.nns_id SET o.`nns_product_id` = T.nns_product_id 

WHERE o.nns_product_id = '' 

  OR o.nns_product_id IS NULL 

 

3、同时更新多个关联数据表的字段属性值。（思路：联接A、B表 + 分别更新）

格式：UPDATE B b JOIN A a ON a.`id` = b.`a_id` SET b.`b_xxx` = '***',a.`a_yyy` = '***' WHERE 1=1 ;

 

实例：更细nns_product_fee表中的nns_product_fee_id字段属性值为 111且更新nns_buy_order表的nns_product_fee_id字段为222，nns_product_fee和nns_buy_order关联字段为nns_product_id。

SQL：

UPDATE 

  nns_buy_order o 

  JOIN nns_product_fee f 

    ON f.`nns_product_id` = o.`nns_product_id` SET o.`nns_product_fee_id` = '111',

  f.`nns_product_fee_id` = '222' 

WHERE 1 = 1 ;