MySQL: 在存储过程中将多个字段选择到多个变量中

我可以在 MySQL 中的同一个选择查询中将多个列 SELECT 为多个变量吗？

例如:

DECLARE iId INT(20);
DECLARE dCreate DATETIME;


SELECT Id INTO iId, dateCreated INTO dCreate
FROM products
WHERE pName=iName;

这个问题的正确语法是什么？

148730 次浏览
小开
最佳答案

您的 语法不完全正确: 您需要在 INTO 之前按顺序列出字段，并在 INTO 之后列出相应的目标变量:

SELECT Id, dateCreated
INTO iId, dCreate
FROM products
WHERE pName = iName
小开

或者，对于 Martin 的回答，您也可以在查询的末尾添加 INTO 部分，以使查询更具可读性:

SELECT Id, dateCreated FROM products INTO iId, dCreate
小开

= = = = = = = = = = = = 建议 = = = = = = = = = = = = = = = =

@ Martin Clayton 回答正确，但这只是建议。

请避免在存储过程中使用不明确的变量。

例如:

SELECT Id, dateCreated
INTO id, datecreated
FROM products
WHERE pName = iName

上面的示例将导致错误(空值错误)

下面的例子是正确的。我希望这有意义。

Example :

SELECT Id, dateCreated
INTO val_id, val_datecreated
FROM products
WHERE pName = iName

您也可以通过引用表来使它们不含糊，比如:

[来源: 马格纳普]

SELECT p.Id, p.dateCreated INTO id, datecreated FROM products p
WHERE pName = iName

提交答案