mysql-load-data

2020-07-13 宋洋葱 宋洋葱

创建表

CREATE TABLE IF NOT EXISTS `uid_pcode`(
   `uid` bigint,
   `pcode` VARCHAR(32) NOT NULL,
   PRIMARY KEY ( `uid` ) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

/mnt/data1/uid_phone.txt文件内容格式如下

1 13888888888

即：uid\tphone\n

需要对phone对称加密后存储到pcode里

mysql -uroot -p
mysql> LOAD DATA INFILE '/mnt/data1/uid_phone.txt' IGNORE INTO TABLE uid_pcode(@c1,@c2) set uid=@c1,pcode=HEX(aes_encrypt(lower(conv(@c2,10,32)),'yourkey'));

mysql在LOAD DATA完成之前无法查询数据。

查看导入进度：

# 查看mysql的pid
ps -ef | grep mysqld
# 本次是19411
# 查看打开的文件描述符编号
sudo ls -l /proc/19411/fd ｜grep uid_phone.txt
# 本次是34
sudo cat /proc/19411/fdinfo/34
# 打印已读取的字节数
# pos:	621936640

如果需要导出数据

SELECT uid,pcode INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM uid_pcode;

参考文献: