Get affected rows but no execute SQL in MySQL

Question

Assume we have a SQL DELETE FROM sys_user WHERE id = '1001', if execute with MySQl got things like:

Query OK, 1 row affected...

And the recored with id 1001 deleted, how can I get the number for a SQL affected without really execute it? For the previous SQL, without delete the record and got 1 row affected.

Answer 1

You have a couple of options:

1)

Any UPDATE / DELETE query you can run has a WHERE conditional, so instead of running the UPDATE or DELETE you can drop in a SELECT COUNT(*) replacement so you get the numbers of rows MySQL finds when that query is run.

2)

You can run a MySQL transaction getting a counter of the number of affected rows and then rollback that transaction, undoing these changes.

*

Depending on the complexity of both the tables (foreign keys, etc.) and the SQL itself, there might be edge cases where one of both the above doesn't work in that particular instance.

See also....

Answer 2

Or, you can run a SELECT instead:

SELECT COUNT(*) FROM sys_user WHERE id = '1001';

This returns the number of rows that match.

Note: This doesn't take triggers in count, which might affect the number of rows affected by the DELETE.