I had a need to create some MySQL tables on a new MySQL server, from some MySQL views. The idea is rather than replicating all the tables that make the data in the view, I can just replicate the view data into a new table. Two advantages: 1) Less data to replicate, 2) The data is available instantly on the destination MySQL server, rather than relying on the SQL query (that makes the view) to process.
SELECT "CREATE TABLE IF NOT EXISTS testDB.table1
(" AS 'Data'
WHEN `IS_NULLABLE` = "NO" THEN
CONCAT("`",column_name,"` ",column_type," NOT NULL,")
WHEN COLUMN_DEFAULT IS NOT NULL THEN
CONCAT("`",column_name,"` ",column_type," DEFAULT ",column_default,",")
CONCAT("`",column_name,"` ",column_type," DEFAULT NULL,")
END AS 'Data'
UNION SELECT "** ADD ANY PRIMARY KEYS/UNIQUE KEYS/INDEXES HERE OR REMOVE THE TRAILING , **" AS 'Data'
UNION SELECT ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" AS 'Data';
As the output above says, "Add any primary keys/unique keys/indexes..." as required.
Examples of primary key/unique key/index is below:
- PRIMARY KEY (`id`),
- UNIQUE KEY `idx_unique` (`subtenant`,`bucket`),
- KEY `idx_subtenant` (`subtenant`) USING BTREE, KEY `idx_bucket` (`bucket`)