xSky 实验室关注高性能计算,分布式系统/存储,大数据/机器学习
目录
  • 首页
  • 知识┊技术相关
  • 作品┊原创作品
  • 技术┊WEBRTC研究
  • 随笔┊心情日记

mysql存储过程使用心得

2018-04-11 04:23:40

由于经常跟MYSQL数据库打交道,在工作中因为各种需要写了一些存储过程,

这里把平时使用存储过的心得体会写一下:
1.MYSQL存储函数
mysql存储过程以是SQL语言开发的,SQL语言平时使用中最常用的就是 UPDATE,INSERT/REPLACE,DELETE
等了,但是,由于MYSQL 提供了很多内部函数,因此,结合SQL语言,我们可以实现很多功能。
比如,有的时候,我们需要对某个字段进行使用某个HASH函当选,一般来说我们都是通过外部程序编写代码实现
hash函数,处理好后再交给MYSQL进行操作。
当然如果对性能要求不是那么高的话,我们可以使用MYSQL存储函数来实现,可以简化对数据的操作,直接使用SQL就可以了,
省去了外部脚本的开发。
unsigned long ELFHash(const char *key)
{
unsigned long h = 0;
unsigned long x = 0;
while (*key)
{
h = (h << 4) + (*key++);
if ((x = h & 0xF0000000L) != 0)
{
h ^= (x >> 24);
h &= ~x;
}
}
return (h & 0x7FFFFFFF);
}
这里是 ELFHash 的SQL实现:
CREATE DEFINER=`test`@`127.0.0.1` FUNCTION `elfhash`(strkey varchar(256)) RETURNS int(11)
NO SQL
BEGIN

DECLARE h INT UNSIGNED DEFAULT 0;
DECLARE x INT UNSIGNED DEFAULT 0;
DECLARE k INT UNSIGNED DEFAULT 0;
DECLARE strLen INT UNSIGNED DEFAULT 0;
DECLARE pos INT UNSIGNED DEFAULT 0;
DECLARE cc char(1) DEFAULT NULL;

SELECT LENGTH(strkey)+1 INTO strLen ;

WHILE (pos<strLen) DO
set k = ASCII(MID(strkey,pos,1));
set h = (h << 4) + k;
SET x = h & 0xF0000000;
IF (x!= 0) THEN
set h = h^(x >> 24);
set h = h&(~x);
END IF;

SET pos = pos +1;
END WHILE;

SET h = h & 0x7FFFFFFF;
RETURN h;
END

2. 在SQL里使用一些内部函数,可以实现一些比较有用的功能,
比如,MYSQL提供的BIT类型长度有限,最大只能是64位,
如果需要操作大于64位的BITARRY,MYSQL提供的类型就不能满足了。
这时我们可以使用存储函数来模拟一个大于64位的BITARRY

# 取得某一位的值
CREATE DEFINER=`ttuser`@`116.228.188.74` FUNCTION `getbitarry`(aStatus varchar(22), aTaskID int) RETURNS int(11)
NO SQL
BEGIN
#Routine body goes here...

RETURN MID(aStatus,aTaskID,1);
END


# 给BIT位赋值
CREATE DEFINER=`ttuser`@`116.228.188.74` FUNCTION `setbitarray`(aStatus varchar(22), aTaskID int, aflag int) RETURNS varchar(22) CHARSET utf8
NO SQL
BEGIN
#Routine body goes here...
SET aStatus = CONCAT(MID(aStatus, IF(aflag,1,0), aTaskID-1), aflag, MID(aStatus, aTaskID+1, LENGTH(aStatus)-aTaskID));
RETURN aStatus;
END


CREATE TABLE `t_test` (
`Fid` int(11) NOT NULL,
`Fstatus` varchar(22) NOT NULL DEFAULT '',
PRIMARY KEY (`Fid`,`Fstatus`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

UPDATE t_test SET Fstatus = setbitarray(Fstatus, 4, 1) ;
SELECT *, getbit(Fstatus,1), getbit(Fstatus,2) FROM t_test;



3. mysql存储过程里的拼SQL问题:

很多时候由于数据库被分库分表,在写存储过程的时候,库表名就需要动态生成, 简单的用set或者declare语句定义变量,然后直接作为sql的表名是不行的,mysql会把变量名当作表名。解决方法是将整条sql语句作为变量,其中穿插变量作为表名,
然后用 prepare stmt 语句执行。

SET @sqlStr = CONCAT('SELECT COUNT(DISTINCT TABLE_SCHEMA) FROM information_schema.TABLES WHERE TABLE_SCHEMA=\'',
@strDBname, '\' AND TABLE_NAME=\'', @strTBname,'\' INTO @nCount');
SELECT @sqlStr;
prepare stmt from @sqlStr;EXECUTE stmt;DEALLOCATE prepare stmt;

预处理语句的SQL语法可以在存储过程中使用,但是不能在存储函数或触发程序中使用。

By:xSky | 知识┊技术相关 |

  • 分类目录

    • 知识┊技术相关 (48)
    • 作品┊原创作品 (7)
    • 技术┊WEBRTC研究 (2)
    • 随笔┊心情日记 (4)
  • 最新文章

    • 使用google heap profiler进行内存占用分析
    • Linux中的常用内存问题检测工具
    • xRedis 1.4 版本发布!
    • 我的电子书
    • 史上最全的WebRTC服务器技术选型分析
    • 百亿流量微服务网关的设计与实现
    • 爱奇艺网络协程编写高并发应用实践
    • MySQL 的 crash-safe 原理解析
    • 海量数据,海明距离高效检索(smlar)
    • 开发小技巧备忘
    • Redis 6 新功能提前看!
    • 使用Sysdig监视您的Ubuntu
    • 安装VMware没有虚拟网卡出现解决方法
    • 想要搞懂数据可视化,看这篇就够了!
    • MySQL 数据库铁律
    • 微服务:分布式事务
    • 多队列网卡及网卡中断绑定阐述
    • epoll 的本质是什么?
    • 千万并发不是梦:TCPBurn并发测试
    • 使用R数据进行数据分析
    • 消息中间件选型:Kafka还是RabbitMQ?
    • 一套原创分布式即时通讯(IM)系统理论架构方案
    • 一套高可用、易伸缩、高并发的IM群聊、单聊架构方案设计实践
    • 百度App网络深度优化系列《二》连接优化
    • 百度App网络深度优化系列《一》DNS优化
    • ZooKeeper 一致性协议 ZAB 原理
    • Linux/Unix下ODBC的安装、配置与编程
    • 最适合数据分析师的数据库
    • CentOS6 在线安装PostgreSQL10
    • xChart数据可视化工具使用说明
  • 链接

    • xSky的Blog
    • 我的开源/github
    • 实时监控图表
    • 预印本
    • xredis 在线文档
    • xSkyProxy
    • xChart 数据在线测试
    • 我的电子书
    • xChart 数据可视化系统
    • 树莓派技术圈
    • WebRTC开发者社区
  • 开源项目

    • xReis C++的redis客户端库
    • xBlog-C++ 博客程序
    • xSkyProxy-新的MySQL代理网关
    • 数据可视化平台- xChart
    • xhttpcache 高速数据缓存服务
    • xMonitor-图形监测工具
    • test

Powered By xblog Copyright 0xsky.com All Rights Reserved.

Copyright 0xsky.com All Rights Reserved.