MySQL はテーブル構造とテーブル データをコピーします

1. はじめに

関数がローカルまたはテスト環境で開発およびテストされた後、次のような状況に遭遇することがよくあります: 特別なテスト データの場合、テスト プロセスにはテーブル内のデータの変更が含まれ、多くの場合テストは成功しません。そのため、各テストの実行後、元のテーブルのデータは実際には変更されており、次のテストのためにデータを復元する必要があります。テスト。 。

私の一般的なアプローチは次のとおりです: まず、テストに使用する user テーブルなどのコピー テーブルを作成します。テストの前とテスト後にコピー テーブル user_bak を作成します。 test を実行し、ユーザー テーブルをクリアしてから、レプリカ テーブル user_bak から user テーブルにデータをインポートします。

上記の操作は、table をバックアップすることです。関連するテーブルが多すぎる場合は、database のコピーを作成できます。

次に、ここでテーブル構造のレプリケーションとテーブルデータのレプリケーションについて説明します。 はデータベースのレプリケーション原理ではありません。 ！ ！ ！

staff テーブル

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 のテーブル構造は次のとおりです。具体的な方法

2.1. 古いテーブルの作成 SQL を実行してテーブルを作成します

元のテーブルがすでに存在する場合は、コマンドを使用してテーブルの作成ステートメントを表示できます:

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)

ご覧のとおり、上記 show creat table xxのコマンド実行結果のCreate Tableの値がテーブルを作成する文です。テーブルを作成するSQLを直接コピーして再度実行してください。

データ テーブルにデータがある場合、スタッフ テーブルの作成に使用される SQL は若干異なります。たとえば、staff に 2 つのレコードを追加しました:

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)

現時点では、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)

上記の結果の最後から 2 行目は

であることに注意してください。

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

staff テーブルの ID は自動増加し、すでに 2 つのレコードがあるため、次に挿入されるデータの自動増加 ID は 3 である必要があります。この情報はテーブル作成 SQL にも表示されます。

2.2. like を使用して新しいテーブルを作成します (テーブル構造のみを含む)

like を使用して、既存のテーブルに基づいて新しいテーブルを作成します。

1. 元のテーブルのテーブル構造定義情報を確認する必要がなく便利;

2. 作成された新しいテーブルでは、テーブル構造定義と整合性制約が設定されます。元の表と一致しています。

3. 作成された新しいテーブルは空のテーブル、つまりデータのない新しいテーブルです。

使用法は次のとおりです:

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. as を使用して新しいテーブル (データを含む) を作成します。 as を使用して作成する 新しいテーブルには次の特性があります:
1. 新しいテーブルにどのフィールドが含まれるかを選択して決定できます;

2. 作成された新しいテーブルにはデータが含まれます;

3. 作成された新しいテーブルには、古いテーブルの整合性制約 (主キー、インデックスなど) は含まれず、最も基本的なテーブル構造のみが含まれます。意味。

使用法は次のとおりです:

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)

as を使用してテーブルを作成する場合、整合性制約は保持されません。実際、これは次のように考えると理解できます。それについては慎重に。 as を使用してテーブルを作成するときに、新しいテーブルに含まれるフィールドを指定できるため、新しいテーブルの作成時にいくつかのフィールドを無視すると、たとえ整合性制約が保持されていたとしても、データを保存するときに整合性制約を満たすことができなくなります。 。

たとえば、staff テーブルには、name フィールドと dep フィールドで構成されるインデックス idx1 がありますが、作成した新しいテーブルには、name フィールドと dep フィールドはありません (他のフィールドのみが選択されています) )、新しいテーブルに idx1 を保存する必要はありませんね。

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. like insert select を使用して元のテーブルのコピーを作成します (推奨)

like を使用して新しいテーブルを作成します。古いテーブルは保持されますが、さまざまなテーブル構造定義と整合性制約がありますが、古いテーブルから新しいテーブルにデータをインポートするにはどうすればよいですか?
最も極端な方法: 最初に古いテーブルのデータを読み取り、それを新しいテーブルに書き込むプログラムを作成します。この方法は試しません。

比較的単純なコマンドがあります:

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)

実際、この SQL ステートメントは 2 つのテーブルのテーブル構造と整合性制約が同じであることを認識しているため、* を選択できます。直接。

insert into staff_bak_1 select * from staff;

2 つのテーブルの構造が同じでない場合、この方法も使用できます。例:

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)

これは、2 つのテーブルのフィールドの数が同じである状況です。この時点では、列名を手動で指定する必要があります。そうしないと、エラーが報告されます。

さらに、2 つのテーブルのフィールドの数とフィールドの種類が同じ順序であれば、すべてのフィールドをコピーすると、フィールド名が異なっていても、直接コピーできます。

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

以上がMySQL はテーブル構造とテーブル データをコピーしますの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。