CODEWITHSUNDEEP
mysqllinuxbashshell
ranj1185
ranj1185

Reputation: 73

Script which searches for tables matching a pattern and then truncates them

I want to create a script which searches for tables matching a pattern and then truncates them.

I have logged into mysql and ran the following which gives the correct tables: 

SELECT concat('TRUNCATE TABLE `', TABLE_NAME, '`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'weather%';

However I am not sure how to embedd this into a shell script which would output the SQL like so: 

TRUNCATE TABLE `weather`;
TRUNCATE TABLE `weathermonth`;
TRUNCATE TABLE `weathermonthlp`;

and then execute the statements.

Thanks

Upvotes: 2

Views: 134

Answers (2)

janos
janos

Reputation: 124648

To run MySQL commands from the shell, you can pipe to the standard input of mysql, for example:

mysql << 'EOF'
SELECT concat('TRUNCATE TABLE `', TABLE_NAME, '`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'weather%';
EOF

Now, your MySQL commands produce further MySQL commands... If you want to execute them, you could again pipe to mysql, also using the -s flag to suppress non-query output produced by MySQL itself in the process, like this:

mysql -s << 'EOF' | mysql
SELECT concat('TRUNCATE TABLE `', TABLE_NAME, '`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'weather%';
EOF

Note that << 'EOF' is a here-document whose content is taken literally, so that you don't need to escape any special characters in the MySQL commands.

Upvotes: 1

bocian85
bocian85

Reputation: 130

You can start with something like this, and add more code if needed, for exmaple the database parameter. Also be sure to use quotes when invoking search with spaces.

./truncate.sh "some search with spaces if its even possible to have space in table name"

#!/bin/bash
searchpattern="$1"

table_list=$(mysql -BN -e "SELECT CONCAT(TABLE_SCHEMA,'.', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '$searchpattern%';")
echo $table_list;
IFS=" "

while read i; do

    echo "TRUNCATE TABLE $i;"

done < <(echo $table_list)

that script should output the sql, but you need to verify it before running

Upvotes: 1

Related Questions