久久人人青草97香蕉,亚洲成色999久久网站,噜噜噜色噜噜噜久久

MySQL復制表結構和表數據

1、前言

在功能開發完畢，在本地或者測試環境進行測試時，經常會遇到這種情況：有專門的測試數據，測試過程會涉及到修改表中的數據，經常不能一次測試成功，所以，每次執行測試后，原來表中的數據其實已經被修改了，下一次測試，就需要將數據恢復。

我一般的做法是：先創建一個副本表，比如測試使用的user表，我在測試前創建副本表user_bak，每次測試后，將user表清空，然后將副本表user_bak的數據導入到user表中。

上面的操作是對一個table做備份，如果涉及到的table太多，可以創建database的副本。

接下來我將對此處的表結構復制以及表數據復制進行闡述，并非數據庫的復制原理?。。。?/span>

下面是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&gt;?show?create?table?staffG ***************************?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，然后重新執行一次就行了。

當數據表中有數據的時候，看到的創建staff表的sql就會稍有不同。比如，我在staff中添加了兩條記錄：

mysql&gt;?insert?into?staff?values?(null,?'李明',?'RD',?1,?'北京'); Query?OK,?1?row?affected?(0.00?sec) ? mysql&gt;?insert?into?staff?values?(null,?'張三',?'PM',?0,?'上海'); Query?OK,?1?row?affected?(0.00?sec) ? mysql&gt;?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&gt;?show?create?table?staffG ***************************?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創建新表（僅包含表結構）

使用like根據已有的表來創建新表，特點如下：

1、方便，不需要查看原表的表結構定義信息；

2、創建的新表中，表結構定義、完整性約束，都與原表保持一致。

3、創建的新表是一個空表，全新的表，沒有數據。

用法如下：

mysql&gt;?select?*?from?staff;　　#舊表中已有2條數據 +----+--------+-----+--------+--------+ |?id?|?name???|?dep?|?gender?|?addr???| +----+--------+-----+--------+--------+ |??1?|?李明???|?RD??|??????1?|?北京???| |??2?|?張三???|?PM??|??????0?|?上海???| +----+--------+-----+--------+--------+ 2?rows?in?set?(0.00?sec) ? mysql&gt;?create?table?staff_bak_1?like?staff;??#?直接使用like，前面指定新表名，后面指定舊表（參考的表） Query?OK,?0?rows?affected?(0.02?sec) ? mysql&gt;?show?create?table?staff_bak_1G ***************************?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&gt;?select?*?from?staff_bak_1;?#?沒有包含舊表的數據 Empty?set?(0.00?sec)

2.3、使用as來創建新表（包含數據）

使用as來創建新表，有一下特點：

1、可以有選擇性的決定新表包含哪些字段；

2、創建的新表中，會包含舊表的數據；

3、創建的新表不會包含舊表的完整性約束（比如主鍵、索引等），僅包含最基礎的表結構定義。

用法如下：

mysql&gt;?create?table?staff_bak_2?as?select?*?from?staff; Query?OK,?2?rows?affected?(0.02?sec) Records:?2??Duplicates:?0??Warnings:?0 ? mysql&gt;?select?*?from?staff_bak_2; +----+--------+-----+--------+--------+ |?id?|?name???|?dep?|?gender?|?addr???| +----+--------+-----+--------+--------+ |??1?|?李明???|?RD??|??????1?|?北京???| |??2?|?張三???|?PM??|??????0?|?上海???| +----+--------+-----+--------+--------+ 2?rows?in?set?(0.00?sec) ? mysql&gt;?show?create?table?staff_bak_2G ***************************?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表有一個索引idx1，由name和dep字段組成；但是我創建的新表中，沒有name和dep字段（只選擇了其他字段），那么新表中保存idx1也沒有必要，對吧。

mysql&gt;?--??只選擇id、gender、addr作為新表的字段，那么name和dep組成的索引就沒必要存在了 mysql&gt;?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&gt;?show?create?table?staff_bak_3G ***************************?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&gt;?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&gt;?select?*?from?staff;?#原表數據 +----+--------+-----+--------+--------+ |?id?|?name???|?dep?|?gender?|?addr???| +----+--------+-----+--------+--------+ |??1?|?李明???|?RD??|??????1?|?北京???| |??2?|?張三???|?PM??|??????0?|?上海???| +----+--------+-----+--------+--------+ 2?rows?in?set?(0.00?sec) ? mysql&gt;?select?*?from?staff_bak_1;?#?使用like創建的表，與原表相同的表結構和完整性約束（自增除外） Empty?set?(0.00?sec) ? mysql&gt;?insert?into?staff_bak_1?select?*?from?staff;??#?將staff表的所有記錄的所有字段值都插入副本表中 Query?OK,?2?rows?affected?(0.00?sec) Records:?2??Duplicates:?0??Warnings:?0 ? mysql&gt;?select?*?from?staff_bak_1; +----+--------+-----+--------+--------+ |?id?|?name???|?dep?|?gender?|?addr???| +----+--------+-----+--------+--------+ |??1?|?李明???|?RD??|??????1?|?北京???| |??2?|?張三???|?PM??|??????0?|?上海???| +----+--------+-----+--------+--------+ 2?rows?in?set?(0.00?sec)

其實這條SQL語句，是知道兩個表的表結構和完整性約束相同，所以，可以直接select *。

insert?into?staff_bak_1?select?*?from?staff;

如果兩個表結構不相同，其實也是可以這個方式的，比如：

mysql&gt;?show?create?table?demoG ***************************?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&gt;?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&gt;?select?*?from?demo; +-----+--------+---------+ |?_id?|?_name??|?_gender?| +-----+--------+---------+ |???1?|?李明???|???????1?| |???2?|?張三???|???????1?| +-----+--------+---------+ 2?rows?in?set?(0.00?sec)

這是兩個表的字段數量不相同的情況，此時需要手動指定列名，否則就會報錯。

另外，如果兩個表的字段數量，以及相同順序的字段類型相同，如果是全部字段復制，即使字段名不同，也可以直接復制：

#?staff_bak_5的字段名與staff表并不相同，但是字段數量、相同順序字段的類型相同，所以可以直接插入 mysql&gt;?show?create?table?staff_bak_5G ***************************?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&gt;?insert?into?staff_bak_5?select?*?from?staff; Query?OK,?2?rows?affected?(0.00?sec) Records:?2??Duplicates:?0??Warnings:?0 ? mysql&gt;?select?*?from?staff_bak_5; +-----+--------+------+---------+--------+ |?_id?|?_name??|?_dep?|?_gender?|?_addr??| +-----+--------+------+---------+--------+ |???1?|?李明???|?RD???|???????1?|?北京???| |???2?|?張三???|?PM???|???????0?|?上海???| +-----+--------+------+---------+--------+ 2?rows?in?set?(0.00?sec)