SQL 加载数据特殊字符

发布于 2024-09-11 16:23:33 字数 1125 浏览 5 评论 0原文

我正在尝试将数据从 PHP My Admin 中的 CSV 导入 SQL，因此这可能是 PHP My Admin 问题。我遇到的问题是某些列使用特殊字符，例如：

“Adán，Antonio”

最终只是“Ad”。

列结构如下：

CREATE TABLE IF NOT EXISTS `players` (
  `player_name` varchar(255) COLLATE utf8 NOT NULL,
  `player_nation` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_club` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_position` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_age` tinyint(2) NOT NULL,
  `player_dob` varchar(32) CHARACTER SET utf8 NOT NULL,
  `player_based` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_id` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8;

我猜这与字符集有关，但 mysql.com 只是建议将表更改为字符集 utf8，它已经是了。

我还有什么想法可以防止这种情况发生吗？

更新

插入数据库很好，所以我猜这与我的表结构无关。它似乎专门与从 CSV 导入有关。

这是加载数据的查询，由 PHP My Admin

LOAD DATA LOCAL INFILE 'C:\\Windows\\Temp\\php21E4.tmp' INTO TABLE `players` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'.

解决方案

生成，我发现了问题。我的 CSV 使用西欧字符集进行编码。经过一番努力将其转换为 UTF8 后，它导入得很好。根本不是 SQL 问题。

原文

I'm trying to import data into SQL from a CSV in PHP My Admin so it may be a PHP My Admin problem. The problem i'm having is that some of the columns use special characters for instance:

"Adán, Antonio"

Ends up as just "Ad".

The column structure is as follows:

CREATE TABLE IF NOT EXISTS `players` (
  `player_name` varchar(255) COLLATE utf8 NOT NULL,
  `player_nation` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_club` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_position` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_age` tinyint(2) NOT NULL,
  `player_dob` varchar(32) CHARACTER SET utf8 NOT NULL,
  `player_based` varchar(255) CHARACTER SET utf8 NOT NULL,
  `player_id` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8;

I'm guessing it's something to do with the character set but mysql.com just suggests to alter the table to characters set utf8 which it already is.

Any ideas how else I can prevent this?

UPDATE

Inserting into the database is fine, so i'm guessing it's not to do with my table structure. It seems to be specifically to do with importing from a CSV.

This is the query for load data, as generated by PHP My Admin

LOAD DATA LOCAL INFILE 'C:\\Windows\\Temp\\php21E4.tmp' INTO TABLE `players` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'.

SOLUTION

I discovered the problem. My CSV was encoded with a Western Europe charset. After faffing around for a bit to convert it to UTF8 it imported just fine. Not an SQL problem at all.

分享到QQ

分享到微博