Zmień kodowanie i zestawianie we wszystkich kolumnach we wszystkich tabelach w MySQL

Muszę wykonać te instrukcje we wszystkich tabelach dla wszystkich kolumn.

alter table table_name charset=utf8;
alter table table_name alter column column_name charset=utf8;

Czy jest możliwe zautomatyzowanie tego w jakikolwiek sposób w MySQL? Wolałbym unikać mysqldump

Aktualizacja: Richard Bronosky pokazał mi drogę :-)

Zapytanie, które musiałem wykonać w każdej tabeli:

alter table DBname.DBfield CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Szalone zapytanie, aby wygenerować wszystkie pozostałe zapytania:

SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, '  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';

Chciałem go wykonać tylko w jednej bazie danych. Wykonanie wszystkiego za jednym przejściem trwało zbyt długo. Okazało się, że generuje jedno zapytanie na pole na tabelę. Konieczne było tylko jedno zapytanie na tabelę (w odróżnieniu od akcji ratunkowej). Uzyskiwanie danych wyjściowych z pliku było tym, jak to sobie uświadomiłem.

Jak wygenerować dane wyjściowe do pliku:

mysql -B -N --user=user --password=secret -e "SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, '  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';" > alter.sql

I na koniec, aby wykonać wszystkie zapytania:

mysql --user=user --password=secret < alter.sql

Dzięki, Richard. Jesteś mężczyzną!

Po pierwsze, nie wierz mi na słowo! Przetestuj moją sugestię za pomocą tego:

select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10;

Jeśli czujesz się dobrze z wynikiem tego, usuń klauzule limitu i zapisz dane wyjściowe w skrypcie SQL lub uzyskaj fantazyjne przesyłanie danych wyjściowych bezpośrednio do mysql, podobnie jak tutaj . To by wyglądało tak:

mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema'; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" | mysql --host=prod-db1 --user=admin --password=secret

Kiedy zaczniesz myśleć o używaniu prawidłowego SQL do generowania poprawnego SQL, zmienia to całą grę. Zdziwisz się, ile zastosowań znajdziesz.

W rzeczywistości możesz użyć CONVERT TO w tabeli, aby przekonwertować wszystkie kolumny w tej tabeli na zestaw znaków i zestawienie.

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename';

Ponadto sensowniej jest dla mnie wybrać rzeczywistą bazę danych, na której chcesz to zrobić. Więc to:

... WHERE TABLE_SCHEMA = 'databasename';

zamiast tego:

... WHERE TABLE_SCHEMA != 'information_schema';

Ale myślę, że jeśli naprawdę chcesz to zrobić na wszystkich stołach, możesz użyć tego pierwszego. Wydaje mi się to jednak trochę ciężkie. :)

Aby zmienić sortowanie we wszystkich kolumnach, których użyłem

SELECT CONCAT(  'ALTER TABLE ',  `TABLE_NAME` ,  ' CHANGE `',  `COLUMN_NAME` ,  '` `',`COLUMN_NAME` ,  '` ',  `DATA_TYPE` ,  '(',  `CHARACTER_MAXIMUM_LENGTH` ,  ') CHARACTER SET utf8 COLLATE utf8_swedish_ci ;' ) FROM  `COLUMNS` WHERE  `TABLE_SCHEMA` =  <schema> AND  `COLLATION_NAME` !=  'utf8_swedish_ci' ORDER BY  `TABLE_NAME` ,  `ORDINAL_POSITION` ;

Możesz użyć information_schemabazy danych, aby znaleźć kolumnę i tabelę, które musisz zmienić. Możesz je znaleźć za pomocą:

SELECT table_name, column_name FROM information_schema.`COLUMNS`
WHERE table_schema='your database' AND collation_name LIKE 'latin%';

Następnie możesz zautomatyzować zmianę za pomocą skryptu SQL, procedury składowania lub preferowanego języka programowania.