主页 / dba / 问题 / 204776
Accepted
klor
Asked: 2018-04-25 01:59:00 +0800 CST

MYSQL：用名称替换类别 ID 的存储函数

  • 772

path我有一个存储函数，它应该用列中的名称替换列中的类别 ID name。然后将结果字符串存储到名为 的新列path_long中。

我使用 Debian 8，MySQL v5.5。

例子

我有一个名为path“/426/427/428”的列。我想用类别名称替换类别 ID 号。结果类似于“/Computers/Other accessories/Laser printers”。

我有这个存储功能：

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 的路径解码为可读路径格式。

如何修复存储的函数以使其工作？

mysql stored-procedures

1 个回答

  1. Best Answer

    这行得通吗？我在 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 进行测试。

    • 1

相关问题