mysql - 如何规范表列中的逗号分隔值，然后运行查询

Question

假设我有一个具有以下结构的表:

 | column1    |    column2       |
 |------------|------------------|
 | a          |    1,L,3,K,5,    |
 | b          |    R,6,7,8,9     |
 | c          |    8,9,10,D      |
 | d          |    1,2,3,H       |

假设第 1 列可以继续到 z，第 2 列可以继续使用随机数字和字母。我想要一个通用的解决方案，可以应用于任意数量的行和列，以及第 2 列中的值的数量。

我想在MySQL中运行一个查询，该查询将搜索column2中的所有值并输出column1中包含column2中的3的字母。输出应该是:

 | column1    |
 |------------|
 | a          | 
 | d          |  

许多帖子都有可以直接完成此操作的查询答案，但我想以正确的方式进行操作。我是 sql 新手，但我相信这意味着通过创建一个新表并在这个新表上运行查询来标准化列 2 中的数据。

有人可以帮助我编写代码来规范化并在 MySQL 中运行此查询吗？谢谢。

Answer 1

您需要使用FIND_IN_SET()

SELECT column1 FROM table WHERE FIND_IN_SET('3', column2);

Fiddle Demo

您应该永远将数据存储在表中作为逗号分隔的列表...所以如果我是您，我会考虑将其分成行

要规范化您的数据库，您可以执行这样的查询...注意您需要知道第 2 列中的 block 数...

CREATE TEMPORARY TABLE IF NOT EXISTS normalized_table AS 
(   SELECT
      column1,
      SUBSTRING_INDEX(SUBSTRING_INDEX(column2, ',', n.digit+1), ',', -1) column2
    FROM test
    JOIN(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
        ON LENGTH(REPLACE(column2, ',' , '')) <= LENGTH(column2)-n.digit
    ORDER BY column1, n.digit
);

DROP table test;
CREATE table test (column1 varchar(2), column2 varchar(2));
INSERT INTO test (column1, column2) 
SELECT column1, column2 FROM normalized_table;

NORMALIZED RESULTS

每个数字的 JOIN 是您的关键...如果您有 6 个项目，以逗号分隔，然后联合 0-5 个数字进行连接。

如果您不知道有多少个，那么只需运行此命令即可知道要合并多少个数字

SELECT MAX(LENGTH(REPLACE(column2, ',', ''))) FROM test;