我可以在没有外部工具的情况下在 MySQL 中按分号分隔值连接表吗？

我可以用纯 MySQL 解决这个问题吗？ （加入；列中的分隔值）

问题：

您有数据存储在两个表中，第一个表中的一列列出了多个值以分号分隔。您需要根据这些分隔的值执行内部联接，但您没有链接表，也无法使用任何外部编程语言。

讨论：

挑战在于将分号分隔的列表转换为单独的行，从而可以与第二个表连接。这可以使用一种称为“导出”或“规范化”数据的技术来实现。

解决方案：

1。创建整数系列表：

要规范化数据，您首先需要一个包含一系列数字的表。在这种情况下，您可以创建一个整数系列表，其 ID 从 1 到分号分隔列表中预期的最大元素数。

2。使用 JOIN 和子查询：

一旦有了整数系列表，请执行以下查询：

<code class="sql">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</code>

解释：

• 此查询将 user_resource 表与 integerseries 表连接起来，为每个用户和相应资源列中的每个元素生成一组行。
• COUNT_IN_SET 函数计算资源列中的元素数量，并且VALUE_IN_SET 函数根据 isequence.id 提取特定元素。
• 最终与资源表的 join 将提取的元素与 ID 进行匹配，以检索相应的数据。

附加函数（可选）：

查询使用两个自定义函数，COUNT_IN_SET 和 VALUE_IN_SET，可以定义如下：

<code class="sql">-- Function to count the number of delimited items in a string
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 ;

-- Function to get the value at a specific index in a delimited string
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 ;</code>

这些函数提供了通用的方式在 SQL 查询中操作分隔字符串。

示例表和数据：

<code class="sql">-- Integerseries table
CREATE TABLE `integerseries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);
-- Resource table
CREATE TABLE `resource` (
  `id` int(11) NOT NULL,
  `data` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Data for resource table
INSERT INTO `resource` (`id`, `data`) VALUES
(1, 'abcde'),
(2, 'qwerty'),
(3, 'azerty');

-- User_resource table
CREATE TABLE `user_resource` (
  `user` varchar(50) NOT NULL,
  `resources` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`user`)
);

-- Data for user_resource table
INSERT INTO `user_resource` (`user`, `resources`) VALUES
('sampleuser', '1;2;3'),
('stacky', '2'),
('testuser', '1;3');</code>

输出：

执行对样本数据的查询将产生以下输出：

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

以上是我可以在没有外部工具的情况下在 MySQL 中按分号分隔值连接表吗？的详细内容。更多信息请关注PHP中文网其他相关文章！