使用row_count()的一点小意外

今天测试了一个数据发布的过程，用row_count()统计影响的行数，发现直接insert结果老是-1.后来改成通过变量赋值，才显示正确的结果

DELIMITER $$

DROP PROCEDURE IF EXISTS `ad_analyse_temp`.`pro_issue_data_temp`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_issue_data_temp`()
begin

set @type=1;
insert into ad_analyse.user_subjoin
(user_id,game,server_name,date,subjoin_data1,subjoin_data2,subjoin_data3)
select user_id,game,server_name,date,subjoin_data1,subjoin_data2,subjoin_data3 from user_subjoin;

set @type=2;
if @type=2 then
insert into ad_analyse.syn_trace
(pro_name,DATA_SOURCE,date,count,err_message)
values
('pro_issue_data','user_subjoin',now(),row_count(),'successful');
else
insert into ad_analyse.syn_trace
(pro_name,DATA_SOURCE,date,count,err_message)
values
('pro_issue_data','user_subjoin',now(),0,'faild');
end if;
end$$

DELIMITER ;

 

 

DELIMITER $$

DROP PROCEDURE IF EXISTS `ad_analyse_temp`.`pro_issue_data`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_issue_data`()
begin
declare cnt int default 0;
set @type=1;
insert into ad_analyse.user_subjoin
(user_id,game,server_name,date,subjoin_data1,subjoin_data2,subjoin_data3)
select user_id,game,server_name,date,subjoin_data1,subjoin_data2,subjoin_data3 from user_subjoin;
set cnt=row_count();

-------赋值给变量
set @type=2;
if @type=2 then
insert into ad_analyse.syn_trace
(pro_name,DATA_SOURCE,date,count,err_message)
values
('pro_issue_data','user_subjoin',now(),cnt,'successful');
else
insert into ad_analyse.syn_trace
(pro_name,DATA_SOURCE,date,count,err_message)
values
('pro_issue_data','user_subjoin',now(),0,'faild');
end if;
end$$

DELIMITER ;

转载于:https://www.cnblogs.com/zeromyth/archive/2009/05/11/1454438.html