mysql 双重约束_MySQL Tips 2.0

之前写过一篇MySQL Tips，虽与本篇没有什么连续性，但都是MySQL相关的整理，不妨一看。

1.MySQL存储过程中的变量如何定义？

大体上定义变量的形式分为两种，后两者表现基本一致，差别细微：DECLARE、SET/SELECT

2.如何使用DECLARE定义变量及其约束？

使用DECLARE一次可以定义多个同类型的变量，各变量名称之间以逗号“,”隔开，

但是不能像SQLServer一样同时定义多个不同类型的变量如：DECLARE a INT,b CHAR(1)

定义规则如下：DECLARE 变量名[,...] 类型 DEFAULT [默认值]

注意：同时定义的多个变量只能有一个默认值，如：DECLARE a,b,c INT DEFAULT 0;

3.DECLARE 和 SET 定义变量的区别是？

类型声明：SET 不需要声明类型，DECLARE 必须指定类型

位置：SET 位置可以任意， DECLARE 必须在复合语句的开头，在任何其它语句之前

作用范围：DECLARE 只能在 BEGIN … END 块内使用；SET 定义的变量是用户变量，作用范围是会话/全局

此外，实际使用中还有以下需要注意的点：

DECLARE 定义变量时不能用“@”来修饰，而 SET 可以

DECLARE 定义的是内部变量，而SET定义的是会话变量(或用户变量，以下统称会话变量)，范围更广

SET 可用于对变量的赋值，如果加“@”引用，则是对会话变量赋值，如果不加则修改的是系统变量在当前会话中的值

如果在存储过程内，则会先找内部变量再找系统变量，都不存在则报错：ERROR 1193 (HY000): Unknown system variable 'x'

注意加“@”与不加“@”引用的是不同变量，加“@@”则只能引用全局变量，如：SET @@SESSION.profiling=1;

使用 SELECT 无论在存储过程内外都只能给会话变量赋值，且一定要使用“@param:=value”的形式，否则会被当成条件表达式

FETCH cursor INTO params[,...]; 遍历游标中的变量不能写“@”

EXECUTE sql USING @params[,@...]; 动态语句中的变量必须写“@”

关于系统变量赋值，使用：SET @@GLOBAL/SESSION.variable=value; 的形式，如果不指定范围，则默认是 SESSION

SESSION范围的系统变量绝大部分继承自 GLOBAL，但也有小部分仅作用于当前会话的变量，其中还有部分只读变量

4.关于变量设定的一个综合栗子

USE test;

DROP PROCEDURE IF EXISTS pro_test;

DELIMITER $$

CREATE PROCEDURE pro_test(a INT,IN b INT,OUT c INT,OUT d INT,OUT e INT,INOUT f INT)

BEGIN

DECLARE x,y INT DEFAULT 5;

SELECT a,@a,b,@b,c,@c,d,@d,e,@e,f,@f,@z,x,@x,y,@y;

SET a=10,@a=100,b=20,@b=200,c=30,@d=400,f=60,x=70;

SELECT @y:=70,@f:=600;

#SET z=90;

SET @z=900;

#SELECT z;

SELECT a,@a,b,@b,c,@c,d,@d,e,@e,f,@f,@z,x,@x,y,@y;

END$$

DELIMITER ;

SET @b:=2,@c=3,@f=6;

SELECT @d=4,@e:=5;

CALL pro_test(1,@b,@c,@d,@e,@f);

SELECT @a,@b,@c,@d,@e,@f; 以上SQL执行结果如下图所示：

结果有点混乱，做几点说明：

第10、12行被注释是因为变量z既不是系统变量，又不是存储过程内部变量，因此无法引用；

pro_test过程的变量a和b都是 IN 类型的(默认类型)，因此表现一致，只有加“@”赋值才能改变外部会话变量的值；

SELECT 定义时，@d=4被当成了条件式运算且变量d未申明，结果为NULL，而后者定义成功，返回值；

变量e在此的作用只是为了说明 SELECT 和 SET 设置变量的效果是一样的，用于对比变量c；

变量c、d、e都是OUT类型的变量，在过程内部开始一开始只能以会话变量的形式访问，但是赋值却是以内部变量的形式赋值(不加“@”)才会生效(改变外部变量的值)

变量f是 INOUT 双重变量，在过程内部一开始既是内部变量又是会话变量，但是最终改变其值的只能是会话变量，同上

5.DECLARE定义的顺序

DECLARE 开头的语句(包括定义游标或者变量)必须位于 BEGIN 语句之后(也就是存储过程的开头)

游标必须定义在变量之后，否则报错：ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration

错误处理器必须定义在游标之后，否则报错：ERROR 1338 (42000): Cursor declaration after handler declaration

预处理语句必须定义在 DECLARE语句之后，否则报语法错误：ERROR 1064 (42000): You have an error in your SQL syntax……

如果在循环中定义动态语句，则一定要在循环内将其资源释放，否则报错：ERROR 1243 (HY000): Unknown prepared statement handler (s1) given to DEALLOCATE PREPARE

CREATE PROCEDURE test.test()

BEGIN

loop_label: LOOP

IF TRUE THEN

LEAVE loop_label;

END IF;

PREPARE s1 FROM 'SELECT 1';

EXECUTE s1;

END LOOP;

DEALLOCATE PREPARE s1;

