MySQL command line tool: how to find out the number of rows affected by DELETE?

Question

I am trying to run a script that deletes a bunch of rows in a MySQL table (innodb) in batches by doing the following in a loop:

mysql --user=MyUser --password=MyPassword MyDatabase < SQL_FILE 

where SQL_FILE contains the DELETE FROM ... LIMIT X command.

I need to continue this loop until more matching rows are found. But unlike running in the mysql shell, the above command does not return the number of rows affected. I tried -v and -t but it does not work. How to find out how many lines in a script package are affected?

Thanks!

Answer 1

You can add SELECT ROW_COUNT(); at the end of the script package.

Answer 2

If you add the -vv option, it will output more verbose output, which also contains information about the number of lines affected;

mysql -vv --user = MyUser --password = MyPassword MyDatabase <SQL_FILE