三国演义 三国演义是一部在华人世界非常普及的历史小说,是由罗贯中根据元朝的三国志平话改编,他以东汉末年魏、蜀、吴三国斗争为主题,收集历史资料和说书人的故事,成为这一部大家都非常熟悉的故事。 或许我们现在觉得这些历史已经跟我们没什么关系了,不过大家都知道关公过五关斩六将,刘备三顾茅芦,诸葛孔明的空城记。 这些老掉牙的故事,总是不断的出现在电影、电视剧和各种平台的游戏,一代又一代的传承下去。 这应该是因为三国演义的确是一个好故事,很多很精采的好故事,就像美国畅销作家史帝芬金所说的,一个好故事是不会寂寞的。 三国演义的普及,让人认为里面讲的故事其实就是真的历史,罗贯中在编这本书的时候,大概是为了让它可以比较戏剧化一些,采用了很多当时说书人的内容,这些内容是在民间流传或由说书人编造的,跟历史并不一样。 例如大家熟悉的关公斩华雄,在三国演义中是一段非常的精采故事,作者使用很短的内容让关云长的豪勇,简单、清楚而且非常震憾的呈现给读者。 不过根据史料的考证,其实华雄的头是被孙坚砍掉的。 这也是为什么清朝的时候就有人评论三国演义是「七实三虚,惑乱观者」。…
MySQL入门 (十八) : 汇入与汇出资料
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」子句指定一个档案名称,执行叙述以后回传的资料会储存为档案。 下列是它的语法:
使用「INTO OUTFILE」子句指定档案名称时,要特别注意资料夹的符号,不论是「UNIX」或「WINDOWS」作业系统,都要使用「/」。 下列的叙述会将查询后的结果储存到「C:\cmdev\dept.txt」档案中:
使用文字编辑软体开启上列范例汇入的档案,它的内容会像这样:
MySQL预设的分隔字元使用「TAB」,你可以在汇出档案的叙述中,使用「FIELDS TERMINATED BY」子句设定新的分隔字元:
使用「FIELDS ENCLOSED BY」子句可以设定包围栏位资料的字元符号:
汇出的资料如果遇到「NULL」值的时候,MySQL会使用「\N」储存在档案中:
MySQL预设的跳脱字元符号是「\」,你可以在汇出档案的叙述中,使用「FIELDS ESCAPED BY」子句设定新的跳脱字元符号:
使用「LINES STARTING BY」与「TERMINATED BY」子句可以设定每一列资料开始与结束字串:
使用文字储存资料有许多不同的格式,有一种很常见的格式称为「comma-separated values、CSV」,它的每一笔资料的结尾使用换行字元,每一个资料都使用逗号隔开,而且前后使用双引号包围起来。 许多应用程式都认识这种资料的格式,你可以使用下列的设定输出一个CSV格式的资料档案:
3 使用SQL叙述汇入资料
「LOAD DATA」叙述可以汇入资料到资料库的某个表格中,「LOAD DATA」叙述提供许多子句,可以让你设定资料档案、档案的格式,或是汇入资料的处理。 下列是它的语法:
3.1 指定资料档案
「LOAD DATA」叙述可以将一个包含资料的档案,汇入到一个指定的表格中,下列是它的基本语法:
使用「LOAD DATA」叙述汇入资料前,要明确的指定资料库:
如果你的资料档案放在用户端的电脑中,在使用「LOAD DATA」叙述时要加入「LOCAL」关键字。 指定资料档案时,可以包含磁碟机代号、资料夹与档案名称:
指定的资料档案如果没有磁碟机代号,可是包含资料夹与档案名称,MySQL会使用目前工作中的磁碟机:
指定的资料档案没有磁碟机代号,只有资料夹与档案名称,可是最前面没有资料夹符号,MySQL会使用目前工作中的资料夹:
指定的资料档案只有档案名称,MySQL会使用目前工作中的资料夹:
如果你的资料档案放在伺服器的电脑中,在使用「LOAD DATA」叙述时就不要使用「LOCAL」关键字。 指定资料档案时,可以包含磁碟机代号、资料夹与档案名称:
指定的资料档案如果没有磁碟机代号,可是包含资料夹与档案名称,MySQL会使用伺服器的磁碟机:
指定的资料档案没有磁碟机代号,只有资料夹与档案名称,可是最前面没有资料夹符号,MySQL会使用资料库资料夹:
指定的资料档案只有档案名称,而且在「INTO TABLE」中指定资料库名称,MySQL会使用资料库资料夹的资料库名称:
指定的资料档案只有档案名称,在执行「LOAD DATA INFILE」叙述前先使用「USE」叙述指定资料库,而且在「INTO TABLE」中没有指定资料库名称,MySQL会使用资料库资料夹的目前使用中资料库名称:
注:使用「SHOW VARIABLES LIKE ‘datadir’」叙述,可以查询MySQL资料库伺服器使用的资料库资料夹。
3.2 设定资料格式
如果没有另外设定的话,使用「LOAD DATA INFILE」叙述汇入的资料档案,MySQL会使用下列的格式:
如果你的资料档案格式跟上列的档案一样的话,使用下列的计就可以汇入资料:
如果要汇入资料的档案是「CSV」格式的话,就要使用「FIELDS」与「LINES」子句设定格式:
3.3 处理汇入的资料
如果汇入的资料档案与表格完全对应的话,「LOAD DATA INFILE」叙述都可以把资料正确的汇入到资料库中。 可是以下列储存在资料档案中的部门资料来说:
因为「cmdev.dept」表格有「deptno」、「dname」与「location」三个栏位,所以执行下列的「LOAD DATA INFILE」叙述就会产生错误:
你可以在「LOAD DATA INFILE」叙述中,指定汇入资料的数量和栏位:
下列的「LOAD DATA INFILE」叙述指定汇入资料时会跳过第一笔,而且指定汇入的栏位只有「deptno」与「dname」两个栏位:
你也可以在「LOAD DATA INFILE」叙述中加入使用者变数:
下列的叙述将「ename」与「job」两个栏位的资料先转换大写后,再汇入到资料库中:
3.4 索引键重复
在新增、修改或汇入资料到资料库的时候,都有可能发生索引值重复的错误,在使用「LOAD DATA INFILE」汇入资料的时候,如果发生索引值重复的情况,你可以使用「 IGNORE」或「REPLACE」来决定资料库该作什么处理:
以部门资料表来说,部门编号已经设定为主索引键,所以它是不可以重复的:
如果资料档储存在MySQL伺服器的电脑中,在汇入资料时没有使用「IGNORE」或「REPLACE」,发生索引重复的情况时,资料库会产生错误讯息,而且不会汇入任何资料:
资料档储存在MySQL伺服器的电脑中时,你可以使用「IGNORE」关键字忽略错误的资料,正确的资料还是汇入到资料库中;使用「REPLACE」关键字请资料库会帮你执行修改资料的动作:
下列的「LOAD DATA INFILE」叙述中使用「IGNORE」关键字汇入资料时,处理索引重复资料的效果:
下列的「LOAD DATA INFILE」叙述中使用「REPLACE」关键字汇入资料时,处理索引重复资料的效果:
资料档储存在用户端的电脑中时,处理汇入资料发生索引重复的作法会不太一样:
使用「REPLACE」关键字的时候,效果就跟资料档储存在MySQL伺服器的电脑中时一样:
3.5 汇入资讯
在执行汇入资料的叙述以后,你应该会想要知道有多少资料汇入到资料库中。 如果你在「MySQL Query Browser」工具中执行「LOAD DATA INFILE」叙述的话,它会告诉你总共影响了几笔资料,包含新增与修改:
如果你在命令提示字元中执行「LOAD DATA INFILE」叙述的话,除了影响的资料数量以外,还会告诉你比较完整的汇入资讯:
在上列的资讯中:
- Records:表示从资料档案中读取的资料数量
- Deleted:表示在发生索引重复的情况下更新资料的数量
- Skipped:表示在发生索引重复的情况下被忽略的资料数量
- Warnings:表示资料档案中有问题的资料数量,例如转换Hello字串为数值
4 使用mysqldump程式汇出资料
MySQL提供许多不同应用的工具程式,让你可以在命令提示字元中执行,这些工具程式都是MySQL才有的,而且它们并不是SQL叙述。 你可以使用「mysqldump」工具程式汇出资料。 下列是它的用法:
下列是「mysqldump」工具程式的基本选项:
选项 | 说明 |
–host=资料库伺服器 | 指定要连线的的资料库伺服器名称,「-h」后面必须有空格;没有使用这个选项的话,表示连线到本机 |
-h 资料库伺服器 | |
–user=使用者帐号 | 指定连线的使用者帐号,「-u」后面必须有空格 |
-u 使用者帐号 | |
–password[=密码] | 指定连线的密码,「-p」后面不可以有空格;没有提供密码的话,执行程式以后会提示你输入密码;没有使用这个选项的话,表示密码为空白 |
-p[密码] |
下列的命令为「mysqldump」加入指定资料库伺服器、使用者帐号与资料库名称的相关资讯。 在命令提示字元中执行下列的命令以后,会在萤幕中显示「cmdev」资料库的资讯:
这些选项都有两种设定方式,以使用者帐号来说:
下列是与汇出资料相关的选项:
选项 | 说明 |
–result-file=档案名称 | 指定汇出资料的档案名称,资料夹符号必须使用「/」 |
–all-databases | 汇出资料库伺服器中所有资料库的资料 |
–tab=资料夹 | 指定汇出资料档案存放的资料夹 |
下列的命令使用「–result-file」指定汇出的档案名称。 执行后储存档案的位置就是你执行「mysqldump」的位置,如果在「C:/cmdev/data/out」资料夹下执行「mysqldump」,你就可以在「C:/cmdev/data/out」资料夹下找到「cmdev.sql」档案:
执行上列的命令以后,开启「C:/cmdev/data/out/cmdev.sql」档案,里面的内容只有建立表格的叙述,并不包含储存在表格里面的资料纪录。
如果想要「mysqldump」工具程式也帮你汇出资料纪录的话,就要使用下列的作法:
「mysqldump」工具程式汇出资料纪录档案的格式,栏位资料间使用「TAB」隔开,每一列资料以「\N」结尾。 如果要控制资料档案格式的话,可以使用下列的选项:
选项 | 说明 |
–fields-terminated-by=字串 | 设定栏位资料间的分隔符号 |
–fields-enclosed-by=字元 | 设定每一个栏位资料的前后字元 |
–fields-optionally-enclosed-by=字元 | |
–fields-escaped-by=字元 | 设定跳脱字元的符号 |
–lines-terminated-by=字串 | 设定每一行的结尾 |
5 使用mysqlimport程式汇入资料
你可以使用「mysqlimport」工具程式汇入资料。 下列是它的用法:
在指定资料档案的名称时,要特别注意下列两个重点:
- 资料档案中不可以包含SQL叙述
- 档案名称会决定汇入资料库中的哪个表格,MySQL会使用去除附加档名后的名称。 例如「dept.dat」为「dept」表格;「dept.txt.dat」同样为「dept」表格
下列是「mysqlimport」工具程式的基本选项,它们的用法与「mysqldump」工具程式一样,其实大部份的MySQL工具程式都有这些选项:
选项 | 说明 |
–host=资料库伺服器 | 指定要连线的的资料库伺服器名称,「-h」后面必须有空格;没有使用这个选项的话,表示连线到本机 |
-h 资料库伺服器 | |
–user=使用者帐号 | 指定连线的使用者帐号,「-u」后面必须有空格 |
-u 使用者帐号 | |
–password[=密码] | 指定连线的密码,「-p」后面不可以有空格;没有提供密码的话,执行程式以后会提示你输入密码;没有使用这个选项的话,表示密码为空白 |
-p[密码] |
如果你的资料档案是下列格式的话:
下列的命令可以把资料档案汇入到「cmdev.dept」中:
下列的选项可以设定资料档案的格式:
选项 | 说明 |
–fields-terminated-by=字串 | 设定栏位资料间的分隔符号 |
–fields-enclosed-by=字元 | 设定每一个栏位资料的前后字元 |
–fields-optionally-enclosed-by=字元 | |
–fields-escaped-by=字元 | 设定跳脱字元的符号 |
–lines-terminated-by=字串 | 设定每一行的结尾 |
下列的选项可以决定发生索引值重复的错误时,资料库该作什么处理:
选项 | 说明 |
–ignore | 忽略索引键重复的汇入资料 |
–replace | 索引键重复时,以汇入的资料更新资料库中的资料 |
–local | 指定汇入的资料档案来源为用户端 |
翻译自: http://www.codedata.com.tw/database/mysql-tutorial-19-outfile-dump-infile-import/
更多参考:
MySQL入门 (十一) : Prepared Statements
MySQL入门 (十二) : Stored Routines 入门
MySQL入门 (十三) : Stored Routines 的变数与流程
MySQL入门 (十四) : Stored Routines 进阶
MySQL入门 (十六) : 查询information_schema