To only copy the column attributes of another table, without copying over any indexes or data, you can do the following:
- Use the
CREATE TABLE ... SELECT
statement, as it does not automatically copy indexes from the original table; - Specify a falsy condition in the
WHERE
clause of theSELECT
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) | | +-------+-------------+------+-----+---------+-------+
Hope you found this post useful. It was published . Please show your love and support by sharing this post.