mysqli - How to find out which rows were affected after UPDATE statement inside MySQL procedure? -

mysqli - How to find out which rows were affected after UPDATE statement inside MySQL procedure? -

how find out affected rows after using update statements within procedure in mysql?(without using api mysql_affected_rows()). thanks!

drop procedure if exists users_login; delimiter // create procedure users_login(in _id int unsigned) begin declare _error tinyint default false; declare go on handler sqlexception set _error = true; update users set login = now() id = _id; if (_error = true) show errors; end if; end// delimiter ;

i want create check within of procedure.

you don't need procedure. produces unnecessary overhead.

to reply question. if want know, should select first, check rows affected. if you're using innodb (it supports transactions) instead of myisam, can lock rows before updating, concurrent sessions don't alter rows between select , update statement. should in transaction, this:

start transaction; select whatever your_table foo = 'bar' update; update your_table set whatever = 'new_value' foo = 'bar'; commit;

when transaction finished commit;, lock released again.

if don't want select first, can utilize variables store primary keys of affected rows.

(a bit hacky , not tested, should work)

set @affected_ids := null; update your_table set whatever = 'new_value', primary_key_column = if(@affected_ids := concat_ws(',', primary_key_column, @affected_ids), primary_key_column, primary_key_column) foo = 'bar';

it works this. if() function has syntax

if(<boolean expression>, <then>, <else>)

in <boolean expression> part, add together current rows primary key variable. true. nonetheless update primary key primary key, specify in both true , false part. value doesn't alter , mysql in fact smart plenty not touch it.

after update statement

select @affected_ids;

and rows updated.

mysql mysqli