Exporting DDL
Within MySQL Workbench, go to
Server > Data Export
and select the schema of interest:
I generally prefer to select the checkbox which states "Skip Table Data (no-data)".
This will dump the DDL only to a series of *.sql files:
Importing DDL
Open MySQL Workbench, and go to Data Import/Restore:
Importing DDL (terminal session)
Assumption: I have a schema named soundex, and a username/password with identical values.
This simple python script creates a table for each alpha character:
import string f = open('tables.ddl','w') f.write("use soundex;") for letter in string.ascii_lowercase: f.write("CREATE TABLE tb" + letter + " ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null );\n") f.close()
I run this script using
$ python generate-ddl.py
And the output looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | CREATE TABLE tba ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbb ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbc ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbd ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbe ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbf ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbg ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbh ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbi ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbj ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbk ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbl ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbm ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbn ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbo ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbp ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbq ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbr ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbs ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbt ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbu ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbv ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbw ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbx ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tby ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); CREATE TABLE tbz ( id SMALLINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), k varchar(256) not null, v varchar(256) not null ); |
And I can import this into MySQL using this command:
mysql -u soundex -psoundex -hlocalhost < tables.ddl
Within MySQL I can verify that this worked successfully:
$ mysql -u soundex -psoundex -hlocalhost Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 83 Server version: 5.5.41-0ubuntu0.14.10.1 (Ubuntu) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use soundex; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------+ | Tables_in_soundex | +-------------------+ | tba | | tbb | | tbc | | tbd | | tbe | | tbf | | tbg | | tbh | | tbi | | tbj | | tbk | | tbl | | tbm | | tbn | | tbo | | tbp | | tbq | | tbr | | tbs | | tbt | | tbu | | tbv | | tbw | | tbx | | tby | | tbz | +-------------------+ 26 rows in set (0.00 sec) mysql> describe tba; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | smallint(6) | NO | PRI | NULL | auto_increment | | k | varchar(256) | NO | | NULL | | | v | varchar(256) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql>
No comments:
Post a Comment