mysql - csv 从 unicode 导入到 utf8 phpmyadmin

Question

我想导入一些汉字到MySQL数据库。但是有些内容被省略了，有些是胡说八道。如下图所示:

数据库中的内容:

excel 文件中的内容:

谢谢!

您好，这里是附加信息:

文本文件

巴掌  bāzhang (a slap of the) palm     打  beat    human activities
巴掌  bāzhang (a slap of the) palm     搧  spank   human activities
巴掌  bāzhang (a slap of the) palm     揍  hit human activities
把   bá  tools and objects with a handle 扫帚  broom   tools
把   bá  tools and objects with a handle 锁   lock    man-made

显示创建表

CREATE TABLE `table 1` (
 `CL_in_Character` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
 `CL_in_Pinyin` varchar(14) CHARACTER SET utf8 DEFAULT NULL,
 `Definition_in_Dictionary` varchar(74) CHARACTER SET utf8 DEFAULT NULL,
 `Associated_nouns_in_Chinese` varchar(16) CHARACTER SET utf8 DEFAULT NULL,
 `Associated_nouns_in_English` varchar(38) CHARACTER SET utf8 DEFAULT NULL,
 `Associated_noun_categories` varchar(38) CHARACTER SET utf8 DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

我无法说明开放文档格式和 phpmyadmin，但是您可以使用以下命令成功导入格式正确的 CSV 数据

LOAD DATA INFILE '/path/to/your/file.csv' INTO TABLE `table 1`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES

我的意思是正确格式化为 CSV

CL_in_Character,CL_in_Pinyin,Definition_in_Dictionary,Associated_nouns_in_Chinese,Associated_nouns_in_English,Associated_noun_categories"巴掌","bāzhang","(a slap of the) palm","打","beat","human activities""巴掌","bāzhang","(a slap of the) palm","搧","spank","human activities""巴掌","bāzhang","(a slap of the) palm","揍","hit","human activities""把","bá","tools and objects with a handle","扫帚","broom","tools""把","bá","tools and objects with a handle","锁","lock","man-made"

让我们试试吧

mysql> CREATE TABLE `table 1` (    ->  `CL_in_Character` varchar(10) CHARACTER SET utf8 DEFAULT NULL,    ->  `CL_in_Pinyin` varchar(14) CHARACTER SET utf8 DEFAULT NULL,    ->  `Definition_in_Dictionary` varchar(74) CHARACTER SET utf8 DEFAULT NULL,    ->  `Associated_nouns_in_Chinese` varchar(16) CHARACTER SET utf8 DEFAULT NULL,    ->  `Associated_nouns_in_English` varchar(38) CHARACTER SET utf8 DEFAULT NULL,    ->  `Associated_noun_categories` varchar(38) CHARACTER SET utf8 DEFAULT NULL    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;Query OK, 0 rows affected (0.03 sec)mysql> LOAD DATA INFILE '/tmp/utf.csv' INTO TABLE `table 1`    -> CHARACTER SET 'utf8'    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'    -> LINES TERMINATED BY '\n'    -> IGNORE 1 LINES;Query OK, 5 rows affected (0.00 sec)Records: 5  Deleted: 0  Skipped: 0  Warnings: 0mysql> select * from `table 1`;+-----------------+--------------+---------------------------------+-----------------------------+-----------------------------+----------------------------+| CL_in_Character | CL_in_Pinyin | Definition_in_Dictionary        | Associated_nouns_in_Chinese | Associated_nouns_in_English | Associated_noun_categories |+-----------------+--------------+---------------------------------+-----------------------------+-----------------------------+----------------------------+| 巴掌            | bāzhang      | (a slap of the) palm            | 打                          | beat                        | human activities           || 巴掌            | bāzhang      | (a slap of the) palm            | 搧                          | spank                       | human activities           || 巴掌            | bāzhang      | (a slap of the) palm            | 揍                          | hit                         | human activities           || 把              | bá           | tools and objects with a handle | 扫帚                        | broom                       | tools                      || 把              | bá           | tools and objects with a handle | 锁                          | lock                        | man-made                   |+-----------------+--------------+---------------------------------+-----------------------------+-----------------------------+----------------------------+5 rows in set (0.00 sec)