0

I am trying to capture the number of deleted rows from mysql table from a bash script using --execute option, and it seems to be not working. I have gone through some questions where it mentions using -vv options, it works but not as expected. I used various combinations of -vvv --silent and --skip-column-names with mysql command and awk and sed as well but nothing seems to work.

MySQL Client is mysql Ver 8.0.39 for Linux on aarch64 (MySQL Community Server - GPL)

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

How to get number of rows affected, while executing MySQL query from bash?

How can you output UPDATE / INSERT query results when using MySQL -e,--execute?

Here is the code

deleted=$(mysql --login-path=lp1 --init-command="SET SQL_LOG_BIN = 0" -vv --silent --skip-column-names db -e "delete from table1 where column1 date_format('2022-04-01 00:00:00','%Y-%m-%d 00:00:00') and date_format('2023-03-31 23:59:59','%Y-%m-%d 23:59:59') order by column1 limit 20;")

echo ${deleted}

The statement deletes certain rows but prints an output like this

-------------- delete from table1 where column1 between date_format('2022-04-01 00:00:00','%Y-%m-%d 00:00:00') and date_format('2023-03-31 23:59:59','%Y-%m-%d 23:59:59') order by column1 limit 20 -------------- Query OK, 20 rows affected --------------  1 row in set Bye

If I add one more line to get the row_count then it gives me -1

mysql --login-path=${LP_wallet_0} -e "select row_count()"

Even if I try row_count() in the same delete query it just appends this line select row_count() -------------- 2 1 row in set Bye

I tried different combinations of --vv --silent --skip-column-names and awk and sed too but I am unable to get the count.

Improve this question

1 Answer 1

Reset to default
2

You don't need -vv if you query ROW_COUNT();.

Here's an example I just tested. I have a table mytable in my test schema, the table had 6 rows.

mysql -s -N -e "delete from mytable; select row_count()" test

Output:

6

I used -s for silent, -N for skipping column names, and I made sure to select row_count() in the same session.

Thus it outputs just 6, indicating the number of rows affected. Since the output is simply that result, it's easy to capture without needing to do any parsing with sed or awk.

If you try to query row_count() in a subsequent mysql command, it won't work, because it only reports the number of rows affected by the preceding SQL statement of the same session.

Improve this answer
3
  • it worked like magic for me @bill. Thanks tons :-D. Commented Nov 4, 2024 at 0:44
  • @AvinashPawar are you familiar with What should I do when someone answers my question? ? Commented Nov 4, 2024 at 8:24
  • oops apology, I was too eager get my scripts working and forgot to mark the answer Commented Nov 4, 2024 at 9:04

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.