MySQL数据导入导出.docx

1、MySQL数据导入导出MySQL数据导入导出(转载) 数据导出 数据导出主要有以下几种方法: 1)使用select into outfile filename语句 2)使用mysqldump实用程序 使用select into outfile filename语句 可以在mysql的命令行下或在php程序中执行它。我下面以在mysql命令行下为例。在php中使用时,将其改成相应的查询进行处理即可。不过在使用这个命令时,要求用户拥有file的权限。如我们有一个库为phptest,其中有一个表为driver。现在要把driver卸成文件。执行命令: mysql use phptest; Datab

2、ase Changed mysql select * from driver into outfile a.txt; Query OK, 22 rows affected (0.05 sec) 上面就可以完成将表driver从数据库中卸到a.txt文件中。注意文件名要加单引号。那么这个文件在哪呢?在mysql目录下有一个data目录,它即是数据库文件所放的地方。每个库在单独占一个子目录,所以phptest的目录为c:mysqldataphptest(注意:我的mysql 安装在c:mysql下)。好,现在我们进去,a.txt就是它。打开这个文件,可能是: 1 Mika Hakinnen 1 2

3、 David Coulthard 1 3 Michael Schumacher 2 4 Rubens Barrichello 2 . 可能还有很多记录。每个字段之间是用制表符分开的(t)。那么我们可以修改输出文件名的目录,以便放在指定的位置。如a.txt可以改成./a.txt或/a.txt。其中./a.txt放在 c:mysqldata目录下了, 而/a.txt文件则放在c:目录下了。所以select命令认为的当前目录是数据库的存放目录, 这里是 c:mysqldata。 使用select命令还可以指定卸出文件时,字段之间的分隔字符,转义字符,包括字符,及记录行分隔字符。列在下面: FIELD

4、S TERMINATED BY t OPTIONALLY ENCLOSED BY ESCAPED BY LINES TERMINATED BY n TERMINATED 表示字段分隔 OPTIONALLY ENCLOSED 表示字段用什么字符包括起来,如果使用了OPTIONALLY则只有CHAR和VERCHAR被包括ESCAPED 表示当需要转义时用什么作为转义字符LINES TERMINATED 表示每行记录之间用什么分隔 上面列的是缺省值,而且这些项都是可选的,不选则使用缺省值。可以根据需要进行修改。给出一个例子如下: mysql select * from driver into out

5、file a.txt fields terminated by , enclosed by ; Query OK, 22 rows affected (0.06 sec) 结果可能如下: 1,Mika,Hakinnen,1 2,David,Coulthard,1 3,Michael,Schumacher,2 4,Rubens,Barrichello,2 . 可以看到每个字段都用,进行了分隔,且每个字段都用包括了起来。注意,行记录分隔符可以是一个字符串,请大家自行测试。不过,如果输出文件在指定目录下如果存在的话就会报错,先删除再测试即可。 使用mysqldump实用程序 从上面的select方法

6、可以看出,输出的文件只有数据,而没有表结构。而且,一次只能处理一个表,要处理多个表则不是很容易的。不过可以将select命令写入一个sql 文件(复制文本应该是很容易的吧),然后在命令行下执行即可:mysql 库名先来个最简单的吧: mysqldump phptest a.sql 可能结果如下: # MySQL dump 7.1 # # Host: localhost Database: phptest #- # Server version 3.22.32-shareware-debug # # Table structure for table driver # CREATE TABLE

7、driver ( drv_id int(11) DEFAULT 0 NOT NULL auto_increment, drv_forename varchar(15) DEFAULT NOT NULL, drv_surname varchar(25) DEFAULT NOT NULL, drv_team int(11) DEFAULT 0 NOT NULL, PRIMARY KEY (drv_id) ); # # Dumping data for table driver # INSERT INTO driver VALUES (1,Mika,Hakinnen,1); INSERT INTO

