Como formatar verticalmente as saídas das queries no MySQL

Quando a saída das suas consultas MySQL aparenta estar muito longa (ou larga), é sempre possível reorganizar as informações verticalmente, o que torna a lista mais extensa por um lado — e mais legível por outro.
logo do mysql sobre tela exibindo resultados de uma query
O principal motivo para usar esta formatação é evitar uma apresentação muito “bagunçada” dos seus dados na tela.
* Pessoalmente, esta formatação me lembra as listagens do dBase III, quando comecei a brincar com bancos de dados 😉
Algumas queries têm resultados tão extensos que ocupam mais de uma linha, na listagem. Isto pode tornar a leitura e a compreensão dos dados mais difícil.

select * from clientesEstaduais;

exemplo de exibição de uma query consulta MySQL
A proposta deste post é reorganizar a saída em um outro formato.
Isto pode ser feito adicionando o parâmetro \G ao comando de consulta. Veja:

SELECT * FROM clientesEstaduais\G;
*************************** 1. row ***************************
         id: 9999
nomecliente: Alea N. Valenzuela
     estado: M
     cidade: Cork
*************************** 2. row ***************************
         id: 10006
nomecliente: Wyatt E. Merritt
     estado: Akwa Ibom
     cidade: Uyo
*************************** 3. row ***************************
         id: 10013
nomecliente: Brenda X. Jefferson
     estado: Provence-Alpes-Côte d'Azur
     cidade: Toulon
*************************** 4. row ***************************
         id: 10020
nomecliente: Dolan Y. Joyner
     estado: CE
     cidade: Maranguape
*************************** 5. row ***************************
         id: 10027
nomecliente: Richard D. Bass
     estado: Karnataka
     cidade: Bijapur

...

Experimente isto com suas próprias queries.

Como executar a query direto da linha de comando

Usuários Linux podem fazer o mesmo direto da linha de comando do seu sistema. Abra um terminal e experimente:

mysql -u root -p -e "SELECT * FROM clientesEstaduais WHERE id>10670\G" tutorial

No comando, acima, fazemos o login como root e executamos a query SELECT * FROM clientesEstaduais WHERE id>10670\G contra o banco de dados tutorial. O meu resultado ficou assim:

Enter password: 
*************************** 1. row ***************************
         id: 10671
nomecliente: Remedios J. Owen
     estado: Alajuela
     cidade: Alajuela
*************************** 2. row ***************************
         id: 10678
nomecliente: Hope U. Houston
     estado: ABR
     cidade: San Giovanni Lipioni
*************************** 3. row ***************************
         id: 10685
nomecliente: Graiden F. Donaldson
     estado: Namen
     cidade: Ligny
*************************** 4. row ***************************
         id: 10692
nomecliente: Petra M. Dejesus
     estado: L
     cidade: Dublin

Se você tiver interesse em compreender melhor como fazer consultas ao banco de dados MySQL direto da linha de comando, leia este artigo.

Como listar resultados das tabelas com linhas numeradas no MySQL

Veja como numerar cada linha do resultado de uma query no MySQL. A solução pode ser implementada com uma saída pós processada ou com o uso de uma variável SQL.
mysql numbered lines tables
Neste texto, vou mostrar 2 maneiras bem objetivas e práticas para obter os resultados de uma query (ou consulta) MySQL em linhas numeradas, dentro de uma tabela.

Como obter uma relação numerada de itens de uma tabela via linha de comando

Na linha de comando do Linux ou Unix, você pode combinar a execução de código MySQL com o comando cat -n, conforme é possível observar no exemplo abaixo:

mysql -u root -p -N -e "SELECT nome, credito FROM minhaTabela" tutorial | cat -n
Enter password: 
     1	Geoffrey Rojas	9.33
     2	Brenda N. Reynolds	9.03
     3	Unity Peterson	9.19
     4	Gay Warren	9.11
     5	Hedda E. Kaufman	9.71
     6	Audrey X. Silva	9.21
     7	Myra J. Wright	9.49
     8	Wilma Bird	9.45
     9	Thaddeus P. Shaw	9.06

Com este método, não é preciso entrar no MySQL para conseguir realizar a tarefa.
Vamos dissecar o comando?

  1. mysql -u root -p— início clássico para entrar no cliente MySQL, como root. Como a senha não é fornecida na linha de comando, ela será pedida a seguir.
  2. -N— faz com que a saída do comando não exiba os nomes das colunas.
  3. -e— executa o comando, que segue entre aspas e sai do cliente MySQL. Opcionalmente, você pode usar a variante --execute.
  4. "SELECT nome, credito FROM minhaTabela"query ou consulta a ser executada.
  5. tutorial— banco de dados a ser inquirido.
  6. | cat -n— direciona a saída ao comando cat. A opção -n especifica que cada linha deverá ser contada e numerada.

