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

时间:2022-07-02 16:39:15

Can I SELECT multiple columns into multiple variables within the same select query in MySQL?

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

For example:

例如:

DECLARE iId INT(20);
DECLARE dCreate DATETIME;

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

What is the correct syntax for this?

正确的语法是什么?

3 个解决方案

#1


168  

Your syntax isn't quite right: you need to list the fields in order before the INTO, and the corresponding target variables after:

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

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

#2


8  

==========Advise==========

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

@martin clayton Answer is correct, But this is an advise only.

@martin clayton回答是正确的,但这只是一个建议。

Please avoid the use of ambiguous variable in the stored procedure.

请避免在存储过程中使用模糊变量。

Example :

例子:

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

The above example will cause an error (null value error)

上面的示例将导致一个错误(null值错误)

Example give below is correct. I hope this make sense.

下面给出的例子是正确的。我希望这说得通。

Example :

例子:

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

You can also make them unambiguous by referencing the table, like:

您还可以通过引用该表使它们变得明确,例如:

[ Credit : maganap ]

[信贷:maganap]

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

#3


1  

Alternatively to Martin's answer, you could also add the INTO part at the end of the query to make the query more readable:

对于Martin的回答,您也可以在查询末尾添加部分,以使查询更加可读:

SELECT Id, dateCreated FROM products INTO iId, dCreate

#1


168  

Your syntax isn't quite right: you need to list the fields in order before the INTO, and the corresponding target variables after:

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

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

#2


8  

==========Advise==========

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

@martin clayton Answer is correct, But this is an advise only.

@martin clayton回答是正确的,但这只是一个建议。

Please avoid the use of ambiguous variable in the stored procedure.

请避免在存储过程中使用模糊变量。

Example :

例子:

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

The above example will cause an error (null value error)

上面的示例将导致一个错误(null值错误)

Example give below is correct. I hope this make sense.

下面给出的例子是正确的。我希望这说得通。

Example :

例子:

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

You can also make them unambiguous by referencing the table, like:

您还可以通过引用该表使它们变得明确,例如:

[ Credit : maganap ]

[信贷:maganap]

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

#3


1  

Alternatively to Martin's answer, you could also add the INTO part at the end of the query to make the query more readable:

对于Martin的回答,您也可以在查询末尾添加部分,以使查询更加可读:

SELECT Id, dateCreated FROM products INTO iId, dCreate