mysql批量导入与导出csv文件

一.导入

数据格式：

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';