MySQL Unicode Unterstützung: Unterschied zwischen den Versionen
(Die Seite wurde neu angelegt: „{{Zum Überarbeiten}} {{Neu}} en:MySQL_full_unicode_support“) |
Keine Bearbeitungszusammenfassung |
||
| Zeile 1: | Zeile 1: | ||
{{Servereinstellungen}} | |||
{{Zum Überarbeiten}} | {{Zum Überarbeiten}} | ||
{{Neu}} | {{Neu}} | ||
==UTF-8== | |||
UTF-8 ist ein Zeichensatz, den die meisten Webseiten verwenden. Dieser Zeichensatz codiert 1 112 064 gültige Zeichen. Um diese Zeichen zu kodieren, werden 4 Byte benötigt. Die am häufigsten vorkommenden Zeichen können mit 3 Bytes kodiert werden. MySQL verwendet standardmäßig nur 3 Bytes zum Kodieren, so dass Zeichen, die mit 4 Bytes kodiert werden (z.B. asiatische Schriftzeichen und Emojis), nicht gespeichert werden können. Alle Versuche, in einem Moodle-Text ein 4 Byte Zeichen einzugeben, führen daher zu einem Moodle-Datenbank-Fehler. | |||
MySQL unterstützt auch UTF-8 Zeichen, die mit 4 Bytes kodiert werden, aber dafür müssen geeignete Datenbankeinstellungen vorgenommen werden. Bestehende Moodle-Datenbanken müssen daher entsprechend konfiguriert werden, um die volle MySQL-Unterstützung zu gewährleisten. | |||
Moodle stellt dafür ein CLI-Kommandozeilen-Skript zur Konvertierung der Datenbank bereit, so dass danach UTF-8 für MySQL (und MariaDB) voll unterstützt wird. | |||
danach volle MySQLkonvertiert comes with a Command Line Interface (CLI) script for converting to full UTF-8 for MySQL (and MariaDB). Before Moodle versions 3.1.5 and 3.2.2 this conversion tool would only change the Collation to some variant of 'utf8_bin'. 'utf8_unicode_ci' was the recommended Collation. We now recommend using 'utf8mb4_unicode_ci' which supports four byte characters (utf8_unicode_ci only supports three). | |||
This script will attempt to change the database Collation, Character set, default table settings and column definitions. | |||
To summarise: | |||
* Fresh installs of Moodle 3.1.5 and 3.2.2 onwards will use utf8mb4 by default, if the database server is configured appropriately (see below). | |||
* Sites upgrading to Moodle 3.1.5 or 3.2.2 can use the script to update to utf8mb4. In Moodle 3.3 a warning will show that the database isn't using full UTF-8 support and suggest moving to 'utf8mb4_unicode_ci', but you may choose to keep using 'utf8_*'. | |||
===Dateiformat=== | |||
To allow for large indexes on columns that are a varchar, a combination of settings needs to be set. The file format for the system needs to be using "Barracuda". This allows for the row format to be set to "Compressed" or "Dynamic". To enable this setting see the upgrade steps listed below. | |||
===File per table=== | |||
To enable this setting see the upgrade steps listed below. | |||
===Large prefix=== | |||
This in conjunction with the row format being either "Compressed" or "Dynamic" allows for large varchar indexes above 191 characters. | |||
To enable this setting see the upgrade steps listed below. | |||
==Aktualisierung== | |||
Most important: Please backup your database before making any changes or running the CLI script. | |||
* Change configuration settings for MySQL (exactly the same for MariaDB). This step is optional. You can run the script and it will try and make these changes itself. If errors occur then try manually changing these settings as listed below. | |||
** On Linux based systems you will want to alter my.cnf. This may be located in '/etc/mysql/'. | |||
** Make the following alterations to my.cnf: | |||
<code> | |||
[client] | |||
default-character-set = utf8mb4 | |||
[mysqld] | |||
innodb_file_format = Barracuda | |||
innodb_file_per_table = 1 | |||
innodb_large_prefix | |||
character-set-server = utf8mb4 | |||
collation-server = utf8mb4_unicode_ci | |||
skip-character-set-client-handshake | |||
[mysql] | |||
default-character-set = utf8mb4 | |||
</code> | |||
* Restart your MySQL server. | |||
* Run the CLI script to convert to the new Character set and Collation (requires Moodle 3.1.5, 3.2.2 or newer): | |||
<pre> | |||
$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci | |||
</pre> | |||
'''NOTE: For large sites, this script will be slow. It is recommended to dump and reimport your data according to https://docs.moodle.org/33/en/Converting_your_MySQL_database_to_UTF8#Default_Mysql_character_set''' | |||
* Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server: | |||
<pre> | |||
$CFG->dboptions = array( | |||
… | |||
'dbcollation' => 'utf8mb4_unicode_ci', | |||
… | |||
); | |||
</pre> | |||
* Try adding some Emojis (e.g. 😂💩) to your Moodle site to verify that the upgrade was successful. | |||
[[Category:Environment|UTF-8]] | |||
[[Category:UTF-8]] | |||
[[en:MySQL_full_unicode_support]] | [[en:MySQL_full_unicode_support]] | ||
Version vom 29. Juni 2017, 09:47 Uhr
Diese Seite muss überarbeitet werden.
Neue Funktionalität
in Moodle 3.3!
UTF-8
UTF-8 ist ein Zeichensatz, den die meisten Webseiten verwenden. Dieser Zeichensatz codiert 1 112 064 gültige Zeichen. Um diese Zeichen zu kodieren, werden 4 Byte benötigt. Die am häufigsten vorkommenden Zeichen können mit 3 Bytes kodiert werden. MySQL verwendet standardmäßig nur 3 Bytes zum Kodieren, so dass Zeichen, die mit 4 Bytes kodiert werden (z.B. asiatische Schriftzeichen und Emojis), nicht gespeichert werden können. Alle Versuche, in einem Moodle-Text ein 4 Byte Zeichen einzugeben, führen daher zu einem Moodle-Datenbank-Fehler.
MySQL unterstützt auch UTF-8 Zeichen, die mit 4 Bytes kodiert werden, aber dafür müssen geeignete Datenbankeinstellungen vorgenommen werden. Bestehende Moodle-Datenbanken müssen daher entsprechend konfiguriert werden, um die volle MySQL-Unterstützung zu gewährleisten.
Moodle stellt dafür ein CLI-Kommandozeilen-Skript zur Konvertierung der Datenbank bereit, so dass danach UTF-8 für MySQL (und MariaDB) voll unterstützt wird.
danach volle MySQLkonvertiert comes with a Command Line Interface (CLI) script for converting to full UTF-8 for MySQL (and MariaDB). Before Moodle versions 3.1.5 and 3.2.2 this conversion tool would only change the Collation to some variant of 'utf8_bin'. 'utf8_unicode_ci' was the recommended Collation. We now recommend using 'utf8mb4_unicode_ci' which supports four byte characters (utf8_unicode_ci only supports three).
This script will attempt to change the database Collation, Character set, default table settings and column definitions.
To summarise:
- Fresh installs of Moodle 3.1.5 and 3.2.2 onwards will use utf8mb4 by default, if the database server is configured appropriately (see below).
- Sites upgrading to Moodle 3.1.5 or 3.2.2 can use the script to update to utf8mb4. In Moodle 3.3 a warning will show that the database isn't using full UTF-8 support and suggest moving to 'utf8mb4_unicode_ci', but you may choose to keep using 'utf8_*'.
Dateiformat
To allow for large indexes on columns that are a varchar, a combination of settings needs to be set. The file format for the system needs to be using "Barracuda". This allows for the row format to be set to "Compressed" or "Dynamic". To enable this setting see the upgrade steps listed below.
File per table
To enable this setting see the upgrade steps listed below.
Large prefix
This in conjunction with the row format being either "Compressed" or "Dynamic" allows for large varchar indexes above 191 characters. To enable this setting see the upgrade steps listed below.
Aktualisierung
Most important: Please backup your database before making any changes or running the CLI script.
- Change configuration settings for MySQL (exactly the same for MariaDB). This step is optional. You can run the script and it will try and make these changes itself. If errors occur then try manually changing these settings as listed below.
- On Linux based systems you will want to alter my.cnf. This may be located in '/etc/mysql/'.
- Make the following alterations to my.cnf:
[client]
default-character-set = utf8mb4
[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake
[mysql]
default-character-set = utf8mb4
- Restart your MySQL server.
- Run the CLI script to convert to the new Character set and Collation (requires Moodle 3.1.5, 3.2.2 or newer):
$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
NOTE: For large sites, this script will be slow. It is recommended to dump and reimport your data according to https://docs.moodle.org/33/en/Converting_your_MySQL_database_to_UTF8#Default_Mysql_character_set
- Adjust the $CFG->dboptions Array in your config.php to make sure that Moodle uses the right Collation when connecting to the MySQL Server:
$CFG->dboptions = array( … 'dbcollation' => 'utf8mb4_unicode_ci', … );
- Try adding some Emojis (e.g. 😂💩) to your Moodle site to verify that the upgrade was successful.
