博客

mysql批量导入与导出csv文件

  • Post author:
  • Post category:mysql



一.导入

数据格式：
38685553,48892651535927700000,525510
38685554,46572883155250700000,525510
38685555,52446794994419400000,525510
38685556,53130337139017900000,525510
38685557,41372542661334700000,525510
38685558,39889333997901200000,525510
38685559,48892651535927700000,525510
38685560,46572883155250700000,525510
38685561,52446794994419400000,525510
38685562,53130337139017900000,525510
在处理csv文件时，如果包含中文，有可能会出现编码问题，可以在linux下使用如下命令进行转码：


iconv -f GB2312 -t UTF-8 old.csv > new.csv


如果转换失败，说明被转换的内容超过了字符集的限定，所以可以使用如下的字符集进行转换：


GBK

GB18030



字符集范围：


GB18030 > GBK > GB2312


转码后用more查看时显示中文正常即可。
CREATE TABLE `tbl_card_point_i` (
`capo_num` int(11) DEFAULT NULL COMMENT ‘序号’,
`capo_password` varchar(255) DEFAULT NULL COMMENT ‘密码’,
`capo_picinum` int(11) DEFAULT NULL COMMENT ‘批次’,
`capo_id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘ID’,
`capo_agency_name` varchar(255) DEFAULT NULL COMMENT ‘代理loginid’,
PRIMARY KEY (`capo_id`),
UNIQUE KEY `capo_num` (`capo_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’数据明细’;

命令方式：
mysqlimport –local -h 192.168.36.29 –user=root –password=password cardstore  -i  –fields-enclosed-by='”‘ –fields-terminated-by=’,’ –lines-terminated-by=’\r\n’ ~/tbl_card_point_i.csv
参数说明：
-L, –local         Read all files through the client. 读取的所有文件都是基于执行命令时所在的客户端。
-h, –host=name     Connect to host. 数据库服务器的IP地址。
-u, –user=name     User for login if not current user.
-p, –password[=name]
Password to use when connecting to server. If password is
not given it’s asked from the tty.

-i, –ignore        If duplicate unique key was found, keep old row.

如果希望替换，需要使用 -r 参数，两个参数不能同时出现。
–fields-enclosed-by='”‘：每个字段以空字符结尾。
–fields-terminated-by=’,’：字段间以逗号分隔。
–lines-terminated-by=’\r\n’：以回车符为每行的结束。
此方法要求文件名称必须为表名称“

tbl_card_point_i”。


此时由于没有指定数据与字段对应方式，所以默认按表中字段顺序导入，也就是前三个字段，如果要指定导入的字段，这需要按如下方法导入:
mysqlimport –local -h 192.168.36.29 –user=root –password=password cardstore  -i  –fields-enclosed-by='”‘ –fields-terminated-by=’,’ –lines-terminated-by=’\r\n’  –columns=capo_num,capo_password,capo_picinum ~/tbl_card_point_i.csv
参数说明：

–columns=capo_num,capo_password,capo_picinum：逗号分隔字段名称。


如果数据文件中的头部有title，可以指定忽略掉头部的几行：
mysqlimport –local -h 192.168.36.29 –user=root –password=password cardstore  -r  –fields-enclosed-by='”‘ –fields-terminated-by=’,’ –lines-terminated-by=’\r\n’  –columns=capo_num,capo_password,capo_picinum –ignore-lines=1 ~/tbl_card_point_i.csv
参数说明：
–ignore-lines=1：忽略掉第一行。
-r, –replace       If duplicate unique key was found, replace old row.
sql执行方式：
登录：mysql -u root -p
切换数据库：use cardstore
mysql> load data local infile ‘~/tbl_card_point_i.csv’

replace

into table tbl_card_point_i FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ ignore 1 lines(capo_num,capo_password,capo_picinum);
参数说明：与mysqlimport命令基本类似，上面是主键重复时执行替换操作，下面是执行保留原数据操作：

mysql>



load data local infile ‘~/tbl_card_point_i.csv’

ignore

into table tbl_card_point_i FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ ignore 1 lines(capo_num,capo_password,capo_picinum);
此种方法不要求文件名称与表名称一致，因为指定了要导入到那张表



into table tbl_card_point_i。
 

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]


二.导出
命令方式：

mysqldump -uroot -ppassword cardstore tbl_card_point_i –fields-enclosed-by='”‘ –fields-terminated-by=’,’ –lines-terminated-by=’\r\n’ –tab=/mysqldump_dir

参数说明：

–tab=/mysqldump_dir：生成文件的路径，这里要确保mysql用户对


/mysqldump_dir有读写权限，


chown mysql:mysql /mysqldump_dir

生成的文件包含两个，一个是数据文件：tbl_card_point_i.txt，一个是见表语句：tbl_card_point_i.sql

如果同时导出多张表，则也会分别生成相应的2个文件。

mysqldump -uroot -ppassword cardstore table1 table2 –fields-enclosed-by='”‘ –fields-terminated-by=’,’ –lines-terminated-by=’\r\n’ –tab=/mysqldump_dir

sql执行方式：

mysql> select * from tbl_card_point_i into outfile ‘/mysqldump_dir/tbl_card_point_i.csv’ fields terminated by ‘,’  ENCLOSED BY ‘”‘ lines terminated by ‘\r\n’;


版权声明：本文为hanqunfeng原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: