Можно заставить смайлы отображаться на PHP с MySQL 5.x, но не с 8.x

У меня есть проект PHP 7.3, который в настоящее время использует MySQL 5.5 с таблицами utf8. Некоторые таблицы содержат данные смайликов, которые отлично отображаются в текущем проекте. Я пытаюсь обновить проект до MySQL 8.x, но когда я это делаю, данные смайликов отображаются неправильно.

Сначала я обновил все таблицы 5.5 для использования uf8mb4. В этом состоянии данные появились. Затем я обновился до 5.7, и все продолжало работать. Я сбросил эти данные, обновил их до версии 8.0 и перезагрузил (я использовал флаг --default-character-set=utf8mb4 как при дампе, так и при загрузке), а затем данные перестали отображаться правильно, например, лампочка отображалась как 💡.

Я запускаю каждую из этих служб в докере. Мне удалось без проблем выполнить обновление с версии 5.5 до 5.7, используя тот же объем данных, но при попытке обновления с версии 5.7 до 8.0 возникли ошибки, которые я не смог устранить, и в итоге я выполнил дамп/восстановление данных.

Пример таблицы с полем со смайлом:

DROP TABLE IF EXISTS `forums`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `forums` (
  `forumID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `forumType` varchar(1) COLLATE utf8_unicode_ci DEFAULT 'f',
  `parentID` int(11) DEFAULT NULL,
  `heritage` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `order` int(5) NOT NULL,
  `gameID` int(11) DEFAULT NULL,
  `threadCount` int(11) NOT NULL,
  PRIMARY KEY (`forumID`),
  UNIQUE KEY `heritage` (`heritage`),
  KEY `parentID` (`parentID`)
) ENGINE=MyISAM AUTO_INCREMENT=11551 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `forums`
--

LOCK TABLES `forums` WRITE;
/*!40000 ALTER TABLE `forums` DISABLE KEYS */;
INSERT INTO `forums` VALUES (8003,'💡 Gamers\' Plane development',NULL,'f',2,'0002-8003',3180,3181,4);
/*!40000 ALTER TABLE `forums` ENABLE KEYS */;
UNLOCK TABLES;

Чтобы обновить таблицу до utf8mb4, я сделал

ALTER TABLE forums CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Как я тестирую возврат этих данных:

<?php
$mysql = new PDO("mysql:host=mysql;dbname=gamersplane", 'gamersplane', 'mypass');
$mysql->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$forum = $mysql->query('select * from forums where forumID = 8003')->fetch();
?>
<html>
<header>
        <meta http-equiv = "Content-Type" content = "text/html; charset=UTF-8">
</header>
<body>
<?php print_r($forum['title']); ?>
</body>
</html>

Я прочитал [UTF-8 полностью][1] и

[1]: UTF-8 полностью и

  • я использую utf8mb4 в базе данных
  • есть charset=utf8mb4 в моей строке PDO
  • Я явно установил default_charset в моем php.ini, а также попытался установить его во время выполнения
  • попробовали Content-Type: text/html; charset=utf-8 в качестве заголовка PHP, а также метатега HTML

🤔 А знаете ли вы, что...
PHP позволяет встраивать код в HTML-страницы с использованием специальных тегов <?php ?>.


51
1

Ответ:

Решено

Кодирование в базах данных — это всегда очень весело! К сожалению, когда вы меняете набор символов, он не обновляет данные, а только то, как база данных интерпретирует данные, а MySQL не выполняет изменение кодировки «на лету» и всегда записывает байты по мере их поступления от клиента. Из примера вы можете видеть, что 💡 — это latin1 представление 💡, и когда вы выгружаете данные, он выгружает их уже в неправильной кодировке.

Чтобы проверить проблему, вы можете попытаться преобразовать данные с помощью запроса:

SELECT
  CONVERT(BINARY(CONVERT(title USING latin1)) USING utf8mb4)
FROM forums 
WHERE id = 8003;

в вашей последней среде MySQL8 эмодзи должны отображаться правильно. Если это так, вам следует попытаться снова сбросить данные, и на этот раз использовать кодировку, в которой они были закодированы изначально, скорее всего, latin1 с использованием --default-character-set=latin1. Файл дампа должен содержать смайлы вместо текста в виде 💡.

Имейте в виду, что если у вас есть новый контент в таблице, он будет дважды закодирован или заполнение дампа не удастся. Если новый текст несовместим с кодировкой latin1, лучше сделать это с исходным набором, если у вас все еще есть доступ к нему.