声明和使用MySQL varchar变量

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

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

我在使用MySQL工作台时遇到了同样的问题。根据 MySQL documentation ,和 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;

你好像忘了变量声明中的@。我还记得有问题 SET

尝试

DECLARE @FOO varchar(7);
DECLARE @oldFOO varchar(7);
SELECT @FOO = '138';
SELECT @oldFOO = CONCAT('0', @FOO);

update mypermits 
   set person = @FOO 
 where person = @oldFOO;

试试这个:

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

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

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

如果使用phpmyadmin添加新的例程,那么不要忘记在开始和结束之间包装代码

Declare @variable type(size);

Set @variable = 'String' or Int ;

 Declare @id int;
 set @id = 10;

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