MYSQL：用名稱替換類別ID的儲存函式

path我有一個儲存函式，它應該用列中的名稱替換列中的類別 ID name。然後將結果字元串儲存到一個名為path_long.

我使用 Debian 8、MySQL v5.5。

例子

我有一個名為path“/426/427/428”之類的內容的列。我想用類別名稱替換類別 ID 編號。結果將類似於“/電腦/其他附件/雷射列印機”。

我有這個儲存的功能：

CREATE DEFINER=`root`@`%` FUNCTION `decode_path`(
   `path_input` MEDIUMTEXT
)
RETURNS mediumtext CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''

BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS 
t1
ENGINE=MyISAM
AS (
   SELECT
        n AS nr
        , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM @path_input)), '/', tmp.n), '/', -1) AS catid
        , (
               SELECT name FROM category
               WHERE category.id = catid
           ) AS name
        , (
               SELECT path FROM category
               WHERE category.id = catid
           ) AS path
   FROM
     (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
           FROM category
           CROSS JOIN (SELECT @rownum := 0) r
       ) AS tmp 
   GROUP BY catid
   ORDER BY
      n
);

INSERT INTO t2
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;

RETURN (SELECT path_long FROM t2 limit 1);

END

這是測試 DDL：

CREATE TABLE `category` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `path` VARCHAR(100) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=429
;

還有測試數據：

INSERT INTO `category` (`id`, `name`, `path`) VALUES (1, 'A', '/1');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (2, 'B', '/1/2');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (3, 'C', '/1/2/3');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (4, 'D', '/4');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (5, 'E', '/4/5');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (6, 'F', '/4/5/6');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (7, 'G', '/7');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (8, 'H', '/7/8');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (9, 'I', '/7/8/9');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (426, 'Computers', '/426');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (427, 'Other accessories', '/426/427');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (428, 'Laser printers', '/426/427/428');

不幸的是我不能改變設計。它在軟體中給出。可怕的模式與否，可怕的數據庫與否，這就是我所擁有的。框架使用這個模式，數據庫是 MySQL。我必須在這個系統上進行查詢，我必須得到想要的結果。

使用查詢：

SELECT decode_path(category.path) as decoded FROM category

問題

查詢結果如下：

decoded 
A
A
A
A
A
A
A
A
A
A
A
A

源列如下所示（顯示未解碼的路徑）：

path
/426/427/428
/1/2/3
/4/5/6
/7/8/9

所需的結果列應該是這樣的（顯示解碼路徑）：

path_long
/Computers/Other accessories/Laser printers
/A/B/C
/D/E/F
/G/H/I

基本上**，它應該使用類別名稱將具有類別 ID 的路徑解碼為可讀的路徑格式。**

如何修復儲存的功能以使其工作？

這行得通嗎？我在 MariaDB 上匆忙寫了這篇文章，所以可能不是 100% 正確，但希望它也可以在 MySQL 5.5 上執行，或者至少只需要稍作調整。它似乎適用於您的測試數據。

DELIMITER //
CREATE FUNCTION decode_path (
   `path_input` MEDIUMTEXT
)
RETURNS mediumtext CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
 BEGIN
   DECLARE cnt INT UNSIGNED DEFAULT 2;
   DECLARE cat_id INT UNSIGNED;
   DECLARE cat_name VARCHAR(200) DEFAULT '';
   DECLARE decoded_path MEDIUMTEXT;
   SET decoded_path = '';

   WHILE cnt < LENGTH(path_input) - LENGTH(REPLACE(path_input, '/', '')) + 2 DO
     SET cat_id = SUBSTRING_INDEX(SUBSTRING_INDEX(path_input,'/',cnt), '/', -1);
     SELECT `name` INTO cat_name FROM category WHERE id = cat_id;
     SET decoded_path := CONCAT(decoded_path, '/', cat_name);
     SET cnt := cnt + 1;
   END WHILE;
   RETURN decoded_path;
 END
//
DELIMITER ;

使用它，我得到：

SELECT path, `name`, decode_path(path) FROM category;
+--------------+-------------------+---------------------------------------------+
| path         | name              | decode_path(path)                           |
+--------------+-------------------+---------------------------------------------+
| /1           | A                 | /A                                          |
| /1/2         | B                 | /A/B                                        |
| /1/2/3       | C                 | /A/B/C                                      |
| /4           | D                 | /D                                          |
| /4/5         | E                 | /D/E                                        |
| /4/5/6       | F                 | /D/E/F                                      |
| /7           | G                 | /G                                          |
| /7/8         | H                 | /G/H                                        |
| /7/8/9       | I                 | /G/H/I                                      |
| /426         | Computers         | /Computers                                  |
| /426/427     | Other accessories | /Computers/Other accessories                |
| /426/427/428 | Laser printers    | /Computers/Other accessories/Laser printers |
+--------------+-------------------+---------------------------------------------+

因此，要將函式的結果字元串儲存到名為 path_long 的新列中：

UPDATE category SET path_long = decode_path(path); 

在db-fiddle.com上使用 MySQL 5.5 進行了測試。

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