mysql 生明变量,声明和使用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.

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.

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