更新ip访问次数的存储过程

    5点了，今天弄了一天，弄出来另一个更新ip的存储过程，100多行的程序，在editplus里写完后运行，竟然没有bug，擦啊。

 

 

说明：客户端传来的串和数据表里的字段 request_ip 的格式必须遵循一样的规律，都是类似 192.168.3.44,389-10.110.10.12,590-$ 这样，以-分隔的ip,ip出现的次数，以$结尾
具体应该代表：
客户端ip,此ip出现的次数-客户端ip,此ip出现的次数-$
举例：
192.168.3.44,389-10.110.10.12,590-$

算法为：
初始化set NewValue='';
第一个循环，以数据库中的老ip为基础
从表里select出来request_ip，存入字符串@OldValue中，循环处理，取出每一个 “ip,次数” 的子串，在传入的字符串中寻找是否有此ip
若有：增加此ip出现的次数
否则：不增加次数
拼凑ip,times串，然后concat到NewValue中
第二个循环，以客户端传过来的AddValue为基础，循环比对数据库中的值，
若有：不进行任何处理，因为在前一个循环里已经累加过了
否则：将此对ip,times 增加到NewValue中

最后，添加上 $ ,update NewValue

知识准备：
INSTR(str,substr)
    返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同，除了参数被颠倒。

    mysql> select INSTR('foobarbar', 'bar');
                -> 4
    mysql> select INSTR('xbar', 'foobar');
                -> 0

    这函数是多字节可靠的。
   
SUBSTRING(str,pos)
    　
SUBSTRING(str FROM pos)
    从字符串str的起始位置pos返回一个子串。

    mysql> select SUBSTRING('Quadratically',5);
                -> 'ratically'
    mysql> select SUBSTRING('foobarbar' FROM 4);
                -> 'barbar'

 

调用方法为：

call UpdateRequestIp('access_log_reg_201203','/jsp10','2012-03-22 12:30:00','10.110.10.159,23-$',@a);

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
drop procedure if exists UpdateRequestIp;

 

DELIMITER $$
-- Purl 是存储过程接收的access_url, Pdatetime是存储过程接收的access_datetime
create procedure UpdateRequestIp(IN tablename varchar(80), IN Purl varchar(100), IN Pdatetime datetime ,IN AddValue varchar(256), OUT ReturnValue varchar(80) )
top:BEGIN

--OldValue循环加上此存储过程接收的参数AddValue后，形成NewValue
declare NewValue varchar(1024);
--保存OldValue中，以-分隔的各个ip,times串
declare TmpStrOld_ip_times varchar(80);
declare TmpStrOld_ip varchar(80);
declare TmpStrOld_times varchar(80);
--保存AddValue中，以-分隔的各个ip,times串
--从AddValue中取出寻找到的ip到结尾的串，备用下面分隔ip和次数
declare TmpStrAdd varchar(80);
declare TmpStrAdd_ip_times varchar(80);
declare TmpStrAdd_ip varchar(80);
declare TmpStrAdd_times varchar(80);

-- 保存AddValue中，寻找到的ip的起始位置
declare instr_i int ;
declare Length int ;
declare i int ;

--初始化新字符串
set NewValue='';
set autocommit=0;

--这句话弄了半下午，主要是表名用变量和select into到变量这两个问题。
SET @SqlCmd = concat('select request_ip into @OldValue from ', tablename ,' where access_url=\'', Purl, '\' and access_datetime=\'', Pdatetime, '\' for update');
PREPARE stmt FROM @SqlCmd;
EXECUTE stmt;

--第一个循环，以数据库中的串为基础
set Length = length(@OldValue);
set i = 1;
-- 以OldValue的长度为遍历次数，按-分隔，取出"ip,times"这样的串，一个个地累加OldValue和AddValue，遇到'$'时退出循环，最后形成NewValue，注意，数据库中的OldValue很有可能会比客户端调用的AddValue要多，因为AddValue只传过来top10，而数据库中是累加的。

