声明和使用MySQL varchar变量 [英] Declaring and using MySQL varchar variables

问题描述

我正在尝试对MySQL 5.0中的变量进行一些简单的操作，但是我无法完全正常工作.我已经看到了DECLARE/SET的许多(非常！)不同的语法，我不确定为什么……在任何情况下，我都可能会混淆它们/选择错误的语法/混合它们.

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;

我也尝试过用BEGIN ... END包装它；并作为一个程序.在这种情况下，MySQL Workbench会帮助我:第一行显示')'附近的SQL语法错误"，第二行显示'DECLARE oldFOO varchar(7)'附近的SQL语法错误".否则，它会将这两行全部作为错误显示，并在两者上均显示"SQL语法错误...".

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.

我忘了提到我在变量上使用@s时都尝试过的情况.有的资源有，有的没有.

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?

推荐答案

这对于使用MySQL 5.1.35的我来说很好用:

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 ;

表格:

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 varchar变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！