MySQL kopiert Tabellenstruktur und Tabellendaten

1. Vorwort

Nachdem die Funktion lokal oder in einer Testumgebung entwickelt und getestet wurde, stoßen wir häufig auf diese Situation: Spezielle Testdaten Der Testvorgang erfordert eine Änderung der Daten in der Tabelle, und der Test ist häufig nicht erfolgreich. Daher wurden nach der Ausführung jedes Tests tatsächlich die Daten in der Originaltabelle geändert und die Daten müssen für den nächsten wiederhergestellt werden prüfen. .

Mein allgemeiner Ansatz besteht darin, zuerst eine Kopiertabelle zu erstellen, z. B. die Tabelle user, die zum Testen verwendet wird. Nach jedem Test erstelle ich die Kopiertabelle user_bak , löschen Sie die Benutzertabelle und importieren Sie dann die Daten der Replikattabelle user_bak in die Tabelle user.

Der obige Vorgang dient zum Sichern einer Tabelle. Wenn zu viele Tabellen beteiligt sind, können Sie eine Kopie der Datenbank erstellen.

Als nächstes werde ich hier die Replikation der Tabellenstruktur und der Tabellendaten erläutern Es ist nicht das Replikationsprinzip der Datenbank! ! ! !

Das Folgende ist die Tabellenstruktur der Staff-Tabelle

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. Spezifische Methoden

2.1. Führen Sie die Erstellungs-SQL der alten Tabelle aus, um die Tabelle zu erstellen

Wenn die ursprüngliche Tabelle bereits vorhanden ist, können Sie den Befehl verwenden, um die Erstellungsanweisung der Tabelle anzuzeigen:

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)

Wie Sie oben sehen können Im Ergebnis der Befehlsausführung von show creat table xx ist der Wert von Create Table die Anweisung, mit der die Tabelle erstellt wird Kopieren Sie direkt die SQL, die die Tabelle erstellt, und führen Sie sie dann erneut aus.

Wenn die Datentabelle Daten enthält, unterscheidet sich die zum Erstellen der Personaltabelle verwendete SQL geringfügig. Ich habe zum Beispiel zwei Datensätze im Stab hinzugefügt:

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)

Zu diesem Zeitpunkt führe ich den Befehl „show create table“ aus:

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)

Beachten Sie, dass die vorletzte Zeile im obigen Ergebnis

ist

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

Da die ID der Personaltabelle automatisch inkrementiert wird und bereits 2 Datensätze vorhanden sind, sollte die automatisch inkrementierende ID der nächsten eingefügten Daten 3 sein. Diese Informationen werden auch in der Tabellenerstellungs-SQL angezeigt.

2.2. Verwenden Sie Like, um eine neue Tabelle zu erstellen (enthält nur Tabellenstruktur)

Verwenden Sie Like, um eine neue Tabelle basierend auf einer vorhandenen Tabelle zu erstellen folgt:

1. Praktisch, die Tabellenstrukturdefinitionsinformationen der ursprünglichen Tabelle müssen nicht überprüft werden.

2 stimmen mit der Originaltabelle überein.

3. Die neu erstellte Tabelle ist eine leere Tabelle, eine brandneue Tabelle ohne Daten.

wird wie folgt verwendet:

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. Verwenden Sie as, um eine neue Tabelle (mit Daten) zu erstellen.

Verwenden zum Erstellen der neuen Tabelle:

1. Sie können selektiv entscheiden, welche Felder die neue Tabelle enthalten soll.

2 die alte Tabelle ;

3. Die neu erstellte Tabelle enthält nicht die Integritätsbeschränkungen der alten Tabelle (wie Primärschlüssel, Indizes usw.), sondern nur die grundlegendste Tabellenstrukturdefinition.

wird wie folgt verwendet:

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)

Bei der Verwendung von as zum Erstellen einer Tabelle werden Integritätsbeschränkungen nicht beibehalten. Tatsächlich können Sie dies herausfinden, wenn Sie darüber nachdenken es sorgfältig. Denn wenn Sie as zum Erstellen einer Tabelle verwenden, können Sie angeben, welche Felder die neue Tabelle enthält. Wenn Sie beim Erstellen einer neuen Tabelle mehrere Felder ignorieren, können die Integritätsbeschränkungen beim Speichern der Daten nicht eingehalten werden .

Zum Beispiel hat die Personaltabelle einen Index idx1, der aus den Feldern „name“ und „dep“ besteht, aber in der neuen Tabelle, die ich erstellt habe, gibt es keine Felder „name“ und „dep“ (nur andere Felder sind ausgewählt). ), dann in der neuen Tabelle Es ist nicht nötig, idx1 zu speichern, oder?

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. Verwenden Sie Like+Einfügen+Auswählen, um eine Kopie der Originaltabelle zu erstellen (empfohlen)

Verwenden Sie Like, um eine neue zu erstellen Tabelle, obwohl sie beibehalten wird Verschiedene Tabellenstrukturdefinitionen und Integritätseinschränkungen der alten Tabelle, aber wie importiert man die Daten der alten Tabelle in die neue Tabelle?

Der extremste Weg: Schreiben Sie ein Programm, um zuerst die alten Tabellendaten zu lesen und sie dann in die neue Tabelle zu schreiben. Ich werde diese Methode nicht ausprobieren.

Es gibt einen relativ einfachen Befehl:

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)

Tatsächlich weiß diese SQL-Anweisung, dass die Tabellenstruktur und Integritätsbeschränkungen der beiden Tabellen gleich sind, sodass Sie sie direkt auswählen können *.

insert into staff_bak_1 select * from staff;

Wenn die Strukturen der beiden Tabellen nicht gleich sind, können Sie dies tatsächlich auf diese Weise tun, zum Beispiel:

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)

Dies ist eine Situation, in der die Anzahl der Felder in den beiden In diesem Fall müssen Sie den Spaltennamen manuell angeben, andernfalls wird ein Fehler gemeldet.

Wenn außerdem die Anzahl der Felder in den beiden Tabellen und die Feldtypen in derselben Reihenfolge gleich sind und alle Felder kopiert werden, können sie direkt kopiert werden, auch wenn die Feldnamen unterschiedlich sind:

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

Das obige ist der detaillierte Inhalt vonMySQL kopiert Tabellenstruktur und Tabellendaten. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!