first_while_label: while i < Length
 do
 -- 获取数据库中串里的ip和次数
 set TmpStrOld_ip_times = substring_index( substring_index(@OldValue, '-', i), '-', -1 );

 -- 判断结束，结束条件是数据库中的串的最后一位是$，否则要返回错误，通知调用者
 if(TmpStrOld_ip_times = '$')
 then
  --返回值应该是SUCCESS,这里返回一半，最后update完毕后返回全部
  set ReturnValue = 'SUCCESSfirstloop';
  --select ReturnValue;
  leave first_while_label;
 end if;

 set TmpStrOld_ip = substring_index(TmpStrOld_ip_times, ',', 1);
 set TmpStrOld_times = substring_index(TmpStrOld_ip_times, ',', -1);

 -- 在AddValue 中寻找此ip
 set instr_i = instr(AddValue, TmpStrOld_ip);
 if(instr_i > 0)
  then
   set TmpStrAdd = substring(AddValue, instr_i);
   set TmpStrAdd_ip_times = substring_index(TmpStrAdd, '-', 1);
   set TmpStrAdd_ip = substring_index(TmpStrAdd_ip_times, ',', 1);
   set TmpStrAdd_times = substring_index(TmpStrAdd_ip_times, ',', -1);
   -- 前面的一系列都是为了这句：
   set TmpStrOld_times = TmpStrOld_times + TmpStrAdd_times ;
 end if;

 -- 构造新值
 set NewValue =  concat(NewValue, TmpStrOld_ip, ',', TmpStrOld_times, '-');

 set i=i+1;
end while;

--如果第一个循环结束时，最后一个字符不是$，就说明格式不对，需要退出此存储过程。
if(TmpStrOld_ip_times != '$')
 then
 set ReturnValue = 'format error, exit with no update';
 leave top;
 select 'can you see this?';
end if;

--第二个循环，以客户端传过来的 AddValue 串为基础
set Length = length(AddValue);
set i = 1;
-- 以AddValue的长度为遍历次数，按-分隔，取出"ip,times"这样的串，如果这个串在数据库中，前面的循环已经处理了，累加不在的串即可。

second_while_label: while i < Length
 do
 -- 获取串里的ip和次数
 set TmpStrAdd_ip_times = substring_index( substring_index(AddValue, '-', i), '-', -1 );

 -- 判断结束，结束条件是串的最后一位是$，否则要返回错误，通知调用者
 if(TmpStrAdd_ip_times = '$')
 then
  --返回值应该是SUCCESS,这里返回一个错误的，有利于判断程序在哪里出错，最后update完毕后返回全部
  set ReturnValue = 'SUCCESSsecondloop';
  --select ReturnValue;
  leave second_while_label;
 end if;

 set TmpStrAdd_ip = substring_index(TmpStrAdd_ip_times, ',', 1);
 --set TmpStrAdd_times = substring_index(TmpStrAdd_ip_times, ',', -1);

 -- 在 @OldValue 中寻找此ip
 set instr_i = instr(@OldValue, TmpStrAdd_ip);
 if(instr_i = 0)
  then
   set NewValue =  concat(NewValue, TmpStrAdd_ip_times, '-');
 end if;

 set i=i+1;
end while;

--如果循环结束时，最后一个字符不是$，就说明格式不对，需要退出此存储过程。
if(TmpStrAdd_ip_times != '$')
 then
 set ReturnValue = 'format error, exit with no update';
 leave top;
 select 'can you see this in second loop?';
end if;

set NewValue =  concat(NewValue, '$');

-- 前面有错误的话，会直接退出了，所以update的时候放心update即可。
--update tablename set request_ip=NewValue where access_url=Purl and access_datetime=Pdatetime;
SET @SqlCmd = concat('update ', tablename ,' set request_ip=\'', NewValue,'\' where access_url=\'', Purl, '\' and access_datetime=\'', Pdatetime, '\'');
PREPARE stmt FROM @SqlCmd;
EXECUTE stmt;

-- 拼凑最后的返回值，应该是SUCCESS
set ReturnValue = 'SUCCESS';

commit;
set autocommit=1;
END $$

DELIMITER ;
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－