將帶有 #-delimiters 的 varchar 欄位轉換為多行

我正在使用一個數據庫，其中一些數據（表示下拉菜單的選定選項的整數值）僅在一個表列中保存為字元串（varchar）。在products_table（假設）中，有一product_id列映射到一selected-options列。在後者中，數據保存為#3#9#15#其中 3、9 和 15 是使用者已選擇的下拉菜單選項的 ID。還有另一個表（比方說option_table），其中 ID 為 1 到 15 的每個選項都被賦予了更多屬性（如 alabel和 a weight）。

出於統計原因，我想創建一個 MySQL 視圖，其中包含列selected option和number of products（已選擇此選項的產品數量）。要到達那裡，我需要來自 的單個值#3#9#15#，也就是說，在途中某處爆炸字元串。

現在我讀到不鼓勵這種形式的保存數據，這就是為什麼 MySQL 中沒有內置的字元串分解/拆分功能。但我無法更改數據格式，這是在一個巨大的軟體內部確定的。我沒有做出這個設計選擇，但我必須以某種方式使用它。

使用內置函式substring_index()是沒有選擇的，因為保存在此欄位中的選定選項的數量從 0 到 15 不等。此外，最大數量不是固定的，因為可能會不時創建新選項。

我嘗試編寫一個自己的過程，該過程將從中獲取一個數字#3#9#15#（例如，通過反復修剪 # 分隔符）並用它創建一個新行。SELECT該過程應該使用語句訪問該值，使用創建一個新視圖CREATE OR REPLACE VIEW，然後插入一個新行，其中僅包含第一個選項 (3​​) 選項，例如INSERT INTO viewname(product_id,option) VALUES(@productID, SUBSTR(@options,2,1));where 。@options == #3#9#15#我試著在一開始只為第一個選項編寫程序，但在這個階段它已經失敗了。

我的其他想法包括嘗試將字元串@options轉換為 SET 數據類型，以便我可以使用find_in_set()函式。我也閱讀了這個問題和這篇文章，但發現它們很難理解。

只有 MySQL 的基本知識，我有什麼方法可以創建這個視圖嗎？我將非常感謝任何提示我應該繼續研究的方向。

編輯：我有伺服器版本 10.3.28 - MariaDB 伺服器。您需要更多有關這方面的資訊嗎？

Edit2：由於 phpMyAdmin 中的（持續）錯誤，我無法使用 SQLWITH語句（請參見此處）。

Edit3 : 原來，軟體使用的分隔符實際上並不是一個#標誌，而只是由 phpMyAdmin 顯示。相反，它是 ASCII 控製字元0001或"^A".

強烈基於@matigo 的回答，我想出以下 SQL 呼叫將包含在 phpMyAdmin 的CREATE VIEW viewname AS .... 它從一個表列中獲取一個值（以及在另一個表中賦予該值的 ID），在指定分隔符的每個實例（此處為：）處拆分該值，CHAR(1)並將這些拆分值與前面提到的 ID 一起輸出。

有關設置的資訊：我們有兩個從中獲取資訊的表，一個稱為split_table_name此處（它包含split_column_name應在分隔符處拆分值的列）和另一個稱為id_table_name（包含產品的唯一 ID；包含此 ID insplit_table_name作為外鍵列稱為id_table_id)。

請注意，該軟體使用的分隔符實際上並不是一個#符號（正如我在我的問題中所假設的那樣），而是僅由 phpMyAdmin 顯示。它實際上是 ASCII 控製字元 0001。所以我需要將它轉換為一個字元，CHAR(1)這樣我才能在SUBSTRING_INDEX函式中使用它。

 SELECT CAST(
     COALESCE(
NULLIF(
SUBSTRING_INDEX ((SUBSTRING_INDEX (split_column_alias, CHAR(1), num.id)), CHAR(1), -1), ''),
         '0')  AS SIGNED)
    as one_split, content_alias.id_column_alias as `id_column_alias` FROM
(SELECT `split_table_alias`.`split_column_name` AS `split_column_alias`, `id_table_alias`.`id` AS `id_column_alias`
FROM `database_name`.`split_table_name` `split_table_alias`
JOIN `database_name`.`id_table_name` `id_table_alias`
ON `id_table_alias`.`id` = `split_table_alias`.`id_table_id`
       ) content_alias
INNER JOIN
(SELECT (h*10+t+1) as id FROM (
     SELECT 0 h  UNION
     SELECT 1    UNION
     SELECT 2    UNION
     SELECT 3    UNION
     SELECT 4    UNION
     SELECT 5    UNION
     SELECT 6    UNION
     SELECT 7    UNION
     SELECT 8    UNION
     SELECT 9) a,
   (
     SELECT 0 t  UNION
     SELECT 1    UNION
     SELECT 2    UNION
     SELECT 3    UNION
     SELECT 4    UNION
     SELECT 5    UNION
     SELECT 6    UNION
     SELECT 7    UNION
     SELECT 8    UNION
     SELECT 9) b ) num
WHERE num.id > 0
) zz
WHERE zz.one_split > 0
ORDER BY zz.one_split;

這是一種方法：

SELECT zz.`idx`
 FROM (SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX('#3#9#15#', '#', num.`id`), '#', -1) AS SIGNED) as `idx`
         FROM (SELECT (h*10+t+1) as `id`
                 FROM (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
                      (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b) num
                WHERE num.`id` > 0) zz
WHERE zz.`idx` > 0
ORDER BY zz.`idx`;

您最多可以將 100 個#分隔的 ID 傳遞到字元串中，並將您需要的內容拆分為UNSIGNED整數。

引用自：https://dba.stackexchange.com/questions/291361