Neste caso, portanto, quem numera as linhas não é o MySQL, mas o utilitário cat.
Em listas muito grandes, este método permite usar o comando grep, para filtrar os resultados.
Se você tem interesse em saber mais sobre como executar queries MySQL da linha de comando do Linux/Unix, leia este artigo.

Use uma variável SQL para obter uma lista numerada

Uma alternativa inteligente, para solucionar o problema é usar uma variável auto incrementável para listar as linhas numeradas.
Esta solução, ao contrário da anterior, pede que você execute os comandos de dentro do cliente MySQL.
Veja o exemplo:

SET @n = 0;
Query OK, 0 rows affected (0.00 sec)

SELECT @n := @n+1 AS ordem, nome, credito, debito FROM minhaTabela;
+-------+-----------------------+---------+--------+
| ordem | nome                  | credito | debito |
+-------+-----------------------+---------+--------+
|     1 | Lani Z. Lawrence      | 4.70    | 2.01   |
|     2 | Geoffrey Rojas        | 9.33    | 6.09   |
|     3 | Holly Mcknight        | 3.79    | 9.84   |
|     4 | Emerson F. Tate       | 6.09    | 8.57   |
|     5 | Alyssa A. Goodman     | 3.29    | 4.73   |
|     6 | Kibo H. Cruz          | 8.52    | 6.41   |
|     7 | May Hoffman           | 5.51    | 2.40   |
|     8 | Heidi F. Lawson       | 8.37    | 1.42   |
|     9 | Isaac U. Herring      | 6.85    | 1.93   |
|    10 | Amanda Roberson       | 5.97    | 9.47   |
|    11 | Oren M. Tanner        | 4.05    | 9.96   |
|    12 | Wyatt Becker          | 2.81    | 2.81   |
+-------+-----------------------+---------+--------+
12 rows in set (0.00 sec)

Dissecando o comando:

  1. SET @n = 0; — define a variável @n com o valor 0.
  2. SELECT @n := @n+1 AS ordem, nome, credito, debito FROM minhaTabela; — a segunda linha de comando é a responsável pela confecção e exibição da lista.

Se você gostaria de entender melhor o uso de variáveis no MySQL, leia este tutorial.

Como enviar queries pro MySQL da linha de comando

O MySQL aceita queries direto da linha de comando, o que poupa tempo para realizar tarefas rápidas e evita ter que usar o modo interativo provido pelo cliente.
O conceito é simples e você provavelmente já o viu ser aplicado a inúmeros outros programas.
Oracle and MySQL logos
Uma query pode ser fornecida ao MySQL, diretamente da linha de comando, com a adição da opção -e (ou –execute) ao cliente.
O exemplo abaixo mostra como ver a quantidade de registros em na tabela clientes, dentro do banco de dados admin:

mysql -e "SELECT COUNT(*) FROM clientes" admin
+----------+
| COUNT(*) |
+----------+
|
12 |
+----------+

Se quiser usar mais de uma query, faça como sempre — separe as queries com ; (ponto e vírgula):

mysql -e "SELECT COUNT(*) FROM clientes;SELECT NOW( )" admin
+----------+
| COUNT(*) |
+----------+
|
12 |
+----------+
+---------------------+| NOW( )
|
+---------------------+
| 2001-07-04 10:42:22 |
+---------------------+

MySQL: Otimize suas consultas com Query Cache

O QUERY CACHE armazena o texto de uma declaração SELECT junto ao seu resultado, no cliente. Quando uma declaração idêntica é recebida, mais tarde, o servidor apresenta o resultado, já pronto, em vez de fazer o trabalho de pesquisa novamente.
O query cache é compartilhado entre sessões e, portanto, pode ser aproveitado por diversos outros clientes.

Quando o Query Cache pode ser útil?

Imagine um ambiente em que haja múltiplas tabelas, que não sofram muitas mudanças, para as quais o servidor receba várias consultas idênticas. Em uma situação como esta, é mais rápido fornecer a resposta pronta do que pesquisar a mesma coisa várias vezes.
O query cache não retorna dados velhos ou vencidos. Quando as tabelas são alteradas, qualquer entrada relevante no query cache é descartada.
Até as versões atuais do MySQL, há certas limitações:

  • O query cache não funciona em ambiente onde haja múltiplos servidores mysqld atualizando tabelas MyISAM.
  • O query cache não suporta tabelas particionadas e é automaticamente desabilitado nestes casos. Você não pode habilitá-lo nestes casos.

