如何在mysql LOAD DATA INFILE查询中使用unicode字符作为字段分隔符

Question

我正在尝试使用下面的查询将文件导入MySql数据库。文件中的字段由‘π’(断开的竖线) U+00A6分隔。我正在执行来自PhpMyAdmin的查询

LOAD DATA LOCAL INFILE "/path/filename.csv" INTO TABLE tablename 
CHARACTER SET 'UTF8'
FIELDS TERMINATED BY '¦' 

Answer 1

源文档可能是用不需要的字符编码的，如Excel所做的。尝试在TextEdit或其他基本文本编辑器中打开和保存，确保数据保存为UTF-8。然后：

LOAD DATA LOCAL INFILE "filepath/file.csv" INTO TABLE `table`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY '\¦';

在断开的横线前有转义字符。还要对整个表使用UTF-8Unicode编码，而不仅仅是行。

Answer 2

您可以将其表示为X'A6'

LOAD DATA LOCAL INFILE "/path/filename.csv" INTO TABLE tablename 
CHARACTER SET 'UTF8'
FIELDS TERMINATED BY X'A6'

Answer 3

SET NAMES utf8;   -- You were probably missing this (or the equivalent)
LOAD DATA LOCAL INFILE "/path/filename.csv" INTO TABLE tablename 
    CHARACTER SET 'UTF8'
    FIELDS TERMINATED BY '¦' 

(假设您可以输入utf8，而不是latin1)

线索是"Â" gibrish character at the end of every sting，和¦一起成为¦。