blocks|key|1218414|text|Per+the+MySQL+docs+DECLARE只在存储程序中的BEGIN...END块的开头起作用。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1218415|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/declare.html^0|4|E|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Per <a href="http://dev.mysql.com/doc/refman/5.0/en/declare.html" rel="noreferrer">the MySQL docs</a> DECLARE works only at the start of a BEGIN...END block as in a stored program.

blocks|key|1218507|text|您不需要在MySQL中声明变量。变量的类型是在第一次为其赋值时自动确定的。其类型可以是以下类型之一:整数、十进制、浮点、二进制或非二进制字符串或空值。有关详细信息，请参阅用户定义变量文档：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1218508|http://dev.mysql.com/doc/refman/5.0/en/user-variables.html|offset|length|1218509|您可以使用SELECT+...要将列分配给变量，请执行以下操作：|1218510|http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html|1218511|示例：|1218512|mysql>+SELECT+1+INTO+@var;
Query+OK,+1+row+affected+(0.00+sec)

mysql>+SELECT+@var;
%2B------%2B
%7C+@var+%7C
%2B------%2B
%7C+1++++%7C
%2B------%2B
1+row+in+set+(0.00+sec)|code-block|syntax|javascript|1218513|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|1M|0|0|0|0|1T|1|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@$D|10|E|11|1|12]]|A|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@$D|15|E|16|1|17]]|A|$]]|$1|J|3|K|5|6|7|18|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|19|8|@]|9|@]|A|$O|P]]|$1|Q|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|C]]|X|$5|T|U|V|A|$W|I]]]]

You don't need to DECLARE a variable in MySQL. A variable's type is determined automatically when it is first assigned a value. Its type can be one of: integer, decimal, floating-point, binary or nonbinary string, or NULL value. See the User-Defined Variables documentation for more information:

<a href="http://dev.mysql.com/doc/refman/5.0/en/user-variables.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/user-variables.html</a>

You can use SELECT ... INTO to assign columns to a variable:

<a href="http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html</a>

Example:

<pre><code>mysql&gt; SELECT 1 INTO @var;
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT @var;
+------+
| @var |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
</code></pre>

blocks|key|1004160|text|最后，存储过程是我的问题的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1004161|以下是有用的信息：|1004162|DELIMITER+//

CREATE+PROCEDURE+test+()
BEGIN
++DECLARE+myvar+DOUBLE;
++SELECT+somevalue+INTO+myvar+FROM+mytable+WHERE+uid+=+1;

++SELECT+myvar;
END//

DELIMITER+;

call+test();|code-block|syntax|javascript|1004163|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

In the end a stored procedure was the solution for my problem.
Here´s what helped:
<pre><code>DELIMITER //

CREATE PROCEDURE test ()
BEGIN
 DECLARE myvar DOUBLE;
 SELECT somevalue INTO myvar FROM mytable WHERE uid = 1;

 SELECT myvar;
END//

DELIMITER ;

call test();
</code></pre>

blocks|key|1218614|text|我遇到了同样的问题，但我想我知道是什么导致了混乱。如果您使用MySQL查询分析器，则可以很好地完成以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1218615|SELECT+myvalue+
INTO+@myvar+
FROM+mytable+
WHERE+anothervalue+=+1;|code-block|syntax|javascript|1218616|但是，如果您将相同查询放在MySQL工作台中，它将抛出语法错误。我不知道它们为什么会不同，但它们确实不同。|1218617|要在MySQL工作台中解决此问题，您可以像这样重写查询：|1218618|SELECT+@myvar:=myvalue
FROM+mytable
WHERE+anothervalue+=+1;|1218619|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I ran into this same issue, but I think I know what's causing the confusion. If you use MySQL Query Analyzer, you can do this just fine:
<pre><code>SELECT myvalue 
INTO @myvar 
FROM mytable 
WHERE anothervalue = 1;
</code></pre>
However, if you put that same query in MySQL Workbench, it will throw a syntax error. I don't know why they would be different, but they are.
To work around the problem in MySQL Workbench, you can rewrite the query like this:
<pre><code>SELECT @myvar:=myvalue
FROM mytable
WHERE anothervalue = 1;
</code></pre>

blocks|key|1218659|text|我在Windows+Vista上使用版本6(Windows6.0.9版本11421+Build1170的MySQL工作台社区(GPL)+)。我对以下选项没有问题。可能是他们修好了，因为这些家伙三年前就有问题了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1218660|/*+first+option+*/
SELECT+ID+
INTO+@myvar+
FROM+party+
WHERE+Type+=+'individual';

--+get+the+result
select+@myvar;

/*+second+option+*/
SELECT+@myvar:=ID
FROM+party
WHERE+Type+=+'individual';


