注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

MySQL数据导出和导入  

2015-11-16 09:41:10|  分类: MySQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
以下内容是MySQL学习笔记
mysqlimport导入数据,mysqlimport相当于Oracle sql loader。
1. 建立表结构
CREATE TABLE `ld_csv1` (
  `班长` varchar(20) NOT NULL,
  `姓名` varchar(20) NOT NULL,
  `手机号码` varchar(30) NOT NULL,
  `原因` varchar(20) NOT NULL,
  `原因细分` varchar(20) NOT NULL,
  `查询航班周期` varchar(20) NOT NULL DEFAULT '');
2. 上传utf8格式的txt或csv文件到服务器/home/mysql,并将文件命名为ld_csv1,导入文件名根表名必须一致。
3. 导入 
mysqlimport -usystem -p'123456' -S /data/mysqldata/3306/mysql.sock test --fields-terminated-by=','  /home/mysql/ld_csv1
mysqlimport -usystem -p'123456' -S /data/mysqldata/3306/mysql.sock test --fields-terminated-by=',' /home/mysql/ld_csv1 --fields-enclosed-by='"'
mysqlimport -usystem -p'123456' -S /data/mysqldata/3306/mysql.sock test --fields-terminated-by=',' /home/mysql/ld_csv1 --fields-enclosed-by=\"

mysqlimport命令详解:
$ mysqlimport -help
Loads tables from text files in various formats.  The base name of the text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and read the text file directly. In other cases the client will open the text
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.

Usage: mysqlimport [OPTIONS] database textfile...
  --default-character-set=name  Set the default character set.
  -c, --columns=name  Use only these columns to import the data to. Give the column names in a comma separated list.
                                      This is same as  giving columns to LOAD DATA INFILE.
  -C, --compress      Use compression in server/client protocol.
  -#, --debug[=name]  Output debug log. Often this is 'd:t:o,filename'.
  --debug-check       Check memory and open file usage at exit.
  --debug-info        Print some debug info at exit.
  --default-auth=name Default authentication client-side plugin to use.
  -d, --delete        First delete all rows from table.   --导入前,先删除表中所有对象
  --fields-terminated-by=name Fields in the input file are terminated by the given string.   --指定每列的分隔符
  --fields-enclosed-by=name Fields in the import file are enclosed by the given character.  --指定包住列值的符号
  --fields-optionally-enclosed-by=name     --同--fields-enclosed-by,区别是如果字符集有限定符则使用限定符,没有的话就忽略。
  --fields-escaped-by=name     Fields in the input file are escaped by the given character.
  -f, --force         Continue even if we get an SQL error.  --导入时遇到错误,仍然继续
  -h, --host=name     Connect to host.
  -i, --ignore        If duplicate unique key was found, keep old row.        --如果插入过程中,发现重复键,那么该条记录不处理。
  -r, --replace       If duplicate unique key was found, replace old row.    --如果插入过程中,发现重复键,则覆盖旧记录。
  --ignore-lines=#    Ignore first n lines of data infile.  --跳过文件中的前n行记录。
  --lines-terminated-by=name   Lines in the input file are terminated by the given string. --指定文件中每行的结束符,默认是换行符。
  -L, --local         Read all files through the client.   --从执行mysqlimport命令的客户端读取文件,默认从服务器端。
  -l, --lock-tables   Lock all tables for write (this disables threads).  --导入时先锁定表
  -s, --silent        Be more silent.  --静默方式导入数据,不输出操作结果。
  -S, --socket=name   The socket file to use for connection.
  --use-threads=#     Load files in parallel. The argument is the number of threads to use for loading data.  --并行方式加载,启动多个线程加载数据。


原文:
1000007#"胡#
三"#"成都"#husan@test.com
1000008#张小三#重庆#zhangsan@test.com

修改后:
1000007#"胡#
三"#"成都"#husan@test.com##
1000008#张小三#重庆#zhangsan@test.com##


mysql修改列属性:alter table navcat_import change `编号` `编号` int(3) DEFAULT 0;


load data infile
基本语法:
load data  [low_priority] [local] infile 'file_name txt' 
[replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name,   )]

(system@localhost) [test]> load data infile '/home/mysql/navcat_import' into table navcat_import fields terminated by ',';
load data infile默认分隔符是Tab键,set names或系统变量character_set_client的设置对导入的数据无效,如果数据文件要导入的字符集和character_set_database不一致,则导入后可能出现乱码,load data infile通过子句charset gbk来解决。
load data infile支持fields terminated by、fields  optionally enclosed by、lines terminated by等数据集处理语法。
mysqlimport命令实际上就是load data infile语句的命令行调用接口,因此两者的参数一致。
(system@localhost) [test]> load data local infile '/home/mysql/navcat_import' into table navcat_import fields terminated by ','; 文件在客户端,则用navcat更方便。使用local方式比服务端更慢些,如果禁用了local_infile选项,就不能以local方式导入了。

load data infile 'myfile.txt' into table db2.my_table;    ./myfile.txt则是data目录下寻找,myfile.txt是指从当前数据库下找文件,而不管指定要导入的库名。

如果指定了secure_file_priv 变量,要处理的文件必须位于该参数指定的路径下。


