MySQL存储过程使用建议 慎用存储过程,不要使用触发器,不使用外键 - 老陈
& MySQL存储过程使用建议 慎用存储过程,不要使用触发器,不使用外键
MySQL存储过程使用建议 慎用存储过程,不要使用触发器,不使用外键
这是10年发在公司内部论坛上的. 分享给大家.
我们看下传统的说法:
If you have a repetitive task that requires checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that’s stored on the server. Then there won’t be messages going back and forth between server and client, for every step of the task.
存储过程,外键,触发器这些东东,在传统行业,用得比较多, 许多复杂的业务逻辑用存储过程来实现,可保证安全,进行权限控制, 集中控制业务逻辑,客户端可大大简化, 如果业务逻辑变更,只需要修改下存储过程即可,而不需要繁琐地升级大量的客户端(一些编译发布的程序的很麻烦). 而且数据库服务器往往更强劲,也执行得更快更有效率, 网络通信来回往返传输的开销也可以节省.
虽然存储过程,外键,触发器有这么许多的好处,但现实中却存在许多问题, 随着业务规模的扩大,数据库会逐渐成为系统的瓶颈, 在客户端和数据库中间增加应用服务器(应用层)实现业务逻辑,用应用服务器(客户端)来确保数据完整性和一致性, 是伸缩性更好的方案,
如今的计算模式也已经和以前有了很大不同,特别是互联网,廉价的PC服务器集群大量应用,硬盘容量更大,价格更低,更倾向水平扩展,而没有必要把负荷都堆积到中心的数据库服务器上.所以对于我们互联网应用,存储过程,外键约束,触发器这些东东不再凸显重要性,许多项目基本不用.
理论上来说,业务逻辑,各种约束越靠近数据库,会越安全, 也能最大化的充分利用数据库.但对于我们互联网应用,一般没有那么高的数据安全,不需要很强的数据完整性和一致性,如果确实有非常严苛的数据一致性需求,你也可以专门实现一个”数据访问层”,其他应用通过它来访问数据库.
2. 性能和扩展性:
mysql(5.1)的触发器只支持行级别((for each row)一种方式,对于大数据量表的处理很低效.触发器没有when条件,不能控制何时触发,可能造成性能瓶颈.无谓消耗资源.
外键,对并发性能的影响很大,因为每次修改数据都需要去另外一个表检查数据,需要获取额外的锁,高并发的环境下很容易出性能问题. 而级联更新删除之类的特性也比我们正常执行批量更新删除要慢得多. 所以更好的办法是在应用层实现外键约束.
应用层实现业务逻辑的网络通信的成本可能高了点, 但这是一个相对的概念,在距离很遥远的情况下,客户端和服务器端通信成本比较大,这个时候存储过程更显优势,但app服务器和数据库服务器一般位于同一个集群的内网,网络交互很快很稳定成本也很低.
数据库实现存储过程,触发器,外键很大一个背景是数据库服务器很强劲,传统行业一般是昂贵小型机,有非常强劲的处理能力,配备的是oracle等商业产品,业务需求相对稳定,需要充分利用数据库的能力而不仅仅当作一个数据的容器. 而互联网行业一般使用的是mysql数据库,相对廉价的PC, 业务的增长不确定,甚至是爆炸式的,如果数据构架不足,数据库很可能成为整个系统的瓶颈,数据库的资源一般比较紧张(服务器和人),扩展性不强,更昂贵, 而web服务器相对来说更便宜,更容易水平扩展, 把业务逻辑放到web服务器上去实现可以保证系统有良好的伸缩性.
如果需要在不同的数据库产品间迁移,虽然有一些文档,各种各样的迁移方案供我们选择,但存储过程,触发器的迁移是一个难题.往往需要投入巨大的精力开发和测试.
4. 升级,维护,诊断,调优
降低了上线,升级的效率,dba和研发同学需要高度协调.以前一般是分离的, 或者升级代码或者升级数据库结构, 而现在需要升级存储在数据库服务器上的代码,但dba往往并不熟悉业务.
升级失败不易马上恢复.影响面太大. 而升级web服务器,可以一台一台升级. (一般情况下是可以做到一台一台升级的)
业务非常繁忙的系统, 升级存储过程可能导致系统出现异常,因为要升级的存储过程可能正被频繁访问. 或者应用系统足够复杂,存储过程互相调用,升级单个存储需要特别小心,以免影响了整个系统.
开发(测试)环境和生产环境可能不一致,导致开发开发环境的存储过程,触发器需要经过修改,才能升级到生产环境.(存储过程,视图,触发器等附加了一些额外不一致的信息)
备份恢复不方便.
存储过程,触发器debug不易.且触发器隐藏了实际执行的步骤,不易发现问题
mysql不能临时禁用,启用触发器,这点如果做数据迁移,修复会比较麻烦,需要临时drop触发器,可能影响到生产环境.
由于存储过程,触发器不易测试,或者未做充分测试,一旦升级失败可能导致数据错误,因为已经先drop了存储过程或者触发器.
不易分析存储过程,触发器的性能.不能通过慢查询日志去分析存储过程,触发器的具体执行情况.仅仅记录call procedure_name();这样简单的信息;
触发器可能导致死锁;
以上只是列举一些问题,具体的使用过程中,mysql的存储过程和触发器离商业产品的距离还有很远.
存储过程,触发器的调试比较困难,没有什么好的工具和方法.
触发器一个表同类型触发器只能建立一个,可能导致代码逻辑很复杂,不易阅读和维护,因为你需要把许多不相关的逻辑都写在一个触发器代码内.(MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’)
存储过程有诸多限制: 官方文档的存储过程的一些限制参考:
如果没有完善的,一致的文档,开发人员往往不熟悉(遗漏)数据库上的存储过程.
存储过程比较简单,功能很有限,而程序代码可以实现更多的功能, 实现更复杂的业务逻辑.
小结: 存储过程慎用,业务逻辑不要放在存储过程中,;
不要使用触发器.
不要用外键,在高并发情况下,会大大降低并发性,外键自身的维护性管理性也欠佳.
　& 转载保留版权：
& 本文链接地址：
　& 如果喜欢可以：
本文目前尚无任何 trackbacks 和 pingbacks.
2017年十月
2017年五月
2017年三月
2017年二月
2016年六月
2016年五月
2016年四月
2015年六月
2015年五月
2015年四月
2015年二月
2014年十二月
2014年九月
2014年八月
2014年六月
2014年五月
2014年四月
2014年二月
2014年一月
2013年十二月
2013年十一月
2013年十月
2013年九月
2013年八月
2013年七月
2013年六月
2013年五月
2013年四月
2013年三月
2013年二月
2013年一月
2012年十二月
2012年十一月
2012年十月
2012年九月
2012年八月
2012年七月
2012年六月
2012年五月
2012年四月
2012年三月
2012年二月
2012年一月
2011年十二月
2011年十一月
2011年十月
2011年九月
2011年七月
2011年六月
2011年五月
2011年二月
2011年一月
2010年十月
2010年九月
2010年六月
2010年五月
2010年四月
2010年三月
2010年二月
2010年一月
2009年十二月
2009年十一月
2009年十月
2009年九月
2009年八月
2009年七月
2009年六月
2009年五月
2009年四月
2009年三月
2009年二月
2009年一月
2008年十二月
2008年十月
2008年九月
2008年八月
2008年七月
2008年六月
2008年五月
2008年四月
2008年三月
2008年二月
2008年一月
2007年十二月
2007年十一月
2007年十月
2007年九月
2007年八月
2007年七月
2007年六月
2007年五月
2007年四月
2007年三月
2007年二月
2007年一月
2006年十二月
2006年十一月
2006年十月
2006年九月
2006年八月
2006年七月
2006年六月
2006年五月
2006年四月
2006年三月
2006年二月
2006年一月
2005年十二月
2005年十月
2005年九月
2005年七月
2005年五月
2005年二月
2004年十月
2004年五月
2004年四月用户名：elbertchen
文章数：145
评论数：31
访问量：478570
注册日期：
阅读量：1297
阅读量：3317
阅读量：584502
阅读量：469909
[匿名]非常感谢：
51CTO推荐博文
数据库Connection和字段的字符集都采用utf-8.
开始使用存储过程插入数据，代码大约如下
&&&&&&&&&&&&&& MySqlCommand cmd = new MySqlCommand("apend_testproject",&&&&&&&&&&&&&&&&&& new MySqlConnection(cConnectMySqlHelp.GetConnectStr().ConnectionString));&&&&&&&&&&&&&&& commonfunc.logfile(cmd.Connection.ConnectionString);&&&&&&&&&&&&&&& mandType = CommandType.StoredP&&&&&&&&&&&&&&& cmd.Parameters.Add(new MySqlParameter("_PM", _PM));&&&&&&&&&&&&&&& cmd.Parameters.Add(new MySqlParameter("_PUBLISH_TIME", _PUBLISH_TIME));&&&&&&&&&&&&&&& cmd.Parameters.Add(new MySqlParameter("_PRODUCT_LINE", _PRODUCT_LINE));&&&&&&&&&&&&&&& cmd.Parameters.Add(new MySqlParameter("_NAME", HttpUtility.UrlDecode(_NAME)));&&&&&&&&&&&&&&& cmd.Parameters.Add(new MySqlParameter("_PROGRESS", 100));&&&&&&&&&&&&&&& cmd.Parameters.Add(new MySqlParameter("_TEST_SUB_TIME", _TEST_SUB_TIME));&&&&&&&&&&&&&&& cmd.Parameters.Add(new MySqlParameter("_PROJECT_TYPE", _PROJECT_TYPE));
结果数据库表中显示乱码，很郁闷，以为是字符集编码的问题，去网上找了些资料说可以如下操作，意思是
在connectstring里边加入字符集说明utf-8
&&&&&&&& public static MySqlConnectionStringBuilder GetConnectStr()&&&&&&& {&&&&&&&&&&& MySqlConnectionStringBuilder connBuilder =&&&&&&&&&&& new MySqlConnectionStringBuilder();
&&&&&&&&&&& connBuilder.Add("Database", "testplat");&&&&&&&&&&& connBuilder.Add("Data Source", "10.2.226.20");&&&&&&&&&&& connBuilder.Add("User Id", "root");&&&&&&&&&&& connBuilder.Add("Password", "");&&&&&&&&&&& //connBuilder.Add("charset", "utf8");&&&&&&&&&&& return connB&&&&&&& }
结果还是错误。实在没有办法，根据测试直觉，是不是由于存储过程是在数据库端，所以，，，，那么直接写Sql调用试试，
& MySqlCommand cmd = new MySqlCommand();&&&&&&&&&&&&&&& mandType = CommandType.T&&&&&&&&&&&&&&& cmd.Connection = new MySqlConnection(cConnectMySqlHelp.GetConnectStr().ConnectionString);&&&&&&&&&&&&&&& mandText = "insert `testplat`.`TESTPROJECT` (PM,PUBLISH_TIME,PRODUCT_LINE,NAME,PROGRESS,TEST_SUB_TIME,PROJECT_TYPE) "+&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& "values('" + _PM + "','" + _PUBLISH_TIME + "','" + _PRODUCT_LINE + "','" + _NAME + "'," + 100 + ",'" + _TEST_SUB_TIME + "'," + _PROJECT_TYPE + ")";中文不再显示乱码，于是可以断言是存储过程的问题。为了验证，直接到Mysql客户端工具中
执行存储过程：
CALL apend_testproject('elbert', '', 0, '木牛流马',2, '', 2);
Incorrect string value: '\xE6\x9C\xA8\xE7\x89\x9B...' for column '_NAME' at row 1&&&&&&&&&&&&&&& 至此可以断言MySql有虽然经过验证，但是毕竟不是mysql方面专家，所以标题加上疑似！
附mysql版本 mysql& Ver 14.12 Distrib 5.0.81, for pc-linux-gnu (i686) using readline 5.1
&本文出自 “” 博客，请务必保留此出处
了这篇文章
类别：┆阅读(0)┆评论(0)2014年7月 其他数据库开发大版内专家分月排行榜第二2014年3月 其他数据库开发大版内专家分月排行榜第二2013年7月 其他数据库开发大版内专家分月排行榜第二2013年4月 其他数据库开发大版内专家分月排行榜第二2012年3月 其他数据库开发大版内专家分月排行榜第二2011年8月 其他数据库开发大版内专家分月排行榜第二2011年7月 其他数据库开发大版内专家分月排行榜第二2009年11月 其他数据库开发大版内专家分月排行榜第二2009年7月 其他数据库开发大版内专家分月排行榜第二2009年6月 其他数据库开发大版内专家分月排行榜第二2009年5月 其他数据库开发大版内专家分月排行榜第二2009年4月 其他数据库开发大版内专家分月排行榜第二2009年3月 其他数据库开发大版内专家分月排行榜第二2009年1月 其他数据库开发大版内专家分月排行榜第二2008年10月 其他数据库开发大版内专家分月排行榜第二2008年8月 其他数据库开发大版内专家分月排行榜第二2003年9月 Delphi大版内专家分月排行榜第二
2014年4月 其他数据库开发大版内专家分月排行榜第三2013年10月 其他数据库开发大版内专家分月排行榜第三2013年9月 其他数据库开发大版内专家分月排行榜第三2013年6月 其他数据库开发大版内专家分月排行榜第三2012年12月 其他数据库开发大版内专家分月排行榜第三2012年11月 其他数据库开发大版内专家分月排行榜第三2012年10月 其他数据库开发大版内专家分月排行榜第三2012年9月 其他数据库开发大版内专家分月排行榜第三2012年1月 其他数据库开发大版内专家分月排行榜第三2011年11月 其他数据库开发大版内专家分月排行榜第三2011年9月 其他数据库开发大版内专家分月排行榜第三2011年6月 其他数据库开发大版内专家分月排行榜第三2011年5月 其他数据库开发大版内专家分月排行榜第三2011年4月 其他数据库开发大版内专家分月排行榜第三2011年3月 其他数据库开发大版内专家分月排行榜第三2010年11月 其他数据库开发大版内专家分月排行榜第三2010年4月 其他数据库开发大版内专家分月排行榜第三2010年1月 其他数据库开发大版内专家分月排行榜第三2009年12月 其他数据库开发大版内专家分月排行榜第三2009年2月 其他数据库开发大版内专家分月排行榜第三2008年7月 其他数据库开发大版内专家分月排行榜第三2008年6月 其他数据库开发大版内专家分月排行榜第三2008年1月 其他数据库开发大版内专家分月排行榜第三2007年12月 其他数据库开发大版内专家分月排行榜第三2003年12月 Delphi大版内专家分月排行榜第三2003年10月 Delphi大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。中国领先的IT技术网站
51CTO旗下网站
MySQL基础教程之存储过程
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件，虽然它们的作用不仅限于批处理。在我看来， 存储过程就是有业务逻辑和流程的集合， 可以在存储过程中创建表，更新数据， 删除等等。
作者：godruoyi来源：| 14:01
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件，虽然它们的作用不仅限于批处理。
在我看来， 存储过程就是有业务逻辑和流程的集合， 可以在存储过程中创建表，更新数据， 删除等等。
为什么要使用存储过程
通过把处理封装在容易使用的单元中，简化复杂的操作(正如前面例子所述)。
由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
一个简单的存储过程
create&procedure&porcedureName&()&begin&&&&&select&name&from&user;&end;&&
存储过程用create procedure 创建， 业务逻辑和sql写在begin和end之间。mysql中可用call porcedureName
();来调用过程。
&call&porcedureName&();&&
该存储过程没有参数， 只是在调用的时候查询了用户表的用户名而已， 调用结果如下
删除存储过程
DROP&PROCEDURE&IF&EXISTS&porcedureN&&
使用参数的存储过程
create&procedure&procedureName(&&&&&out&min&decimal(8,2),&&&&&out&avg&decimal(8,2),&&&&&out&max&decimal(8,2)&)&BEGIN&&&&&select&MIN(price)&INTO&min&fromℴ&&&&&select&AVG(price)&into&avg&fromℴ&&&&&select&MAX(price)&into&max&fromℴ&END;&&
此过程接受三个参数， 分别用于获取订单表的最小、平均、最大价格。每个参数必须具有指定的类
型，这里使用十进制值(decimal(8,2))， 关键字OUT指出相应的参数用来从存储过程传出
一个值(返回给调用者)
MySQL支持IN(传递给存储过程)、OUT(从存储过程传出，如这里所用)和INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量(通过指定INTO关键字)
为调用此修改过的存储过程，必须指定3个变量名，如下所示：(所有MySQL变量都必须以@开始。)
&call&procedureName(@min,&@avg,&@max);&&
该调用并没有任何输出， 只是把调用的结果赋给了调用时传入的变量(@min, @avg, @max)。然后即可调用显示该变量的值。
select&@min,&@avg,&@max;&
使用in参数, 输入一个用户id， 返回该用户所有订单的总价格。
create&procedure&getTotalById&(&&&&&in&userId&int,&&&&&out&total&decimal(8,2)&)&BEGIN&&&&&select&SUM(r.price)&from&order&r&&&&&where&r.u_id&=&userId&&&&&into&&END;&&
调用存储过程
call&getTotalById(1,&@total);&select&@&&
结果将返回该用户所有订单的合计价格。
复杂一点的过程， 根据用户id获取该用户的所有订单价格， 并动态的选择是否加税。代码设计如下
create&procedure&getTotalByUser2(&&&&&in&userId&int,&&&&&in&falg&boolean,&&&&&&out&total&decimal(8,2)&)&begin&&&&&DECLARE&tmptotal&DECIMAL(8,2);&&&&&DECLARE&taxrate&int&DEFAULT&6;&&&&&&&&&&select&SUM(r.price)&from&order&r&&&&&where&r.u_id&=&userId&&&&&into&&&&&&&&&&&if&taxable&then&&&&&&&&&select&tmptotal&+&(tmptotal/1000*taxrate)&into&&&&&&end&&&&&&&&&&&select&tmptotal&into&&END;&&
该过程传入三个参数， 用户id， 是否加税以及返回的总价格，在过程内部， 定义两个局部变量tmptotal和taxrate，把查询出来的结果赋给临时变量，
在判断是否加税。最后把局部变量的值赋给输出参数。
call&getTotalByUser2(1,&false,&@total);&&call&getTotalByUser2(1,&true,&@total);&&&select&@&
【编辑推荐】【责任编辑： TEL：（010）】
大家都在看猜你喜欢
头条头条热点头条头条
24H热文一周话题本月最赞
讲师：159200人学习过
讲师：218403人学习过
讲师：13707人学习过
精选博文论坛热帖下载排行
它从最简单的地方入手，不仅讲述了JavaScript的基础知识，还讲述了JavsScript如何操作CSS、DOM等Ajax基础技术。而关于跨浏览器兼容问题的解...
订阅51CTO邮刊&>&mysql利用存储过程批量添加数据库表随机测试内容
mysql利用存储过程批量添加数据库表随机测试内容
上传大小：116KB
mysql利用存储过程批量添加数据库表随机测试内容
综合评分：3.7（3位用户评分）
所需积分/C币：
下载个数：14
{%username%}回复{%com_username%}{%time%}\
/*点击出现回复框*/
$(".respond_btn").on("click", function (e) {
$(this).parents(".rightLi").children(".respond_box").show();
e.stopPropagation();
$(".cancel_res").on("click", function (e) {
$(this).parents(".res_b").siblings(".res_area").val("");
$(this).parents(".respond_box").hide();
e.stopPropagation();
/*删除评论*/
$(".del_comment_c").on("click", function (e) {
var id = $(e.target).attr("id");
$.getJSON('/index.php/comment/do_invalid/' + id,
function (data) {
if (data.succ == 1) {
$(e.target).parents(".conLi").remove();
alert(data.msg);
$(".res_btn").click(function (e) {
var q = $("#form1").serializeArray();
console.log(q);
var res_area_r = $.trim($(".res_area_r").val());
if (res_area_r == '') {
$(".res_text").css({color: "red"});
$.post("/index.php/comment/do_comment_reply/", q,
function (data) {
if (data.succ == 1) {
var $target,
evt = e || window.
$target = $(evt.target || evt.srcElement);
var $dd = $target.parents('dd');
var $wrapReply = $dd.find('.respond_box');
console.log($wrapReply);
var mess = $(".res_area_r").val();
var str = str.replace(/{%header%}/g, data.header)
.replace(/{%href%}/g, 'http://' + window.location.host + '/user/' + data.username)
.replace(/{%username%}/g, data.username)
.replace(/{%com_username%}/g, _username)
.replace(/{%time%}/g, data.time)
.replace(/{%id%}/g, data.id)
.replace(/{%mess%}/g, mess);
$dd.after(str);
$(".respond_box").hide();
$(".res_area_r").val("");
$(".res_area").val("");
$wrapReply.hide();
alert(data.msg);
}, "json");
/*删除回复*/
$(".rightLi").on("click",'.del_comment_r', function (e) {
var id = $(e.target).attr("id");
$.getJSON('/index.php/comment/do_comment_del/' + id,
function (data) {
if (data.succ == 1) {
$(e.target).parent().parent().parent().parent().parent().remove();
$(e.target).parents('.res_list').remove()
alert(data.msg);
//填充回复
function KeyP(v) {
$(".res_area_r").val($.trim($(".res_area").val()));
评论共有1条
还不错，正好是我要用的
审核通过送C币
MySQL基础知识书籍
创建者：ivccav
创建者：chenhongxin
MySQL数据库学习电子书
上传者其他资源上传者专辑
Mysql-mtop性能监控
linux安装yii2详解
repcached2.3.1-1.4.13内存复制工具
mecached-1.4.13.tar.gz
sysbench进行cpu,磁盘IO,内存,OLTP基准测试
数据库热门标签
VIP会员动态
下载频道用户反馈专区
下载频道积分规则调整V1710.18
开通VIP，海量IT资源任性下载
spring mvc+mybatis+mysql+maven+bootstrap 整合实现增删查改简单实例.zip
CSDN&VIP年卡&4000万程序员的必选
为了良好体验，不建议使用迅雷下载
mysql利用存储过程批量添加数据库表随机测试内容
会员到期时间：
剩余下载个数：
剩余C币：593
剩余积分：0
为了良好体验，不建议使用迅雷下载
积分不足！
资源所需积分/C币
当前拥有积分
您可以选择
程序员的必选
绿色安全资源
资源所需积分/C币
当前拥有积分
当前拥有C币
(仅够下载10个资源)
全站1200个资源免积分下载
为了良好体验，不建议使用迅雷下载
资源所需积分/C币
当前拥有积分
当前拥有C币
全站600个资源免积分下载
资源所需积分/C币
当前拥有积分
当前拥有C币
您的积分不足，将扣除 10 C币
全站1200个资源免积分下载
为了良好体验，不建议使用迅雷下载
你当前的下载分为234。
你还不是VIP会员
开通VIP会员权限，免积分下载
你下载资源过于频繁，请输入验证码
你下载资源过于频繁，请输入验证码
您因违反CSDN下载频道规则而被锁定帐户，如有疑问，请联络:!
若举报审核通过，可奖励20下载分
被举报人：
举报的资源分：
请选择类型
资源无法下载
资源无法使用
标题与实际内容不符
含有危害国家安全内容
含有反动色情等内容
含广告内容
版权问题，侵犯个人或公司的版权
*详细原因：
mysql利用存储过程批量添加数据库表随机测试内容