MySQL copie la structure de la table et les données de la table

1. Préface

Une fois la fonction développée et testée localement ou dans un environnement de test, nous rencontrons souvent cette situation : Données de test spéciales , le processus de test impliquera la modification des données de la table et, souvent, le test ne peut pas réussir. Par conséquent, après l'exécution de chaque test, les données de la table d'origine ont effectivement été modifiées et les données doivent être restaurées pour le suivant. test. .

Mon approche générale consiste à créer d'abord une table de copie, telle que la table user utilisée pour les tests. Je crée la table de copie user_bak avant de tester. , effacez la table utilisateur, puis importez les données de la table de réplique user_bak dans la table user.

L'opération ci-dessus consiste à sauvegarder une table S'il y a trop de tables impliquées, vous pouvez créer une copie de la base de données.

Ensuite, j'expliquerai ici la réplication de la structure des tables et la réplication des données des tables Ce n'est pas le principe de réplication de la base de données ! ! ! !

Voici la structure de la table stafftable

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 Méthode spécifique

2.1. Exécutez le SQL de création de l'ancienne table pour créer la table

Si la table d'origine existe déjà, vous pouvez utiliser la commande pour afficher l'instruction de création de la 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)

Comme vous pouvez le voir ci-dessus

Dans le résultat de l'exécution de la commande show creat table xx, la valeur de Create Table est l'instruction qui crée la table à ce moment-là. copiez directement le SQL qui crée la table, puis réexécutez-le.

Lorsqu'il y a des données dans la table de données, le SQL utilisé pour créer la table du personnel sera légèrement différent. Par exemple, j'ai ajouté deux enregistrements dans 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)

À ce moment, j'exécute la commande show create 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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='员工表'
1 row in set (0.00 sec)

Notez que l'avant-dernière ligne du résultat ci-dessus est

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

Comme l'ID de la table du personnel est auto-incrémenté et qu'il y a déjà 2 enregistrements, l'ID auto-incrémenté des prochaines données insérées devrait être 3. Cette information apparaîtra également dans le SQL de création de table.

2.2. Utilisez like pour créer une nouvelle table (contient uniquement la structure de la table)

Utilisez like pour créer une nouvelle table basée sur une table existante. suit :

1. Pratique, pas besoin de vérifier les informations de définition de la structure de la table d'origine

2. Dans la nouvelle table créée, la définition de la structure de la table et les contraintes d'intégrité ; sont conformes au tableau original.

3. La nouvelle table créée est une table vide, une toute nouvelle table sans données.

est utilisé comme suit :

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 Utiliser as pour créer une nouvelle table (contenant des données)

Utiliser quant à créer La nouvelle table a les caractéristiques suivantes :

1 Vous pouvez décider sélectivement quels champs la nouvelle table contient

2. l'ancienne table ;

3. La nouvelle table créée ne contiendra pas les contraintes d'intégrité de l'ancienne table (telles que les clés primaires, les index, etc.), mais seulement la définition la plus élémentaire de la structure de la table.

est utilisé comme suit :

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)

Lors de l'utilisation de as pour créer une table, les contraintes d'intégrité ne sont pas conservées. En fait, vous pouvez le comprendre si vous y réfléchissez. soigneusement. Car lorsque vous utilisez as pour créer une table, vous pouvez spécifier les champs que contient la nouvelle table. Si vous ignorez plusieurs champs lorsque vous créez une nouvelle table, même si les contraintes d'intégrité sont conservées, les contraintes d'intégrité ne peuvent pas être respectées lors de la sauvegarde des données. .

Par exemple, la table staff a un index idx1, qui est composé de champs name et dep ; mais dans la nouvelle table que j'ai créée, il n'y a pas de champs name et dep (seuls les autres champs sont sélectionnés ), puis dans le nouveau tableau, il n'est pas nécessaire de sauvegarder idx1, n'est-ce pas ?

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. Utilisez like+insert+select pour créer une copie de la table originale (recommandé)

Utilisez like pour créer une nouvelle table, bien qu'elle soit conservée Diverses définitions de structure de table et contraintes d'intégrité de l'ancienne table, mais comment importer les données de l'ancienne table dans la nouvelle table ?

La manière la plus extrême : écrire un programme pour lire d'abord les données de l'ancienne table puis les écrire dans la nouvelle table. Je n'essaierai pas cette méthode.

Il existe une commande relativement simple :

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)

En fait, cette instruction SQL sait que la structure de la table et les contraintes d'intégrité des deux tables sont les mêmes, vous pouvez donc directement sélectionner *.

insert into staff_bak_1 select * from staff;

Si les structures des deux tables ne sont pas les mêmes, vous pouvez effectivement le faire de cette manière, par exemple :

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)

Il s'agit d'une situation où le nombre de champs dans les deux Les tables sont différentes. Dans ce cas, vous devez spécifier manuellement le nom des colonnes, sinon une erreur sera signalée.

De plus, si le nombre de champs dans les deux tables et les types de champs dans le même ordre sont les mêmes, si tous les champs sont copiés, même si les noms de champs sont différents, ils peuvent être copiés directement :

# 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视频教程》  

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!