MYSQL: Stored function to replace category ids with names

Question

I have a stored function which should replace category ids in column path with names from name column. Then store result string into a new column named path_long.

I use Debian 8, MySQL v5.5.

Example

I have a column named path with content like '/426/427/428'. I would like to replace category id numbers with category names. Result would be like '/Computers/Other accessories/Laser printers'.

I have this stored function:

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

Here is the test 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
;

Also test data:

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');

Unfortunately I can't change the design. It is given in the software. Horrible schema or not, horrible database or not, this is what I have. The framework uses this schema, and the database is MySQL. I have to do the query on this system and I have to get the desired result.

Using query:

SELECT decode_path(category.path) as decoded FROM category

Problem

The query results the following:

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

Source column looks like this (showing undecoded paths):

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

Desired result column should be like this (showing decoded paths):

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

Basically it should decode a path having category ids to readable path format using category names.

How to fix the stored function to make it work?

• dbdemon: Which MySQL version is this? It might be helpful to tag the question with the version.
• klor: Using MySQL v5.5

Answer 1

Does this work? I wrote this up in a hurry on MariaDB so may not be 100% correct, but hoping it should work on MySQL 5.5 as well, or at least only require minor tweaks. It seems to work on your test data.

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 ;

Using this, I get:

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

So to store the result string from the function into a new column named path_long:

UPDATE category SET path_long = decode_path(path); 

Tested with MySQL 5.5 on db-fiddle.com here.

• klor: Thanks for the great solution! I will test it later. Does it supposed to work on unlimited depth paths recursively?
• dbdemon: @klor It's not a recursive function (I don't think MySQL allows recursive functions), but it should work on "unlimited" paths, although it's restricted by the length of the decoded_path variable and it will also be slow on long paths since it does a select for every category id in the path.
• klor: I meant "recursive" by working on "unlimited" path depth. My original query stores the resolved path elements into a temporary table, then merges the elements. This way should work on "unlimited" depth. The problem I had was that I was not able to loop the function for each row. I would be glad, if your solution would work.
• dbdemon: @klor Ok, I see. I've now tested it MySQL 5.5 on db-fiddle.com and updated the answer with a link to the test.
• klor: Excellent! Works exactly as I wanted!