Início / dba / Perguntas / 346039
Accepted
Joel
Asked: 2025-04-05 05:01:50 +0800 CST

Posso alterar a ordenação de uma tabela e suas colunas sem usar convert to?

  • 772

Todas as tabelas e colunas do meu banco de dados usam o conjunto de caracteres utf8mb4 e a ordenação utf8mb4_unicode_ci. Gostaria de migrar para a ordenação utf8mb4_0900_ai_ci, mas usá-la convert toé inviável em minhas tabelas grandes, a menos que eu use gh-ost ou pt-osc.

Parece-me que isso convert tonão deveria ser necessário, visto que o conjunto de caracteres não muda, mas não consigo encontrar nenhuma documentação que confirme ou negue isso. Preciso usar convert toneste caso ou é alter table ... modify col ... collate ...suficiente?

Dei uma olhada em "Usando CONVERTER EM CONJUNTO DE CARACTERES para alterar apenas a ordenação?", que faz uma pergunta muito semelhante, mas a idade dessa pergunta torna as respostas um tanto obsoletas. Mesmo assim, uma resposta parece sugerir que isso convert toé desnecessário. A outra resposta se aprofunda na conversão de conjuntos de caracteres e no uso de pt-osc, o que eu gostaria de evitar.

mysql

2 respostas

  1. Best Answer

    O conjunto de caracteres não muda, mas uma alteração na ordenação provavelmente altera a ordem de classificação dos caracteres, o que exige que os índices sejam reconstruídos.

    Se houver um índice em uma coluna com ordenação, ele não permitirá uma alteração no local (testando no MySQL 8.4.4):

    mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `t` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `t` (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> alter table mytable 
  convert to character set utf8mb4 collate utf8mb4_general_ci, 
  algorithm=inplace, lock=none;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. 
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

    Você também não pode alterar a ordenação de uma coluna, se essa coluna tiver um índice.

    mysql> alter table mytable 
    modify column t varchar(20) character set utf8mb4 collate utf8mb4_unicode_ci, 
    algorithm=inplace, lock=none;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. 
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

    Mas se você remover o índice, o que pode ser feito como uma operação no local, você poderá alterar a ordenação, também como uma operação no local.

    mysql> alter table mytable 
  drop key t, 
  algorithm=inplace, lock=none;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table mytable 
  convert to character set utf8mb4 collate utf8mb4_general_ci, 
  algorithm=inplace, lock=none;
Query OK, 0 rows affected (0.01 sec)

    Depois de alterar a ordenação, você pode recriar o índice, novamente sem uma cópia da tabela:

    mysql> alter table mytable
  add key (t),
  algorithm=inplace, lock=none;
Query OK, 0 rows affected (0.00 sec)

    O MySQL deveria ser capaz de automatizar essas três etapas para você? Talvez. Tente pesquisar em https://bugs.mysql.com para ver se alguém já solicitou esse recurso e, em caso afirmativo, qual foi a resposta dos desenvolvedores do MySQL.

    A criação de um índice leva tempo se a tabela tiver muitas linhas de dados. Esta é uma alteração local, pois não requer uma cópia da tabela e permite DML concorrente, mas há limites para isso. Leia https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html para mais detalhes.

    Não testei o caso em que a chave primária é uma coluna de string com agrupamento, mas presumo que isso não funcione. Você não pode "remover" o índice clusterizado no InnoDB, pois essa é a própria tabela.

    Pessoalmente, eu usaria pt-online-schema-change para tal alteração. No meu último emprego, usávamos pt-osc centenas de vezes por semana, durante o horário de produção, sem interromper o tráfego, inclusive em tabelas grandes (que definimos como 500 GB ou mais).

    • 2

  2. Como você está apenas alterando a ordenação, não é necessário fazer uma conversão. Você não está alterando o conjunto de caracteres.

    É por isso que o comando existe.

    ALTER TABLE nome_da_tabela
    MODIFY COLUMN nome_da_coluna tipo_de_dados COLLATE utf8mb4_0900_ai_ci

    • -1

relate perguntas