开发者社区> 问答> 正文

MySQL中GROUP_CONCAT的对立面是什么?

我似乎经常遇到这个问题,因为我的数据格式如下:

+----+----------------------+ | id | colors | +----+----------------------+ | 1 | Red,Green,Blue | | 2 | Orangered,Periwinkle | +----+----------------------+ 但我想要这样格式化:

+----+------------+ | id | colors | +----+------------+ | 1 | Red | | 1 | Green | | 1 | Blue | | 2 | Orangered | | 2 | Periwinkle | +----+------------+ 有什么好方法吗?这种操作甚至叫做什么?

展开
收起
保持可爱mmm 2020-05-10 21:34:02 392 0
1 条回答
写回答
取消 提交回答
  • 我认为这是您需要的(存储过程):Mysql将列字符串拆分为行

    DELIMITER $$

    DROP PROCEDURE IF EXISTS explode_table $$ CREATE PROCEDURE explode_table(bound VARCHAR(255))

    BEGIN

    DECLARE id INT DEFAULT 0; DECLARE value TEXT; DECLARE occurance INT DEFAULT 0; DECLARE i INT DEFAULT 0; DECLARE splitted_value INT; DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT table1.id, table1.value FROM table1 WHERE table1.value != ''; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    DROP TEMPORARY TABLE IF EXISTS table2; CREATE TEMPORARY TABLE table2( id INT NOT NULL, value VARCHAR(255) NOT NULL ) ENGINE=Memory;

    OPEN cur1; read_loop: LOOP FETCH cur1 INTO id, value; IF done THEN LEAVE read_loop; END IF;

    SET occurance = (SELECT LENGTH(value)
                             - LENGTH(REPLACE(value, bound, ''))
                             +1);
    SET i=1;
    WHILE i <= occurance DO
      SET splitted_value =
      (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
      LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ',', ''));
    
      INSERT INTO table2 VALUES (id, splitted_value);
      SET i = i + 1;
    
    END WHILE;
    

    END LOOP;

    SELECT * FROM table2; CLOSE cur1; END; $$来源:stack overflow

    2020-05-10 21:34:17
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
搭建电商项目架构连接MySQL 立即下载
搭建4层电商项目架构,实战连接MySQL 立即下载
PolarDB MySQL引擎重磅功能及产品能力盛大发布 立即下载

相关镜像