使用mysql LOAD从txt文件中导入数据库,发现文件属性CRLF的导入有问题:
[ebsql@appdb1 mysql-files]$ file jushuju.all.20240110
jushuju.all.20240110: Unicode text, UTF-8 text, with CRLF line terminators
[ebsql@appdb1 mysql-files]$ file jushuju.all.20240110.unix
jushuju.all.20240110.unix: Unicode text, UTF-8 text
通过使用dos 转换成unix解决:
iconv -f GBK -t UTF-8 dos2unix jushuju.all.20240110 > dos2unix jushuju.all.20240110.unl
rpm -ivh dos2unix-6.0.3-7.el7.x86_64.rpm
dos2unix jushuju.all.20240110
使用 with CRLF line terminators导入的数据发现少一行竖线,查询电话号码也是有误的。
使用正常的unix文件可以正常导入:
mysql> truncate table numtabmob_original;
Query OK, 0 rows affected (0.01 sec)
mysql> LOAD DATA INFILE '/home/ebsql/mysql/mysql-files/jushuju.all.20240110.unix' INTO TABLE numtabmob_original FIELDS TERMINATED BY '|' LINES TERMINATED BY 'n';
Query OK, 205731 rows affected (1.06 sec)
Records: 205731 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from numtabmob_original where provicence='咸阳';