文档章节

MySQL Tips 2.0

cwalet
 cwalet
发布于 2015/04/21 10:46
字数 2264
阅读 76
收藏 1
点赞 0
评论 0

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


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

大体上定义变量的形式分为两种,后两者表现基本一致,差别细微:DECLARESET/SELECT


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

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

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

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

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


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

两者的基本区别分别是:(引用自:mysql的set和declare区别

  • 类型声明: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在此的作用只是为了说明 SELECTSET 设置变量的效果是一样的,用于对比变量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$$

以下内容分别引用自:mysql游标循环的使用MySQL存储过程变量用Declare,Declare要注意的几点

注意,声明各种变量的顺序。首先要进行变量声明,然后声明条件,随后声明游标,再后面才是声明错误处理器,如果没有按照顺序声明,系统会提示错误信息。
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执行差异不大,关键在于具体执行语句的分析和检索过程。

参考:MySQL中Stmt 预处理提高效率问题的小研究


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

以上内容整理自:让bash的man看上去多姿多彩

此外,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,值得一提的是作者说他花了一年才搞定这个事情!

© 著作权归作者所有

共有 人打赏支持
cwalet
粉丝 42
博文 91
码字总数 85861
作品 0
其他
Mac OS X 10.8之下运行Absinthe 2.0的方法

iOS 5.1.1完美越狱工具Absinthe 2.0发布了,但是对于Mac 10.8的用户来说又有一个问题了,怎样在10.8里运行 Absinthe 2.0? 1. 显示包的内容。 2. 将10.5 或者 10.6 拖入终端 3.点击返回.然后...

鉴客 ⋅ 2012/05/26 ⋅ 1

MySQL慢查询分析案例

MySQL慢查询分析案例 MySQL 随着业务量的增长,运营同事反馈有个报表页面越来越慢,从对应的报表语句中逐个子查询筛查,找出如下最慢的语句: 可以看到,其中有个子集全表扫了300多万行数据。...

messi_10 ⋅ 2016/05/09 ⋅ 0

数据库优化之降龙十八掌

技术老铁们,工作累了,我们就一起来放松一下!老张我呢是个金庸迷,在金庸小说中,降龙十八掌无愧巅峰外功,它的威力之大可想而知。而今儿,老张要给大家介绍18招式,来优化我们的 MySQL 数...

cruisezhao ⋅ 2017/07/21 ⋅ 0

字符串、数组、链表、栈、二叉树

1.1 字符串 确定两个字符串同构 StringA的字符重新排列后,能否变成StringB 详细 tips: 第一步先判断两个字符串的长度是否相等 字符串的长度为有括号 1.2 数组 清除二维数组行列 将数组中所有...

Jansens ⋅ 2016/11/05 ⋅ 0

数据库优化之降龙十八掌

技术老铁们,工作累了,我们就一起来放松一下!老张我呢是个金庸迷,在金庸小说中,降龙十八掌无愧巅峰外功,它的威力之大可想而知。而今儿,老张要给大家介绍18招式,来优化我们的 MySQL 数...

superZS ⋅ 2017/07/19 ⋅ 0

Mac OS X Yosemite 上安装 MySql 5.6.26的几个坑

坑1,缺省端口不是3306 使用mysql-5.6.26-osx10.9-x8664.dmg安装以后会在“系统偏好设置”中添加一个“MySql”控制面板 但是启动MySql Server的端口有可能为3307不是缺省的3306,如果想使用3...

luan.ma ⋅ 2015/09/18 ⋅ 3

Tips for Optimizing MySQL Queries

http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/

企图穿越 ⋅ 2010/02/20 ⋅ 0

一份最实用的云数据库架构设计与实践指南(内含PPT)

Tips:登陆云盘:http://pan.baidu.com/s/1bo9Ni7l 即可下载5月21日DBAplus社群上海站沙龙PPT。 在这个云为先、一切皆可上云的时代,越来越多的企业已经或即将把数据库上云,5月21日,针数据...

DBAplus社群 ⋅ 2017/05/23 ⋅ 0

用amfphp中出现的问题1

这几天刚刚接触到了amfphp,从中我知道用amfphp可以将php,flex,mysql,apache,结合起来的,今天我看了一段这个教程,模 仿上面的demo做的了,由于当时他们做的时候用的是flex2吧,跟我现在...

路边拾草人 ⋅ 2011/05/08 ⋅ 0

MySQL/Galera 2.0 GA 发布

MySQL/Galera 2.0 GA 发布了,该版本包含: Galera 2.0 MySQL-wsrep 5.5.20 MySQL-wsrep 5.1.60 这是一个新的里程碑版本,同时修复了关于增量状态转换、外键以及许多关键的 bug,详情请进入:...

红薯 ⋅ 2012/02/20 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

RabbitMQ学习以及与Spring的集成(三)

本文介绍RabbitMQ与Spring的简单集成以及消息的发送和接收。 在RabbitMQ的Spring配置文件中,首先需要增加命名空间。 xmlns:rabbit="http://www.springframework.org/schema/rabbit" 其次是模...

onedotdot ⋅ 19分钟前 ⋅ 0

JAVA实现仿微信红包分配规则

最近过年发红包拜年成为一种新的潮流,作为程序猿对算法的好奇远远要大于对红包的好奇,这里介绍一种自己想到的一种随机红包分配策略,还请大家多多指教。 算法介绍 一、红包金额限制 对于微...

楠木楠 ⋅ 31分钟前 ⋅ 0

Python 数电表格格式化 xlutils xlwt xlrd的使用

需要安装 xlutils xlwt xlrd 格式化前 格式化后 代码 先copy读取的表格,然后按照一定的规则修改,将昵称中的学号提取出来替换昵称即可 from xlrd import open_workbookfrom xlutils.copy ...

阿豪boy ⋅ 今天 ⋅ 0

面试题:使用rand5()生成rand7()

前言 读研究生这3 年,思维与本科相比变化挺大的,这几年除了看论文、设计方案,更重要的是学会注重先思考、再实现,感觉更加成熟吧,不再像个小P孩,人年轻时总会心高气傲。有1 道面试题:给...

初雪之音 ⋅ 今天 ⋅ 0

Docker Toolbox Looks like something went wrong

Docker Toolbox 重新安装后提示错误:Looks like something went wrong in step ´Checking if machine default exists´ 控制面板-->程序与应用-->启用或关闭windows功能:找到Hyper-V,如果处......

随你疯 ⋅ 今天 ⋅ 0

Guacamole 远程桌面

本文将Apache的guacamole服务的部署和应用,http://guacamole.apache.org/doc/gug/ 该链接下有全部相关知识的英文文档,如果水平ok,可以去这里仔细查看。 一、简介 Apache Guacamole 是无客...

千里明月 ⋅ 今天 ⋅ 0

nagios 安装

Nagios简介:监控网络并排除网络故障的工具:nagios,Ntop,OpenVAS,OCS,OSSIM等开源监控工具。 可以实现对网络上的服务器进行全面的监控,包括服务(apache、mysql、ntp、ftp、disk、qmail和h...

寰宇01 ⋅ 今天 ⋅ 0

AngularDart注意事项

默认情况下创建Dart项目应出现以下列表: 有时会因为不知明的原因导致列表项缺失: 此时可以通过以下步骤解决: 1.创建项目涉及到的包:stagehand 2.执行pub global activate stagehand或pub...

scooplol ⋅ 今天 ⋅ 0

Java Web如何操作Cookie的添加修改和删除

创建Cookie对象 Cookie cookie = new Cookie("id", "1"); 修改Cookie值 cookie.setValue("2"); 设置Cookie有效期和删除Cookie cookie.setMaxAge(24*60*60); // Cookie有效时间 co......

二营长意大利炮 ⋅ 今天 ⋅ 0

【每天一个JQuery特效】淡入淡出显示或隐藏窗口

我是JQuery新手爱好者,有时间就练练代码,防止手生,争取每天一个JQuery练习,在这个博客记录下学习的笔记。 本特效主要采用fadeIn()和fadeOut()方法显示淡入淡出的显示效果显示或隐藏元...

Rhymo-Wu ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部