Was ist die beste Kollation für MySQL mit PHP?

Question

Ich frage mich, ob es eine "beste" Wahl für die Sortierung in MySQL für eine allgemeine Website gibt, bei der man sich nicht 100%ig sicher ist, was eingegeben wird? Ich verstehe, dass alle Kodierungen gleich sein sollten, wie MySQL, Apache, HTML und alles in PHP.

In der Vergangenheit habe ich PHP so eingestellt, dass es in "UTF-8" ausgibt, aber zu welcher Sortierung passt das in MySQL? Ich denke, es ist eine der UTF-8-Kollationen, aber ich habe die utf8_unicode_ci , utf8_general_ci y utf8_bin vor.

Answer 1

Der Hauptunterschied besteht in der Sortiergenauigkeit (beim Vergleich von Zeichen in der Sprache) und der Leistung. Die einzige Besonderheit ist utf8_bin, das für den Vergleich von Zeichen im Binärformat gedacht ist.

utf8_general_ci ist etwas schneller als utf8_unicode_ci , aber weniger genau (für die Sortierung). Die spezifische Sprache utf8-Kodierung (wie zum Beispiel utf8_swedish_ci ) enthalten zusätzliche Sprachregeln, die sie für diese Sprachen am genauesten sortierbar machen. Die meiste Zeit verwende ich utf8_unicode_ci (Ich bevorzuge Genauigkeit gegenüber kleinen Leistungsverbesserungen), es sei denn, ich habe einen guten Grund, eine bestimmte Sprache zu bevorzugen.

Sie können mehr über spezifische Unicode-Zeichensätze im MySQL-Handbuch nachlesen - http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html

Answer 2

Wahrscheinlich sollten Sie die utf8_unicode_ci o utf8_general_ci .

• utf8_general_ci sortiert, indem es alle Akzente entfernt und so sortiert, als ob es ASCII wäre
• utf8_unicode_ci verwendet die Unicode-Sortierreihenfolge, so dass in mehr Sprachen korrekt sortiert wird

Wenn Sie jedoch nur englischen Text speichern wollen, sollten sich diese nicht unterscheiden.

Answer 3

Seien Sie sich dieses Problems bewusst, das auftreten kann, wenn Sie utf8_general_ci .

MySQL unterscheidet nicht zwischen einigen Zeichen in Select-Anweisungen, wenn utf8_general_ci Kollationierung verwendet wird. Dies kann zu sehr unangenehmen Fehlern führen - vor allem, wenn es um Benutzernamen geht. Je nach der Implementierung, die die Datenbanktabellen verwendet, könnte dieses Problem es böswilligen Benutzern ermöglichen, einen Benutzernamen zu erstellen, der einem Administratorkonto entspricht.

Dieses Problem tritt zumindest in frühen 5.x-Versionen auf - ich bin nicht sicher, ob sich dieses Verhalten später geändert hat.

Ich bin kein DBA, aber um dieses Problem zu vermeiden, verwende ich immer utf8-bin anstelle einer Groß- und Kleinschreibung.

Das folgende Skript beschreibt das Problem anhand eines Beispiels.

-- first, create a sandbox to play in
CREATE DATABASE `sandbox`;
use `sandbox`;

-- next, make sure that your client connection is of the same 
-- character/collate type as the one we're going to test next:
charset utf8 collate utf8_general_ci

-- now, create the table and fill it with values
CREATE TABLE `test` (`key` VARCHAR(16), `value` VARCHAR(16) )
    CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `test` VALUES ('Key ONE', 'value'), ('Key TWO', 'valúe');

-- (verify)
SELECT * FROM `test`;

-- now, expose the problem/bug:
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get BOTH keys here! MySQLs UTF8 collates that are 
-- case insensitive (ending with _ci) do not distinguish between 
-- both values!
--
-- collate 'utf8_bin' doesn't have this problem, as I'll show next:
--

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get just one key now, as you'd expect.
--
-- This problem appears to be specific to utf8. Next, I'll try to 
-- do the same with the 'latin1' charset:
--

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_general_ci

-- next, convert the values that we've previously inserted
-- in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected). This shows 
-- that the problem with utf8/utf8_generic_ci isn't present 
-- in latin1/latin1_general_ci
--
-- To complete the example, I'll check with the binary collate
-- of latin1 as well:

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected).
--
-- Finally, I'll re-introduce the problem in the exact same 
-- way (for any sceptics out there):

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_generic_ci

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- now, re-check for the problem/bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Two keys.
--

DROP DATABASE sandbox;

Answer 4

Es ist am besten, den Zeichensatz utf8mb4 mit der Zusammenstellung utf8mb4_unicode_ci .

Der Zeichensatz, utf8 unterstützt nur eine geringe Anzahl von UTF-8-Codepunkten, etwa 6 % der möglichen Zeichen. utf8 unterstützt nur die Basic Multilingual Plane (BMP). Es gibt 16 weitere Ebenen. Jede Ebene enthält 65.536 Zeichen. utf8mb4 unterstützt alle 17 Flugzeuge.

MySQL schneidet 4-Byte-UTF-8-Zeichen ab, was zu beschädigten Daten führt.

El utf8mb4 Zeichensatz wurde in MySQL 5.5.3 am 24.03.2010 eingeführt.

Einige der erforderlichen Änderungen zur Verwendung des neuen Zeichensatzes sind nicht trivial:

• Möglicherweise müssen Sie Änderungen in Ihrem Anwendungsdatenbankadapter vornehmen.
• Es müssen Änderungen an my.cnf vorgenommen werden, einschließlich der Einstellung des Zeichensatzes, der Sortierung und der Umstellung von innodb_file_format auf Barracuda
• SQL CREATE-Anweisungen müssen möglicherweise enthalten: ROW_FORMAT=DYNAMIC
  • DYNAMIC ist für Indizes auf VARCHAR(192) und größer erforderlich.

