CODEWITHSUNDEEP
mysqlshell
simonlord
simonlord

Reputation: 4367

How to get number of rows deleted from mysql in shell script

I can't work out how to get the mysql client to return the number of rows deleted to the shell when running a delete. Does anyone know what option will enable this? Or ways around it?

Here's what i'm trying, but i get no output:

#!/bin/bash
deleted=`mysql mydb -e "delete from mytable where insertedtime < '2010-04-01 00:00:00'"|tail -n 1`

I was expecting something like this as the output from mysql:

deleted
999999

Which is why i have the tail -n 1 so i only pick up the count and not the column name.

When running the command by hand (mysql mydb -e "delete from mytable where insertedtime < '2010-04-01 00:00:00'") there is no output. When running the command interactively when running the mysql client i ge the following: 

mysql>delete from mytable where insertedtime < '2010-04-01 00:00:00';
Query OK, 0 rows affected (0.00 sec)

I want to get the rows affected count into my shell variable.

Any help would be most appreciated.

Upvotes: 2

Views: 9409

Answers (3)

Anders B
Anders B

Reputation: 3457

add "-vv"

mysql mydb -e "delete from mytable where insertedtime < '2010-04-01 00:00:00' -vv

https://dba.stackexchange.com/questions/23527/how-can-you-output-update-insert-query-results-when-using-mysql-e-execute

Upvotes: 1

Felix Kling
Felix Kling

Reputation: 816364

This question was asked previously: How to get number of rows affected, while executing MySQL query from bash?

Upvotes: 5

Badr
Badr

Reputation: 10648

 deleted=`mysql mydb -e "delete from mytable where insertedtime < '2010-04-01 00:00:00'"|tail -n 1`


 int icount = mysql_CountRow(deleted);

it works for me try this.

Upvotes: 0

Related Questions