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?

Which MySQL version is this? It might be helpful to tag the question with the version. — dbdemon
– dbdemon, Commented Apr 24, 2018 at 12:04

klor · Accepted Answer · 2018-04-25 09:30:10Z

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.

Thanks for the great solution! I will test it later. Does it supposed to work on unlimited depth paths recursively? — klor
– klor, Commented Apr 24, 2018 at 18:02
@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. — dbdemon
– dbdemon, Commented Apr 24, 2018 at 18:30
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. — klor
– klor, Commented Apr 24, 2018 at 19:30
@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. — dbdemon
– dbdemon, Commented Apr 24, 2018 at 20:26

Stack Exchange Network

MYSQL: Stored function to replace category ids with names

Example

Using query:

Problem

1 Answer 1

Your Answer

Hot Network Questions

MYSQL: Stored function to replace category ids with names

Example

Using query:

Problem

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions