mysql使用存储过程处理json数据

#客户表
CREATE TABLE `client` (
 `id` int NOT NULL AUTO_INCREMENT,
 `brand_name` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

#表数据
INSERT INTO `client` (`id`, `brand_name`) VALUES
(1, ''),
(2, '["2024""]'),
(3, NULL),
(4, '["品牌1","品牌2","品牌3"]'),
(5, '["sale1","2024@“”","2024@“”@¥）；-","2024@$，，，&‘’"]');

#品牌图片表
CREATE TABLE `gallery` (
 `id` int NOT NULL AUTO_INCREMENT,
 `relation_id` int NOT NULL,
 `relation_type` int NOT NULL,
 `comments` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
 PRIMARY KEY (`id`) USING BTREE,
 KEY `relation_type` (`relation_type`) USING BTREE,
 KEY `relation_id` (`relation_id`,`relation_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

#表数据
INSERT INTO `gallery` (`id`, `relation_id`,`relation_type`,`comments`) VALUES
(1, 2,102,'2024"'),
(2, 4,102,'品牌1'),
(3, 4,102,'品牌2'),
(4, 4,102,'品牌3'),
(5, 5,102,'sale1'),
(6, 5,102,'2024@“”'),
(7, 5,102,'2024@“”@¥）；-'),
(8, 5,102,'2024@$，，，&‘’');

relation_id与client表中的id关联，comments与client表中brand_name每个元素相等，先需要将brand_name转换为以下格式：

创建客户品牌与图片关联表：

DROP TABLE IF EXISTS `client_brand_name`;
CREATE TABLE `client_brand_name` (
 `id` int NOT NULL AUTO_INCREMENT,
 `client_id` int NOT NULL DEFAULT '0',
 `brand_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 `gallery_id` int NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`) USING BTREE,
 KEY `client_id` (`client_id`) USING BTREE,
 KEY `gallery_id` (`gallery_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;

处理方式有两种，一种是通过代码处理，处理起来比较方便，但是当数据库比较多时，执行起来比较麻烦，当然也可以通过循环数据库的方式执行；另一种方式是直接写SQL语句处理；以下是通过MySQL方式处理：创建存储过程，通过执行存储过程处理数据，执行完之后删除存储过程即可。

#获取客户品牌，循环插入到关联表中
DROP PROCEDURE IF EXISTS `InsertBrandNames`;
DELIMITER $$
CREATE PROCEDURE InsertBrandNames()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE cur_id INT;
    DECLARE cur_brand_name VARCHAR(1024);

    DECLARE brand_cursor CURSOR FOR 
        SELECT id, brand_name FROM client WHERE brand_name IS NOT NULL AND TRIM(brand_name) <> '';

    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
    
    OPEN brand_cursor;
    
    get_brands: LOOP
        FETCH brand_cursor INTO cur_id, cur_brand_name;
        IF finished = 1 THEN 
            LEAVE get_brands;
        END IF;
        
        CALL InsertBrand(cur_id, cur_brand_name);
    END LOOP;

    CLOSE brand_cursor;
END$$
DELIMITER ;

#插入到关联表
DROP PROCEDURE IF EXISTS `InsertBrand`;
DELIMITER $$
CREATE PROCEDURE InsertBrand(cur_id INT, cur_brand_name VARCHAR(1024))
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE cur_brand_len INT DEFAULT 0;
  DECLARE cur_gallery_id INT DEFAULT 0;
  DECLARE brand VARCHAR(255);
  
  SET cur_brand_len = JSON_LENGTH(cur_brand_name);
  
  WHILE i < cur_brand_len DO
      SET brand = JSON_UNQUOTE(JSON_EXTRACT(cur_brand_name, CONCAT('$[', i, ']')));
      IF brand IS NOT NULL AND brand != '' THEN
        #需要注意comments字符集和排序规则
        SELECT id INTO cur_gallery_id FROM `gallery` WHERE `relation_id`=cur_id AND `comments`=brand COLLATE utf8mb3_unicode_ci  AND `relation_type`=102;
        IF cur_gallery_id IS NOT NULL THEN
            INSERT INTO `client_brand_name`(`client_id`,`brand_name`,`gallery_id`)VALUE(cur_id,brand,cur_gallery_id);
        ELSE
            INSERT INTO `client_brand_name`(`client_id`,`brand_name`,`gallery_id`)VALUE(cur_id,brand,0);
        END IF;
        SET cur_gallery_id = NULL;
      END IF;
      SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

#调用存储过程，处理数据
CALL InsertBrandNames();
#处理完毕后，删除存储过程
DROP PROCEDURE IF EXISTS `InsertBrandNames`;
DROP PROCEDURE IF EXISTS `InsertBrand`;

在执行存储过程时，出现以下错误：
MySQL执行出现以下错误：Illegal mix of collations(utf8mb3_general_ci,IMPLICIT) and (utf8mb3_unicode_ci,IMPLICIT) for operation '='

因为涉及到不同排序规则的字符串比较，通常发生在存储过程中的 SELECT 或 INSERT 操作，尤其是在比较或连接 company 表的 brand_name 列和 gallery 表的 comments 列时。

问题核心

在表结构和存储过程定义中，涉及字符串操作的列使用了不同的排序规则。
表定义：

company.brand_name 列的排序规则为 utf8mb3_general_ci
gallery.comments 列的排序规则也为 utf8mb3_general_ci

因此，排序规则冲突可能来源于其他操作，可能是数据类型转换或函数处理产生了默认的排序规则变化。

解决策略

1. 统一表中的字符集和排序规则

最简单的方法是确保所有表和相关列使用相同的字符集和排序规则。可以选择一个常用的排序规则（如 utf8mb3_general_ci 或 utf8mb3_unicode_ci），并统一所有表和列：

ALTER TABLE company CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;
ALTER TABLE gallery CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;
ALTER TABLE client_brand_name CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;

2. 在SQL查询中显式指定排序规则

如果不希望改变表的物理结构，可以在具体的 SQL 查询中显式指定排序规则，尤其是在进行比较或赋值的地方：

SELECT id INTO cur_gallery_id FROM `gallery` 
WHERE `relation_id` = cur_id 
AND `comments` = brand COLLATE utf8mb3_unicode_ci 
AND `relation_type` = 102;

在这里，将 comments 列在比较时使用 utf8mb3_unicode_ci，确保与 brand 变量的排序规则一致。

3. 检查存储过程中的变量和函数

存储过程中使用的函数（如 JSON_UNQUOTE 和 JSON_EXTRACT）可能也影响到排序规则的应用。确保在使用这些函数时考虑到排序规则的一致性。如果需要，也可以在函数处理结果上应用 COLLATE：

SET brand = JSON_UNQUOTE(JSON_EXTRACT(cur_brand_name, CONCAT('$[', i, ']'))) COLLATE utf8mb3_unicode_ci;