如何在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.

谢谢

推荐答案

p>我陷入类似的情况，发现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.

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

使用样本：

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屋！