MySQL copies table structure and table data

1. Preface

After the function is developed and tested locally or in a test environment, we often encounter this situation: Special test data, the test process will involve modifying the data in the table, and often the test cannot be successful. Therefore, after each test is executed, the data in the original table has actually been modified, and the data needs to be restored for the next test. .

My general approach is: first create a copy table, such as the user table used for testing. I create the copy table user_bak before testing, and after each test , clear the user table, and then import the data from the replica table user_bak into the user table.

The above operation is to back up a table. If there are too many tables involved, you can create a copy of database.

Next I will explain the table structure replication and table data replication here, is not the replication principle of the database! ! ! !

The following is the table structure of the staff table

create table staff (
    id int not null auto_increment comment '自增id',
    name char(20) not null comment '用户姓名',
    dep char(20) not null comment '所属部门',
    gender tinyint not null default 1 comment '性别：1男; 2女',
    addr char(30) not null comment '地址',
    primary key(id),
    index idx_1 (name, dep),
    index idx_2 (name, gender)
) engine=innodb default charset=utf8mb4 comment '员工表';

2. Specific method

##2.1. Execute the creation SQL of the old table to create the table

If the original table already exists, you can use the command to view the creation statement of the table:

mysql> show create table staff\G
*************************** 1. row ***************************
       Table: staff
Create Table: CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` char(20) NOT NULL COMMENT '用户姓名',
  `dep` char(20) NOT NULL COMMENT '所属部门',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别：1男; 2女',
  `addr` char(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`name`,`dep`),
  KEY `idx_2` (`name`,`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表'
1 row in set (0.01 sec)

As you can see, the above

In the command execution result of show creat table xx, the value of Create Table is the statement that creates the table. At this time, you can directly copy the SQL that creates the table and then execute it again.

When there is data in the data table, the sql used to create the staff table will be slightly different. For example, I added two records in staff:

mysql> insert into staff values (null, '李明', 'RD', 1, '北京');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into staff values (null, '张三', 'PM', 0, '上海');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from staff;
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
2 rows in set (0.00 sec)

At this time, I am executing the show create table command:

mysql> show create table staff\G
*************************** 1. row ***************************
       Table: staff
Create Table: CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` char(20) NOT NULL COMMENT '用户姓名',
  `dep` char(20) NOT NULL COMMENT '所属部门',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别：1男; 2女',
  `addr` char(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`name`,`dep`),
  KEY `idx_2` (`name`,`gender`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='员工表'
1 row in set (0.00 sec)

Note that the penultimate line in the above result is

ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='员工表'

Because the id of the staff table is auto-increasing and there are already 2 records, the auto-incrementing id of the next inserted data should be 3. This information will also appear in the table creation sql.

2.2. Use like to create a new table (only including table structure)

Use like to create a new table based on an existing table. The features are as follows:

1. Convenience, no need to check the table structure definition information of the original table;

2. In the created new table, the table structure definition and integrity constraints are consistent with the original table.

3. The new table created is an empty table, a brand new table with no data.

The usage is as follows:

mysql> select * from staff;　　#旧表中已有2条数据
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
2 rows in set (0.00 sec)
 
mysql> create table staff_bak_1 like staff;  # 直接使用like，前面指定新表名，后面指定旧表（参考的表）
Query OK, 0 rows affected (0.02 sec)
 
mysql> show create table staff_bak_1\G
*************************** 1. row ***************************
       Table: staff_bak_1
Create Table: CREATE TABLE `staff_bak_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` char(20) NOT NULL COMMENT '用户姓名',
  `dep` char(20) NOT NULL COMMENT '所属部门',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别：1男; 2女',
  `addr` char(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`name`,`dep`),
  KEY `idx_2` (`name`,`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表'  # 注意没有AUTO_INCREMENT=3
1 row in set (0.00 sec)
 
mysql> select * from staff_bak_1; # 没有包含旧表的数据
Empty set (0.00 sec)

2.3. Use as to create a new table (containing data)

Use as to create The new table has the following characteristics:

1. You can selectively decide which fields the new table contains;

2. The new table created will contain the data of the old table. ;

3. The new table created will not contain the integrity constraints of the old table (such as primary keys, indexes, etc.), but only the most basic table structure definition.

The usage is as follows:

mysql> create table staff_bak_2 as select * from staff;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from staff_bak_2;
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
2 rows in set (0.00 sec)
 
mysql> show create table staff_bak_2\G
*************************** 1. row ***************************
       Table: staff_bak_2
Create Table: CREATE TABLE `staff_bak_2` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT '自增id',
  `name` char(20) CHARACTER SET utf8mb4 NOT NULL COMMENT '用户姓名',
  `dep` char(20) CHARACTER SET utf8mb4 NOT NULL COMMENT '所属部门',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别：1男; 2女',
  `addr` char(30) CHARACTER SET utf8mb4 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

When using as to create a table, the integrity constraints are not retained. In fact, you can understand this if you think about it carefully. Because when you use as to create a table, you can specify which fields the new table contains. If you ignore several fields when you create a new table, even if the integrity constraints are retained, the integrity constraints cannot be met when saving the data.

For example, the staff table has an index idx1, which is composed of name and dep fields; but in the new table I created, there are no name and dep fields (only other fields are selected), then in the new table There is no need to save idx1, right?

mysql> --  只选择id、gender、addr作为新表的字段，那么name和dep组成的索引就没必要存在了
mysql> create table staff_bak_3 as (select id, gender, addr from staff);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> show create table staff_bak_3\G
*************************** 1. row ***************************
       Table: staff_bak_3
Create Table: CREATE TABLE `staff_bak_3` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT '自增id',
  `gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别：1男; 2女',
  `addr` char(30) CHARACTER SET utf8mb4 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
mysql> select * from staff_bak_3;
+----+--------+--------+
| id | gender | addr   |
+----+--------+--------+
|  1 |      1 | 北京   |
|  2 |      0 | 上海   |
+----+--------+--------+
2 rows in set (0.00 sec)

2.4. Use like insert select to create a copy of the original table (recommended)

Use like to create a new table, although the old table is retained Various table structure definitions and integrity constraints, but how to import data from the old table into the new table?

The most extreme way: write a program to first read the old table data, and then write it to the new table. I will not try this method.

There is a relatively simple command:

mysql> select * from staff; #原表数据
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
2 rows in set (0.00 sec)
 
mysql> select * from staff_bak_1; # 使用like创建的表，与原表相同的表结构和完整性约束（自增除外）
Empty set (0.00 sec)
 
mysql> insert into staff_bak_1 select * from staff;  # 将staff表的所有记录的所有字段值都插入副本表中
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from staff_bak_1;
+----+--------+-----+--------+--------+
| id | name   | dep | gender | addr   |
+----+--------+-----+--------+--------+
|  1 | 李明   | RD  |      1 | 北京   |
|  2 | 张三   | PM  |      0 | 上海   |
+----+--------+-----+--------+--------+
2 rows in set (0.00 sec)

In fact, this SQL statement knows that the table structure and integrity constraints of the two tables are the same, so you can select * directly.

insert into staff_bak_1 select * from staff;

If the structures of the two tables are not the same, this method can also be used. For example:

mysql> show create table demo\G
*************************** 1. row ***************************
       Table: demo
Create Table: CREATE TABLE `demo` (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `_name` char(20) DEFAULT NULL,
  `_gender` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
 
# 只将staff表中的id和name字段组成的数据记录插入到demo表中，对应_id和_name字段
mysql> insert into demo (_id, _name) select id,name from staff;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from demo;
+-----+--------+---------+
| _id | _name  | _gender |
+-----+--------+---------+
|   1 | 李明   |       1 |
|   2 | 张三   |       1 |
+-----+--------+---------+
2 rows in set (0.00 sec)

This is a situation where the number of fields in the two tables is different. At this time, you need to manually specify the columns. name, otherwise an error will be reported.

In addition, if the number of fields in the two tables and the field types in the same order are the same, if all fields are copied, even if the field names are different, they can be copied directly:

# staff_bak_5的字段名与staff表并不相同，但是字段数量、相同顺序字段的类型相同，所以可以直接插入
mysql> show create table staff_bak_5\G
*************************** 1. row ***************************
       Table: staff_bak_5
Create Table: CREATE TABLE `staff_bak_5` (
  `_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `_name` char(20) NOT NULL COMMENT '用户姓名',
  `_dep` char(20) NOT NULL COMMENT '所属部门',
  `_gender` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别：1男; 2女',
  `_addr` char(30) NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `idx_1` (`_name`,`_dep`),
  KEY `idx_2` (`_name`,`_gender`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='员工表'
1 row in set (0.00 sec)
 
mysql> insert into staff_bak_5 select * from staff;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from staff_bak_5;
+-----+--------+------+---------+--------+
| _id | _name  | _dep | _gender | _addr  |
+-----+--------+------+---------+--------+
|   1 | 李明   | RD   |       1 | 北京   |
|   2 | 张三   | PM   |       0 | 上海   |
+-----+--------+------+---------+--------+
2 rows in set (0.00 sec)

推荐 《mysql视频教程》  

The above is the detailed content of MySQL copies table structure and table data. For more information, please follow other related articles on the PHP Chinese website!