#ROW_COUNT()返回被前面语句升级的、插入的或删除的行数。
#这个行数和 mysql 客户端显示的行数及 mysql_affected_rows() C API 函数返回的值相同。
use test;
create table t(id int,NAME varchar(200),addr varchar(200));
INSERT INTO t VALUES(1),(2),(3);
#未查询到dml的行数，应该是数据已经提交导致
SELECT ROW_COUNT();

DELETE FROM t WHERE id IN(1,2);

SELECT ROW_COUNT();
----------------------------------------------
#调用procedure，f_error_code为输出参数
#  if char_length(f_str2) != 0 then的参数传递时需要加3个单引号，应该是bug
call `sp_test_prepare`(1,'un1','ustr11','''ustr2''',@f_error_code);
call `sp_test_prepare`(1,'n1','str','''str2''',@f_error_code);

SELECT ROW_COUNT();
SELECT @f_error_code;

create table lk5(id int,name varchar(100),str varchar(100),str2 varchar(100));
truncate lk5;
insert into lk5 values(1,'n1','str11','str21'),(2,'n2','str12','str22');
select * from lk5;
------------
#定义分隔符
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_test_prepare` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test_prepare`(
  IN f_id int,
  IN f_name varchar(64), 
  IN f_str varchar(255),
  IN f_str2 varchar(255),
  OUT f_error_code int
)
BEGIN
  declare cnt int default 0;
  set @stmt = concat('update lk5 set `name` =''',f_name,''', str = ''',f_str,'''');
  set f_error_code = FALSE;
  if char_length(f_str2) != 0 then
    set @stmt = concat(@stmt,', str2 = ',f_str2);
  end if;
  set @stmt = concat(@stmt, ' where id = ',f_id);
  prepare s1 from @stmt;
  execute s1;
  -- Must be above of the deallocate statement.
  -- 只能放在这里才能显示出正确的结果。
  set cnt = row_count();
  deallocate prepare s1;
  if cnt > 0 then
    set f_error_code =cnt
; end if; END$$ #DELIMITER ;