- The original format of the data is unknown
- The new table is in
utf8mb4_general_ci
If I do CONVERT(BINARY CONVERT(column USING latin1) USING UTF8)
as mentioned here - it fixes all text, but converts something like: ©
in the original column to ?
in the new column.
If it helps to determine what original encoding it was in, the original text renders as e.g. KotaÄići
and converts to Kotačići
.
Is there a way to both preserve special characters and restore correct utf8 text format?
As requested in the comments an example via hex:
HEX(col)
:
C398C2A3C398C2BAC399E280A0C399C5A0C398C2A920C398C2B3C399E280A620C398C2A7C399E2809EC399E2809EC399E280A1
CONVERT(BINARY CONVERT(col USING latin1) USING UTF8)
:
أغنية سم الله
Just raw:
أغنية سم الله
The dump-file starts with:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
Tables get created with: ENGINE=InnoDB DEFAULT CHARSET=utf8mb4