Shulou(Shulou.com)06/01 Report--

This article is to share with you about the table structure and table data of MySQL database. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

1. Preface

After the functional development is completed, when testing in the local or test environment, we often encounter this situation: there is special test data, the testing process will involve modifying the data in the table, and the test is often not successful at one time, so, after each test, the data in the original table has actually been modified, and the next test will need to restore the data.

My general practice is: first create a replica table, such as the user table used in the test, I create the replica table user_bak before testing, after each test, empty the user table, and then import the data of the replica table user_bak into the user table.

The above operation is to make a backup of a table. If too many tables are involved, you can create a copy of the database.

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

Here is the table structure of the staff table

Create table staff (id int not null auto_increment comment 'add id', name char (20) not null comment' user name', dep char (20) not null comment 'Department', gender tinyint not null default 1 comment 'gender: 1 male; 2 female', addr char (30) not null comment 'address', primary key (id), index idx_1 (name, dep), index idx_2 (name, gender) engine=innodb default charset=utf8mb4 comment 'employee Table'

2. Specific ways

2.1.Performing the SQL of creating the old table to create the table

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

Mysql > show create table staff\ Graph * 1. Row * * Table: staffCreate Table: CREATE TABLE `room` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-added id', `name` char (20) NOT NULL COMMENT' user name', `dep`char (20) NOT NULL COMMENT 'Department' `gender` tinyint (4) NOT NULL DEFAULT'1' COMMENT 'gender: 1 male 2 female', `addr` char (30) NOT NULL, PRIMARY KEY (`id`), KEY `idx_ 1` (`name`, `dep`), KEY `idx_ 2` (`name`, `gender`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' employee form '1row in set (0.01sec)

As you can see, in the command execution result of the above show creat table xx, the value of Create Table is the statement that creates the table. At this point, you can directly copy the SQL that created the table, and then execute it again.

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

Mysql > insert into staff values (null,'Li Ming', 'RD', 1,' Beijing'); Query OK, 1 row affected (0.00 sec) mysql > insert into staff values (null, 'Zhang San', 'PM', 0,' Shanghai'); Query OK, 1 row affected (0.00 sec) mysql > select * from staff +-+ | id | name | dep | gender | addr | +-- + | 1 | Li Ming | RD | 1 | Beijing | | 2 | Zhang San | PM | 0 | Shanghai | | +-+ 2 rows in set (0.00 sec) |

The show create table command is being executed at this point:

Mysql > show create table staff\ Graph * 1. Row * * Table: staffCreate Table: CREATE TABLE `room` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-added id', `name` char (20) NOT NULL COMMENT' user name', `dep`char (20) NOT NULL COMMENT 'Department' `gender` tinyint (4) NOT NULL DEFAULT'1' COMMENT 'gender: 1 male 2 female', `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=' employee form'1 row in set (0.00 sec)

Notice that the penultimate line in the above result

ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT=' employee Table'

Because the id of the staff table is self-incrementing and there are already two records, the self-increment id of the next insert data should be 3, and this information will also appear in the table creation sql.

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

Use like to create a new table based on an existing table with the following characteristics:

1. Convenient. There is no need to view the table structure definition information of the original table.

2. In the new table created, 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 # there are 2 data items in the old table +-+ | id | name | dep | gender | addr | +-- + | 1 | Li Ming | RD | 1 | Beijing | 2 | Zhang Three | PM | 0 | Shanghai | +-+ 2 rows in set (0.00 sec) mysql > create table staff_bak_1 like staff # use like directly, and specify the new table name before Specify the old table (reference table) Query OK, 0 rows affected (0.02 sec) mysql > show create table staff_bak_1\ Graph * 1. Row * * Table: staff_bak_1Create Table: CREATE TABLE `staff_bak_ 1` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increment id' `name` char (20) NOT NULL COMMENT 'user name', `dep` char (20) NOT NULL COMMENT 'department', `gender` tinyint (4) NOT NULL DEFAULT'1' COMMENT 'gender: 1 male 2 female', `addr` char (30) NOT NULL, PRIMARY KEY (`id`), KEY `idx_ 1` (`name`, `dep`), KEY `idx_ 2` (`name`, `gender`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' employee table'# Note there is no AUTO_INCREMENT=31 row in set (0.00 sec) mysql > select * from staff_bak_1; # does not contain the data Empty set (0.00 sec) of the old table

2.3.Use as to create new tables (including data)

Using as to create new tables has the following features:

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 key, index, 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 | Li Ming | RD | 1 | Beijing | | 2 | Zhang San | PM | 0 | Shanghai | | +-+ 2 rows in set (0.00 sec) mysql > show create table staff_bak_2\ row * * Table | : staff_bak_2Create Table: CREATE TABLE `staff_bak_ 2` (`id` int (11) NOT NULL DEFAULT'0' COMMENT 'self-increment id' `name` char (20) CHARACTER SET utf8mb4 NOT NULL COMMENT 'user name', `dep` char (20) CHARACTER SET utf8mb4 NOT NULL COMMENT 'department', `gender` tinyint (4) NOT NULL DEFAULT'1' COMMENT 'gender: 1 male 2 female', `addr` char (30) CHARACTER SET utf8mb4 NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

There are no integrity constraints when creating tables with as, but you can figure this out if you think about it. Because when you use as to create a table, you can specify which fields the new table contains. If you ignore several fields when creating a new table, even if you retain the complete constraint, saving the data will not meet the integrity constraint.

For example, the staff table has an index idx1, which consists of name and dep fields; but there are no name and dep fields in the new table (only other fields are selected), so there is no need to keep idx1 in the new table, right?

Mysql >-select only id, gender, and addr as the fields of the new table, then there is no need for the index composed of name and dep to exist 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\ Graph * 1. Row * * Table: staff_bak_3Create Table: CREATE TABLE `staff_bak_ 3` (`id` int (11) NOT NULL DEFAULT'0' COMMENT 'self-increment id' `gender` tinyint (4) NOT NULL DEFAULT'1' COMMENT 'gender: 1 male 2 female', `addr` char (30) CHARACTER SET utf8mb4 NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec) mysql > select * from staff_bak_3 +-+ | id | gender | addr | +-+ | 1 | 1 | Beijing | | 2 | 0 | Shanghai | +-+ 2 rows in set (0.00 sec)

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

Using like to create a new table, although you retain the various table structure definitions and integrity constraints of the old table, how do you import the data from the old table into the new table?

The most extreme way: I won't try to write a program that reads the data from the old table and then writes it to the new table.

There is a relatively simple command:

Mysql > select * from staff # original table data +-- + | id | name | dep | gender | addr | +-- + | 1 | Li Ming | RD | 1 | Beijing | | 2 | Zhang San | PM | 0 | Shanghai | +-+ 2 rows in set (0.00 sec) mysql > select * from staff_bak_1 # use like to create a table with the same table structure and integrity constraints (except self-increment) Empty set (0.00 sec) mysql > insert into staff_bak_1 select * from staff; # insert all field values of all records in the staff table into the replica table 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 | Li Ming | RD | 1 | Beijing | | 2 | Zhang San | PM | 0 | Shanghai | | +-+ 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 directly select *.

Insert into staff_bak_1 select * from staff

If the structure of two tables is different, it can also be done in this way, such as:

Mysql > show create table demo\ Gateway * 1. Row * * Table: demoCreate 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=utf8mb41 row in set (0.00 sec) # only data records composed of id and name fields in the staff table are inserted into the demo table Corresponding to _ id and _ name fields 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 | Li Ming | 1 | 2 | Zhang San | 1 | +-+ 2 rows in set (0.00 sec)

This is a situation where the number of fields in the two tables is different, and you need to specify the column name manually, 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, you can copy them directly:

The field name of # staff_bak_5 is not the same as that of staff, but the number of fields and the type of fields in the same order are the same. So you can insert mysql > show create table staff_bak_5\ gateway * 1. Row * * Table: staff_bak_5Create Table: CREATE TABLE `staff_bak_ 5` (`_ id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increment id' `name` char (20) NOT NULL COMMENT 'user name', `_ dep` char (20) NOT NULL COMMENT 'department',` _ gender` tinyint (4) NOT NULL DEFAULT'1' COMMENT 'gender: 1 male 2 female', `_ addr` char (30) NOT NULL, PRIMARY KEY (` _ id`), KEY `name` (`_ name`,` _ dep`), KEY `idx_ 2` (`_ name`,` _ gender`) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT=' employee table'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 | Li Ming | RD | 1 | Beijing | | 2 | Zhang San | PM | 0 | Shanghai | +-+ 2 rows in set (0.00 sec)

The above is the table structure and table data of the MySQL database. Although the length is very complex, the sample code is very detailed and easy to understand. If you want to know more about it, please pay attention to the industry information.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.