>  기사  >  데이터 베이스  >  MySQL은 테이블 구조와 테이블 데이터를 복사합니다.

MySQL은 테이블 구조와 테이블 데이터를 복사합니다.

angryTom
angryTom앞으로
2019-11-29 15:08:203059검색

MySQL은 테이블 구조와 테이블 데이터를 복사합니다.

1. 소개

로컬 또는 테스트 환경에서 함수를 개발하고 테스트한 후에는 특별한 테스트 데이터가 있고 테스트 프로세스에 테이블의 데이터를 수정하는 상황이 자주 발생합니다. 테스트가 한 번에 성공할 수 없는 경우가 많으므로 각 테스트를 실행한 후 원래 테이블의 데이터가 실제로 수정되었으며 다음 테스트를 위해 데이터를 복원해야 합니다.

내 일반적인 접근 방식은 다음과 같습니다. 먼저 테스트에 사용된 user 테이블과 같은 복사 테이블을 만듭니다. 각 테스트 후에는 사용자 테이블을 지우고 복사본을 바꿉니다. table user_bak 의 데이터를 user 테이블로 가져옵니다. 위 작업은

테이블

을 백업하는 것입니다. 관련된 테이블이 너무 많으면 데이터베이스의 복사본을 만들 수 있습니다. 다음에는 여기서 테이블 구조 복제와 테이블 데이터 복제에 대해 설명하겠습니다.

데이터베이스의 복제 원리는 아닙니다! ! ! !

다음은

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에

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)

위 결과에서 두 번째 줄인

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

는 Staff 테이블의 ID가 다음과 같기 때문입니다. 그리고 이미 2개의 레코드가 있으므로 다음에 삽입되는 데이터의 자동 증가 ID는 3이 되어야 합니다. 이 정보는 테이블 생성 SQL에도 표시됩니다.

2.2 새 테이블 생성(테이블 구조만 포함)

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 문은 두 테이블의 테이블 구조와 무결성 제약 조건이 동일하다는 것을 알고 있으므로 *를 직접 선택할 수 있습니다.

insert into staff_bak_1 select * from staff;

두 테이블의 구조가 동일하지 않은 경우 다음과 같은 방법을 사용할 수도 있습니다.

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)

이때 두 테이블의 필드 수가 다른 상황입니다. 열 이름을 수동으로 지정해야 합니다. 그렇지 않으면 오류가 보고됩니다.

또한 두 테이블의 필드 수와 동일한 순서의 필드 유형이 동일한 경우 모든 필드를 복사하면 필드 이름이 달라도 직접 복사할 수 있습니다.

# 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명:
이 기사는 cnblogs.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제