通过存储过程（MySQL）添加变更操作日志

添加变更操作日志

先上代码
创建存储过程代码如下：

DROP PROCEDURE IF EXISTS p_common_chgLog;create procedure  p_common_chgLog(IN   _t_name VARCHAR(600),##变更记录表名称    _ctype TINYINT(4),##变更操作 更新0，删除1，注销2    _uniqueKey VARCHAR(100),##标识字段 名称    _uniqueValue VARCHAR(200),##标识字段 值    _cOperatorIp VARCHAR(50),##操作人IP    _cuid BIGINT(20),##操作人id    _cpageName VARCHAR(300)##变更页面名称)BEGINDECLARE  _colname varchar(300) DEFAULT ''; #当前游标字段名DECLARE  _colname_list varchar(1000);#源表去掉row_id 的字段 集DECLARE  _colname_list_c varchar(1000);#select字段 集DECLARE  _colname_list_d varchar(1000);#VALUES 字段 集DECLARE  sqlcmd longtext; #预处理的SQL语句DECLARE I INT DEFAULT 0; #DECLARE _cDeleted INT;##判断源表是否有deleted字段DECLARE  source_t_name VARCHAR(100) DEFAULT  REVERSE(substring(REVERSE(_t_name),LOCATE('_',REVERSE(_t_name))+1)); #根据 变更操作表名 获得源表名称（变更表去掉最后一个'_'后面内容后的表名）    DECLARE rec_columnName CURSOR FOR  select COLUMN_NAME from information_schema.columns where TABLE_SCHEMA = 'pointrepository' AND table_name=source_t_name; #创建_t_name表字段名的游标    DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  _colname = '13';   #针对NOT FOUND的 异常处理    OPEN rec_columnName; #打开游标    LOOP_LABLE:LOOP  #创建LOOP_LABLE循环        SET I = I+1;    FETCH rec_columnName INTO _colname; #获取游标当前指针的记录        IF I=1 THEN            SET _colname_list = _colname;        ELSEIF _colname THEN            LEAVE LOOP_LABLE; #离开LOOP_LABLE循环            SET I=0;        ELSE             SET _colname_list =CONCAT(_colname_list,',',_colname);        END IF;    END LOOP LOOP_LABLE; #结束LOOP_LABLE循环    CLOSE rec_columnName; #关闭游标    SET _colname_list = REPLACE(_colname_list,'row_id,',''); #去掉row_id    SET _colname_list_c = CONCAT(_colname_list,',c_u_id,c_type,c_operator_ip,c_pageName');#增加 登录账号id，变更类型，变更操作功能名（select字段名）     SET _colname_list_d = CONCAT_WS(',',_colname_list,'?','?','?','?');#（VALUES 字段名）    ##SET @source_t_name = REVERSE(substring(REVERSE(_t_name),LOCATE('_',REVERSE(_t_name))+1)); #根据 变更操作表名 获得源表名称（变更表去掉最后一个'_'后面内容后的表名）    SET _cDeleted = FIND_IN_SET('deleted',_colname_list_c);  IF _cDeleted = 0 THEN ##判断源表是否有deleted字段    SET @sqlcmd = CONCAT('INSERT INTO ',_t_name,' (',_colname_list_c,') (SELECT ',_colname_list_d,' FROM ',source_t_name,' where ',_uniqueKey,'= ? LIMIT 1 );');#设计insert语句    ELSE    SET @sqlcmd = CONCAT('INSERT INTO ',_t_name,' (',_colname_list_c,') (SELECT ',_colname_list_d,' FROM ',source_t_name,' where ',_uniqueKey,'= ? and deleted=0 LIMIT 1);');#设计insert语句    END IF;    SET @cuid = _cuid;    SET @ctype = _ctype;    SET @cOperatorIp=_cOperatorIp;    SET @cpageName = _cpageName;##  SET @rowId = _rowId;    SET @uniqueValue = _uniqueValue;    PREPARE  insert_chgLog FROM  @sqlcmd;#使用PREPARE 准备一个SQL语句    EXECUTE insert_chgLog USING @cuid,@ctype,@cOperatorIp,@cpageName,@uniqueValue;#执行预处理语句    DEALLOCATE PREPARE  insert_chgLog;/*删除PREPARE定义*/  select @sqlcmd;END;

MySQL命令行调用方法：
call p_common_chgLog（变更记录表名称，变更操作，标识字段（名称），标识字段（值），操作人IP，操作人ID，变更页面名称）；

call p_common_chgLog('t_company_info_chglog',0,'ci_id','1035','192.168.1.2',10010,'组织管理->公司修改');

关键代码分析

1、

根据 变更操作表名 获得源表名称（变更表去掉最后一个’_’后面内容后的表名）
如：根据t_company_info_chglog（公司信息变更记录表）获得源表source_t_name=t_company_info

DECLARE  source_t_name VARCHAR(100) DEFAULT  REVERSE(substring(REVERSE(_t_name),LOCATE('_',REVERSE(_t_name))+1));

2、

声明源表列名的游标为rec_columnName

DECLARE rec_columnName CURSOR FOR  select COLUMN_NAME from information_schema.columns where TABLE_SCHEMA = 'pointrepository' AND table_name=source_t_name;

游标值为：

select COLUMN_NAME from information_schema.columns where TABLE_SCHEMA = 'databaseName' AND table_name=source_t_name;

这条sql语句是根据“表名”获取表所有字段名，如果获取的所有字段是放在一行或者一个字段里，处理起来要容易点，但获取到的是一列结果，所以需要用游标处理；TABLE_SCHEMA 为数据库名，table_name为表名
结果类似这样：

information_schema是MySQL元数据库（其他术语包括“数据词典”和“系统目录”），元数据–关于数据的数据。COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
来源：元数据库（information_schema）.表介绍

3、

游标处理：循环把字段名添加到_colname_list，_colname_list最后为完整的源表所有字段值；
类似于：row_id，ci_id，ci_link_man，ci_telephone，ci_fax，ci_mobile，ci_email，ci_address，ci_company_no，ci_company_name，ci_company_address

4

、在源表的基础上添加变更记录表多出的字段名
如：#增加 登录账号id，变更类型，变更操作功能名（select字段名）

SET _colname_list_c = CONCAT(_colname_list,',c_u_id,c_type,c_operator_ip,c_pageName')

5、

把之前拼接好的columns和values 合并，再拼接为insert语句

SET @sqlcmd = CONCAT('INSERT INTO ',_t_name,' (',_colname_list_c,') (SELECT ',_colname_list_d,' FROM ',source_t_name,' where ',_uniqueKey,'= ? LIMIT 1 );');#设计insert语句

6、

使用PREPARE 准备一个SQL语句

PREPARE  insert_chgLog FROM  @sqlcmd;#使用PREPARE 准备一个SQL语句

7、

值为调用存储过程时输入的参数

EXECUTE insert_chgLog USING @cuid,@ctype,@cOperatorIp,@cpageName,@uniqueValue;#执行预处理语句

8、

DEALLOCATE PREPARE  insert_chgLog;/*删除PREPARE定义*/

预处理语句

MySQL官方将prepare、execute、deallocate统称为PREPARE STATEMENT。
我习惯称其为【预处理语句】
预处理语句的SQL语法基于三个SQL语句：
*PREPARE stmt_name FROM preparable_stmt;

EXECUTE stmt_name [USING @var_name [, @var_name] …];

{DEALLOCATE | DROP} PREPARE stmt_name;*

PREPARE语句：用于预备一个语句，并赋予它名称stmt_name，借此在以后引用该语句。语句名称对案例不敏感。preparable_stmt可以是一个文字字符串，也可以是一个包含了语句文本的用户变量。‘?’字符可以被用于制作参数，以指示当您执行查询时，数据值在哪里与查询结合在一起。参数制作符只能被用于数据值应该出现的地方，不用于SQL关键词和标识符等。

EXECUTE语句：在预备了一个语句后，您可使用一个EXECUTE语句。（该语句引用了预制语句名称）来执行它。如果预制语句包含任何参数制造符，则您必须提供一个列举了用户变量（其中包含要与参数结合的值）的USING子句。参数值只能有用户变量提供，USING子句必须准确地指明用户变量。用户变量的数目与语句中的参数制造符的数量一样多。

DEALLOCATE PREPARE：对一个预制语句解除分配，需使用DEALLOCATE PREPARE语句。尝试在解除分配后执行一个预制语句会导致错误。如果您终止了一个客户端会话，同时没有对以前已预制的语句解除分配，则服务器会自动解除分配。

以下SQL语句可以被用在预制语句中：CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE和多数的SHOW语句。目前不支持其它语句。
来源：预处理语句语法

整个逻辑–以上，该存储过程主要有三个技术点：
1>获取表的所有列；
2>循环处理列名需要的SQL数据格式；
3>把拼接好的insert语句通过预处理语句，在存储过程中执行；

写在后面：

对于处理变更记录（日志数据）的问题，可能有更好的解决方案，我的方法在这里为大家提供一种解决方式的参考。