How to duplicate a MySQL table, indices, and data?

To duplicate a MySQL table, including its indices and data, you can use the CREATE TABLE statement with the SELECT statement.

Here’s an example of how to duplicate a MySQL table named original_table to a new table named duplicate_table:

CREATE TABLE duplicate_table SELECT * FROM original_table;

This will create a new table named duplicate_table with the same structure as original_table, including all of the indices and constraints. It will also copy all of the data from original_table into duplicate_table.

If you want to create a new table with a different name but the same structure and indices as an existing table, you can use the SHOW CREATE TABLE statement to get the CREATE TABLE statement for the original table, and then modify the table name in the CREATE TABLE statement as needed.

For example:

SHOW CREATE TABLE original_table;

/* The output will be something like this:

CREATE TABLE `original_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

*/

To create a new table with the same structure and indices as original_table, you can use the CREATE TABLE statement from the SHOW CREATE TABLE output, but replace original_table with the new table name:

CREATE TABLE new_table (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4