使用文件进行交互

主讲教师：傅志辉浙江商业职业技术学院快速消费品数据分析09拓展：数据库设计与数据存取———使用文件进行交互数据导入和导出的方法不同方法之间的区别010201-数据导出方法方法一：使用selectintooutfile'filename'语句方法二：使用mysqldump实用程序01-数据导出方法两种方法的区别：使用select方法输出的文件只有数据，没有表结构，而且一次只能处理一个表，要处理多个表则不是很容易，不过可以将select命令写入一个sql文件，然后在命令行下执行即可在(mysqldump命令的讲解中可以看到如何执行这个sql文件)；mysqldump可以导出纯数据文本或只导出含有创建表结构而没有数据的sql文件，同时可以对库中的多个表进行操作，比select的方法灵活，强大的多。

select适合利用程序进行处理，而mysqldump则为手工操作，同时提供强大的导出功能，并且可以处理整个库，或库中指定的多表。使用selectintooutfile语句只能导出表的数据，不能导出表结构语法SELECT*FROMTABLE_NAMEINTOOUTFILE‘filename’要求：用户拥有file的权限

使用样例mysql>usecookbook;

DatabaseChanged

mysql>select*fromstu_infointooutfile‘c:/a.txt’;

QueryOK,12rowsaffected(0.05sec)

这样就把stu_info表的数据导在了‘a.txt’文件里面指定分隔符使用select命令还可以指定导出文件时，字段之间的分隔字符，转义字符，包括字符，及记录行分隔字符。

FIELDSTERMINATEDBY

字段分割字符，默认为’\t’

[OPTIONALLY]ENCLOSEDBY

字段用什么字符包括，默认为无,如果使用了OPTIONALLY，则只有CHAR和VERCHAR被包括；

ESCAPEDBY转义字符，默认为’\\’

LINESTERMINATEDBY行分隔符，默认无实例mysql>select*fromstu_infointooutfile‘c:/a1.txt’fieldsterminatedby‘,’enclosedby‘“’;

QueryOK,12rowsaffected(0.06sec)

结果可能如下：

"200801001","杨振军","1",\N,"男""200801002","白云","1",\N,"女""200801003","吴楠","2",\N,"女"看到每个字段都用‘,’进行了分隔，且每个字段都用‘”’包括了起来。注意，行记录分隔符可以是一个字符串，请大家自行测试。不过，如果输出文件在指定目录下如果存在的话就会报错，先删除再测试即可。使用mysqldump实用程序先举个简单的例子

C:\Users\zp>mysqldump-uroot-pcookbook>d:\a.sql

Enterpassword:

可能结果如下：

