Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
建表:分离主键、索引、外键、AUTO_INCREMENT (separate primary key, index, foreign key, auto_increment from table creation sql exported by mysqldump)
#!/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))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.