O query cache não é “ciência exata” e sua eficiência pode variar em função da carga de trabalho a que o servidor está submetido.

Como funciona o Query Cache?

Antes de serem executadas, os textos das consultas são comparados aos que já se encontram armazenados no query cache, que é sensível à caixa. Portanto:

SELECT * FROM nome_da_tabela
select * from nome_da_tabela

são duas queries diferentes.
As consultas têm que ser exatamente as mesmas (em cada byte) para serem reconhecidas como idênticas.
Há outros casos em que queries, mesmo idênticas, serão consideradas diferentes:

  • quando usarem bancos de dados diferentes;
  • ao usarem versões divergentes de protocolo;
  • ao ter diferentes default character set.

O cache vai armazenar as queries, inseridas nos casos acima, separadamente.
O cache não vai aceitar queries dentro das seguintes condições:

  • se refere a uma função definida por usuário — User Defined Function ou UDF;
  • em que sejam executadas de dentro de uma stored function, uma trigger ou um evento;
  • se refere a variáveis definidas por usuário ou programas locais;
  • se refere às tabelas dentro dos bancos de dados MySQL, INFORMATION_SCHEMA ou performance_schema;
  • não usa tabelas;
  • usa tabelas temporárias;
  • gera avisos;
  • o usuário não tem privilégios suficientes para sua completa execução;

Agora, que já terminei de explicar como o query cache não funciona, vou tentar explicar aonde ele vai funcionar – o que é bem mais simples.
Antes de entregar o resultado de uma query, o MySQL verifica se o usuário tem privilégios de SELECT referentes aos bancos de dados e tabelas envolvidos. Se não, o resultado armazenado no cache não poderá ser usado.
O query cache também funciona entre transações, quando você estiver usando tabelas InnoDB.
A partir da versão 5.7 do MySQL, o resultado de uma consulta SELECT em uma VIEW será armazenada no cache.

Como configurar

É possível configurar o valor do query cache

  • no terminal do seu sistema, com o comando mysqld;
  • no arquivo de configuração my.cnf (permanentemente);
  • no cliente do MySQL.

Neste texto, vou mostrar como configurar o query cache direto no cliente MySQL. Abra um terminal e autentique-se no servidor:

mysql -u root -p

Dentro do MySQL, verifique se o query cache está disponível:

SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

Para a gente pode brincar, aqui, o valor da variável have_query_cache precisará ser igual a YES, mesmo que o query cache esteja desativado.
Dentro do MySQL ajuste o tamanho do query cache através da definição da variável de sistema query_cache_size:

SET GLOBAL query_cache_size = 40000

Para desabilitar a função, ajuste o valor de query_cache_size para 0. Seja cuidadoso com o tamanho total da variável. As threads precisam trancar o cache durante sua atualização — caches muito grandes ficam mais tempo trancados.

Textos relacionados:
  • O comando SELECT — conheça várias dicas de uso do comando, no MySQL.
  • Data types — os tipos de dados possíveis para criar campos em uma tabela.
  • Storage engines — conheça os mecanismos de armazenamento do MySQL.

Que tamanho usar pro query cache

O manual do MySQL adverte que o tamanho mínimo possível para query_cache_size é 40Kb, para que ele possa alocar suas estruturas.
Já o tamanho adequado depende da arquitetura do seu sistema. Como já foi dito, valores muito altos podem causar mais prejuízos do que benefícios — uma vez que aumentam o tempo em que o cache fica indisponível a cada atualização.
Valores muito pequenos podem causar a exibição de uma mensagem de aviso e o desativamento do query cache. Veja:

SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect query_cache_size value: '40000'
*************************** 2. row ***************************
  Level: Warning
   Code: 1282
Message: Query cache failed to set size 39936;
new query cache size is 0
2 rows in set (0.01 sec)

Na última linha da mensagem, o sistema avisa que alterou o valor da variável query_cache_size para 0.
Neste caso, você deve aumentar gradualmente o valor da variável e testar o seu sistema por alguns dias.
O manual da versão 5.7 do MySQL recomenda um valor mínimo de 1000000, para que o query cache consiga guardar um número razoável de resultados de consultas.

screenshot mysql set global query_cache_size
Clique para ampliar