Create a table in MySQL that matches another table?-Mysql Tutorial-php.cn

Create a table in MySQL that matches another table?

PHPz

Release： 2023-08-23 19:13:09

forward

1333 people have browsed it

Create a table in MySQL that matches another table?

To create a table in MySQL that matches with another table, use CREATE TABLE command with LIKE operator. The syntax is as follows −

create table yourNewTableName like yourOldTableName;

Copy after login

The above syntax creates structure of the table.

If you want all records then use INSERT INTO…...SELECT *FROM command. The syntax is as follows −

insert into yourNewTableName select *from yourOldTableName.

Copy after login

I have an old table and some data −

mysql> create table WholeWordMatchDemo
   &minus;> (
   &minus;> Words varchar(200)
   &minus;> );
Query OK, 0 rows affected (0.84 sec)

Copy after login

First, we will create a table structure. The query is as follows −

mysql> create table NewTableDuplicate Like WholeWordMatchDemo;
Query OK, 0 rows affected (0.62 sec)

Copy after login

Now you can check the table structure has been created or not with the help of show command. The query is as follows −

mysql> show create table NewTableDuplicate;

Copy after login

The following is the output −

+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| NewTableDuplicate | CREATE TABLE `newtableduplicate` (`Words` varchar(200) DEFAULT NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Copy after login

Copy all records in the new table with the name ‘NewTableDuplicate’. The query is as follows to copy all the data into new table −

mysql> insert into NewTableDuplicate select *from WholeWordMatchDemo;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0

Copy after login

Now you can check all records are present in the new table or not with the help of SELECT statement. The query is as follows −

mysql> select *from NewTableDuplicate;

Copy after login

The following is the output −

+----------------------+
| Words                |
+----------------------+
| My Name is John      |
| Carol                |
| My Name is Johnson   |
+----------------------+
3 rows in set (0.00 sec)

Copy after login

Check whether the old table has the same records or not −

mysql> select *from WholeWordMatchDemo;

Copy after login

The following is the output −

+----------------------+
| Words                |
+----------------------+
| My Name is John      |
| Carol                |
| My Name is Johnson   |
+----------------------+
3 rows in set (0.00 sec)

Copy after login

The above is the detailed content of Create a table in MySQL that matches another table?. For more information, please follow other related articles on the PHP Chinese website!