mysql - Ñ 和其他可能被截断的特殊字符 MySQL

Question

我正在尝试用一些包含一些拉丁字符的行填充我的数据库中的一个表，特别是“Ñ”。此列表来 self 转换为 csv 文件的 excel。

我的 CSV 文件包含如下内容:

"1619","137601000","CITY OF LAS PIÑAS","METRO MANILA","NCR","National Capital Region","MM""1620","137602000","CITY OF MAKATI","METRO MANILA","NCR","National Capital Region","MM""1627","137604000","CITY OF PARAÑAQUE","METRO MANILA","NCR","National Capital Region","MM"

I load this list to my target table with the following command:

LOAD DATA LOCAL INFILE '/my/path/to/file.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

我也尝试过将 CHARACTER SET 添加到我的加载语句中，但我得到了相同的结果:

LOAD DATA LOCAL INFILE '/my/path/to/file.csv' INTO TABLE mytable CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

但是加载后，当我检查表格的内容时，包含“Ñ”的字段被截断了。

| 1619 | 137601000 | CITY OF LAS PI                                 | METRO MANILA              | NCR    | National Capital Region              | MM   || 1620 | 137602000 | CITY OF MAKATI                                 | METRO MANILA              | NCR    | National Capital Region              | MM   || 1627 | 137604000 | CITY OF PARA                                   | METRO MANILA              | NCR    | National Capital Region              | MM   |

Tried some of the suggestion on this resource but still to no avail.

I tried to extract the content of the table as it may only be a representation problem, but field with "Ñ" were really truncated.

Below are helpful infos that may help in resolving this issue.

I created my database with with this command:

CREATE DATABASE myDB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci

还有我的 table :

CREATE TABLE `location` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`code` int(11) NOT NULL,
`city` varchar(60) NOT NULL,
`province` varchar(30) NOT NULL,
`region` varchar(4) NOT NULL,
`regionname` varchar(40) NOT NULL,
`area` varchar(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

操作系统变体和版本

CentOS Linux release 7.1.1503 (Core)
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

CentOS Linux release 7.1.1503 (Core)
CentOS Linux release 7.1.1503 (Core)

MySQL 变体和版本

• mysql Ver 15.1 Distrib 5.5.41-MariaDB，适用于使用 readline 5.1 的 Linux (x86_64)

表格列字符设置

+-------------+--------------------+-----------------+| column_name | character_set_name | collation_name  |+-------------+--------------------+-----------------+| id          | NULL               | NULL            || code        | NULL               | NULL            || city        | utf8               | utf8_unicode_ci || province    | utf8               | utf8_unicode_ci || region      | utf8               | utf8_unicode_ci || regionname  | utf8               | utf8_unicode_ci || area        | utf8               | utf8_unicode_ci |+-------------+--------------------+-----------------+

我的数据库设置

+-------------+----------------------------------------------------------------------------------------------+| Database    | Create Database                                                                              |+-------------+----------------------------------------------------------------------------------------------+| myDbName    | CREATE DATABASE `myDbName` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */    |+-------------+----------------------------------------------------------------------------------------------+

Answer 1

截断通常是由于

• 客户使用 latin1 编码的重音字符，并且
• SET NAMES utf8 在 INSERT 期间生效(或在您的情况下为 LOAD DATA)

也就是说，csv文件并不是真正的utf8格式。在 LOAD DATA 中尝试一些其他 CHARACTER SET，可能是 latin1。