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

白与黑 2024-04-19 13:59:32 84℃ 5511 0条
#客户表
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_idclient表中的id关联,commentsclient表中brand_name每个元素相等,先需要将brand_name转换为以下格式:

cleint_idbrand_namegallery_id
22024"0
4品牌12
4品牌23
4品牌34
5sale15
52024@“”6
52024@“”@¥);-7
52024@$,,,&‘’8

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

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_ciutf8mb3_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_UNQUOTEJSON_EXTRACT)可能也影响到排序规则的应用。确保在使用这些函数时考虑到排序规则的一致性。如果需要,也可以在函数处理结果上应用 COLLATE

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

非特殊说明,本博所有文章均为博主原创。

上一篇 php获取html节点内容
下一篇 没有了

评论啦~