Shell script that iterates through a sql "delete" statement

Question

I need a shell script that deletes 1000 rows from a massive database until there is no more rows left

It is very simple, but i am very weak in shell scripting, and the many tutorials online offer extremely similar, but different nuances in syntax

UPDATE:

Would it be possible to get some sample code?
I need to delete 1000 rows at a time
I have 120 Million rows to delete in total, and deleting them all at once will lock up the DB

Do you need a shell script that runs an SQL statement, or are you planning on using the shell script for logic? — Jeremy Morgan
– Jeremy Morgan, Commented May 14, 2010 at 19:01
Is it not possible to just write an SQL query that deletes all 1000 rows? — Michael Mrozek
– Michael Mrozek, Commented May 14, 2010 at 19:01
What is the question? How to write a shell script or how to execute a mysql-query from a shell script or how to delete rows? — edwin
– edwin, Commented May 14, 2010 at 19:33
Updated again - i need to delete 1000 at a time because i have 120M rows in total, and i need the DB to be up and running — meow
– meow, Commented May 14, 2010 at 20:29

xalves · Accepted Answer · 2020-09-15 15:00:24Z

2

You could also do this using just mysql:


#!/bin/sh
mysql -D <database> -u <user> -p<password> -e 'DELETE FROM <table> WHERE <condition> LIMIT 10000;'

-e - accepts a query as an argument

answered Sep 15, 2020 at 15:00

xalves

3582 silver badges19 bronze badges

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

Comments

knittl · Accepted Answer · 2010-05-14 20:24:07Z

1

you can call the mysql command line tool:

mysql -u username -p password < statements.txt

or

echo "your statement here" | mysql -u username -p password

in statements.txt put:

delete from `table` limit 1000;

although i do not understand why you only want to delete 1k at a time

a full script for sh would look like this:

#!/bin/sh
echo 'DELETE FROM `table` LIMIT 1000;' | mysql -u username -p password

answered May 14, 2010 at 20:24

knittl

269k59 gold badges339 silver badges405 bronze badges

2 Comments

meow Over a year ago

Thanks knittl! I was wondering more about how i can construct the loop? I just could not get the full script to work. I have 120 Million rows to delete in total, and deleting them all at once will lock up the DB

knittl Over a year ago

@ming: have you considered using TRUNCATE TABLE? it's much faster than using the delete statement, when you have to delete all rows from a table

Yu Jiaao · Accepted Answer · 2017-04-18 04:42:34Z

1

Just hope my script maybe help someone who search similar questions

#!/bin/sh
# mysql_delta_del.sh 
# -----------------------------

db_user="root"
db_host="127.0.0.1"
db_passwd="yourpass"
db="yourdb"
db_table="mytable"
keep_records=10000000
## start id, you can change according your db
start_id=31000000

# mysql bin's path
MYSQL="$(which mysql)"


maxid=`$MYSQL -u $db_user -h $db_host -p$db_passwd $db -e 'select max(id) from $db_table'`

maxid=`echo $maxid |cut -d" " -f2`

#keep last 10 million records
delto=`echo $maxid - $keep_records  |bc`

echo "target id: $delto"


i=$start_id 
while [ $i -lt $delto ]
do
 echo "delete to $i "

 echo "DELETE FROM $db_table where id<$i LIMIT 1000 " | mysql -u $db_user -p$db_passwd -h $db_host $db
 true $(( i=i+1000 ))

done

edited Apr 18, 2017 at 4:42

answered Apr 18, 2017 at 4:15

Yu Jiaao

4,7845 gold badges48 silver badges63 bronze badges

Collectives™ on Stack Overflow

Shell script that iterates through a sql "delete" statement

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related