END$$

最好的做法是在 DECLARE 语句之后申明预定义语句，然后在存储过程的最后执行 DEALLOCATE。如：

CREATE PROCEDURE test.test()

BEGIN

PREPARE s1 FROM 'SELECT 1';

loop_label: LOOP

IF TRUE THEN

LEAVE loop_label;

END IF;

EXECUTE s1;

END LOOP;

DEALLOCATE PREPARE s1;

END$$

注意，声明各种变量的顺序。首先要进行变量声明，然后声明条件，随后声明游标，再后面才是声明错误处理器，如果没有按照顺序声明，系统会提示错误信息。

DECLARE语句必须用在DEGIN…END语句块中，并且必须出现在DEGIN…END语句块的最前面，即出现在其他语句之前

DECLARE定义的变量的作用范围仅限于DECLARE语句所在的DEGIN…END块内及嵌套在该块内的其他DEGIN…END块

存储过程中的变量名不区分大小写.

6.关于预处理语句的效率问题

CREATE PROCEDURE test.pro_test()

BEGIN

DECLARE i INT DEFAULT 0;

PREPARE s1 FROM 'SELECT ?';

loop_label: LOOP

IF i > 1000000 THEN

LEAVE loop_label;

END IF;

SET i=i+1;

EXECUTE s1 USING @i;

END LOOP;

DEALLOCATE PREPARE s1;

END$$

CREATE PROCEDURE test.pro_test2()

BEGIN

DECLARE i INT DEFAULT 0;

loop_label: LOOP

IF i > 1000000 THEN

LEAVE loop_label;

END IF;

SET i=i+1;

PREPARE s1 FROM 'SELECT ?';

EXECUTE s1 USING @i;

DEALLOCATE PREPARE s1;

END LOOP;

END$$

CREATE PROCEDURE test.pro_test3()

BEGIN

DECLARE i INT DEFAULT 0;

loop_label: LOOP

IF i > 1000000 THEN

LEAVE loop_label;

END IF;

SET i=i+1;

SELECT i;

END LOOP;

END$$

分别对比以上三个存储过程的执行时间：

time mysql -uroot -ptest -Ne 'CALL test.pro_test()'>/dev/null

# real 0m17.314s

# user 0m7.055s

# sys 0m1.305s

time mysql -uroot -ptest -Ne 'CALL test.pro_test2()'>/dev/null

# real 0m36.291s

# user 0m7.568s

# sys 0m1.419s

time mysql -uroot -ptest -Ne 'CALL test.pro_test3()'>/dev/null

# real 0m17.293s

# user 0m7.650s

# sys 0m0.795s

需要申明的是以上测试的SQL语句比较简单，并不能代表真实情况。

显然，MySQL动态语句的构建和清理需要耗费大量的时间和空间，

此外，预处理和硬编码的SQL执行差异不大，关键在于具体执行语句的分析和检索过程。

7.最后来一个完整的栗子

以下存储过程包含上文所述的变量、游标、循环、预处理等相关语句的使用：

CREATE PROCEDURE test.new_procedure(db VARCHAR(100))

BEGIN

DECLARE tb,col VARCHAR(100);

DECLARE done TINYINT DEFAULT FALSE;

DECLARE cur CURSOR FOR SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=IF(DATABASE()=db,'test',db);

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

PREPARE s1 FROM 'SELECT ?,?';

OPEN cur;

read_loop: LOOP

FETCH cur INTO tb,col;

IF done THEN

LEAVE read_loop;

END IF;

EXECUTE s1 USING @tb,@col;

END LOOP;

CLOSE cur;

DEALLOCATE PREPARE s1;

END$$

需要注意的是 DATABASE()函数的使用，无论在哪个库调用该过程，返回的结果都是test，

所以要传递库名的话需要给存储过程增加参数。

8.关于colorfull-console

很久以前就看不惯黑压压的linux终端窗口了，那就来一番装修八：

# man美化

mkdir ~/.terminfo && cd ~/.terminfo

wget -q http://nion.modprobe.de/mostlike.txt

tic mostlike.txt

echo 'alias man="TERMINFO=~/.terminfo/ LESS=C TERM=mostlike PAGER=less man"' >> ~/.bash_profile

# 终端美化

wget -q http://cwrapper.sourceforge.net/cw-1.0.16.tar.gz

tar xf cw-1.0.16.tar.gz

cd cw-1.0.16

./configure && make install

echo 'export PATH=/usr/local/lib/cw:$PATH' >> ~/.bash_profile

此外，mysql终端查询的数据量一多就眼花缭乱，还好在SF上已有高人做出解答，那就是神奇的colour-mysql-console。

它是利用pager(MySQL内部用于过滤或者格式化结果集的自带命令)调用外部的格式化程序将结果集输出为带颜色变量的字符，

它这里使用的是python程序grc做的处理，安装过程如下：

wget http://korpus.juls.savba.sk/~garabik/software/grc/grc_1.9.orig.tar.gz

tar xf grc_1.9.orig.tar.gz

cd grc-1.9

sh install.sh

wget https://github.com/nitso/colour-mysql-console/archive/master.zip -O master.zip

unzip -q master.zip

mv colour-mysql-console-master/* ~ 参考：

Mysql Color Scheme，值得一提的是作者说他花了一年才搞定这个事情！