mysql 如何存储过程返回记录的更新条数

时间：2009-08-24 来源：sss0213

ROW_COUNT()

ROW_COUNT() returns the number of rows updated, inserted, or deleted by the preceding statement. This is the same as the row count that the mysql client displays and the value from the mysql_affected_rows() C API function.

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)

ROW_COUNT()
ROW_COUNT()返回被前面语句升级的、插入的或删除的行数。这个行数和 mysql 客户端显示的行数及 mysql_affected_rows() C API 函数返回的值相同。以下摘自：http://yueliangdao0608.blog.51cto.com/397025/81276 今天用PREPARE动态处理了UPDATE语句后，
发现ROW_COUNT（）函数返回的老是-1 ，检查了下原来是把row_count()放到了
deallocate 语句后面了。

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 boolean)
BEGIN
  -- Determinate whether update is successful or failed.
  declare cnt int default 0;
  -- Update to new record.
  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;
  -- Get the last record.
  if cnt > 0 then
    set f_error_code = TRUE;
   end if;
END$$

DELIMITER ;

相关阅读更多 +