HINWEIS: Das Umschalten auf Barracuda de Antelope kann es erforderlich sein, den MySQL-Dienst mehr als einmal neu zu starten. innodb_file_format_max ändert sich erst, nachdem der MySQL-Dienst neu gestartet wurde: innodb_file_format = barracuda .

MySQL verwendet die alte Antelope InnoDB-Dateiformat. Barracuda unterstützt dynamische Zeilenformate, die Sie benötigen, wenn Sie die SQL-Fehler bei der Erstellung von Indizes und Schlüsseln nach der Umstellung auf den Zeichensatz vermeiden wollen: utf8mb4

• 1709 - Index-Spaltengröße zu groß. Die maximale Spaltengröße beträgt 767 Bytes.

• 1071 - Der angegebene Schlüssel war zu lang; die maximale Schlüssellänge beträgt 767 Bytes

Das folgende Szenario wurde mit MySQL 5.6.17 getestet: Standardmäßig ist MySQL wie folgt konfiguriert:

SHOW VARIABLES;

innodb_large_prefix = OFF
innodb_file_format = Antelope

Stoppen Sie Ihren MySQL-Dienst und fügen Sie die Optionen zu Ihrer bestehenden my.cnf hinzu:

[client]
default-character-set= utf8mb4

[mysqld]
explicit_defaults_for_timestamp = true
innodb_large_prefix = true
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_per_table = true

# Character collation
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

Beispiel einer SQL CREATE-Anweisung:

CREATE TABLE Contacts (
 id INT AUTO_INCREMENT NOT NULL,
 ownerId INT DEFAULT NULL,
 created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 contact VARCHAR(640) NOT NULL,
 prefix VARCHAR(128) NOT NULL,
 first VARCHAR(128) NOT NULL,
 middle VARCHAR(128) NOT NULL,
 last VARCHAR(128) NOT NULL,
 suffix VARCHAR(128) NOT NULL,
 notes MEDIUMTEXT NOT NULL,
 INDEX IDX_CA367725E05EFD25 (ownerId),
 INDEX created (created),
 INDEX modified_idx (modified),
 INDEX contact_idx (contact),
 PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT=DYNAMIC;

• Sie können den Fehler #1709 sehen, der für INDEX contact_idx (contact) si ROW_FORMAT=DYNAMIC wird aus der CREATE-Anweisung entfernt.

HINWEIS: Wenn Sie den Index so ändern, dass er sich auf die ersten 128 Zeichen von contact beseitigt die Anforderung für die Verwendung von Barracuda mit ROW_FORMAT=DYNAMIC

INDEX contact_idx (contact(128)),

Beachten Sie auch: Wenn es heißt, die Größe des Feldes sei VARCHAR(128) das sind nicht 128 Bytes. Sie können 128, 4 Byte Zeichen oder 128, 1 Byte Zeichen verwenden.

Diese INSERT Anweisung sollte das 4-Byte-Zeichen "poo" in der Zeile 2 enthalten:

INSERT INTO `Contacts` (`id`, `ownerId`, `created`, `modified`, `contact`, `prefix`, `first`, `middle`, `last`, `suffix`, `notes`) VALUES
(1, NULL, '0000-00-00 00:00:00', '2014-08-25 03:00:36', '1234567890', '12345678901234567890', '1234567890123456789012345678901234567890', '1234567890123456789012345678901234567890', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678', '', ''),
(2, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '', '', '', '', ''),
(3, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '', '', '123', '', '');

Sie können sehen, wie viel Platz die last Spalte:

mysql> SELECT BIT_LENGTH(`last`), CHAR_LENGTH(`last`) FROM `Contacts`;
+--------------------+---------------------+
| BIT_LENGTH(`last`) | CHAR_LENGTH(`last`) |
+--------------------+---------------------+
|               1024 |                 128 | -- All characters are ASCII
|               4096 |                 128 | -- All characters are 4 bytes
|               4024 |                 128 | -- 3 characters are ASCII, 125 are 4 bytes
+--------------------+---------------------+

In Ihrem Datenbankadapter können Sie den Zeichensatz und die Sortierung für Ihre Verbindung festlegen:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

In PHP würde dies für gesetzt werden: \PDO::MYSQL_ATTR_INIT_COMMAND

Referenzen:

• Mysql 5.6 Referenzhandbuch: Begrenzungen für InnoDB-Tabellen
• Wie man vollen Unicode in MySQL-Datenbanken unterstützt

Answer 5

Kollationen wirken sich darauf aus, wie Daten sortiert werden und wie Zeichenfolgen miteinander verglichen werden. Das heißt, Sie sollten die Sortierreihenfolge verwenden, die die meisten Ihrer Benutzer erwarten.

Beispiel aus dem Dokumentation für den Zeichensatz Unicode :

utf8_general_ci ist ebenfalls zufriedenstellend sowohl für Deutsch als auch für Französisch, außer dass ' gleich 's' ist, und nicht gleich 'ss'. Wenn dies für Ihre Anwendung akzeptabel ist Anwendung akzeptabel ist, dann sollten Sie utf8_general_ci weil es schneller ist. Ansonsten verwenden Sie utf8_unicode_ci weil sie genauer ist.

Es hängt also von der erwarteten Nutzerbasis ab und davon, wie viel Sie brauchen. richtig Sortieren. Für eine englische Benutzerbasis, utf8_general_ci sollte ausreichen, für andere Sprachen, wie z. B. Schwedisch, wurden spezielle Sortierungen erstellt.