我可以用純 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>
解釋:
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中文網其他相關文章!