2

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.

Improve this question
3
  • What output does the command give you when running it manually? (Please edit your question and provide an example of the output.) Commented Apr 13, 2010 at 9:16
  • If the query was successful, then the output will be something like Query OK, 1 row affected (0.06 sec) Commented Apr 13, 2010 at 9:18
  • Duplicate: stackoverflow.com/questions/1083866/… Commented Apr 13, 2010 at 9:35

3 Answers 3

Reset to default
5

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi Felix, when running the command from the shell with the -e option i don't get any output, so i don't get 'Query OK, 1 row affected (0.06 sec)'
@simonlord: Oops didn't know that... let me think again.
Cheers for the link Felix - tried searching for answers earlier - guess i didn't use the right keywords.
1

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

Improve this answer

Comments

0 
 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.

Improve this answer

1 Comment

When I google for mysql_CountRow I get only one result: your answer ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.