/*+third+option.+The+same+as+SQL+Server+does+*/
SELECT+@myvar+=+ID+FROM+party+WHERE+Type+=+'individual';|code-block|syntax|javascript|1218661|上面的所有选项都给了我一个正确的结果。|1218662|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I am using version 6 (MySQL Workbench Community (GPL) for Windows version 6.0.9 revision 11421 build 1170) on Windows Vista. I have no problem with the following options. Probably they fixed it since these guys got the problems three years ago. 

<pre><code>/* first option */
SELECT ID 
INTO @myvar 
FROM party 
WHERE Type = 'individual';

-- get the result
select @myvar;

/* second option */
SELECT @myvar:=ID
FROM party
WHERE Type = 'individual';


/* third option. The same as SQL Server does */
SELECT @myvar = ID FROM party WHERE Type = 'individual';
</code></pre>

All option above give me a correct result.

blocks|key|1218713|text|这些答案不能很好地涵盖多变量。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1218714|在存储过程中执行内联赋值会导致这些结果也在结果集中传回。这可能会让人感到困惑。要对多个变量使用SELECT...INTO语法，需要执行以下操作：|1218715|SELECT+a,+b+INTO+@a,+@b+FROM+mytable+LIMIT+1;|code-block|syntax|javascript|1218716|SELECT必须只返回1行，因此限制为1，尽管这并不总是必要的。|1218717|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

These answers don't cover very well MULTIPLE variables. 

Doing the inline assignment in a stored procedure causes those results to ALSO be sent back in the resultset. That can be confusing. To using the SELECT...INTO syntax with multiple variables you do:

<pre><code>SELECT a, b INTO @a, @b FROM mytable LIMIT 1;
</code></pre>

The SELECT must return only 1 row, hence LIMIT 1, although that isn't always necessary.

blocks|key|1218753|text|您也可以使用SET代替DECLARE|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1218754|SET+@myvar+:=+(SELECT+somevalue+INTO+myvar+FROM+mytable+WHERE+uid=1);

SELECT+myvar;|code-block|syntax|javascript|1218755|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can also use SET instead of DECLARE

<pre><code>SET @myvar := (SELECT somevalue INTO myvar FROM mytable WHERE uid=1);

SELECT myvar;
</code></pre>

blocks|key|1004382|text|你可能错过了值前面的@符号，就像那个select+'test'+INTO+@myValue；|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1004383|entityMap^0|I|R|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

You maybe miss the @ symbol before your value,like that <code>select 'test' INTO @myValue</code>;

blocks|key|1218826|text|对于那些现在遇到这样的问题的人，只需尝试为表添加一个别名，这应该是个好办法，例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1218827|SELECT+myvalue+
++INTO+myvar+
++FROM+mytable+x
+WHERE+x.anothervalue+=+1;|code-block|syntax|javascript|1218828|这对我很管用。|1218829|干杯。|1218830|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For those running in such issues right now, just try to put an alias for the table, this should the trick, e.g:

<pre><code>SELECT myvalue 
 INTO myvar 
 FROM mytable x
 WHERE x.anothervalue = 1;
</code></pre>

It worked for me.

Cheers.

blocks|key|1004467|text|选择c1、c2、c3、...变成@v1，@v2，@v3，...来自table_name的WHERE条件；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1004468|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

SELECT 
 c1, c2, c3, ...
INTO 
 @v1, @v2, @v3,...
FROM 
 table_name
WHERE 
 condition;

I´d like to SELECT a single value into a variable. I´d tried to following:

<pre><code>DECLARE myvar INT(4);
</code></pre>

-- immediately returns some syntax error.

<pre><code>SELECT myvalue 
 FROM mytable 
 WHERE anothervalue = 1;
</code></pre>

-- returns a single integer

<pre><code>SELECT myvalue 
 INTO myvar 
 FROM mytable 
 WHERE anothervalue = 1;
</code></pre>

-- does not work, also tried @myvar

Is possible to use DECLARE outside of stored procedures or functions?

Maybe I just dont get the concept of user variables... I just tried:

<pre><code>SELECT myvalue INTO @var FROM `mytable` WHERE uid = 1;
SELECT @var;
</code></pre>

...which worked just like it´s supposed to. But if I run each query at a time i just get @var NULL.

SELECT INTO Variable in MySQL DECLARE causes syntax error?

SQL Server

Windows

MySQL

我想在一个变量中选择一个值。我试着这样做：DECLARE myvar INT(4);--立即返回一些语法错误。SELECT myvalue   FROM mytable  WHERE anothervalue = 1;--返回单个整数SELECT myvalue   INTO myvar   FROM mytable ...

问SELECT INTO Variable in MySQL DECLARE导致语法错误？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SELECT INTO Variable in MySQL DECLARE导致语法错误？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SELECT INTO Variable in MySQL DECLARE导致语法错误？
EN