Jaki jest najlepszy zestaw do MySQL z PHP? [zamknięte]

Bądź bardzo, bardzo świadomy tego problemu, który może wystąpić podczas używania utf8_general_ci.

MySQL nie rozróżni niektórych znaków w poleceniach select, jeśli używana jest klasyfikacja utf8_general_ci. Może to prowadzić do bardzo paskudnych błędów - zwłaszcza w przypadku, gdy nazwy użytkowników są zaangażowane. W zależności od implementacji, która korzysta z tabel bazy danych, ten problem może umożliwić złośliwym użytkownikom utworzenie nazwy użytkownika pasującej do konta administratora.

Ten problem ujawnia się co najmniej na początku 5.wersje x - nie jestem pewien, czy to zachowanie zmieniło się później.

Nie jestem DBA, ale aby uniknąć tego problemu, zawsze wybieram utf8-bin zamiast niewrażliwego na wielkość liter.

Poniższy skrypt opisuje problem za pomocą przykładu.

-- 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;

Właściwie, prawdopodobnie chcesz użyć utf8_unicode_ci LUB utf8_general_ci.

• utf8_general_ci sortowanie przez usunięcie wszystkich akcentów i sortowanie tak, jakby to było ASCII
• utf8_unicode_ci używa porządku sortowania Unicode, więc sortuje poprawnie w większej liczbie języków

Jednakże, jeśli używasz tego tylko do przechowywania tekstu w języku angielskim, nie powinny się one różnić.

Najlepiej jest użyć zestawu znaków utf8mb4 z kolacją utf8mb4_unicode_ci.

Zestaw znaków, utf8, obsługuje tylko niewielką ilość punktów kodu UTF-8, około 6% możliwych znaków. utf8 obsługuje tylko podstawową płaszczyznę wielojęzyczną (BMP). Istnieje 16 innych samolotów. Każda płaszczyzna zawiera 65536 znaków. Obsługuje wszystkie 17 płaszczyzn.

MySQL będzie obcinał 4-bajtowe znaki UTF-8, co spowoduje uszkodzenie danych.

Zestaw znaków utf8mb4 został wprowadzony w MySQL 5.5.3 2010-03-24.

Niektóre zmiany wymagane do użycia nowego zestawu znaków nie są trywialne:

• może być konieczne wprowadzenie zmian w adapterze bazy danych aplikacji.
• zmiany będą musiały zostać wprowadzone do mojego.cnf, w tym ustawianie zestawu znaków, zestawianie i przełączanie innodb_file_format na Barracuda
• instrukcje SQL CREATE mogą zawierać: ROW_FORMAT=DYNAMIC
  • dynamiczny jest wymagany dla indeksów na VARCHAR(192) i większe.

Uwaga: Przejście na Barracuda z Antelope może wymagać ponownego uruchomienia usługi MySQL więcej niż jeden raz. innodb_file_format_max zmienia się dopiero po ponownym uruchomieniu usługi MySQL na: innodb_file_format = barracuda.

MySQL używa starego formatu pliku Antelope InnoDB. Barracuda obsługuje dynamiczne formaty wierszy, które będą potrzebne, jeśli nie chcesz trafić w Błędy SQL do tworzenia indeksów i kluczy po przełączeniu na zestaw znaków: utf8mb4

• # 1709-indeks wielkości kolumny zbyt duży. Maksymalny rozmiar kolumny to 767 bajtów.
• # 1071-podany klucz był zbyt długi; maksymalna długość klucza to 767 bajtów

Następujący scenariusz został przetestowany na MySQL 5.6.17: Domyślnie MySQL jest skonfigurowany w następujący sposób:

SHOW VARIABLES;

innodb_large_prefix = OFF
innodb_file_format = Antelope

Zatrzymaj usługę MySQL i dodaj opcje do istniejącego my.cnf:

