Altera o charset e o collation em todas as colunas em todas as tabelas no MySQL

Question

Eu preciso executar essas instruções em todas as tabelas para todas as colunas.

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

É possível automatizar isso de alguma forma dentro do MySQL? Eu preferiria evitar o mysqldump

Atualização: Richard Bronosky me mostrou o caminho: -)

A consulta que precisei executar em todas as tabelas:

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

Consulta maluca para gerar todas as outras consultas:

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

Eu só queria executá-lo em um banco de dados. Estava demorando muito para executar tudo em um único passe. Descobriu-se que estava gerando uma consulta por campo por tabela. E apenas uma consulta por tabela foi necessária (distinta para o resgate). Obtendo a saída em um arquivo foi como eu percebi isso.

Como gerar a saída para um arquivo:

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

E, finalmente, para executar todas as consultas:

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

Obrigado Richard. Você é o cara!

    

Answer 1

Primeiro, não acredite apenas na minha palavra! Teste minha sugestão com isto:

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;

Se você se sentir bem com o resultado disso, remova as cláusulas limit e salve a saída em um script SQL ou, obtenha a fantasia e canalize a saída diretamente para o mysql, de forma semelhante ao que demonstrar here . Isso ficaria assim:

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

Quando você começa a pensar em usar um SQL válido para gerar um SQL válido, ele muda todo o jogo. Você ficará surpreso com a quantidade de usos que encontrar.

    

Answer 2

Na verdade, você pode usar CONVERT TO em uma tabela para convertê-las em todas as colunas dessa tabela para o conjunto de caracteres e agrupamento.

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

Além disso, faz mais sentido para mim selecionar o banco de dados real em que você deseja fazer isso. Então isso:

... WHERE TABLE_SCHEMA = 'databasename';

em vez disso:

... WHERE TABLE_SCHEMA != 'information_schema';

Mas eu acho que se você realmente quisesse fazer isso em todas as tabelas, você poderia usar o primeiro. Parece um pouco pesado para mim embora. :)

    

Answer 3

Para alterar o agrupamento em todas as colunas que usei

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

    

Answer 4

Você pode usar o banco de dados information_schema para encontrar a coluna e a tabela a serem alteradas. Você pode encontrá-los com:

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

Depois, você pode automatizar a alteração com um script SQL, procedimento de armazenamento ou com sua linguagem de desenvolvimento preferida.