如何在MySQL字段中选择逗号分隔值的唯一值？

Something like this:

像这样的东西:

Select UNIQUE_VALUES('1,2,3,5,3,4,2,1,2,3,1,4') as CSVUnique

The result of this will be:

结果将是:

CSVUnique
1,2,3,5,4

Thanks!

2 个解决方案

#1

No way with mysql for getting unique like this way.

没有办法使用mysql来获得这种方式的独特性。

First it's bad that you storing values like this way. You should use parent child tables for storing such data instead of storing com,a separated values. Or should filter such duplicates before inserting.

首先,以这种方式存储值是很糟糕的。您应该使用父子表来存储此类数据,而不是存储com,这是一个单独的值。或者应该在插入之前过滤此类重复项。

Normalize your database.

规范化您的数据库。

get values from table and use php explode() , and use array_unique to remove duplicate values.

从表中获取值并使用php explode(),并使用array_unique删除重复值。

#2

Here is one way

这是一种方式

CREATE TABLE t
( vals VARCHAR (100));
INSERT INTO t VALUES('1,2,3,5,3,4,2,1,2,3,1,4');


SELECT
  GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1)) val
FROM
  t
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit;

See it working

看它工作

#1