建表：分离主键、索引、外键、AUTO_INCREMENT (separate primary key, index, foreign key, auto_increment from table creation sql exported by mysqldump)

sql_splitter.py

#!/usr/bin/python
#coding:utf-8

import os
import sys
import re

# Usage:
#     mysqldump -u$user -p$pass --no-data --databases $database > db.sql
#     ./sql_splitter.py db.sql create.sql modify.sql

if len(sys.argv) < 4:
    print >>sys.stderr, "Usage: %s <input:sql> <output:sql without index/fk/ai> <output:modification sql>"
    sys.exit(0)

f_sql = open(sys.argv[1])

all_sql = []
arr_create_table = []

in_create = False
table_name = None
create_sql = []
modify_sql = []
foreign_sql = []
increment_sql = None
use_sql = None

for line in f_sql:
    line = line.rstrip('\n')
    if line.startswith('CREATE TABLE'):
        create_sql = [line]
        modify_sql = []
        increment_sql = None
        table_name = line[line.find('`')+1:line.rfind('`')]
        #print table_name
        in_create = True
    else:
        if in_create:
            if ' AUTO_INCREMENT' in line and not line.startswith(')'):
                increment_sql = 'ALTER TABLE `%s` MODIFY COLUMN %s' % (table_name, line.rstrip(',') + ';')
                line = line.replace(' AUTO_INCREMENT', '')

            for word in ['PRIMARY KEY', 'KEY ', 'CONSTRAINT ', 'UNIQUE KEY']:
                if line.strip().startswith(word):
                    line = line.rstrip(',') + ';'
                    sql = 'ALTER TABLE `%s` ADD %s' % (table_name, line)
                    if word.startswith('CONSTRAINT'):
                        foreign_sql.append(sql)
                    else:
                        modify_sql.append(sql)
                    break
            else:
                create_sql.append(line)

            if line.startswith(')'):
                create_sql[-2] = create_sql[-2].rstrip(',')
                in_create = False
                if 'AUTO_INCREMENT' in line:
                    auto_increment = re.sub('^.*AUTO_INCREMENT=([0-9]*).*$', '\\1', line)
                else:
                    auto_increment = 0
                arr_create_table.append([table_name, create_sql, modify_sql, auto_increment])
                if increment_sql:
                    modify_sql.append(increment_sql)
                all_sql += create_sql
        else:
            all_sql.append(line)
            if line.startswith('USE'):
                use_sql = line


f_create = open(sys.argv[2], 'w')
print >>f_create, '\n'.join(all_sql)

"""
for table_name, create_sql, modify_sql in arr_create_table:
    print '\n'.join(create_sql)
    print ''
"""

f_modify = open(sys.argv[3], 'w')

print >>f_modify, use_sql + "\n"

for table_name, create_sql, modify_sql, auto_increment in arr_create_table:
    print >>f_modify, '-- table `%s`\n%s' % (table_name, '\n'.join(modify_sql))
    if auto_increment:
        print >>f_modify, 'ALTER TABLE `%s` AUTO_INCREMENT=%s;' % (table_name, auto_increment)
    print >>f_modify, ''

print >>f_modify, '\n%s\n' % ('\n'.join(foreign_sql))