MySQL: How to Create A Table from A Table Schema File?

Question

I am using MySQL to capture snapshots of my data everyday, so I need to create multiple tables with the same columns.

my_foobar_table_20170125
my_foobar_table_20170126
my_foobar_table_20170127

What's the easiest way to create the tables? Would it be with a table schema file? Or, with a create table query?

I am leaning toward using a table schema file if that is possible. As it seems cleaner than a sql query to create the table.

I have googled around, and surprisingly there is no clear answer on this. In fact, it's not even obvious what exactly is a "table schema file", or how to generate this from mysql workbench, or use the schema file to create the table.

I'm not sure of Workbench's terminology but a schema file is probably just a `CREATE TABLE` statement. Does this help? http://stackoverflow.com/questions/21314477/creating-a-database-with-mysql-workbench-from-existing-schema-model — Michael Berkowski, Jan 26 '17 at 00:40

score 0 · Answer 1 · edited Nov 11 '17 at 09:07

Definitely create a table .sql file and load it into MySQL then log in. Manually making it each time is a ton of work and this allows you to also customize each table if needed, and to use drop database and to refresh easily as well.

CREATE DATABASE databasename;

USE database;

CREATE TABLE  tableofthings (
  ID INT NOT NULL AUTO_INCREMENT,
  otherdatastring VARCHAR(50)...
  PRIMARY KEY (ID)
);

...then put other tables below...

MySQL: How to Create A Table from A Table Schema File?

1 Answers1