如何在 MySQL 的单个列中删除重复的逗号分隔值 [英] How to remove duplicate comma separated value in a single column in MySQL

问题描述

SELECT id, country FROM my_records

我从 MySQL 查询中得到了上述结果，我想从结果中删除重复的 ID.不是借助 PHP 代码，而是借助 MySQL 查询.是否有任何功能或查询可以做同样的事情.

I've got the above result from MySQL query and i want to remove duplicate ID from the result. Not with the help of PHP code but do with MySQL query. Is there any function or query to do the same.

谢谢

推荐答案

我陷入了类似的情况，发现MySql没有提供任何预定义的函数来解决这个问题.

I stuck into the similar situation and found that MySql does not provide any predefined function to overcome this problem.

为了克服我创建了一个 UDF，请看下面的定义和用法.

To overcome I created a UDF, Please have a look below on the defination and usage.

DROP FUNCTION IF EXISTS `get_unique_items`;
DELIMITER //
CREATE FUNCTION `get_unique_items`(str varchar(1000)) RETURNS varchar(1000) CHARSET utf8
BEGIN

        SET @String      = str;
        SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
        SET @ret='';
        myloop: WHILE (@Occurrences > 0)
        DO 
            SET @myValue = SUBSTRING_INDEX(@String, ',', 1);
            IF (TRIM(@myValue) != '') THEN
                IF((LENGTH(@ret) - LENGTH(REPLACE(@ret, @myValue, '')))=0) THEN
                        SELECT CONCAT(@ret,@myValue,',') INTO @ret;
                END if;
            END IF;
            SET @Occurrences = LENGTH(@String) - LENGTH(REPLACE(@String, ',', ''));
            IF (@occurrences = 0) THEN 
                LEAVE myloop; 
            END IF;
            SET @String = SUBSTRING(@String,LENGTH(SUBSTRING_INDEX(@String, ',', 1))+2);
        END WHILE;    
SET @ret=concat(substring(@ret,1,length(@ret)-1), '');
return @ret;

END //
DELIMITER ;

示例用法:

SELECT get_unique_items('2,2,2,22,2,3,3,3,34,34,,54,5,45,,65,6,5,,67,6,,34,34,2,3,23,2,32,,3,2,,323') AS 'Items';

结果:

2,22,3,34,54,45,65,67,23,32,323

希望对您有所帮助！

这篇关于如何在 MySQL 的单个列中删除重复的逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！