声明和使用 MySQL varchar 变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3981432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Declaring and using MySQL varchar variables
提问by Charles
I'm trying to do some simple manipulations with variables in MySQL 5.0 but I can't quite get it to work. I've seen many (very!) different syntaxen for DECLARE/SET, I'm not sure why... in any case I'm presumably confusing them/picking the wrong one/mixing them.
我正在尝试对 MySQL 5.0 中的变量进行一些简单的操作，但我无法让它正常工作。我见过很多（非常！）不同的 DECLARE/SET 语法，我不知道为什么......无论如何，我大概是在混淆它们/选择错误的/混合它们。
Here's a minimal fragment that fails:
这是一个失败的最小片段：
DECLARE FOO varchar(7);
DECLARE oldFOO varchar(7);
SET FOO = '138';
SET oldFOO = CONCAT('0', FOO);
update mypermits
set person = FOO
where person = oldFOO;
I've also tried wrapping it with BEGIN... END; and as a PROCEDURE. In this case MySQL Workbench helpfully tells me: "SQL syntax error near ')'" on the first line and "SQL syntax error near 'DECLARE oldFOO varchar(7)'" on the second. Otherwise it gives both lines as errors in full, with "SQL syntax error near ..." on both.
我也试过用 BEGIN... END; 包裹它。并作为一个程序。在这种情况下，MySQL Workbench 很有帮助地告诉我：第一行“SQL 语法错误')'附近”和第二行“'DECLARE oldFOO varchar(7)'附近的SQL 语法错误”。否则，它会将两行都显示为错误，并在两者上都显示“SQL 语法错误接近...”。
Edit: I forgot to mention that I've tried it with and without @s on the variables. Some resources had it with, others without.
编辑：我忘了提到我已经尝试过在变量上使用和不使用@s。有些资源有它，有些没有。
What dumb mistake am I making?
我犯了什么愚蠢的错误？
回答by OMG Ponies
This works fine for me using MySQL 5.1.35:
这对我使用 MySQL 5.1.35 很好：
DELIMITER $$
DROP PROCEDURE IF EXISTS `example`.`test` $$
CREATE PROCEDURE `example`.`test` ()
BEGIN
DECLARE FOO varchar(7);
DECLARE oldFOO varchar(7);
SET FOO = '138';
SET oldFOO = CONCAT('0', FOO);
update mypermits
set person = FOO
where person = oldFOO;
END $$
DELIMITER ;
Table:
桌子：
DROP TABLE IF EXISTS `example`.`mypermits`;
CREATE TABLE `example`.`mypermits` (
`person` varchar(7) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO mypermits VALUES ('0138');
CALL test()
回答by Amos Long
I ran into the same problem using MySQL Workbench. According to the MySQL documentation, the DECLARE"statement declares local variables within stored programs." That apparently means it is only guaranteed to work with stored procedures/functions.
我在使用 MySQL Workbench 时遇到了同样的问题。根据MySQL 文档，DECLARE“语句在存储的程序中声明局部变量”。这显然意味着它只能保证使用存储过程/函数。
The solution for me was to simply remove the DECLAREstatement, and introduce the variable in the SETstatement. For your code that would mean:
我的解决方案是简单地删除DECLARE语句，并在语句中引入变量SET。对于您的代码，这意味着：
-- DECLARE FOO varchar(7);
-- DECLARE oldFOO varchar(7);
-- the @ symbol is required
SET @FOO = '138';
SET @oldFOO = CONCAT('0', FOO);
UPDATE mypermits SET person = FOO WHERE person = oldFOO;
回答by Repky
If you are using phpmyadmin to add new routine then don't forget to wrap your code between BEGIN and END
如果您使用 phpmyadmin 添加新例程，请不要忘记在 BEGIN 和 END 之间包装您的代码
回答by minhas23
In Mysql, We can declare and use variables with set command like below
在 Mysql 中，我们可以使用 set 命令声明和使用变量，如下所示
mysql> set @foo="manjeet";
mysql> select * from table where name = @foo;
回答by rickythefox
Looks like you forgot the @ in variable declaration. Also I remember having problems with SETin MySql a long time ago.
看起来您忘记了变量声明中的 @。我还记得SET很久以前在 MySql中遇到问题。
Try
尝试
DECLARE @FOO varchar(7);
DECLARE @oldFOO varchar(7);
SELECT @FOO = '138';
SELECT @oldFOO = CONCAT('0', @FOO);
update mypermits
set person = @FOO
where person = @oldFOO;
回答by IWriteApps
try this:
尝试这个：
declare @foo varchar(7),
@oldFoo varchar(7)
set @foo = '138'
set @oldFoo = '0' + @foo
回答by ketan
Declare @variable type(size);
Set @variable = 'String' or Int ;
Example:
例子：
Declare @id int;
set @id = 10;
Declare @str char(50);
set @str='Hello' ;
