Jakie jest najlepsze zestawienie dla MySQL z PHP? [Zamknięte]

Zastanawiam się, czy istnieje „najlepszy” wybór do sortowania w MySQL dla ogólnej witryny, w której nie jesteś w 100% pewien, co zostanie wprowadzone? Rozumiem, że wszystkie kodowania powinny być takie same, takie jak MySQL, Apache, HTML i cokolwiek w PHP.

W przeszłości ustawiałem PHP na wyświetlanie w „UTF-8”, ale które zestawienie pasuje do tego w MySQL? Ja myślę, że to jeden z tych, UTF-8, ale użyłem utf8_unicode_ci, utf8_general_cii utf8_binwcześniej.

Główną różnicą jest dokładność sortowania (przy porównywaniu znaków w języku) i wydajność. Jedynym specjalnym jest utf8_bin, który służy do porównywania znaków w formacie binarnym.

utf8_general_cijest nieco szybszy niż utf8_unicode_ci, ale mniej dokładny (do sortowania). Specyficzny język kodowania utf8 (takie jak utf8_swedish_ci) zawierają dodatkowe zasady językowe, które czynią je najbardziej dokładne sortowanie dla tych języków. Większość czasu używam utf8_unicode_ci(wolę dokładność niż małe ulepszenia wydajności), chyba że mam dobry powód, aby preferować określony język.

Możesz przeczytać więcej na temat określonych zestawów znaków Unicode w podręczniku MySQL - http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html

W rzeczywistości prawdopodobnie chcesz użyć utf8_unicode_cilub utf8_general_ci.

• utf8_general_ci sortuje, usuwając wszystkie akcenty i sortując jak ASCII
• utf8_unicode_ci używa kolejności sortowania Unicode, więc sortuje poprawnie w większej liczbie języków

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

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

MySQL nie rozróżnia niektórych znaków w instrukcjach select, jeśli utf8_general_ciużywane jest sortowanie. Może to prowadzić do bardzo nieprzyjemnych błędów - szczególnie na przykład w przypadku nazw użytkowników. W zależności od implementacji korzystającej z tabel bazy danych ten problem może pozwolić złośliwym użytkownikom na utworzenie nazwy użytkownika pasującej do konta administratora.

Ten problem ujawnia się przynajmniej we wczesnych wersjach 5.x - nie jestem pewien, czy to zachowanie zmieniło się później.

Nie jestem DBA, ale aby uniknąć tego problemu, zawsze wybieram utf8-binzamiast rozróżniania wielkości liter.

Poniższy skrypt opisuje problem na przykładzie.

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

Najlepiej używać zestawu znaków utf8mb4z zestawieniem utf8mb4_unicode_ci.

Zestaw znaków utf8obsługuje tylko niewielką liczbę punktów kodowych UTF-8, około 6% możliwych znaków. utf8obsługuje tylko Basic Multilingual Plane (BMP). Jest 16 innych samolotów. Każdy samolot zawiera 65 536 znaków. utf8mb4obsługuje wszystkie 17 samolotów.

MySQL skróci 4-bajtowe znaki UTF-8, co spowoduje uszkodzenie danych.

Zestaw utf8mb4znaków został wprowadzony w MySQL 5.5.3 w dniu 24.03.2010.

Niektóre z wymaganych zmian w celu użycia nowego zestawu znaków nie są trywialne:

• Konieczne może być wprowadzenie zmian w adapterze bazy danych aplikacji.
• Konieczne będzie wprowadzenie zmian w pliku my.cnf, w tym ustawienie zestawu znaków, sortowanie i przełączanie pliku format_pliku_wodb na Barracuda
• Instrukcje SQL CREATE mogą wymagać: ROW_FORMAT=DYNAMIC
  • DYNAMIC jest wymagany dla indeksów w VARCHAR (192) i większych.

UWAGA: Przełączenie Barracudaz Antelope, może wymagać ponownego uruchomienia usługi MySQL więcej niż raz. innodb_file_format_maxnie zmienia się aż po serwis MySQL została wznowiona do: innodb_file_format = barracuda.

MySQL używa starego Antelopeformatu pliku InnoDB. Barracudaobsługuje dynamiczne formaty wierszy, które będą potrzebne, jeśli nie chcesz trafić do błędów SQL podczas tworzenia indeksów i kluczy po przejściu na zestaw znaków:utf8mb4

• # 1709 - Rozmiar kolumny indeksu jest zbyt duży. Maksymalny rozmiar kolumny to 767 bajtów.
• # 1071 - Określony klucz był za długi; maksymalna długość klucza wynosi 767 bajtów

Poniższy 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 pliku 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, INDEX contact_idx (contact)jeśli ROW_FORMAT=DYNAMICzostał usunięty z instrukcji CREATE.

UWAGA: Zmiana indeksu na ograniczenie do pierwszych 128 znaków contacteliminuje konieczność używania Barracuda zROW_FORMAT=DYNAMIC

INDEX contact_idx (contact(128)),

Uwaga: gdy mówi VARCHAR(128), że pole ma rozmiar , to nie jest 128 bajtów. Możesz użyć 128, 4-bajtowych znaków lub 128, 1-bajtowych znaków.

Ta INSERTinstrukcja powinna zawierać 4-bajtowy znak „poo” w 2 rzędzie:

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 zajmowanego przez lastkolumnę:

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że być konieczne ustawienie zestawu znaków i sortowania dla połączenia:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

