Possible to programmatically ALTER multiple MySQL tables?

Question

I have multiple MySQL tables with names of the form "Shard_0", "Shard_1", "Shard_2" ... "Shard_n" All of them have identical table structure. They all live in the same database.

Say I want to add a column to all those tables. Is there a way to do that programmatically?

Something like:

# pseudo code    
for i in range(n):
    tablename = "shard_"+str(i)
    ALTER TABLE tablename ...

Is it possible to do something like that? If so what language and/or library do I need?

Thanks

Cross-posting does not reflect well upon oneself, since all it does is show everyone that one has no idea of what they're doing. — Ignacio Vazquez-Abrams, Oct 27 '10 at 06:43

score 2 · Accepted Answer · answered Oct 27 '10 at 08:41

You could do this with a bash script and a pipe

#!/bin/bash
DBNAME="YourDatabaseName"

TNAME=shard_
TVAL="0"

echo "USE $DBNAME;"
while [ $TVAL -lt 10 ]
do

    echo "ALTER TABLE $TNAME$TVAL ...;"
    TVAL=$[$TVAL+1]
done

and to use it

./scriptname | mysql -u user -p

Possible to programmatically ALTER multiple MySQL tables?

1 Answers1