mysql csv批量update

mysql csv批量update

一.导入

数据格式：

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='rn' ~/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='rn'：以回车符为每行的结束。

此方法要求文件名称必须为表名称“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='rn'  --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='rn'  --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' replaceinto table tbl_card_point_i FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'rn' ignore 1 lines(capo_num,capo_password,capo_picinum);

参数说明：与mysqlimport命令基本类似，上面是主键重复时执行替换操作，下面是执行保留原数据操作：

mysql>load data local infile '~/tbl_card_point_i.csv' ignoreinto table tbl_card_point_i FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'rn' 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='rn' --tab=/mysqldump_dir

参数说明：

--tab=/mysqldump_dir：生成文件的路径，这里要确保mysql用户对/mysqldump_dir有读写权限，chown mysql:mysql /mysqldump_dir

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

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

mysqldump -uroot -ppassword cardstore table1 table2 --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='rn' --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 'rn';