Creating MySQL Tables from Views

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'
UNION (
  SELECT
    CASE
      WHEN `IS_NULLABLE` = "NO" THEN
        CONCAT("`",column_name,"` ",column_type," NOT NULL,")
      ELSE
       (CASE
          WHEN COLUMN_DEFAULT IS NOT NULL THEN
            CONCAT("`",column_name,"` ",column_type," DEFAULT ",column_default,",")
          ELSE
            CONCAT("`",column_name,"` ",column_type," DEFAULT NULL,")
        END)
    END AS 'Data'
  FROM information_schema.columns
  WHERE table_schema='testDB'
  AND table_name='table1')
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`)