8、driver VALUES (2,David,Coulthard,1); INSERT INTO driver VALUES (3,Michael,Schumacher,2); INSERT INTO driver VALUES (4,Rubens,Barrichello,2); . 如果有多表,则分别列在下面。可以看到这个文件是一个完整的sql文件,如果要将其导入到其它的数据库中可以通过命令行方式,很方便:mysql phptest a.sql 如果只想卸出插入数据的sql命令,而不需要建表命令,则命令如下: mysqldump -t phptest a.sql 那么如果我只想要数据,而不想

9、要什么sql命令时,应该如何操作呢? mysqldump -T./ phptest driver 其中,只有指定了-T参数才可以卸出纯文本文件,表示卸出数据的目录,./表示当前目录,即与 mysqldump同一目录。如果不指定driver表,则将卸出整个数据库的数据。每个表会生成 两个文件,一个为.sql文件,包含建表执行。另一个为.txt文件,只包含数据,且没有sql 指令。 对卸出的数据文件,也可以同select方法一样,指定字段分隔符,包括字符,转义字段,行记录分隔符。参数列在下面: -fields-terminated-by= 字段分隔符 -fields-enclosed-by= 字段

10、包括符 -fields-optionally-enclosed-by= 字段包括符,只用在CHAR和VERCHAR字段上-fields-escaped-by= 转义字符 -lines-terminated-by= 行记录分隔符 我想大家应该明白这些参数的意思了吧。一个例子如下: mysqldump -T./ -fields-terminated-by=, -fields-enclosed-by= phptest driver 输出结果为: 1,Mika,Hakinnen,1 2,David,Coulthard,1 3,Michael,Schumacher,2 4,Rubens,Barriche

11、llo,2 . 请注意字符的使用。 小结 以上为使用select和mysqldump实用程序来卸出文本的方法。select适合利用程序进行处理,而mysqldump则为手工操作,同时提供强大的导出功能,并且可以处理整个库,或库中指定的多表。大家可以根据需求自行决定使用。 同时还有一些方法,如直接数据库文件拷贝也可以,但是移动后的数据库系统与原系统应一致才行。这里就不再提了。 = = 导入 同导出相类似,导入也有两种方法: 1)使用LOAD DATA INFILE filename命令 2)使用mysqlimport实用程序 3)使用sql文件 由于前两个处理与导出处理相似,只不过是它们的逆操作

12、,故只给出几种命令使用的例子,不再解释了,大家可以自行查阅手册。 使用load命令: load data infile driver.txt into table driver fields terminated by , enclosed by ; 使用mysqlimport实用程序: mysqlimport -fields-terminated-by=, -fields-enclosed-by= phptest driver.txt 对于第三种,则可以使用由mysqldump导出的sql文件,在命令行下执行: mysq l库名 1.MySQLimport的语法介绍: MySQLimport

13、位于MySQL/bin目录中,是MySQL的一个载入(或者说导入)数据的一个非常有效的工具。这是一个命令行工具。有两个参数以及大量的选项可供选择。这个工具把一个文本文件(text file)导入到你指定的数据库和表中。比方说我们要从文件Customers.txt中把数据导入到数据库Meet_A_Geek中的表Custermers中: MySQLimport Meet_A_Geek Customers.txt 注意:这里Customers.txt是我们要导入数据的文本文件,而Meet_A_Geek是我们要操作的数据库,数据库中的表名是Customers,这里文本文件的数据格式必须与Custome

14、rs 表中的记录格式一致,否则MySQLimport命令将会出错。 其中表的名字是导入文件的第一个句号(.)前面文件字符串,另外一个例子: MySQLimport Meet_A_Geek Cus.to.mers.txt 那么我们将把文件中的内容导入到数据库Meet_A_Geek 中的Cus表中。 上面的例子中,都只用到两个参数,并没有用到更多的选项,下面介绍MySQLimport 的选项 2.MySQLimport的常用选项介绍: 选项功能 -d or -delete 新数据导入数据表中之前删除数据数据表中的所有信息 -f or -force 不管是否遇到错误,MySQLimport将强制继续

