How do I split a large table into a smaller multiple tables using mysql command line tool?

I have a mysql table that I want to split into smaller tables. I want to break up the data in ranges like 1 - 199 and 200 - 299 and put them in new tables. How would I go about doing this from the mysql command 开发者_如何学编程line tool?

The easiest way to achieve this would simply be to use mysqldump to export the existing table schema and data. (I'd recommend using the "--complete-insert" option.)

You could then manually edit the resultant file, by adding the relevant table creation statements and editing the INSERT lines to use the appropriate table name.

Incidentally:

1. Whilst it might not be relevant, beware of data being added to the original table whilst you're carrying out the editing, etc.

2. If this all sounds a bit manual and painful, that's because it is. However, you have a fairly odd requirement here.

figure out the max range, say it's 10000

for i in $(seq 0 100 10000) ; do 
   echo "create table Table$i like MyTable" | mysql mydb
   echo "insert into Table$i select * from MyTable where id >=$i and id < $(($i+100))" | mysql mydb
done

However, do not do this. There's hardly any reason for breaking up data like this, it'll only serve to make querying harder, adminstration harder, whoever(which might be you) is going to maintain your database/application will eventually go mad.