声明和使用MySQL varchar变量。

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.

我试着用0。5的变量来做一些简单的操作但是我不能让它工作。我已经看到了许多(非常!)不同的syntaxen用于声明/设置，我不知道为什么…在任何情况下，我可能会混淆它们/选错了/混合它们。

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.

我也试着开始……结束;作为一个过程。在这个例子中，MySQL工作台帮助我告诉我:“SQL语法错误接近”“在第一行”，“SQL语法错误”在第2条语句中“声明oldFOO varchar(7)”。否则，它将两行都作为错误，在这两方面都有“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?

我犯了什么愚蠢的错误?

7 个解决方案

#1

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()

#2

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工作台遇到了同样的问题。根据MySQL文档，声明“语句声明存储程序中的本地变量”。这显然意味着它只能保证与存储过程/函数一起工作。

The solution for me was to simply remove the DECLARE statement, and introduce the variable in the SET statement. For your code that would mean:

我的解决方案是简单地删除声明语句，并在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;

#3

Looks like you forgot the @ in variable declaration. Also I remember having problems with SET in MySql a long time ago.

看起来您忘记了变量声明中的@。我还记得很久以前在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;

#4

try this:

试试这个:

declare @foo    varchar(7),
        @oldFoo varchar(7)

set @foo = '138'
set @oldFoo = '0' + @foo

#5

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;

#6

If you are using phpmyadmin to add new routine then don't forget to wrap your code between BEGIN and END

如果您正在使用phpmyadmin添加新的例程，那么不要忘记在开始和结束之间结束代码。

#7

-1

Declare @variable type(size);

Set @variable = 'String' or Int ;

Example:

例子:

 Declare @id int;
 set @id = 10;

 Declare @str char(50);
 set @str='Hello' ;

#1