replace子句,出现重复值时,会替换当前存在的记录。
ignore子句,插入遇到重复值时,跳过重复的记录。 ignore 1 lines 跳过第一行。
如果这两个子句都没有指定,处理行为依赖于是否制定了local关键字,没有指定的话,则出现重复记录时就会报错,如果指定了local,则默认是ignore子句,忽略重复记录。测试重复记录的情况

fields 分隔符默认是tab,lines换行符默认是换行,enclosed包括符默认为空,escaped转义符默认是\, starting指定每行开始位置。
fields和lines这两项子句没有指定,等同于
fields  terminated by '\t' enclosed by '' escaped by '\\' lines  terminated by '\n' starting by '' 

lines  starting by 'zhangsan'  从zhangsan字符之后的开始导入,严重问题:如果使用starting by,如果改行不含有指定字符,则该行记录就会被跳过。

(system@localhost) [test]> load data infile '/home/mysql/ld_csv1' into table ld_csv1 fields terminated by ',' ignore 1 lines;

$ cat ld_csv1 | sed "s/,/|*|/g" > ld_csv2  
vi 替换 :%s/被替换的字符/替换成的字符/g
windows的回车换行\r \n 此时应为lines  terminated by '\r\n'  但部分上传软件可以选择以ascii方式上传,工具能够自动对换行符进行转换,就不用lines 子句了。
如果列值中包括换行符,则对load data infile无压力,只要列值被enclosed by 指定的包括符包括着,则中间出现的任何字符都不受影响。

1. 表对象中的列比数据文件中的列多,指定表列及数据文件列顺序
load data infile '/home/mysql/ld_csv1' into table ld_csv1 fields terminated by ','  (id, uname, email, age);

2. 表对象中的列比数据文件中的列少,指定数据文件列顺序,用 @tmp代替空列
load data infile '/home/mysql/ld_csv1' into table ld_csv1 fields terminated by ','  (id, uname, @tmp, @tmp, email, age);



select ... into outfile导出数据
1. fields和lines的语法,对于load data infile和select ... into outfile语句是相同的,功能也一样。
2. into outfile语句是在标准的select语句上做的扩展。
3. 只要select能查询的数据就可以被输出到外部文件。
4. outfile不仅可以是标准的行列分隔文本文件,也可以是into dumpfile,甚至可以是into var_name,将结果集输出到某个变量。

无论select ... into outfile是在服务器端还是客户端执行,输出的文件都在服务器端。
(system@localhost) [test]> select * from ld_cmd;
+---------+-----------+--------+-------------------+
| id      | user_name | city   | email             |
+---------+-----------+--------+-------------------+
| 1000007 | NULL      | NULL   | NULL              |
| 1000007 | NULL      | NULL   | NULL              |
| 1000007 | 胡#三     | 成都   | husan@test.com    |
| 1000007 | 胡#三     | 成都   | husan@test.com    |
| 1000007 | 胡#三     | 成都   | husan@test.com    |
| 1000008 | 张小三    | 重庆   | zhangsan@test.com |
| 1000007 | 胡#三     | 成都   | husan@test.com    |
| 1000008 | 张小三    | 重庆   | zhangsan@test.com |
| 1000007 | 胡#
三    | 成都   | husan@test.com    |
| 1000008 | 张小三    | 重庆   | zhangsan@test.com |
+---------+-----------+--------+-------------------+
10 rows in set (0.00 sec)

(system@localhost) [test]> select * from ld_cmd into outfile '/home/mysql/ld_outfile';
Query OK, 10 rows affected (0.00 sec)

[mysql@biee2 ~]$ cat ld_outfile
1000007 \N      \N      \N
1000007 胡#三   成都    husan@test.com
1000008 张小三  重庆    zhangsan@test.com
1000007 胡#\
三      成都    husan@test.com
1000008 张小三  重庆    zhangsan@test.com

(system@localhost) [test]> select * from ld_cmd limit 6 into outfile '/home/mysql/ld_outfile2'
    -> fields terminated by ',' enclosed by '"';
Query OK, 6 rows affected (0.00 sec)
[mysql@biee2 ~]$ cat ld_outfile2               
"1000007",\N,\N,\N
"1000007",\N,\N,\N
"1000007","胡#三","成都","husan@test.com"
"1000007","胡#三","成都","husan@test.com"
"1000007","胡#三","成都","husan@test.com"
"1000008","张小三","重庆","zhangsan@test.com"

into dumpfile每次只输出单条记录,不支持fields/lines,不支持列分隔符、换行符、转义符,将表记录原原本本地输出到文件中。
(system@localhost) [test]> select * from ld_cmd where id=1000009 into outfile '/home/mysql/ld_out'; 
Query OK, 1 row affected (0.00 sec)
(system@localhost) [test]> select * from ld_cmd where id=1000009 into dumpfile '/home/mysql/ld_dump';  --失败,未显示成一行
Query OK, 1 row affected (0.00 sec)
into dumpfile处理大字段如text、blob时可能会排上用场。

如果设置了secure_file_priv系统变量,那么输出的文件就保存在该变量指定的目录下。


  评论这张
 
阅读(210)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017