How to Copy MySQL Table Structure Excluding Indexes and Data?

To only copy the column attributes of another table, without copying over any indexes or data, you can do the following:

1. Use the CREATE TABLE ... SELECT statement, as it does not automatically copy indexes from the original table;
2. Specify a falsy condition in the WHERE clause of the SELECT statement, so it doesn't copy over any rows.

In this way, you will have the same column attributes, but none of the indexes or rows will be copied over.

To demonstrate this, 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)
);

INSERT INTO user(name)
VALUES ('john'), ('david'), ('john'), ('john'), ('wayne'), ('david');

You can use the MySQL/MariaDB CREATE TABLE ... SELECT statement to create an empty table with only the column attributes copied over, for example, in the following way:

CREATE TABLE user_copy (
    SELECT *
    FROM user
    WHERE 1 = 0;
);

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   |     | 0       |       |
| name  | varchar(25) | No   |     | (null)  |       |
+-------+-------------+------+-----+---------+-------+