mysql在select语句中为多行分配变量

Question

我有这个sql查询

SELECT DISTINCT category.title, category.id FROM category 
INNER JOIN request ON category.id = request.category_id;

+---------------+----+
| title         | id |
+---------------+----+
| Clothing      |  4 |
| Dance         | 12 |
| Entertainment | 37 |
+---------------+----+

我如何获取此查询的所有结果行作为另一个表中的文本，例如:

update message set description = "category : <title>, id: <id>,";

select description from message;

+-------------------------------------------------------------------------------------+
| description        
+-------------------------------------------------------------------------------------+
| category : Clothing, id:  4, category : Dance id: 12 category : Entertainment id: 37|
+-------------------------------------------------------------------------------------+

我想使用存储过程来执行此操作，但我是这方面的新手。感谢您抽出时间。

Answer 1

SELECT DISTINCT "Category: " + category.title + ", id: " + category.id + "," AS Description FROM category INNER JOIN request ON category.id = request.category_id;

作为存储过程(使用 https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html 和 http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx ):

DELIMITER //
CREATE PROCEDURE GetDescription
AS
BEGIN
    SELECT DISTINCT "Category: " + category.title + ", id: " + category.id + "," AS Description FROM category INNER JOIN request ON category.id = request.category_id;
END;
DELIMITER ;