Take the 2-minute tour ×

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?

share|improve this question
Just out of interest, why do you want to do this? –  middaparka Jan 17 '11 at 17:17
The table is a list of profile numbers that coincide with a product. In my case 0 - 199 is casing(wood trim) profiles, 200 - 299 is base profiles and so on. So I figure that it's better for each category to have it's own table. Instead of loading the entire table each time, when all I want is just the casing. Your thoughts? –  gregh Jan 17 '11 at 17:23
@ghostz00 Hard to know without knowing more about the various schemas involved. However, from what you've said the existing table is being used to store un-related items (i.e.: a casing != base profile?), so it sounds like you're doing the right thing™ in the grand scheme of things. :-) –  middaparka Jan 17 '11 at 17:26
Do NOT do this - one table is better than multiple ones. Add a type code if you need to distinguish between records. –  OMG Ponies Jan 17 '11 at 17:28
@OMG Ponies - From the sounds of it, the table is currently a bit of a dump of miscellaneous un-related entries. If this is case, why would you not recommend creating dedicated tables? –  middaparka Jan 17 '11 at 17:34

2 Answers 2

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.


  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.

share|improve this answer

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

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.

share|improve this answer

Your Answer