15、插入数据 -i or -ignore MySQLimport跳过或者忽略那些有相同唯一关键字的行,导入文件中的数据将被忽略。 -l or -lock-tables 数据被插入之前锁住表,这样就防止了,你在更新数据库时,用户的查询和更新受到影响。 -r or -replace 这个选项与-i选项的作用相反;此选项将替代表中有相同唯一关键字的记录。 -fields-enclosed- by= char 指定文本文件中数据的记录时以什么括起的,很多情况下数据以双引号括起。默认的情况下数据是没有被字符括起的。 -fields-terminated- by=char 指定各个数据的值之间的分隔符,在句号

16、分隔的文件中,分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符(Tab) -lines-terminated- by=str 此选项指定文本文件中行与行之间数据的分隔字符串或者 字符。默认的情况下MySQLimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符: 一个新行或者一个回车。 MySQLimport命令常用的选项还有-v 显示版本(version),-p 提示输入密码(password)等。 3.例子:导入一个以逗号为分隔符的文件 文件中行的记录格式是这样的: 1, ORD89876, 1 Dozen Roses, 1999122

17、6 我们的任务是要把这个文件里面的数据导入到数据库Meet_A_Geek中的表格Orders 中,我们使用这个命令: bin/MySQLimport prl fields-enclosed-by= fields-terminated-by=, Meet_A_Geek Orders.txt 这个命令可能看起来很不爽,不过当你熟悉了之后,这是非常简单的。第一部分, bin/MySQLimport ,告诉操作系统你要运行的命令是MySQL/bin目录下的MySQLimport,选项p是要求输入密码,这样就要求你在改动数据库之前输入密码,操作起来会更安全。我们用了r选项是因为我们想要把表中的唯一关键字

18、与文件记录中有重复唯一关键字的记录替换成文件中的数据。我们表单中的数据不是最新的,需要用文件中的数据去更新,因而就用r这个选项,替代数据库中已经有的记录。l选项的作用是在我们插入数据的时候锁住表,这样就阻止了用户在我们更新表的时候对表进行查询或者更改的操作。 批处理是一种非交互式运行MySQL程序的方法,如同您在MySQL中使用的命令一样,你仍然将使用这些命令。 为了实现批处理,您重定向一个文件到MySQL程序中,首先我们需要一个文本文件,这个文本文件包含有与我们在MySQL中输入的命令相同的文本。 比如我们要插入一些数据,使用包含下面文本的文件(文件名为New_Data.sql,当然我们也可

19、以取名为New_Data.txt及任何其他的合法名字,并不一定要以后缀sql结尾): USE Meet_A_Geek; INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, Block); INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, Newton); INSERT INTO Customers (Customer_ID, Last_Name) VALUES(NULL, Simmons); 注意上面的这些句子的语法都必须是正确的,并且每个句子以分号结束。

20、上面的USE命令选择数据库,INSERT命令插入数据。 下面我们要把上面的文件导入到数据库中,导入之前要确认数据库已经在运行,即是MySQLd进程(或者说服务,Windows NT下面称为”服务“,unix下面为”进程“)已经在运行。 然后运行下面的命令: bin/MySQL p mysqldump -tab=/path/to/some/dir -opt -full 你也可以简单地拷贝所有的表文件(“*.frm”、“*.MYD”和“*.MYI”文件),只要服务器不在更新任何东西。(frame,data,index) 2. 停止mysqld如果它正在运行,然后以-log-update选项启动它。你将得到一个名为 “hostname.n”形式的日志文件,这里n是随着你每次执行mysqladmin refresh或 mysqladmin flush-logs、FLUSH LOGS语句、或重启服务器而递增的一个数字。这些日志文件向你提供了在你执行mysqldump处后面进行的复制数据库改变的所需 信息。 如果你必须恢复一些东西,尝试首先用myisamchk -r恢复你的表。这应该处理所有情况的99.9%。如