我可以在沒有外部工具的情況下在 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進行匹配，檢索對應的data.

<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>

附加函數（可選）：

查詢使用兩個自訂函數，COUNT_IN_SET 和VALUE_IN_SET，可以定義如下：

這些函數提供了在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中文網其他相關文章！