Como alterar o agrupamento padrão de um database?

Nosso programador anterior definiu o agrupamento errado em uma tabela (Mysql). Ele montou com o latim collation, quando deveria ser UTF8, e agora eu tenho problemas. Todos os registros com personagens chineses e japoneses mudam para ??? personagem.

É possível alterar o agrupamento e recuperar o detalhe do personagem?

alterar o agrupamento do database:

ALTER DATABASE  CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

alterar o agrupamento da tabela:

 ALTER TABLE  CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

alterar o agrupamento da coluna:

 ALTER TABLE  MODIFY  VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

Mais informações:

  • Quais são as diferenças entre utf8_general_ci e utf8_unicode_ci?
  • Qual é a diferença entre utf8_general_ci e utf8_unicode_ci
  • Como alterar o agrupamento de database, tabela, coluna?

Aqui está como alterar todos os bancos de dados / tabelas / colunas. Execute essas consultas e elas exibirão todas as consultas subsequentes necessárias para converter todo o esquema em utf8. Espero que isto ajude!

– Alterar agrupamento padrão de DATABASE

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

– Alterar Agrupamento TAB / Agrupamento

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

– Alterar agrupamento de COLUMN / Char Char

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

Tenha em atenção que no Mysql, o conjunto de caracteres utf8 é apenas um subconjunto do conjunto de caracteres UTF8 real. Para salvar um byte de armazenamento, a equipe do Mysql decidiu armazenar apenas três bytes de caracteres UTF8 em vez dos quatro bytes completos. Isso significa que alguns idiomas e emoticons do leste asiático não são totalmente compatíveis. Para certificar-se de que você pode armazenar todos os caracteres UTF8, use o tipo de dados utf8mb4_bin e utf8mb4_bin ou utf8mb4_general_ci no Mysql.

Somando-se ao que David Whittaker postou, eu criei uma consulta que gera a tabela completa e a coluna alter statement que irá converter cada tabela. Pode ser uma boa ideia executar

SET SESSION group_concat_max_len = 100000;

primeiro para ter certeza de que sua concat de grupo não ultrapasse o limite muito pequeno como visto aqui .

  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; 

Uma diferença aqui entre a resposta anterior é que estava usando utf8 em vez de ut8mb4 e usando t1.data_type com t1.CHARACTER_MAXIMUM_LENGTH não funcionava para enums. Além disso, minha consulta exclui as visualizações, pois elas terão que ser alteradas separadamente.

Eu simplesmente usei um script Perl para retornar todos esses alters como uma matriz e iterava sobre eles, corrigindo as colunas que eram muito longas (geralmente elas eram varchar (256) quando os dados geralmente só tinham 20 caracteres nelas, então isso era uma correção fácil ).

Eu encontrei alguns dados foi corrompido ao alterar de latin1 -> utf8mb4. Parecia ser utf8 codificado caracteres latin1 em colunas ficaria chateado na conversão. Eu simplesmente segurei dados das colunas que eu sabia que ia ser um problema na memory de antes e depois do alter e os comparei e gerou instruções de atualização para corrigir os dados.

aqui descreve bem o processo. No entanto, alguns dos personagens que não se encheckboxm no espaço latino se foram para sempre. O UTF-8 é um SUPERSET do latin1. Não o contrário. A maioria vai caber no espaço de byte único, mas qualquer um indefinido não irá (verificar uma lista de latin1 – nem todos os 256 caracteres são definidos, dependendo da definição latin1 do mysql)