12

I'm trying to run a script that deletes a bunch of rows in a MySQL (innodb) table in batches, by executing the following in a loop:

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

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

I need to keep running this loop until there's no more matching rows. But unlike running in the mysql shell, the above command does not return the number of rows affected. I've tried -v and -t but neither works. How can I find out how many rows the batch script affected?

Thanks!

ambivalence
  • 252
  • 3
  • 12
  • I think I just found the answer: http://stackoverflow.com/questions/1083866/how-to-get-number-of-rows-affected-while-executing-mysql-query-from-bash – ambivalence May 18 '10 at 01:05

2 Answers2

21

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

newtover
  • 31,286
  • 11
  • 84
  • 89
13

If you add option -vv it will produce more verbose output, which also contain an information about number of affected rows;

mysql -vv --user=MyUser --password=MyPassword MyDatabase < SQL_FILE
kormik
  • 839
  • 8
  • 18