[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

Przykładowa instrukcja SQL CREATE:

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;

• możesz zobaczyć błąd # 1709 wygenerowany dla INDEX contact_idx (contact) jeśli ROW_FORMAT=DYNAMIC zostanie usunięty z CREATE oświadczenie.

Uwaga: Zmiana indeksu na limit do pierwszych 128 znaków na contacteliminuje wymóg używania Barracuda z ROW_FORMAT=DYNAMIC

INDEX contact_idx (contact(128)),

Zauważ również, że jeśli pole ma rozmiar VARCHAR(128), to nie jest to 128 bajtów. Możesz użyć mieć 128, 4-bajtowe znaki lub 128, 1-bajtowe znaki.

To INSERT polecenie powinno zawierać 4-bajtowy znak " poo " w wierszu 2:

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', '', '');

Możesz zobaczyć ilość miejsca używanego przez last kolumna:

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
+--------------------+---------------------+

W adapterze bazy danych możesz ustawić zestaw znaków i kolacje dla połączenia:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

W PHP będzie to ustawione na: \PDO::MYSQL_ATTR_INIT_COMMAND

Bibliografia:

• Mysql 5.6 Reference Manual: Limits on InnoDB Tables
• jak obsługiwać pełny Unicode w bazach danych MySQL

Kolacje wpływają na to, jak dane są sortowane i jak łańcuchy są porównywane ze sobą. Oznacza to, że powinieneś użyć zestawienia, którego oczekuje większość użytkowników.

Przykład z dokumentacji :

utf8_general_ci również jest zadowalający zarówno w języku niemieckim, jak i francuskim, z wyjątkiem że "ß" jest równe "s", a nie "ss". Jeśli jest to dopuszczalne dla Twojego aplikacji, Następnie należy użyć utf8_general_ci ponieważ jest szybszy. W przeciwnym razie użyj utf8_unicode_ci, ponieważ to jest bardziej dokładnie.

Więc - to zależy od oczekiwanej bazy użytkowników i od tego, jak bardzo potrzebujesz poprawnego sortowania. Dla Angielskiej bazy Użytkowników utf8_general_ci powinno wystarczyć, dla innych języków, takich jak Szwedzki, utworzono specjalne zestawienia.

Zasadniczo, to zależy od tego, jak myślisz o łańcuchu.

Zawsze używam utf8_bin ze względu na problem zaznaczony przez Guus. Moim zdaniem, jeśli chodzi o bazę danych, ciąg znaków jest nadal tylko ciągiem znaków. Ciąg znaków to liczba znaków UTF-8. Znak ma reprezentację binarną, więc dlaczego musi znać język, którego używasz? Zazwyczaj ludzie będą konstruować bazy danych dla Systemów o zasięgu dla witryn wielojęzycznych. To jest cały sens używanie UTF - 8 jako zestawu znaków. Jestem trochę pureistą, ale myślę, że ryzyko błędu znacznie przewyższa niewielką przewagę, jaką możesz uzyskać na indeksowaniu. Wszelkie reguły związane z językiem powinny być wykonywane na znacznie wyższym poziomie niż DBMS.

W moich książkach " wartość "Nigdy nie powinna być równa"valúe".

Jeśli chcę zapisać pole tekstowe i wykonać wyszukiwanie bez rozróżniania wielkości liter, użyję funkcji łańcuchowych MYSQL z funkcjami PHP takimi jak LOWER() i php funkcja strtolower().

Dla informacji tekstowych UTF-8, powinieneś użyć utf8_general_ci ponieważ...

• utf8_bin: porównaj ciągi według wartość binarna każdego znaku w ciąg

• utf8_general_ci: porównaj ciągi korzystanie z ogólnych reguł językowych i używanie porównań niewrażliwych na wielkość liter

Przyjęta odpowiedź dość definitywnie sugeruje użycie utf8_unicode_ci, i podczas gdy dla nowych projektów jest to świetne, chciałem odnieść się do moich ostatnich sprzecznych doświadczeń na wypadek, gdyby oszczędziło to komuś trochę czasu.

Ponieważ utf8_general_ci jest domyślną kolacją dla Unicode w MySQL, jeśli chcesz użyć utf8_unicode_ci, musisz określić go w partii miejsc.

Na przykład, wszystkie połączenia klienckie nie tylko mają domyślny charset (dla mnie ma to sens), ale również domyślna Kolacja (tzn. Kolacja zawsze będzie domyślnie ustawiona na utf8_general_ci dla unicode).

Prawdopodobnie, jeśli używasz utf8_unicode_ci dla swoich pól, Twoje skrypty, które łączą się z bazą danych, będą musiały zostać zaktualizowane, aby wyraźnie wspomnieć o żądanej kompilacji - w przeciwnym razie zapytania używające ciągów tekstowych mogą się nie powieść, gdy połączenie używa domyślnej kompilacji.

Wynik jest taki, że konwertując istniejący system dowolnej wielkości na Unicode / utf8, możesz zostać zmuszony do użyj utf8_general_ci ze względu na sposób, w jaki MySQL obsługuje domyślne wartości.

Dla przypadku podkreślonego przez Guus, zdecydowanie sugerowałbym użycie utf8_unicode_cs (rozróżnianie wielkości liter, ścisłe dopasowanie, w większości przypadków prawidłowe zamawianie) zamiast utf8_bin (ścisłe dopasowanie, nieprawidłowe zamawianie).

Jeśli pole ma być przeszukiwane, a nie dopasowane dla użytkownika, użyj utf8_general_ci lub utf8_unicode_ci. Oba są niewrażliwe na wielkość liter, jeden będzie losowo dopasowany ("ß" jest równe "s", a nie "ss"). Istnieją również wersje językowe, takie jak utf8_german_ci, gdzie lose matching jest bardziej odpowiedni dla podanego języka.

[Edytuj-prawie 6 lat później]

Nie polecam już zestawu znaków " utf8 "w MySQL, a zamiast tego polecam zestaw znaków" utf8mb4". Pasują prawie w całości, ale pozwalają na trochę (dużo) więcej znaków unicode.

Realistycznie MySQL powinien mieć zaktualizowany zestaw znaków "utf8" i odpowiednie kolacje, aby pasowały do specyfikacji "utf8", ale zamiast tego osobny znak set i odpowiednie zestawienia, aby nie wpływały na oznaczenie pamięci dla tych, którzy już używają niekompletnego zestawu znaków "utf8".

Uznałem te wykresy zestawiania za pomocne. http://collation-charts.org/mysql60/. nie jestem jednak pewien, który jest używany utf8_general_ci.

Na przykład tutaj jest wykres dla utf8_swedish_ci. Pokazuje, które znaki interpretuje jako takie same. http://collation-charts.org/mysql60/mysql604.utf8_swedish_ci.html

W pliku przesłanym do bazy danych Dodaj wiersz followin przed dowolną linią:

SET NAMES utf8;