mysql json整数数组去重 整数数组精确查找并删除相应数据

都是针对整数数组 。低版本可用。懒得去查找资料的可以参考下。

json整数数组查找具体数据修改或者删除：

update saas_new_tms.eda_logistics_limit  set  service_attribute =json_remove(service_attribute,json_unquote(json_search(replace(service_attribute,7,concat('"',7, '"')), 'one', 7)))  where json_contains(service_attribute, concat(7, '')) = 1 and  logistics_limit_id in (select  logistics_limit_id from eda_logistic_product where transport_type = 1 and  logistics_limit_id is not null);

表：eda_logistics_limit   

列：service_attribute  json类型整组数组数据

要删除的数据：7   

要使用 替换即可

去重：以下查询可以得到一个去重的数据。如果要修改可以参考以上的数据修改。或者 根据所得数据更新即可。

SELECT logistics_limit_id, GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(service_attribute, ',', idx), ',', -1) order by idx)
FROM eda_logistics_limit
CROSS JOIN (
  SELECT 1 + units.i + tens.i * 10 + hundreds.i * 100 AS idx
  FROM
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
     UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) units,
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
     UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tens,
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
     UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) hundreds
) AS numbers
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(service_attribute, ',', idx), ',', -1) != ''  group by logistics_limit_id;

以下是通过 照搬：其它改表名 改字段即可

CROSS JOIN (
  SELECT 1 + units.i + tens.i * 10 + hundreds.i * 100 AS idx
  FROM
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
     UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) units,
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
     UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tens,
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
     UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) hundreds
) AS numbers