65

I know how one can execute MySQL queries / commands from bash:

mysql -u[user] -p[pass] -e "[mysql commands]"

or 

mysql -u[user] -p[pass] `<<`QUERY_INPUT

[mysql commands]

QUERY_INPUT

How can I capture how many rows were affected by the query?
I tried doing:

variable='`mysql -u[user] -p[pass] -e "[mysql commands]"`'

It does execute the command but it does not return the number of affected rows.

Pang
  • 9,564
  • 146
  • 81
  • 122
florin.bunau
  • 1,875
  • 3
  • 17
  • 25

3 Answers3

97

Put

SELECT ROW_COUNT();

as the last statement in your batch and parse the output

Ken Keenan
  • 9,818
  • 5
  • 32
  • 49
  • Yes, this does work, and is more elegant and easier to parse the output. Thank you it was very helpful. – florin.bunau Jul 05 '09 at 11:38
  • 2
    This was introduced in MySQL 5. If you're stuck using 4.x you'll have to parse the output – Mark Baker Mar 31 '11 at 13:38
  • Use @florin.bunau's answer, it's better for seeing how many rows are updated/inserted/removed. – anthumchris Feb 21 '15 at 00:09
  • 1
    I suggest you add the `-s` (silent) before the `-e`, as well as `-N` to remove the header row. You should get the actual numeric value only from this. See this: http://stackoverflow.com/questions/25539218/parse-the-output-of-a-mysql-command-into-a-variable-in-a-bash-script – Oliver Williams Oct 05 '16 at 00:04
33

I might have answered myself the question, been looking at the parameters, and aparently using "-v -v -v" as parameters to the mysql command forces it to be more verbose and it spits out how many rows where affected.

florin.bunau
  • 1,875
  • 3
  • 17
  • 25
  • 7
    `-vvv` is great if you want more details, which includes query execution time. – Qtax Feb 03 '14 at 16:29
  • @Qtax, Is there any difference between `-v` and `-vvv`? – Pacerier Mar 30 '15 at 05:27
  • 1
    @Pacerier, yes they are different. For each `v` you add you get increased verbosity and more information (like query execution times, etc). – Qtax Mar 30 '15 at 07:53
  • 1
    @Qtax, Weird, I seem to be getting the same output regardless of the number of `v`s. What about `-vvv` vs `-v -v -v` vs `-v -vv` vs `-vv -v`? – Pacerier Apr 05 '15 at 17:33
4

Not an answer, but useful addition, you also could try the other MySQL information functions ( which include ROW_COUNT() ) to give you specific information you require. See MySQL reference here

Jason
  • 393
  • 1
  • 5
  • 18
  • 1
    How does this help if i run an update query and want to see how many rows have changed? – tobyodavies Jan 05 '11 at 02:42
  • There is no "verbose dump" anywhere in any of the current answers or the question... – tobyodavies Jan 05 '11 at 02:48
  • If you looked at the function reference in that link ROW_COUNT() is one of the functions and it states that it provides "The number of rows updated". So why the down vote? – Jason Jan 05 '11 at 02:49
  • @tobyodavies - my comment about verbose dump was from theBlinker's answer above "using "-v -v -v" as parameters to the mysql command forces it to be more verbose". – Jason Jan 05 '11 at 02:51
  • ok, i didn't actually see the row_count function last time i looked - i thought you linked to a page without the only function that answered the question. can't un -1 unless u edit – tobyodavies Jan 05 '11 at 03:56