Declaring and using MySQL varchar variables

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.

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 w开发者_如何学Gorapping 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.

Edit: I forgot to mention that I've tried it with and without @s on the variables. Some resources had it with, others without.

What dumb mistake am I making?

This works fine for me using 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()

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.

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:

-- 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;

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

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;

try this:

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

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

In Mysql, We can declare and use variables with set command like below

mysql> set @foo="manjeet";
mysql> select * from table where name = @foo;

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

Declare @variable type(size);

Set @variable = 'String' or Int ;

Example:

 Declare @id int;
 set @id = 10;

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