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