MySQL的 - 加载与LOAD DATA LOCAL INFILE
问题描述:
我试图从TXT文件的数据加载到下面的MySQL表西里尔数据MySQL的 - 加载与LOAD DATA LOCAL INFILE
CREATE TABLE test_cyrillic
(
id INT,
name NATIONAL VARCHAR(200)
);
与数据文件看起来像
1 Отзывы › Техника и оборудование
数据库创建使用UTF8字符集和utf8_general_ci归类。
的命令看起来像
LOAD DATA LOCAL INFILE 'S:\\Projects\\MyDir\\test_cyrillic.txt'
INTO TABLE test_cyrillic
CHARACTER SET utf8
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
LINES TERMINATED BY '\r\n'
然而,从该表中,我有以下输出
id name
0 B7K2K : "5E=8:0 8 >1>@C4>20=85
选择数据,而通过INSERT语句插入数据时
insert into test_cyrillic values(2,N'Отзывы › Техника и оборудование')
一切好。 任何人都可以告诉我在这种情况下LOAD DATA有什么问题?
答
我无法重现该问题。
文件:test_cyrillic.txt
:
1 Отзывы › Техника и оборудование
mysql命令行:
mysql> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 5.6.25 |
+-----------------+
1 row in set (0.00 sec)
mysql> CREATE DATABASE test CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
Query OK, 1 row affected (0.00 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE test_cyrillic (
-> id INT,
-> name VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
->);
Query OK, 0 rows affected (0.01 sec)
mysql> LOAD DATA INFILE '/path/to/file/test_cyrillic.txt'
-> INTO TABLE test_cyrillic
-> CHARACTER SET utf8
-> FIELDS TERMINATED BY '\t'
-> OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
-> LINES TERMINATED BY '\r\n';
Query OK, 1 row affected (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT id, name FROM test_cyrillic;
+------+-------------------------------------------------------------+
| id | name |
+------+-------------------------------------------------------------+
| 1 | Отзывы › Техника и оборудование |
+------+-------------------------------------------------------------+
1 row in set (0.00 sec)
哦,对了,你看不到这个问题可能是因为你的文本文件是UTF-8编码的,但我的是ANSI。看起来我有线索,谢谢。 – MaterialGirl