在MySQL中将字符转换为UTF8（从é到é）

Question

我有一个MySQL问题。我的整个网站除了数据库以外都是UTF8编码的。我最近尝试使用各种命令将其转换为UTF8，但主要问题仍然存在：重音符号无法正常显示。例如，é应该显示为é。同样的问题也出现在è上，会显示其他奇怪的字符。

是否有一种简单的方法可以将所有这些字符（比如é）转换为它们应该显示的形式（例如é），并存储到数据库中？

顺便提一下，数据库采用的是latin1编码。

- Yannick Bloem

---

可能是MySQL将latin1数据转换为UTF8的重复问题。 - Wooble

Answer 1

在这种情况下，é 被解释为LATIN1并转换为UTF-8。也许你的数据库试图提供帮助，在不需要转换的情况下进行了UTF-8转换。

你尝试过切换数据库表的字符集吗？

- tadman

---

是的，我确实有。它说它是UTF8格式，但问题仍然存在。奇怪的是，是否有将已经存在的字符转换为普通字符的解决方案？ - Yannick Bloem

我已经为相关问题提供了答案，可以在此找到：https://dev59.com/JWox5IYBdhLWcg3wSCfk。如果你感觉勇敢，而且备份完备的话，可以将其转换成一个更新语句。 - tadman

非常感谢。我要试一试。我感觉很勇敢，而且我有备份，所以为什么不呢 :-) - Yannick Bloem

Answer 2

你可以尝试这个函数：

CREATE TABLE `utf8decodemap` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `encoded` VARCHAR(128) NOT NULL,
  `decoded` VARCHAR(128) NOT NULL,
  UNIQUE KEY urlcodemapUIdx1(encoded),
  PRIMARY KEY (`id`)  
);

INSERT INTO utf8decodemap (decoded,encoded) VALUES ("â","â");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("é","é");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("è","è");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ê","ê");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ë","ë");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("î","î");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ï","ï");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ô","ô");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ö","ö");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ù","ù");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("û","û");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ü","ü");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ç","ç");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("œ","Å?");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("€","â¬");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("°","°");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("à","Ã");

DELIMITER $$
CREATE FUNCTION `utf8decoder`(str VARCHAR(4096)) RETURNS VARCHAR(4096) DETERMINISTIC
BEGIN
               DECLARE X  INT;               
               DECLARE chr VARCHAR(256);
               DECLARE chrto VARCHAR(256);
               DECLARE result VARCHAR(4096);
               SET X = 1;
               WHILE X  <= (SELECT MAX(id) FROM utf8decodemap) DO
                   SET chr = (SELECT `encoded` FROM utf8decodemap WHERE id = X);
                   SET chrto = (SELECT `decoded` FROM utf8decodemap WHERE id = X);                
                           SET str = REPLACE(str,chr,chrto);
                           SET  X = X + 1;                           
               END WHILE;
               RETURN str;
       END$$

DELIMITER ; 

例子：从MYtable表中查询MyCol列，使用utf8decoder函数

- Louk Oum

Answer 3

感谢上面的列表，也许这个PHP脚本可以帮助某些人

1- 解决方案A

$title = mb_convert_encoding($article['post_title'], 'ISO-8859-1', 'UTF-8');

2- 解决方案 B

function convertSpacialChars($text)
{
    $charsMap = [
        "â" => "â",
        "é" => "é",
        "è" => "è",
        "ê" => "ê",
        "ë" => "ë",
        "î" => "î",
        "ï" => "ï",
        "ô" => "ô",
        "ö" => "ö",
        "ù" => "ù",
        "û" => "û",
        "ü" => "ü",
        "ç" => "ç",
        "Å?" => "œ",
        "â¬" => "€",
        "°" => "°",
        "Ã" => "à",
    ];
    return str_replace(array_keys($charsMap),array_values($charsMap),$text);
}

- Anas Alpure