MySQL SELECT 模式替换为另一个表中的结果

Question

如何用文本匹配替换 SELECT 中的值到另一个表中的行？

**products**
> +-------+--------------------------------------------------+  
> | id    | description                                      |  
> +-------+--------------------------------------------------+  
> | 10001 | This product is %block1% and %block4%            |  
> | 10002 | This product is %block2%, %block3%, and is %block4% |  
> +-------+--------------------------------------------------+  

**descriptions**
> +-----------+-------------------+  
> | blockname | blockcontent      |  
> +-----------+-------------------+  
> | %block1%  | 5 feet tall       |  
> | %block2%  | matte white       |  
> | %block3%  | makes music       |  
> | %block4%  | made of real wood |  
> +-----------+-------------------+  

理想情况下，我想运行一个返回的查询

> +-------+--------------------------------------------------+  
> | id    | newdescription                                      |  
> +-------+--------------------------------------------------+  
> | 10001 | This product is 5 feet tall and made of real wood            |  
> | 10002 | This product is matte white, makes music, and is made of real wood |  
> +-------+--------------------------------------------------+ 

我已经研究了 REPLACE() 和 SUBSTITUTE()，但它们似乎不是我正在寻找的。

Answer 1

您可以创建函数:

DROP FUNCTION my_subst;
DELIMITER $$

CREATE FUNCTION my_subst(str VARCHAR(255))
    RETURNS VARCHAR(255)
BEGIN
    DECLARE pos1 INT DEFAULT 0;
    DECLARE pos2 INT DEFAULT 0;
    DECLARE token VARCHAR(255);
    DECLARE new_token VARCHAR(255);

    label1: LOOP
        SET pos1 = LOCATE('%%',str);
        IF pos1 = 0 THEN
            LEAVE label1;
        END IF;

        SET pos2 = LOCATE('%%',str,pos1+1);
        IF pos2 = 0 THEN
            LEAVE label1;
        END IF;

        SET token = SUBSTR(str,pos1,pos2-pos1+2);

        SELECT blockcontent INTO new_token FROM descriptions WHERE blockname = token;

        SET str = REPLACE(str,token,new_token);

    END LOOP label1;
    RETURN str;
END;
$$
DELIMITER ;

然后使用这个函数进行替换。