WackoWiki: Converting your MySQL database to UTF8

https://wackowiki.org/doc     Version: 27 (12/30/2021 10:48)

Converting your MySQL database to UTF8

This document describes how to convert your MySQL database from the latin1 charset to UTF8[link1] (utf8mb4[link2]). WackoWiki requires that your database is now UTF8 and will not upgrade if your database is not.

  1. WackoWiki applications used in multi-language mode require a on record conversion.
    1. iconv('ISO-8859-1', 'UTF-8', $text)
    2. iconv('ISO-8859-2', 'UTF-8', $text)
    3. iconv('ISO-8859-7', 'UTF-8', $text)
    4. iconv('CP1251', 'UTF-8', $text)
    5. iconv('CP1257', 'UTF-8', $text)
  2. Unicode entities also require a conversion to UTF-8.
    1. $output = preg_replace_callback('/(&#[0-9]+;)/', function($m) { return mb_convert_encoding($m[1], 'UTF-8', 'HTML-ENTITIES'); }, $input);

Migrating to Unicode

database:
 ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_520_ci;


each table:
ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_520_ci;


each column:
 ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_520_ci;

Modify connection, client, and server character sets

Make sure to set the client and server character set as well.
/etc/my.cnf:
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_520_ci


Check if these settings work correctly:
 mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+------------------------+
| Variable_name            | Value                  |
+--------------------------+------------------------+
| character_set_client     | utf8mb4                |
| character_set_connection | utf8mb4                |
| character_set_database   | utf8mb4                |
| character_set_filesystem | binary                 |
| character_set_results    | utf8mb4                |
| character_set_server     | utf8mb4                |
| character_set_system     | utf8                   |
| collation_connection     | utf8mb4_unicode_520_ci |
| collation_database       | utf8mb4_unicode_520_ci |
| collation_server         | utf8mb4_unicode_520_ci |
+--------------------------+------------------------+
10 rows in set (0.00 sec)

Repair and optimize all tables

Run the following MySQL queries for each table you want to repair and optimize:
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;


This can easily be done in one go using the command-line mysqlcheck utility:
 $ mysqlcheck -u root -p --auto-repair --optimize --all-databases