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) | |
+-------+-------------+------+---------+---------+----------------+
This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.