How to Copy MySQL Table Structure and Indexes?

You can use the MySQL/MariaDB CREATE TABLE ... LIKE statement to create an empty table based on the structure of another table.

Let's suppose you have the following MySQL/MariaDB table:

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(25) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT UQ_Name UNIQUE (name)
);

To copy its table structure (i.e. column attributes and indexes as defined in the original table), you can use the following statement:

CREATE TABLE user_copy LIKE user;

Please note that while this would recreate the indexes from the original table, it would not copy any triggers, stored procedures, foreign key constraints, or DATA DIRECTORY or INDEX DIRECTORY table options.

To check the columns and indexes in the new table, you can use any of the following statements (which are equivalent):

DESCRIBE user_copy;

SHOW COLUMNS FROM user_copy;

It would produce an output like the following:

+-------+-------------+------+---------+---------+----------------+
| Field |     Type    | Null |   Key   | Default |      Extra     |
+-------+-------------+------+---------+---------+----------------+
| id    | int(11)     | No   | PRIMARY | (null)  | auto_increment |
| name  | varchar(25) | No   | UNIQUE  | (null)  |                |
+-------+-------------+------+---------+---------+----------------+