企业🤖AI智能体构建引擎,智能编排和调试,一键部署,支持私有化部署方案 广告
**目录** [TOC] # 1 备份与回复 在你开始使用MySQL数据库以后,MySQL会帮你储存与管理所有的资料,依照不同的设定,会有许多的资料档案储存在档案系统中,如果这些档案不小心遗失或损坏,储存的资料可能就全部不见了。为了预防这类的情况发生,MySQL提供许多备份资料的功能,让你可以依照自己的需求,汇出数据库中储存的资料,另外保存起来。如果数据库发生严重的问题,而且储存的资料不见了,你就可以把之前备份的资料,回复到数据库中。备份资料的工作称为“汇出资料、exporting data”;回复资料的工作称为“汇入资料、importing data”。 你可以使用SQL叙述或MySQL提供的用户端程式,执行汇出与汇入的工作。汇出资料可以使用“SELECT INTO OUTFILE”叙述,或是“mysqldump”用户端程式,它们都可以将指定的资料储存为档案保存起来;汇入资料可以使用“LOAD DATA INFILE”叙述,或是“mysqlimport”用户端程式,它们都可以将指定档案中的资料新增到数据库中。 # 2 使用SQL叙述汇出资料 MySQL提供“SELECT INTO OUTFILE”叙述汇出资料,它的用法与一般查询叙述一样,另外使用“INTO OUTFILE”子句指定一个档案名称,执行叙述以后回传的资料会储存为档案。下列是它的语法: [![mysql_19_snap_01](http://box.kancloud.cn/2015-09-15_55f7f4851bd8a.png)](http://box.kancloud.cn/2015-07-17_55a91b04e52d1.png) 使用“INTO OUTFILE”子句指定档案名称时,要特别注意资料夹的符号,不论是“UNIX”或“WINDOWS”作业系统,都要使用“/”。下列的叙述会将查询后的结果储存到“C:\cmdev\dept.txt”档案中: [![mysql_19_snap_02](http://box.kancloud.cn/2015-09-15_55f7f4858891c.png)](http://box.kancloud.cn/2015-07-17_55a91b1accc2d.png) 使用文字编辑软件开启上列范例汇入的档案,它的内容会像这样: [![mysql_19_snap_03](http://box.kancloud.cn/2015-09-15_55f7f485d6ed3.png)](http://box.kancloud.cn/2015-07-17_55a91b1b6c004.png) MySQL默认的分隔字符使用“TAB”,你可以在汇出档案的叙述中,使用“FIELDS TERMINATED BY”子句设定新的分隔字符: [![mysql_19_snap_04](http://box.kancloud.cn/2015-09-15_55f7f4862b9ad.png)](http://box.kancloud.cn/2015-07-17_55a91b26b2cfb.png) 使用“FIELDS ENCLOSED BY”子句可以设定包围字段资料的字符符号: [![mysql_19_snap_05](http://box.kancloud.cn/2015-09-15_55f7f48689335.png)](http://box.kancloud.cn/2015-07-17_55a91b2872cf0.png) 汇出的资料如果遇到“NULL”值的时候,MySQL会使用“\N”储存在档案中: [![mysql_19_snap_06](http://box.kancloud.cn/2015-09-15_55f7f48701ff2.png)](http://box.kancloud.cn/2015-07-17_55a91b2a92068.png) MySQL默认的跳脱字符符号是“\”,你可以在汇出档案的叙述中,使用“FIELDS ESCAPED BY”子句设定新的跳脱字符符号: [![mysql_19_snap_07](http://box.kancloud.cn/2015-09-15_55f7f487414b8.png)](http://box.kancloud.cn/2015-07-17_55a91b2b7c035.png) 使用“LINES STARTING BY”与“TERMINATED BY”子句可以设定每一列资料开始与结束字串: [![mysql_19_snap_08](http://box.kancloud.cn/2015-09-15_55f7f487bd3de.png)](http://box.kancloud.cn/2015-07-17_55a91b2fb26a6.png) 使用文字储存资料有许多不同的格式,有一种很常见的格式称为“comma-separated values、CSV”,它的每一笔资料的结尾使用换行字符,每一个资料都使用逗号隔开,而且前后使用双引号包围起来。许多应用程式都认识这种资料的格式,你可以使用下列的设定输出一个CSV格式的资料档案: [![mysql_19_snap_09](http://box.kancloud.cn/2015-09-15_55f7f4885b943.png)](http://box.kancloud.cn/2015-07-17_55a91b32f26ad.png) # 3 使用SQL叙述汇入资料 “LOAD DATA”叙述可以汇入资料到数据库的某个表格中,“LOAD DATA”叙述提供许多子句,可以让你设定资料档案、档案的格式,或是汇入资料的处理。下列是它的语法: [![mysql_19_snap_10](http://box.kancloud.cn/2015-09-15_55f7f488d29d9.png)](http://box.kancloud.cn/2015-07-17_55a91b346b124.png) ## 3.1 指定资料档案 “LOAD DATA”叙述可以将一个包含资料的档案,汇入到一个指定的表格中,下列是它的基本语法: [![mysql_19_snap_11](http://box.kancloud.cn/2015-09-15_55f7f49392ca3.png)](http://box.kancloud.cn/2015-07-17_55a91b3934d01.png) 使用“LOAD DATA”叙述汇入资料前,要明确的指定数据库: [![mysql_19_snap_12](http://box.kancloud.cn/2015-09-15_55f7f4941113e.png)](http://box.kancloud.cn/2015-07-17_55a91b444aa31.png) 如果你的资料档案放在用户端的电脑中,在使用“LOAD DATA”叙述时要加入“LOCAL”关键字。指定资料档案时,可以包含磁盘机代号、资料夹与档案名称: [![mysql_19_snap_13](http://box.kancloud.cn/2015-09-15_55f7f4949d4d6.png)](http://box.kancloud.cn/2015-07-17_55a91b45530bd.png) 指定的资料档案如果没有磁盘机代号,可是包含资料夹与档案名称,MySQL会使用目前工作中的磁盘机: [![mysql_19_snap_14](http://box.kancloud.cn/2015-09-15_55f7f49a1deb6.png)](http://box.kancloud.cn/2015-07-17_55a91b46ead19.png) 指定的资料档案没有磁盘机代号,只有资料夹与档案名称,可是最前面没有资料夹符号,MySQL会使用目前工作中的资料夹: [![mysql_19_snap_15](http://box.kancloud.cn/2015-09-15_55f7f49aa07e0.png)](http://box.kancloud.cn/2015-07-17_55a91b478f986.png) 指定的资料档案只有档案名称,MySQL会使用目前工作中的资料夹: [![mysql_19_snap_16](http://box.kancloud.cn/2015-09-15_55f7f49cdf460.png)](http://box.kancloud.cn/2015-07-17_55a91b4c78c2a.png) 如果你的资料档案放在服务器的电脑中,在使用“LOAD DATA”叙述时就不要使用“LOCAL”关键字。指定资料档案时,可以包含磁盘机代号、资料夹与档案名称: [![mysql_19_snap_17](http://box.kancloud.cn/2015-09-15_55f7f49d62b4f.png)](http://box.kancloud.cn/2015-07-17_55a91b57a2e1e.png) 指定的资料档案如果没有磁盘机代号,可是包含资料夹与档案名称,MySQL会使用服务器的磁盘机: [![mysql_19_snap_18](http://box.kancloud.cn/2015-09-15_55f7f49d9bc20.png)](http://box.kancloud.cn/2015-07-17_55a91b5b211d6.png) 指定的资料档案没有磁盘机代号,只有资料夹与档案名称,可是最前面没有资料夹符号,MySQL会使用   数据库资料夹: [![mysql_19_snap_19](http://box.kancloud.cn/2015-09-15_55f7f49e23be7.png)](http://box.kancloud.cn/2015-07-17_55a91b7524eca.png) 指定的资料档案只有档案名称,而且在“INTO TABLE”中指定数据库名称,MySQL会使用数据库资料夹的数据库名称: [![mysql_19_snap_20](http://box.kancloud.cn/2015-09-15_55f7f49e96d1d.png)](http://box.kancloud.cn/2015-07-17_55a91b8071f79.png) 指定的资料档案只有档案名称,在执行“LOAD DATA INFILE”叙述前先使用“USE”叙述指定数据库,而且在“INTO TABLE”中没有指定数据库名称,MySQL会使用数据库资料夹的目前使用中数据库名称: [![mysql_19_snap_21](http://box.kancloud.cn/2015-09-15_55f7f49ed035b.png)](http://box.kancloud.cn/2015-07-17_55a91b838f5c2.png) 注:使用“SHOW VARIABLES LIKE ‘datadir’”叙述,可以查询MySQL数据库服务器使用的数据库资料夹。 ## 3.2 设定资料格式 如果没有另外设定的话,使用“LOAD DATA INFILE”叙述汇入的资料档案,MySQL会使用下列的格式: [![mysql_19_snap_22](http://box.kancloud.cn/2015-09-15_55f7f49f59086.png)](http://box.kancloud.cn/2015-07-17_55a91b8599e7e.png) 如果你的资料档案格式跟上列的档案一样的话,使用下列的计就可以汇入资料: [![mysql_19_snap_23](http://box.kancloud.cn/2015-09-15_55f7f4a4bdb04.png)](http://box.kancloud.cn/2015-07-17_55a91b878e4e9.png) 如果要汇入资料的档案是“CSV”格式的话,就要使用“FIELDS”与“LINES”子句设定格式: [![mysql_19_snap_24](http://box.kancloud.cn/2015-09-15_55f7f4a4f1834.png)](http://box.kancloud.cn/2015-07-17_55a91b88dd326.png) ## 3.3 处理汇入的资料 如果汇入的资料档案与表格完全对应的话,“LOAD DATA INFILE”叙述都可以把资料正确的汇入到数据库中。可是以下列储存在资料档案中的部门资料来说: [![mysql_19_snap_25](http://box.kancloud.cn/2015-09-15_55f7f4a6a9b1d.png)](http://box.kancloud.cn/2015-07-17_55a91b8b88252.png) 因为“cmdev.dept”表格有“deptno”、“dname”与“location”三个字段,所以执行下列的“LOAD DATA INFILE”叙述就会产生错误: [![mysql_19_snap_26](http://box.kancloud.cn/2015-09-15_55f7f4a713310.png)](http://box.kancloud.cn/2015-07-17_55a91b8c13318.png) 你可以在“LOAD DATA INFILE”叙述中,指定汇入资料的数量和字段: [![mysql_19_snap_27](http://box.kancloud.cn/2015-09-15_55f7f4a776046.png)](http://box.kancloud.cn/2015-07-17_55a91b8c910b5.png) 下列的“LOAD DATA INFILE”叙述指定汇入资料时会跳过第一笔,而且指定汇入的字段只有“deptno”与“dname”两个字段: [![mysql_19_snap_28](http://box.kancloud.cn/2015-09-15_55f7f4a7bf7ab.png)](http://box.kancloud.cn/2015-07-17_55a91b8ee9d6c.png) 你也可以在“LOAD DATA INFILE”叙述中加入使用者变量: [![mysql_19_snap_29](http://box.kancloud.cn/2015-09-15_55f7f4a82ed54.png)](http://box.kancloud.cn/2015-07-17_55a91b904d528.png) 下列的叙述将“ename”与“job”两个字段的资料先转换大写后,再汇入到数据库中: [![mysql_19_snap_30](http://box.kancloud.cn/2015-09-15_55f7f4ad9765f.png)](http://box.kancloud.cn/2015-07-17_55a91b90c1657.png) ## 3.4 索引键重复 在新增、修改或汇入资料到数据库的时候,都有可能发生索引值重复的错误,在使用“LOAD DATA INFILE”汇入资料的时候,如果发生索引值重复的情况,你可以使用“IGNORE”或“REPLACE”来决定数据库该作什么处理: [![mysql_19_snap_31](http://box.kancloud.cn/2015-09-15_55f7f4add13c1.png)](http://box.kancloud.cn/2015-07-17_55a91b923155e.png) 以部门资料表来说,部门编号已经设定为主索引键,所以它是不可以重复的: [![mysql_19_snap_32](http://box.kancloud.cn/2015-09-15_55f7f4ae15b2d.png)](http://box.kancloud.cn/2015-07-17_55a91b98f0efb.png) 如果资料档储存在MySQL服务器的电脑中,在汇入资料时没有使用“IGNORE”或“REPLACE”,发生索引重复的情况时,数据库会产生错误讯息,而且不会汇入任何资料: [![mysql_19_snap_33](http://box.kancloud.cn/2015-09-15_55f7f4ae97e5d.png)](http://box.kancloud.cn/2015-07-17_55a91b9ae4b89.png) 资料档储存在MySQL服务器的电脑中时,你可以使用“IGNORE”关键字忽略错误的资料,正确的资料还是汇入到数据库中;使用“REPLACE”关键字请数据库会帮你执行修改资料的动作: [![mysql_19_snap_34](http://box.kancloud.cn/2015-09-15_55f7f4af01603.png)](http://box.kancloud.cn/2015-07-17_55a91bb172bb0.png) 下列的“LOAD DATA INFILE”叙述中使用“IGNORE”关键字汇入资料时,处理索引重复资料的效果: [![mysql_19_snap_35](http://box.kancloud.cn/2015-09-15_55f7f4be614fb.png)](http://box.kancloud.cn/2015-07-17_55a91bb3c178e.png) 下列的“LOAD DATA INFILE”叙述中使用“REPLACE”关键字汇入资料时,处理索引重复资料的效果: [![mysql_19_snap_36](http://box.kancloud.cn/2015-09-15_55f7f4beaf3b5.png)](http://box.kancloud.cn/2015-07-17_55a91bb5b7e7b.png) 资料档储存在用户端的电脑中时,处理汇入资料发生索引重复的作法会不太一样: [![mysql_19_snap_37](http://box.kancloud.cn/2015-09-15_55f7f4c44d67d.png)](http://box.kancloud.cn/2015-07-17_55a91bb9a098b.png) 使用“REPLACE”关键字的时候,效果就跟资料档储存在MySQL服务器的电脑中时一样: [![mysql_19_snap_38](http://box.kancloud.cn/2015-09-15_55f7f4c482029.png)](http://box.kancloud.cn/2015-07-17_55a91bbf5d039.png) ## 3.5 汇入资讯 在执行汇入资料的叙述以后,你应该会想要知道有多少资料汇入到数据库中。如果你在“MySQL Query Browser”工具中执行“LOAD DATA INFILE”叙述的话,它会告诉你总共影响了几笔资料,包含新增与修改: [![mysql_19_snap_39](http://box.kancloud.cn/2015-09-15_55f7f4c4e95cc.png)](http://box.kancloud.cn/2015-07-17_55a91bc037d33.png) 如果你在命令提示字符中执行“LOAD DATA INFILE”叙述的话,除了影响的资料数量以外,还会告诉你比较完整的汇入资讯: [![mysql_19_snap_40](http://box.kancloud.cn/2015-09-15_55f7f4ca3afb8.png)](http://box.kancloud.cn/2015-07-17_55a91bc16a9ce.png) 在上列的资讯中: * Records:表示从资料档案中读取的资料数量 * Deleted:表示在发生索引重复的情况下更新资料的数量 * Skipped:表示在发生索引重复的情况下被忽略的资料数量 * Warnings:表示资料档案中有问题的资料数量,例如转换Hello字串为数值 # 4 使用mysqldump程式汇出资料 MySQL提供许多不同应用的工具程式,让你可以在命令提示字符中执行,这些工具程式都是MySQL才有的,而且它们并不是SQL叙述。你可以使用“mysqldump”工具程式汇出资料。下列是它的用法: [![mysql_19_snap_41](http://box.kancloud.cn/2015-09-15_55f7f4cf75a22.png)](http://box.kancloud.cn/2015-07-17_55a91bc2d1736.png) 下列是“mysqldump”工具程式的基本选项: | 选项 | 说明 | | --- | --- | | –host=数据库服务器 或 -h 数据库服务器 | 指定要连线的的数据库服务器名称,“-h”后面必须有空格;没有使用这个选项的话,表示连线到本机 | | –user=使用者帐号 或 -u 使用者帐号 | 指定连线的使用者帐号,“-u”后面必须有空格 | | –password[=密码] 或 -p[密码] | 指定连线的密码,“-p”后面不可以有空格;没有提供密码的话,执行程式以后会提示你输入密码;没有使用这个选项的话,表示密码为空白 | 下列的命令为“mysqldump”加入指定数据库服务器、使用者帐号与数据库名称的相关资讯。在命令提示字符中执行下列的命令以后,会在萤幕中显示“cmdev”数据库的资讯: [![mysql_19_snap_42](http://box.kancloud.cn/2015-09-15_55f7f4cfd366f.png)](http://box.kancloud.cn/2015-07-17_55a91bc56660f.png) 这些选项都有两种设定方式,以使用者帐号来说: [![mysql_19_snap_43](http://box.kancloud.cn/2015-09-15_55f7f4d558552.png)](http://box.kancloud.cn/2015-07-17_55a91bc8154fe.png) 下列是与汇出资料相关的选项: | 选项 | 说明 | | --- | --- | | –result-file=档案名称 | 指定汇出资料的档案名称,资料夹符号必须使用“/” | | –all-databases | 汇出数据库服务器中所有数据库的资料 | | –tab=资料夹 | 指定汇出资料档案存放的资料夹 | 下列的命令使用“–result-file”指定汇出的档案名称。执行后储存盘案的位置就是你执行“mysqldump”的位置,如果在“C:/cmdev/data/out”资料夹下执行“mysqldump”,你就可以在“C:/cmdev/data/out”资料夹下找到“cmdev.sql”档案: [![mysql_19_snap_44](http://box.kancloud.cn/2015-09-15_55f7f4da9093c.png)](http://box.kancloud.cn/2015-07-17_55a91bc8c344b.png) 执行上列的命令以后,开启“C:/cmdev/data/out/cmdev.sql”档案,里面的内容只有建立表格的叙述,并不包含储存在表格里面的资料纪录。 如果想要“mysqldump”工具程式也帮你汇出资料纪录的话,就要使用下列的作法: [![mysql_19_snap_45](http://box.kancloud.cn/2015-09-15_55f7f4dfc6301.png)](http://box.kancloud.cn/2015-07-17_55a91bca51bea.png) “mysqldump”工具程式汇出资料纪录档案的格式,字段资料间使用“TAB”隔开,每一列资料以“\N”结尾。如果要控制资料档案格式的话,可以使用下列的选项: | 选项 | 说明 | | --- | --- | | –fields-terminated-by=字串 | 设定字段资料间的分隔符号 | | –fields-enclosed-by=字符 | 设定每一个字段资料的前后字符 | | –fields-optionally-enclosed-by=字符 | | –fields-escaped-by=字符 | 设定跳脱字符的符号 | | –lines-terminated-by=字串 | 设定每一行的结尾 | # 5 使用mysqlimport程式汇入资料 你可以使用“mysqlimport”工具程式汇入资料。下列是它的用法: [![mysql_19_snap_46](http://box.kancloud.cn/2015-09-15_55f7f4e05977f.png)](http://box.kancloud.cn/2015-07-17_55a91bcc3e2fb.png) 在指定资料档案的名称时,要特别注意下列两个重点: * 资料档案中不可以包含SQL叙述 * 档案名称会决定汇入数据库中的哪个表格,MySQL会使用去除附加档名后的名称。例如“dept.dat”为“dept”表格;“dept.txt.dat”同样为“dept”表格 下列是“mysqlimport”工具程式的基本选项,它们的用法与“mysqldump”工具程式一样,其实大部份的MySQL工具程式都有这些选项: | 选项 | 说明 | | --- | --- | | –host=数据库服务器 或 -h 数据库服务器 | 指定要连线的的数据库服务器名称,“-h”后面必须有空格;没有使用这个选项的话,表示连线到本机 | | –user=使用者帐号 或 -u 使用者帐号 | 指定连线的使用者帐号,“-u”后面必须有空格 | | –password[=密码] 或 -p[密码] | 指定连线的密码,“-p”后面不可以有空格;没有提供密码的话,执行程式以后会提示你输入密码;没有使用这个选项的话,表示密码为空白 | 如果你的资料档案是下列格式的话: [![mysql_19_snap_47](http://box.kancloud.cn/2015-09-15_55f7f4e59a148.png)](http://box.kancloud.cn/2015-07-17_55a91bcd31cbb.png) 下列的命令可以把资料档案汇入到“cmdev.dept”中: [![mysql_19_snap_48](http://box.kancloud.cn/2015-09-15_55f7f4e6031ec.png)](http://box.kancloud.cn/2015-07-17_55a91bcdc7ec6.png) 下列的选项可以设定资料档案的格式: | 选项 | 说明 | | --- | --- | | –fields-terminated-by=字串 | 设定字段资料间的分隔符号 | | –fields-enclosed-by=字符 | 设定每一个字段资料的前后字符 | | –fields-optionally-enclosed-by=字符 | | –fields-escaped-by=字符 | 设定跳脱字符的符号 | | –lines-terminated-by=字串 | 设定每一行的结尾 | 下列的选项可以决定发生索引值重复的错误时,数据库该作什么处理: | 选项 | 说明 | | --- | --- | | –ignore | 忽略索引键重复的汇入资料 | | –replace | 索引键重复时,以汇入的资料更新数据库中的资料 | | –local | 指定汇入的资料档案来源为用户端 |