mysql - 我可以用纯 mysql 解决这个问题吗？ (加入列中的 ';' 分隔值)

Question

长话短说:我有几个表格中的数据需要一起抓取，为了不必绘制一个巨大的表格，我对它们进行了简化。

我需要在一个查询中这样做，但我不能使用 PHP 或任何其他语言来处理结果。 (如果我可以简单地解决这个问题，我会使用 PHP)

如果我有一个将 t1 行连接到 t2 的链接表，这就不是问题，但不幸的是我没有也不能引入一个。

User table: (alias t1)
user(varchar 150),resources(varchar 250)
+-------+-------+
| user1 | 1;2;4 |
+-------+-------+
| user2 | 2     |
+-------+-------+
| user3 | 3;4   |
+-------+-------+

Resources table: (alias t2)
id(int 11 AI), data(text)
+---+-------+
| 1 | data1 |
+---+-------+
| 2 | data2 |
+---+-------+
| 3 | data3 |
+---+-------+
| 4 | data4 |
+---+-------+
| 5 | data5 |
+---+-------+

多个用户可以连接到同一个资源，一个用户可以访问一个或多个资源。

我想要一个接近于:

user,data
+-------+-------+
| user1 | data1 |
+-------+-------+
| user1 | data2 |
+-------+-------+
| user1 | data4 |
+-------+-------+
| user2 | data2 |
+-------+-------+

....等等等等

我有基本的 mysql 知识，但这超出了我的知识范围。有什么办法可以内部加入 t2 吗？

在写这篇文章之前我读过的主题: How to join two tables using a comma-separated-list in the join field

mysql join two table with comma separated ids

Answer 1

如果 user_resources (t1) 是一个“规范化表”，每个 user => resource 组合都有一行，那么获得答案的查询将非常简单就像将表加入一样。

唉，通过将 resources 列作为:“list of resource id”用“;”分隔，它被非规范化性格。

如果我们可以将“资源”列转换为行，那么随着表连接变得简单，很多困难都会消失。

生成请求输出的查询:

SELECT user_resource.user, 
       resource.data

FROM user_resource 
     JOIN integerseries AS isequence 
       ON isequence.id <= COUNT_IN_SET(user_resource.resources, ';') /* normalize */

     JOIN resource 
       ON resource.id = VALUE_IN_SET(user_resource.resources, ';', isequence.id)      
ORDER BY
       user_resource.user,  resource.data

输出:

user        data    
----------  --------
sampleuser  abcde   
sampleuser  azerty  
sampleuser  qwerty  
stacky      qwerty  
testuser    abcde   
testuser    azerty  

如何:

“诀窍”是有一个包含从 1 到某个限制的数字的表格。我称之为整数。它可用于转换“水平”的东西，例如:';'分隔字符串到行。

它的工作方式是，当您使用 integerseries“连接”时，您正在执行一个交叉连接，这是“内部连接”“自然”发生的情况.

每一行都使用来自 integerseries 表的不同“序列号”进行复制，我们将其用作我们要用于该 的列表中“资源”的“索引” >行。

想法是:

• 计算列表中的项目数。
• 根据列表中的位置提取每个项目。
• 使用 integerseries 将一行转换为一组行，从 user 中提取单个“资源 id”。resources 在我们进行的过程中.

我决定使用两个函数:

• 给定“分隔字符串列表”和“索引”的函数将返回列表中位置的值。我称之为:VALUE_IN_SET。即给定“A；B；C”和 2 的“索引”，然后它返回“B”。

• 给定“分隔字符串列表”的函数将返回列表中项目的计数。我称之为:COUNT_IN_SET。即给定 'A;B;C' 将返回 3

事实证明，这两个函数和 integerseries 应该为列中的分隔项列表 提供通用解决方案。

有用吗？

从 ';' 创建“规范化”表的查询列中的分隔字符串。它显示了所有列，包括由于“cross_join”而生成的值(isequence.id as resources_index):

SELECT user_resource.user, 
       user_resource.resources,
       COUNT_IN_SET(user_resource.resources, ';')                AS resources_count, 
       isequence.id                                              AS resources_index,
       VALUE_IN_SET(user_resource.resources, ';', isequence.id)  AS resources_value
FROM 
     user_resource 
     JOIN  integerseries AS isequence 
       ON  isequence.id <= COUNT_IN_SET(user_resource.resources, ';')
ORDER BY
       user_resource.user, isequence.id

“规范化”表输出:

user        resources  resources_count  resources_index  resources_value  
----------  ---------  ---------------  ---------------  -----------------
sampleuser  1;2;3                    3                1  1                
sampleuser  1;2;3                    3                2  2                
sampleuser  1;2;3                    3                3  3                
stacky      2                        1                1  2                
testuser    1;3                      2                1  1                
testuser    1;3                      2                2  3                

使用上面的“规范化”user_resources 表，这是一个提供所需输出的简单连接:

所需的功能(这些是可以在任何地方使用的通用功能)

注意:这些函数的名称与mysql相关FIND_IN_SET function .即他们在字符串列表方面做类似的事情？

COUNT_IN_SET 函数:返回列中字符分隔项 的计数。

DELIMITER $$

DROP FUNCTION IF EXISTS `COUNT_IN_SET`$$

CREATE FUNCTION `COUNT_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1)
                               ) RETURNS INTEGER
BEGIN
      RETURN CHAR_LENGTH(haystack) - CHAR_LENGTH( REPLACE(haystack, delim, '')) + 1;
END$$

DELIMITER ;

VALUE_IN_SET 函数:将delimited list 视为one based array 并返回值给定“索引”。

DELIMITER $$

DROP FUNCTION IF EXISTS `VALUE_IN_SET`$$

CREATE FUNCTION `VALUE_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1), 
                               which INTEGER
                               ) RETURNS VARCHAR(255) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
      RETURN  SUBSTRING_INDEX(SUBSTRING_INDEX(haystack, delim, which),
                     delim,
                     -1);
END$$

DELIMITER ;

相关信息:

• 终于弄清楚了如何获得 SQLFiddle - working code编译函数。

• 有一个适用于SQLite 数据库的版本SQLite- Normalizing a concatenated field and joining with it?

表格(含数据):

CREATE TABLE `integerseries` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `integerseries` */

insert  into `integerseries`(`id`) values (1);
insert  into `integerseries`(`id`) values (2);
insert  into `integerseries`(`id`) values (3);
insert  into `integerseries`(`id`) values (4);
insert  into `integerseries`(`id`) values (5);
insert  into `integerseries`(`id`) values (6);
insert  into `integerseries`(`id`) values (7);
insert  into `integerseries`(`id`) values (8);
insert  into `integerseries`(`id`) values (9);
insert  into `integerseries`(`id`) values (10);

资源:

CREATE TABLE `resource` (
  `id` int(11) NOT NULL,
  `data` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `resource` */

insert  into `resource`(`id`,`data`) values (1,'abcde');
insert  into `resource`(`id`,`data`) values (2,'qwerty');
insert  into `resource`(`id`,`data`) values (3,'azerty');

用户资源:

CREATE TABLE `user_resource` (
  `user` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `resources` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `user_resource` */

insert  into `user_resource`(`user`,`resources`) values ('sampleuser','1;2;3');
insert  into `user_resource`(`user`,`resources`) values ('stacky','3');
insert  into `user_resource`(`user`,`resources`) values ('testuser','1;3');