Jak zmienić domyślne zestawienie bazy danych?

Oto jak zmienić wszystkie bazy danych / tabele/kolumny. Uruchom te zapytania, a oni wyjdą wszystkie kolejne zapytania niezbędne do przekonwertowania całego schematu do utf8. Mam nadzieję, że to pomoże!

-- Zmień domyślne Zestawienie bazy danych

SELECT DISTINCT concat('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like  'database_name';

-- Change table Collation / Char Set

SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like 'database_name';

-- Change COLUMN Collation / Char Set

SELECT concat('ALTER TABLE `', t1.TABLE_SCHEMA, '`.`', t1.table_name, '` MODIFY `', t1.column_name, '` ', t1.data_type , '(' , t1.CHARACTER_MAXIMUM_LENGTH , ')' , ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.columns t1
where t1.TABLE_SCHEMA like 'database_name' and t1.COLLATION_NAME = 'old_charset_name';

Uważaj, że w Mysql zestaw znaków utf8 jest tylko podzbiorem rzeczywistego zestawu znaków UTF8. Aby zapisać jeden bajt pamięci, zespół Mysql zdecydował się przechowywać tylko trzy bajty znaków UTF8 zamiast pełnych czterech bajtów. Oznacza to, że niektóre języki wschodnioazjatyckie i emoji nie są w pełni obsługiwane. Aby upewnić się, że możesz przechowywać wszystkie znaki UTF8, użyj typu danych utf8mb4 oraz utf8mb4_bin lub utf8mb4_general_ci w Mysql.

Dodając do tego, co napisał David Whittaker, stworzyłem zapytanie, które generuje pełną tabelę i kolumnę Alter, która przekonwertuje każdą tabelę. To może być dobry pomysł, aby uruchomić

SET SESSION group_concat_max_len = 100000;

Najpierw upewnij się, że twoja grupa concat nie przekroczy bardzo małego limitu, jak widać tutaj.

     SELECT a.table_name, concat('ALTER TABLE ', a.table_schema, '.', a.table_name, ' DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci, ',
        group_concat(distinct(concat(' MODIFY ',  column_name, ' ', column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ', if (is_nullable = 'NO', ' NOT', ''), ' NULL ',
        if (COLUMN_DEFAULT is not null, CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\''), ''), if (EXTRA != '', CONCAT(' ', EXTRA), '')))), ';') as alter_statement
    FROM information_schema.columns a
    INNER JOIN INFORMATION_SCHEMA.TABLES b ON a.TABLE_CATALOG = b.TABLE_CATALOG
        AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
        AND a.TABLE_NAME = b.TABLE_NAME
        AND b.table_type != 'view'
    WHERE a.table_schema = ? and (collation_name = 'latin1_swedish_ci' or collation_name = 'utf8mb4_general_ci')
    GROUP BY table_name;

Różnica między poprzednią odpowiedzią jest to, że używał utf8 zamiast ut8mb4 i używał t1.data_type with t1.CHARACTER_MAXIMUM_LENGTH nie działa dla enums. Ponadto moje zapytanie wyklucza widoki, ponieważ będą one musiały zostać zmienione osobno.

Po prostu użyłem skryptu Perla, aby zwrócić wszystkie te zmiany jako tablicę i powtórzyłem nad nimi, naprawiłem kolumny, które były zbyt długie(ogólnie były to varchar (256), gdy dane miały tylko 20 znaków, więc było to łatwe rozwiązanie).

Znalazłem, że niektóre dane były uszkodzone podczas zmiany z latin1 - > utf8mb4. Wydaje się być UTF8 zakodowane latin1 znaki w kolumnach zostaną wygłupione podczas konwersji. Po prostu trzymałem dane z kolumn, które wiedziałem, że będą problemem w pamięci przed i po zmianie i porównałem je i wygenerowałem Instrukcje aktualizacji, aby naprawić dane.

Tutaj dobrze opisuje proces. Jednak niektóre postacie, które nie zmieściły się w przestrzeni Łacińskiej, zniknęły na zawsze. UTF-8 jest SUPERSETEM latin1. Nie odwrotnie. Większość z nich mieści się w przestrzeni jednobajtowej, ale żadne niezdefiniowane nie będą (sprawdź listę latin1 - nie wszystkie 256 znaków są zdefiniowane, w zależności od definicji latin1 mysql)