W PHP byłoby to ustawione dla: \PDO::MYSQL_ATTR_INIT_COMMAND

Bibliografia:

• Podręcznik referencyjny Mysql 5.6: Ograniczenia w tabelach InnoDB
• Jak obsługiwać pełny Unicode w bazach danych MySQL

Sortowanie wpływa na sposób sortowania danych i na porównanie łańcuchów. Oznacza to, że powinieneś użyć sortowania, którego oczekuje większość użytkowników.

Przykład z dokumentacji dla kodowania Unicode :

utf8_general_cijest również zadowalający zarówno dla języka niemieckiego, jak i francuskiego, z tym wyjątkiem, że „ß” jest równe „s”, a nie „ss”. Jeśli jest to akceptowalne dla twojej aplikacji, powinieneś użyć, utf8_general_ciponieważ jest szybsze. W przeciwnym razie użyj, utf8_unicode_ciponieważ jest bardziej dokładny.

Tak więc - zależy to od oczekiwanej bazy użytkowników i tego, ile potrzebujesz poprawnego sortowania. Dla angielskiej bazy użytkowników utf8_general_cipowinno wystarczyć, dla innych języków, takich jak szwedzki, stworzono specjalne zestawienia.

Zasadniczo zależy to od tego, jak myślisz o sznurku.

Zawsze używam utf8_bin z powodu problemu wskazanego przez Guusa. Moim zdaniem, jeśli chodzi o bazę danych, ciąg jest nadal tylko ciągiem. Ciąg jest liczbą znaków UTF-8. Postać ma reprezentację binarną, więc dlaczego musi znać język, którego używasz? Zwykle ludzie będą budować bazy danych dla systemów z zakresem dla witryn wielojęzycznych. Taki jest sens używania UTF-8 jako zestawu znaków. Jestem trochę purystą, ale myślę, że ryzyko błędu znacznie przewyższa niewielką przewagę, jaką możesz uzyskać przy 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 równać się „valúe”.

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

Do informacji tekstowych UTF-8 należy użyć, utf8_general_ciponieważ ...

• utf8_bin: porównaj ciągi według wartości binarnej każdego znaku w ciągu

• utf8_general_ci: porównywanie ciągów przy użyciu ogólnych reguł językowych i porównań bez rozróżniania wielkości liter

alias powinno to przyspieszyć / zwiększyć wydajność / użyteczność wyszukiwania i indeksowania danych.

Przyjęta odpowiedź dość zdecydowanie sugeruje użycie utf8_unicode_ci, i chociaż w przypadku nowych projektów, które są świetne, chciałem odnieść się do moich niedawnych przeciwnych doświadczeń na wypadek, gdyby zaoszczędził trochę czasu.

Ponieważ utf8_general_ci jest domyślnym zestawieniem dla Unicode w MySQL, jeśli chcesz używać utf8_unicode_ci, musisz w wielu miejscach podać go .

Na przykład wszystkie połączenia klienckie mają nie tylko domyślny zestaw znaków (co ma dla mnie sens), ale także domyślne zestawienie (tzn. Zestawienie zawsze będzie domyślnie ustawione na utf8_general_ci dla Unicode).

Prawdopodobnie, jeśli użyjesz utf8_unicode_ci dla swoich pól, twoje skrypty łączące się z bazą danych będą musiały zostać zaktualizowane, aby wyraźnie wspomniały o pożądanym sortowaniu - w przeciwnym razie zapytania przy użyciu ciągów tekstowych mogą się nie powieść, gdy twoje połączenie używa domyślnego sortowania.

Konsekwencją jest to, że podczas konwersji istniejącego systemu dowolnej wielkości na Unicode / utf8 możesz zostać zmuszony do użycia utf8_general_ci z powodu sposobu, w jaki MySQL obsługuje wartości domyślne.

W przypadku wyróżnionym przez Guusa zdecydowanie zalecam użycie albo utf8_unicode_cs (rozróżnianie wielkości liter, ścisłe dopasowanie, porządkowanie w większości przypadków) zamiast utf8_bin (ścisłe dopasowanie, niepoprawne porządkowanie).

Jeśli pole ma być przeszukiwane, a nie dopasowane do użytkownika, użyj utf8_general_ci lub utf8_unicode_ci. W obu przypadkach nie jest rozróżniana wielkość liter, jeden będzie pasował do siebie („ß” jest równe „s”, a nie „ss”). Istnieją również wersje specyficzne dla języka, takie jak utf8_german_ci, w których dopasowanie z utratą jest bardziej odpowiednie dla określonego języka.

[Edytuj - prawie 6 lat później]

Nie polecam już zestawu znaków „utf8” na MySQL, a zamiast tego polecam zestaw znaków „utf8mb4”. Dopasowują się prawie całkowicie, ale pozwalają na trochę (dużo) więcej znaków Unicode.

Realistycznie MySQL powinien zaktualizować zestaw znaków „utf8” i odpowiednie sortowania, aby pasowały do ​​specyfikacji „utf8”, ale zamiast tego osobny zestaw znaków i odpowiednie sortowania, aby nie wpływały na oznaczenie pamięci dla tych, którzy już używają ich niekompletnego zestawu znaków „utf8” .

Uważam, że te zestawienia są 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 do przesłania bazy danych dodaj następujący wiersz przed dowolnym wierszem:

SET NAMES utf8;

Twój problem powinien zostać rozwiązany.