MySQL -- How to create new table from existing table with same schema & index

 show create table date_table;

Output:

+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| date_table | CREATE TABLE `date_table` (

`name` varchar(50) DEFAULT NULL,

`birthdate` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- SHOW COLUMNS FROM table;

SHOW COLUMNS FROM date_table;

Output:

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| name | varchar(50) | YES | | NULL | |

| birthdate | datetime | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+

desc date_table

will also return same result as above

Copy Table with indexes:

CREATE TABLE new_table LIKE date_table;

Its important to note that when you create a copy of a

table using the LIKE clause, the new table does not inherit any

foreign key constraints or triggers from the old table.

You will need to recreate them manually if necessary.

Copy Data of Table:

To copy the data from the old table to the new table,

you can use the INSERT INTO statement with a

subquery that selects all rows from the old table:

INSERT INTO new_table(col1,col2,...,coln) SELECT col1,col2,..,coln FROM old_table;