| [备忘]mysql存储过程，字符串处理的效率小问题 | ||
| 添加日期：2015/4/1 11:27:52 | 快速返回 返回列表 | 阅读2314次 |
BEGIN declare pos int; -- Keeping track of the next item's position declare item varchar(256); -- A single item of the input DECLARE idCount int; set idCount=1; while (length(inputString) > 1) do set pos = INSTR(inputString, '|'); set item = LEFT(inputString, pos - 1); set inputString = substring(inputString, pos + 1); set idCount = idCount +1; end while; END 搞了个大字符串，大概40万个字符，结果160秒才能跑完。 想了想，估计substring那步太慢，对一个大字符串截取，又生成一个大字符串。 结果生成了N个大字符串。 改成如下： BEGIN declare pos int; -- Keeping track of the next item's position declare item varchar(256); -- A single item of the input DECLARE idCount int; declare posStart int; set idCount=1; set posStart = 1; set pos = LOCATE('|',inputString, posStart); while (pos>0) do set item = substring(inputString, posStart,pos - posStart); set idCount = idCount +1; set posStart = pos + 1; set pos = LOCATE('|',inputString, posStart); end while; END 查找位置后，直接截取那一段出来，相当于原来的大字符串没动，直接 把每段值截取出来，总大小相当于原串的2倍。 运行，呵呵，和原来差不多，啊哈哈~~~ 靠，气死了~~ 把substring那段去掉，再跑，md，发现差不多， 看来是tm的locate慢啊~~ 又想，既然是大字符串定位慢，那就拆成小块查询。 最后改成这样： declare pos int; -- Keeping track of the next item's position declare item varchar(256); -- A single item of the input DECLARE idCount int; declare posStart int; declare cutStart int; DECLARE buffer varchar(1200); -- 注意比1024大哦 declare bufferSize int; SELECT now(); set idCount=1; set bufferSize = 1024; -- 每次截取的大小 set cutStart = 1; -- 截取的起始位置 set buffer = substring(inputString, cutStart,bufferSize); while (LENGTH(buffer)>0) do set posStart = 1; set pos = LOCATE('|',buffer, posStart); while (pos>0) do -- set item = substring(buffer, posStart,pos - posStart); set idCount = idCount +1; set posStart = pos + 1; set pos = LOCATE('|',buffer, posStart); end while; -- 有剩余 if posStart<= length(buffer) THEN set buffer = SUBSTRING(buffer,posStart); else set buffer = ""; end if; -- 取下一段 set cutStart = cutStart + bufferSize; set buffer = concat(buffer,substring(inputString, cutStart,bufferSize)); end while; SELECT now(); 靠，连buffer都想出来了~~ 执行，大概60秒~~~ 我是没招了。 |
||
| 评论 COMMENTS | |
| 没有评论 No Comments. |