--MySQLdump10.13Distrib5.5.9,forWin32(x86)----Host:localhostDatabase:cookbook----------------------------------------------------------Serverversion 5.5.9/*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;/*!40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;/*!40101SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;/*!40101SETNAMESutf8*/;/*!40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/;/*!40103SETTIME_ZONE='+00:00'*/;如何解决乱码showvariableslike'%char%';character-set-databasegb2312在导出语句中在cookbook之前增加—default-character-set=gb2312字符校对服务器字符集和校对MySQL服务器有一个服务器字符集和一个服务器校对规则，它们均不能设置为空MySQL按照如下方法确定服务器字符集和服务器校对规则：当服务器启动时根据有效的选项设置根据运行时的设定值在服务器级别，确定方法很简单。当启动mysqld时，根据使用的初始选项设置来确定服务器字符集和校对规则。可以使用--default-character-set设置字符集，并且可以在字符集后面为校对规则添加--default-collation。如果没有指定一个字符集，那就与--default-character-set=latin1相同。如果你仅指定了一个字符集（例如，latin1），但是没有指定一个校对规则，那就与--default-charset=latin1--default-collation=latin1_swedish_ci相同，因为latin1_swedish_ci是latin1的默认校对规则。因此，以下三个命令有相同的效果：shell>mysqldshell>mysqld--default-character-set=latin1shell>mysqld--default-character-set=latin1\--default-collation=latin1_swedish_ci数据库字符集和校对每一个数据库有一个数据库字符集和一个数据库校对规则，它不能够为空。CREATEDATABASE和ALTERDATABASE语句有一个可选的子句来指定数据库字符集和校对规则：表字符集和校对每一个表有一个表字符集和一个校对规则，它不为空。为指定表字符集和校对规则，CREATETABLE和ALTERTABLE语句有一个可选的子句：CREATETABLEtbl_name(column_list)[DEFAULTCHARACTERSETcharset_name[COLLATEcollation_name]列字符集和校对每一个“字符”列（即，CHAR、VARCHAR或TEXT类型的列）有一个列字符集和一个列校对规则，它不能为空。列定义语法有一个可选子句来指定列字符集和校对规则：col_name{CHAR|VARCHAR|TEXT}(col_length)[CHARACTERSETcharset_name[COLLATEcollation_name]]例如：CREATETABLETable1(column1VARCHAR(5)CHARACTERSETlatin1COLLATElatin1_german1_ci);MySQL按照下面的方式选择列字符集和校对规则：

如果指定了CHARACTERSETX和COLLATEY，那么采用CHARACTERSETX和COLLATEY。如果指定了CHARACTERSETX而没有指定COLLATEY，那么采用CHARACTERSETX和CHARACTERSETX的默认校对规则。

否则，采用表字符集和服务器校对规则。CHARACTERSET和COLLATE子句是标准的SQL。不同的编码格式会导致同一字符，在不同字符集下的编码会不同。同样同一编码在不同的字符集中代码的字符也不相同。当你的MySQL返回的字符串的编码格式（字符集）与你的客户工具程序（mysql,php,querybrowser,...）当前使用的字符集不同时，就会造成乱码。比如一个英国朋友告诉你Long,当一位中国小学生看到后就会告诉你“龙”而不是“长”MySQL中默认字符集的设置有四级:服务器级，数据库级，表级。最终是字段级的字符集设置。注意前三种均为默认设置，并不代码你的字段最终会使用这个字符集设置。所以我们建议要用showcreatetabletable;或showfullfieldsfromtableName;来检查当前表中字段的字符集设置。MySQL中关于连接环境的字符集设置有Client端，connection,results通过这些参数，MySQL就知道你的客户端工具用的是什么字符集，结果集应该是什么字符集。这样MySQL就会做必要的翻译，一旦这些参数有误，自然会导致字符串在转输过程中的转换错误。基本上99%的乱码由些造成。乱码后需要检查的信息数据库表中字段的字符集设置。showcreatetableTableName或showfullcolumnsfromtableNamemysql>showcreatetablestu_info;CREATETABLE`stu_info`(`id`varchar(20)NOTNULLDEFAULT'',`name`varchar(20)DEFAULTNULL,`class`varchar(10)DEFAULTNULL,`birth_place`int(11)DEFAULTNULL,`sex`char(2)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=gb2312showfullcolumnsfromstu_info;2当前联接系统参数showvariableslike'char%'+--------------------------+----------------|Variable_name|Value+--------------------------+----------------|character_set_client|utf8|character_set_connection|utf8|character_set_database|gb2312|character_set_filesystem|binary|character_set_results|utf8|character_set_server|latin1|character_set_system|utf8|character_sets_dir|C:\ProgramFile+--------------------------+----------------中文，请确保表中该字段的字符集为中文兼容：big5|Big5TraditionalChinesegb2312|GB2312SimplifiedChinesegbk|GBKSimplifiedChineseutf8|UTF-8Unicode确保，联接参数与这个字段字符集一致MySQLdump工具很多方面类似相反作用的工具MySQLimport。它们有一些同样的选项。但MySQLdump能够做更多的事情。它可以把整个数据库装载到一个单独的文本文件中。这个文件包含有所有重建您的数据库所需要的SQL命令。这个命令取得所有的模式（Schema，后面有解释）并且将其转换成DDL语法（CREATE语句，即数据库定义语句），取得所有的数据，并且从这些数据中创建INSERT语句。这个工具将您的数据库中所有的设计倒转。因为所有的东西都被包含到了一个文本文件中。这个文本文件可以用一个简单的批处理和一个合适SQL语句导回到MySQL中。这个工具令人难以置信地简单而快速。决不会有半点让人头疼地地方。

导出创建表结构和插入数据的SQL语句语法：mysqldump连接信息数据库[表1][表2]…>路径\文件名.sql如:mysqldump–uroot–pcookbook>c:\cookbook.sql只导出创建表结构的SQL语句语法：mysqldump连接信息–d数据库[表1][表2]…>路径\文件名.sql如：mysqldump–uroot–p–dcookbookstu_info>f:\backup\stu_info.sql导出表的SQL语句语法：mysqldump连接信息数据库[表1][表2]…>路径\文件名.sql如：mysqldump–uroot–pcookbookstu_info>f:\backup\stu_info.sql这个非常的灵活，您甚至可以使用WHERE从句来选择您需要的记录导出到文件中。要达到这样的目的，可以使用类似于下面的命令：

MySQLdump–uroot–p–w"ID>‘200801001’"cookbookstu_info>f:\backup\stuinfo001.txt只导出插入数据的SQL语句语法：mysqldump连接信息–t数据库[表1][表2]…>路径\文件名.sql如：mysqldump–uroot-p–tcookbookstu_grade>f:\backup\stu_grade.sql单独生成文本文件和SQL文件语法：mysqldump连接信息-T本地路径数据库名[表1][表2]…如：mysqldump–uroot–p–Tf:\backup\cookbookstu_infostu_grade只有指定了-T参数才可以导出纯文本文件，表示导出数据的目录，./表示当前目录，即与执行命令时处于同一目录。如果不指定表，则将导出整个数据库的数据。每个表会生成两个文件，一个为.sql文件，包含建表执行。另一个为.txt文件，只包含数据，且没有sql指令。补充:

mysqldump支持下列选项：

--add-locks

在每个表导出之前增加LOCKTABLES并且之后UNLOCKTABLE。(为了使得更快地插入到MySQL)。

--add-drop-table

在每个create语句之前增加一个droptable。

--allow-keywords

允许创建是关键词的列名字。这由在列名前面加表名的方法做到。

-c,--complete-insert

使用完整的insert语句(用列名字)。

-C,--compress

如果客户和服务器均支持压缩，压缩两者间所有的信息。

--delayed

用INSERTDELAYED命令插入行。

-e,--extended-insert

使用全新多行INSERT语法。（给出更紧缩并且更快的插入语句）

-#,--debug[=option_string]

跟踪程序的使用(为了调试)。

--help

显示一条帮助消息并且退出。

--fields-terminated-by=...

--fields-enclosed-by=...

--fields-optionally-enclosed-by=...

--fields-escaped-by=...

--lines-terminated-by=...

这些选择与-T选择一起使用，并且有相应的LOADDATAINFILE子句相同的含义。

LOADDATAINFILE语法。

-F,--flush-logs

在开始导出前，洗掉在MySQL服务器中的日志文件。

-f,--force,

即使我们在一个表导出期间得到一个SQL错误，继续。

-h,--host=..

从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。

-l,--lock-tables.

为开始导出锁定所有表。

-t,--no-create-info

不写入表创建信息(CREATETABLE语句）

-d,--no-data

不写入表的任何行信息。如果你只想得到一个表的结构的导出，这是很有用的！

--opt

同--quick--add-drop-table--add-locks--extended-insert--lock-tables。

应该给你为读入一个MySQL服务器的尽可能最快的导出。

-pyour_pass,--password[=your_pass]

与服务器连接时使用的口令。如果你不指定“=your_pass”部分，mysqldump需要来自终端的口令。

-Pport_num,--port=port_num

与一台主机连接时使用的TCP/IP端口号。（这用于连接到localhost以外的主机，因为它使用Unix套接字。）

-q,--quick

不缓冲查询，直接导出至stdout；使用mysql_use_result()做它。

-S/path/to/socket,--socket=/path/to/socket

与localhost连接时（它是缺省主机)使用的套接字文件。-T,--tab=path-to-some-directory

对于每个给定的表，创建一个table_name.sql文件，它包含SQLCREATE命令，和一个table_name.txt文件，它包含数据。注意：这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据--fields-xxx和--lines--xxx选项来定。

-uuser_name,--user=user_name

与服务器连接时，MySQL使用的用户名。缺省值是你的登录名。

-Ovar=option,--set-variablevar=option

设置一个变量的值。可能的变量被列在下面。

-v,--verbose

冗长模式。打印出程序所做的更多的信息。

-V,--version

打印版本信息并且退出。

-w,--where='where-condition'

只导出被选择了的记录；注意引号是强制的！

"--where=user='jimf'""-wuserid>1""-wuserid<1"

数据导入使用LOADDATAINFILE'filename'命令使用mysqlimport实用程序使用mysql命令使用load命令loaddatainfile‘c:/a1.txt'intotablestu_infofieldsterminatedby','enclosedby'"';可以带的参数如下：FIELDSTERMINATEDBY''[OPTIONALLY]ENCLOSEDBY''ESCAPEDBY''LINESTERMINATEDBY''

FIELDSTERMINATED表示字段分隔；[OPTIONALLY]ENCLOSED表示字段用什么字符包括起来，如果使用了OPTIONALLY，则只有CHAR和VERCHAR被包括；ESCAPED表示当需要转义时用什么作为转义字符；LINESTERMINATED表示每行记录之间用什么分隔。上面列的是缺省值，而且这些项都是可选的，不选则使用缺省值。可以根据需要进行修改。您也可以设置插入语句的优先级，如果您要把它标记为低优先级（LOW_PRIORITY），那么MySQL将会等到没有其他人读这个表的时候，才把插入数据。可以使用如下的命令：

LOADDATALOW_PRIORITYINFILE“c:/1.txt"INTOTABLEstu_grade;您也可以指定是否在插入数据的时候，取代或者忽略文件与数据表中重复的键值。替代重复的键值的语法：

LOADDATALOW_PRIORITYINFILE“c:/1.txt"REPLACEINTOTABLEstu_info;上面的句子看起来有点笨拙，但却把关键字放在了让您的剖析器可以理解的地方。

TERMINATEDBY描述字段的分隔符，默认情况下是tab字符（\t）

ENCLOSEDBY描述的是字段的括起字符。比方以引号括起每一个字段。

ESCAPEDBY描述的转义字符。默认的是反些杠（backslash：\）.使用前面的MySQLimport命令的例子，用LOADDATAINFILE语句把同样的文件导入到数据库中:select*fromstu_gradeintooutfile'c:/2.txt'fieldsterminatedby','enclosedby'"';LOADDATAINFILE“c:/2.txt"REPLACEINTOTABLEstu_infoFIELDSTERMINATEDBY','ENCLOSEDBY'"';LOADDATAINFILE语句中有一个MySQLimport工具中没有特点：

LOADDATAINFILE可以按指定的列把文件导入到数据库中。

当我们要把数据的一部分内容导入的时候，这个特点就很重要。比方说，我们要从Access数据库升级到MySQL数据库的时候，需要加入一些栏目（列/字段/field）到MySQL数据库中，以适应一些额外的需要。

这个时候，我们的Access数据库中的数据仍然是可用的，但是因为这些数据的栏目(field)与MySQL中的不再匹配，因此而无法再使用MySQLimport工具。尽管如此，我们仍然可以使用LOADDATAINFILE，下面的例子显示了如何向指定的栏目(field)中导入数据：Createtablestud_gradeasselect*fromstu_gradewhere1=2;Altertablestud_gradeaddcolumnmemovarchar(45);LOADDATAINFILE“c:/1.txt"INTOTABLEstud_grade(id,subject_id,score,semester);使用mysqlimport实用程序MySQLimport位于MySQL/bin目录中，是MySQL的一个载入（或者说导入）数据的一个非常有效的工具。这是一个命令行工具。有两个参数以及大量的选项可供选择。这个工具把一个文本文件（textfile）导入到你指定的数据库和表中。比方说我们要从文件stu_info.txt中把数据导入到数据库cookbook中的表stu_info中,表名即为文件名：

MySQLimport–uroot–pcookbookc:\stu_info.txt表名MySQLimport的常用选项介绍-d