首页 > 数据库 > mysql教程 > 我可以在没有外部工具的情况下在 MySQL 中按分号分隔值连接表吗?

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

DDD
发布: 2024-11-02 11:25:30
原创
489 人浏览过

Can I Join Tables on Semicolon-Separated Values in MySQL Without External Tools?

我可以用纯 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中文网其他相关文章!

来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板