MySQL: erro no index

[cjp]

Ao tentar criar um índice, estou recebendo o seguinte erro:

Código: Selecionar todos

Erro SQL (1170): Coluna BLOB 'DETALHES' usada na especificação de chave sem o comprimento da chave

O campo DETALHES é um campo TEXT.

Esta seria a causa do erro? Não é possível criar índice em campo TEXT?

Se não for esta a causa, qual seria? E como resolver isto?

[JoséQuintas]

Leia com atenção.
A mensagem mostra o erro e a solução.

[cjp]

Ler eu li, só não entendi.
Pelo que eu entendi, falta o "comprimento da chave". Mas o que seria exatamente isso?
A solução seria colocar o comprimento da chave. Mas como fazer isso?

[JoséQuintas]

Igual no Harbour/Clipper: SUBSTR()
Mas porque precisa indexar um campo TEXT?

[alxsts]

Olá!

Na minha tabela de testes, o campo usuario é do tipo TEXT. Apesar disto, o DbEaver mostra como VARCHAR(255).

Código: Selecionar todos

CREATE TABLE tbAtiv ( 
   id INT(6) NOT NULL AUTO_INCREMENT,
   tempousado INT(6),
   usuario TEXT(1),
   data DATE,
   acao VARCHAR(20), 
   CONSTRAINT tbAtiv_pk PRIMARY KEY (id)
);

Criei o índice sem problema.

Código: Selecionar todos

CREATE INDEX idx_usuario ON tbAtiv (usuario);

Updated Rows	0
Query	CREATE INDEX idx_usuario ON tbAtiv (usuario)
Finish time	Fri May 07 23:30:39 BRT 2021

1 Erro SQL (1170): Coluna BLOB 'DETALHES' usada na especificação de chave sem o comprimento da chave

O que achei estranho é que está mostrando a coluna como BLOB. Tem certeza que é TEXT mesmo?

[alxsts]

Olá!

Aqui um detalhe interessante, que é comum a todos os SGBDRs:

Neste SELECT, apesar de existir um índice para a coluna especificada no WHERE, ele não usa o índice. A análise do otimizador de queries escolheu este plano de execução por ser "mais barato" (consome menos recursos). Por que? Pelo número de registros que existem na tabela. É mais fácil ler um a um até encontrar ou não (full table scan) que ler o índice e, caso encontre, ler a tabela.

[cjp]

O campo é text sim (veja no anexo).
A ideia de colocar este campo num índice seria para agilizar a consulta. Não adianta?

[alxsts]

Olá!

Mostre o comando que está usando para criar o índice...

[cjp]

Estou fazendo pelo Haidi.
Colei o comando no anexo.

[alxsts]

Olá!

No MySQL, colunas tipo TEXT equivalem aos campos MEMO do xBase. Servem para armazenar textos longos. Tem também os campos BLOB (Binary Large OBjects) para conteúdos binários longos, tipo arquivos de imagem.

No teu caso, é só especificar o tamanho da coluna DETALHES na expressão de criação do índice. Por exemplo: DETALHES(100) ou alterar a estrutura da tabela informando o tamanho deste campo (veja que está em branco no print da estrutura da tabela). Veja se funciona.

Não sei por qual razão escolheu este tipo de campo e nem o que ele vai armazenar. Da próxima vez prefira VARCHAR(n) onde n é o tamanho desejado, que pode ser bem grande.

[cjp]

Acho que na época que criei esta tabela (esta foi a primeira foi a primeira tabela MySQL que fiz na vida, afora as de mero teste), não conhecia VARCHAR. Como o campo CHAR tem limite pequeno, tive que partir para o campo TEXT.
Se eu mudar agora para VARCHAR, não pode haver perda de dados? E essa mudança certamente irá tornar as consultas mais rápidas, né?
Como posso determinar o n do VARCHAR de forma segura? Eu não sei qual o campo com maior tamanho usado atualmente. Tem como descobrir?

[alxsts]

Olá!

Eu não sei qual o campo com maior tamanho usado atualmente. Tem como descobrir?

Código: Selecionar todos

SELECT Max(Length(nome_campo)) 
  FROM nome_tabela;

Se eu mudar agora para VARCHAR, não pode haver perda de dados?

Verifique qual é o máximo tamanho que você tem hoje, com a query acima. Depois altere a tabela colocando um valor adequado. No exemplo abaixo coloquei 1024.

Código: Selecionar todos

ALTER TABLE nome_tabela MODIFY COLUMN nome_campo VARCHAR(1024);

Eu procuro sempre colocar valores multiplos de 8. Acho que ajuda a acomodar nas páginas onde os dados são gravados no banco de dados.

Se houver perda de dados, um erro SQL Error [1406] [22001]: (conn=xxx) Data too long for column 'nome_coluna' at row 1 será gerado.

A capacidade padrão do tipo de coluna TEXT é 65535 bytes.

[cjp]

Deu certo. A tabela diminuiu de tamanho significativamente. E ainda consegui fazer o índice.
Muito obrigado.