Obtendo dados de tabelas, colunas e relacionamentos no MySQL

Artigo que mostra instruções SQL para se obter informações sobre tabelas, colunas e relacionamentos entre tabelas em bancos de dados MySQL.

Golfinho
Introdução

Ao desenvolver um framework ou um sistema que possui uma "porta dos fundos" para o BD, onde é possível acessar a base de dados diretamente através de uma interface genérica, podemos precisar obter informações sobre as tabelas, colunas, relacionamentos, etc. No MySQL, estas informações ficam em um banco reservado chamado information_schema, que possui diversas VIEWS para consulta de dados.

Este artigo apresenta algumas SQLs úteis para se obter informações a respeito de tabelas, colunas, índices, chaves e relacionamentos entre tabelas, no SGBD MySQL.

SQLs úteis para MySQL

1 - Obter informações sobre as tabelas de um banco de dados ou de uma tabela específica:

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'nome_do_bd';
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'nome_do_bd' AND TABLE_NAME = 'nome_da_tabela';

Valores retornados:

  • TABLE_CATALOG - Catálogo (caso exista)
  • TABLE_SCHEMA - Nome do BD
  • TABLE_NAME - Nome da tabela
  • TABLE_TYPE - Tipo de tabela, que pode ser "BASE TABLE" (tabela convencional) ou "VIEW" (visão)
  • ENGINE - Engine usada pela tabela (por exemplo: MyISAM ou InnoDB)
  • VERSION - Versão da estrutura da tabela
  • ROW_FORMAT - Formato da linha
  • TABLE_ROWS - Número de linhas
  • AVG_ROW_LENGTH - Tamanho médio das linhas (em bytes)
  • DATA_LENGTH - Tamanho dos dados da tabela (em bytes)
  • MAX_DATA_LENGTH - Tamanho máximo da tabela ou zero, caso não esteja definido
  • INDEX_LENGTH - Tamanho dos dados de índices (em bytes)
  • DATA_FREE - Bytes alocados, mas não utilizados ainda
  • AUTO_INCREMENT - Valor do próximo número de auto-incremento
  • CREATE_TIME - Data de criação
  • UPDATE_TIME - Data da última modificação
  • CHECK_TIME - Data da última verificação
  • TABLE_COLLATION - Colation padrão
  • CHECKSUM - Valor do checksum para determinar a integridade dos dados (caso disponível)
  • CREATE_OPTIONS - Opções extras usadas no CREATE TABLE
  • TABLE_COMMENT - Comentário da tabela

2 - Obter as colunas (campos) de uma tabela de um banco de dados:

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'nome_do_bd' AND TABLE_NAME = 'nome_da_tabela';

Valores retornados:

  • TABLE_CATALOG - Catálogo
  • TABLE_SCHEMA - Nome do BD
  • TABLE_NAME - Nome da tabela
  • COLUMN_NAME - Nome da coluna (campo da tabela)
  • ORDINAL_POSITION - Posição numérica na tabela
  • COLUMN_DEFAULT - Valor padrão da coluna
  • IS_NULLABLE - Valor pode ser nulo ou não (YES ou NO)
  • DATA_TYPE - Tipo de valor aceitado (exemplo: mediumint, char, varchar)
  • CHARACTER_MAXIMUM_LENGTH - Número máximo de caracteres
  • CHACACTER_OCTET_LENGTH - Número máximo de bytes (já que algumas codificações de caracteres usam mais de um byte para representar um único símbolo).
  • NUMERIC_PRECISION - Precisão de campos numéricos
  • NUMERIC_SCALE - Precisão de casas decimais de campos numéricos
  • CHARACTER_SET_NAME - Nome da codificação (encoding) de caracteres para campos textuais
  • COLLATION_NAME - Colation usado para campos textuais
  • COLUMN_TYPE - Nome completo do tipo
  • COLUMN_KEY - Tipo de chave (caso seja)
  • EXTRA - Informações extras da coluna
  • PRIVILAGES - Privilégios do usuário sobre a coluna
  • COLUMN_COMMENT - Comentário sobre a coluna

3 - Obter os relacionamentos entre tabelas (chaves estrangeiras) de uma tabela:

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'nome_do_bd' AND TABLE_NAME = 'nome_da_tabela' AND REFERENCED_TABLE_NAME IS NOT NULL;

Valores retornados:

  • CONSTRAINT_CATALOG - Catálogo
  • CONSTRAINT_SCHEMA - Nome do BD
  • CONSTRAINT_NAME - Nome da relação (nome usado ao criar a foreign key)
  • TABLE_CATALOG - Catálogo da Tabela
  • TABLE_SCHEMA - Nome do BD da tabela
  • TABLE_NAME - Nome da tabela
  • COLUMN_NAME - Nome da coluna (chave estrangeira / foreign key)
  • ORDINAL_POSITION - Número da relação na tabela
  • POSITION_IN_UNIQUE_CONSTRAINT - ??? (eu não sei, você sabe?)
  • REFERENCED_TABLE_SCHEMA - Nome do BD da tabela referenciada
  • REFERENCED_TABLE_NAME - Nome da tabela rerefenciada (de onde veio a chave)
  • REFERENCED_COLUMN_NAME - Nome da chave da tabela referenciada

51 comentários

Rubens Takiguti Ribeiro (autor do blog) disse...

Marlon, basta ajustar a SQL que busca as chaves estrangeiras de uma tabela. Ficaria assim:

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'nome_do_bd' AND TABLE_NAME = 'nome_da_tabela1' AND REFERENCED_TABLE_NAME = 'nome_da_tabela2';

Se a consulta devolver resultados, serão os relacionamentos das tabelas.

Anônimo disse...

Não sei o que acontece mas o meu campo TABLE_ROWS de algumas tabelas ficam alternando aumentando e diminuindo o valor a cada vez q eu rodo o select, e detalhe ninguem esta usando a tabela pois é um backup local. alguma dica ou sugestão?

Rubens Takiguti Ribeiro (autor do blog) disse...

Anônimo, você está usando InnoDB? Encontrei algo dizendo que TABLE_ROWS é apenas uma estimativa para tabelas da engine InnoDB, já que esta engine não armazena em nenhum lugar a quantidade de linhas.

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

Thiago J disse...

Realmete verifiquei com professor de BD, ele disse que alguns desses dados são apenas estimativas e não um valor real.

Leonardo Rocha disse...

Olá Pessoal, este site está me ajudando muito. Mas gostaria de fazer algo especifico que não estou conseguindo implementar, é o seguinte:

Necessito selecionar todos os campos NULOS de uma determinada tabela e informar a quantidade de campos encontrados. Lembrando que preciso utilizar um parâmetro nesta consulta.

Se alguém aí puder ajudar, agradeço muito.

Abraços!

Leonardo Rocha

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Leonardo

Primeiramente, você vai precisar detectar quais campos podem ser nulos. Isso é simples: basta seguir o item 2 deste artigo e checar o valor de "IS_NULLABLE".

Em seguida, você deve fazer uma consulta para para campo que pode ser nulo. Será algo do tipo:
SELECT COUNT(*) AS total_registros_nulos FROM tabela WHERE campo IS NULL;
(onde "campo" é o nome do campo que pode receber valores nulos).

Creio que o MySQL não guarde esta quantidade em nenhum lugar, por isso a necessidade de consulta.

Leonardo Rocha disse...

Olá Rubens, obrigado pela força.

Mas na verdade não expressei corretamente, necessito que o select me informe quais campos não foram PREENCHIDOS na tabela.

Por exemplo, inseri esta query conforme vc descreveu: SELECT COUNT(*) AS total_registros_nulos FROM cgi.instituicao WHERE id = 81 and secretaria =''

Porém preciso fazer algo aonde o sistema corre por todos os campos da tabela "cgi.instituicao" e verifique quantos campos não foram preenchidos referente ao registro de id = 81.

Ou seja, não me limito aos que são is_nullable ou não e sim a todos os campos.

Por exemplo, o registro 81 da tabela cgi.instituicao tem 32 campos, preenchi apenas 12 campos, então gostaria que o sistema me informasse que 20 campos não foram preenchidos.

Estou finalizando um trabalho e isso seria uma mão na roda.

Mais uma vez Rubens, muito obrigado.

Att,

Leonardo Rocha

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Leonardo
Agora entendi o que quer. Porém, se um campo é "INT" e não pode ser nulo, o que você vai considerar como "preenchido" e "não preenchido"? Será verificado se o valor é zero para ser considerado "não preenchido"?

Se você precisa saber, para um determinado registro, quais (ou quantos) campos não foram preenchidos, então você precisa primeiro obter as características destes campos e depois montar uma query dinamicamente. Nesta query, você incluiria a regra para determinar se uma coluna é considerada preenchida ou não.

Supondo que sua tabela tenha os campos: campo1 = INT NOT NULL, campo2 = VARCHAR NOT NULL, campo3 = VARCHAR NULL. Então montaria a query:
SELECT
(campo1 <> 0) AS campo1_foi_preenchido,
(campo2 <> '') AS campo2_foi_preenchido,
(campo3 IS NOT NULL) AS campo3_foi_preenchido
FROM tabela
WHERE id = 81;

A query vai devolver uma coleção de valores booleanos, indicando se o campo foi preenchido ou não.

Se precisa da quantidade de campos não preenchidos, seria algo do tipo:
SELECT
(
(campo1 = 0) +
(campo2 = '') +
(campo3 IS NULL)
) AS total_nao_preenchido
FROM tabela
WHERE id = 81;

Rubens Takiguti Ribeiro (autor do blog) disse...

Leonardo, a segunda query que passei devolve o número total de campos não preenchidos de um registro específico. Ela funcionou aí?

Para obter o total de campos não preenchidos de toda a tabela precisa usar a função SUM:

SELECT
SUM(
(campo1 = 0) +
(campo2 = '') +
(campo3 IS NULL)
) AS total_nao_preenchido
FROM tabela;

Para obter o total de campos não preenchidos por registro, é só tirar o "where" e selecionar a chave primária:
SELECT
id,
(
(campo1 = 0) +
(campo2 = '') +
(campo3 IS NULL)
) AS total_nao_preenchido
FROM tabela;

Leonardo Rocha disse...

Rubens,

Tentei fazer conforme vc mostrou mas não deu certo, veja abaixo:

SELECT
(
(utiliza is null) +
(freguencia is null) +
(qtd_token_acesso is null)
) as total
FROM cgi.renisp
WHERE id_instituicao = 81

Leonardo Rocha disse...

Por incrível que pareça, no banco está assim mesmo. rsrr

Os erros que o pgadmin dá são estes:

ERROR: operator does not exist: boolean + boolean
LINE 3: (utiliza is null) +
^
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

********** Erro **********

ERROR: operator does not exist: boolean + boolean
SQL state: 42883
Dica: No operator matches the given name and argument type(s). You may need to add explicit type casts.
Caracter: 28

Rubens Takiguti Ribeiro (autor do blog) disse...

Leonardo, eu achava que você estava utilizando o MySQL, já que o artigo é sobre MySQL. No MySQL, não existe o tipo booleano. Então, quando uma comparação é feita, o resultado é devolvido na forma de inteiro ("1" para TRUE e "0" para FALSE). Com isso, fica simples realizar a conta.

No caso do PostgreSQL, você provavelmente vai precisar adaptar a solução para converter este valor booleano para 1 ou 0. Uma opção é usar um operador condicional:
http://www.postgresql.org/docs/8.1/static/functions-conditional.html

Ficaria algo como:

SELECT
(
(CASE WHEN (utiliza is null) THEN 1 ELSE 0 END) +
(CASE WHEN (freguencia is null) THEN 1 ELSE 0 END) +
(CASE WHEN (qtd_token_acesso is null) THEN 1 ELSE 0 END)
) as total
FROM cgi.renisp
WHERE id_instituicao = 81

Talvez exista alguma forma mais simples. Dê uma pesquisada sobre casting no PostgreSQL.

Leonardo Rocha disse...

Rubens, deu tudo certo.

Agradeço muito a disponibilidade em repassar o seu conhecimento que foi de muita importância para o meu projeto.

Deixo aqui meus sinceros agradecimentos.

Um grande abraço!

Leonardo Rocha

JMarcelo disse...

Olá Rubens,

Estou precisando de uma sugestão. Primeiramente, não sou programador, mas por necessidade vivo envolvido com essas questões. E, assim, às vezes me vejo envolvido por dificuldades básicas que não consigo resolver, leio manuais, mas não tive sucesso.
Problema: Estou estudando mortalidade, tenho 433 tabelas algumas com 40 campos outras com 60 (mas os 40 são comuns!). As tabelas originais estavam em .DBF eu as importei (usando ESF Database Migration Toolkit - Pro). Quando fui inserir as chaves primárias me deparei com registros duplicados.
Problema 1.1: por amostragem, percebi que isso ocorria em várias tabelas, isso impediria a atribuição de chaves primárias. Pensei na criação de rotinas genéricas, mas aí enrosquei, exemplo:
BEGIN
DECLARE ctrl_loop INT Default 1996;
DECLARE name_tbl VARCHAR(8) default ' ';

while ctrl_loop <= 2011 DO

set name_tbl = CONCAT('doal', ctrl_loop);

ALTER TABLE name_tbl
ALTER `NUMERODO` DROP DEFAULT,
ALTER `CAUSABAS` DROP DEFAULT;
ALTER TABLE name_tbl
CHANGE COLUMN `NUMERODO` `NUMERODO` VARCHAR(8) NOT NULL FIRST,
CHANGE COLUMN `CAUSABAS` `CAUSABAS` VARCHAR(4) NOT NULL AFTER `NECROPSIA`,
ADD PRIMARY KEY (`NUMERODO`, `CAUSABAS`);
set ctrl_loop = ctrl_loop + 1;
end while;

END

O problema dessa rotina genérica para as tabelas que começam com 'doal' para os anos de 1996 ... 2011 é que recebo a msg "Erro SQL (1146): Table 'bd_mortalidade.name_tbl' doesn't exist.
Eu queria que a variável local name_tbl assumisse os valores de cada tabela, ex. doal1996, executasse a rotina. Novamente executasse a rotina, mas como doal1997 e assim por diante até 2011. Porém o SQL entende que name_tbl é uma tabela do banco de dados bd_mortalidade.
Como faço para tornar essa variável local não ser entendida como uma tabela ou variável de uma das tabelas? (tentei usar o sinal @ antes da variável, mas o SQL diz que há erro de sintaxe.)
Abraço.

JMarcelo disse...

Ah, só para reforçar.
O código do post acima foi só exemplo dos erros que tenho obtido tentando fazer rotinas genéricas.
Eu estou refazendo o código que procura as duplicidades. Vi seu post acima e incorporei a contagem de NULL por registro. Porque ainda tenho mais este problema, tenho registros duplicados que diferenciam-se apenas pela qualidade, tem muitos campos em branco, o que estou assumindo com um padrão de qualidade. Quanto mais NULOS houverem no registro pior é a qualidade e então a decisão será de deletá-lo e deixar seu par "mais completo".
abraço.

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá JMarcelo,

Para usar a variável com o nome da tabela para fazer a consulta, creio que você vai precisar montar uma prepared statement do MySQL. Veja detalhes sobre variáveis do MySQL neste link:
http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

Lá pelo meio da página tem um exemplo parecido com isso:
SET @nome = CONCAT("tabela", "2011");
SET @sql = CONCAT("SELECT * FROM " , @nome);
PREPARE stmt FROM @sql;
EXECUTE stmt;

Ou seja: primeiro você monta o nome da tabela e joga em uma variável @nome; depois monta dinamicamente a query a ser executada e joga na variável @sql; depois você prepara a query que está na variável @sql; depois você executa a query preparada.

JMarcelo disse...

Rubens muito obrigado pela resposta, mas aparece uma mensagem de erro que não consegui solucionar:
/* Erro SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`NUMERODO` VARCHAR(8) NOT NULL FIRST, CHANGE COLUMN `NUMERODO` `NUMERODO` VARCHA' at line 1 */

o código usado foi:
BEGIN
DECLARE ctrl_loop INT Default 1996;

while ctrl_loop <= 2011 DO

-- Atribui nome à variável que recebe o nome da tabela.
SET @name_tbl = CONCAT('doal', ctrl_loop);

-- Atribui valor à variável local que recebe a string concatenada com a sintaxe do comando.
SET @stmt_montado = CONCAT("ALTER TABLE " , @name_tbl, " ALTER `NUMERODO` DROP DEFAULT, ", "ALTER `CAUSABAS` DROP DEFAULT;");

-- Prepara e executa a prepared statement.
PREPARE stmt1 FROM @stmt_montado;
EXECUTE stmt1;
-- libera a alocação de statement stmt1.
DEALLOCATE PREPARE stmt1;

SET @stmt_montado = CONCAT("ALTER TABLE ", @name_tbl, " ALTER `NUMERODO` `NUMERODO` VARCHAR(8) NOT NULL FIRST, ",
"CHANGE COLUMN `NUMERODO` `NUMERODO` VARCHAR(8) NOT NULL FIRST, ", " CHANGE COLUMN `CAUSABAS` `CAUSABAS` VARCHAR(4) NOT NULL AFTER `NECROPSIA`, ",
" ADD PRIMARY KEY (`NUMERODO`, `CAUSABAS`);");
PREPARE stmt2 FROM @stmt_montado;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

-- incrementa o controle de loop.
SET ctrl_loop = ctrl_loop + 1;

end while;

END

O que será que tem de errado agora?

Rubens Takiguti Ribeiro (autor do blog) disse...

JMarcelo,

O problema foi que a SQL que você gerou dinamicamente está com erro de sintaxe. Você pode tentar imprimir a SQL gerada para facilitar a depuração.

JMarcelo disse...

Rubens,

Pois é, o alerta diz isso, mas não consegui encontrar o erro. Pois este mesmo código funcionou para um registro, então coloquei dentro dos parênteses para a prepared stmt e aí algo deu errado.
Tou na busca, qq coisas te conto.
abraço.
Obrigado.

JMarcelo disse...

Olá Rubens, peço uma opinião sua. Preciso inserir chave primária nas tabelas do meu banco de dados. O campo que distingue um registro do outro por vezes se repete. Minha saída 1 é aplicar uma chave composta, a saída 2 seria inserir um campo auto-numerado. Qual dessa alternativas é melhor, ou qual delas é pior?

Rubens Takiguti Ribeiro (autor do blog) disse...

JMarcelo, particularmente prefiro utilizar um campo autoincrementável. A vantagem dele é que para fazer referências aos registros da tabela, basta transferir uma única chave para outra tabela. Com chave composta, precisa passar todos os campos da chave composta para fazer referência. Além disso, com um único campo a consulta é mais fácil e rápida.

Unknown disse...

Parabéns pela postagem, uma dúvida.
Preciso resgatar o tamanho total do BD e tabelas individualmente, precisava também fazer um select e resgatar o tamanho do mesmo.
Ex
GET_SIZE(SELECT * FROM 'TABELA' WHERE ...);

MAS PRECISO FAZER ISSO NO SHELL.
E possível?

Desde já agradeço.

Rubens Takiguti Ribeiro (autor do blog) disse...

Unknown,

Para obter o tamanho aproximado de uma tabela, basta você consultar o campo DATA_LENGTH, conforme mostrado no artigo. Para obter o tamanho total de um banco, basta fazer um SUM(DATA_LENGTH) sobre todas as tabelas de um banco, ou seja, é só buscar pelo nome do banco nas condições.

Sobre buscar o tamanho de uma consulta específica, creio que não tem nada nativo para isso. Neste caso, você precisaria fazer a consulta e estimar o tamanho pelo resultado obtido (avaliando o tamanho de cada campo retornado) ou pela quantidade de registros devolvidos.

Marco Neves disse...

Olá Rubéns meu nome é Marco
Estou com uma dúvida, poderia me ajudar.

Gostaria de realizar um consulta e retornar tando os valores das colunas quanto o esquema da tabela
Ex
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'nome_do_bd' AND TABLE_NAME = 'nome_da_tabela' and (nome_da_tabela.id='1')

Rubens Takiguti Ribeiro (autor do blog) disse...

Marco,

Você pode fazer isso com um select parecido com este (considerando um banco chamado "sistema" e uma tabela "usuarios"):
SELECT sistema.usuarios.*, information_schema.tables.* FROM sistema.usuarios, information_schema.tables WHERE information_schema.tables.table_schema = 'sistema' AND information_schema.tables.table_name = 'usuarios';

Embora isso seja possível, não é recomendado.

O motivo é que para cada registro retornado, você obteria os dados de esquema da tabela consultada e que é igual para todos registros.

O ideal é que você faça duas consultas separadas para poupar tráfego de dados entre sistema e banco: uma para obter os registros e outra para obter o esquema.

Marco Neves disse...

Entendo e compreendo, o problema é que vou usar este tipo de consulta para gerar relatórios, e preciso realmente colocar esses dados em um único array como resposta pois irei retornar com um array json para minha página, única forma que encontrei. Obrigado por me ajudar Rubens, estamos ai qualquer coisa. Valeu cara ;)

Anônimo disse...

Ola estou tentando visualizar os relacionamentos utilizando
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'cadastro' AND TABLE_NAME = 'cidades' AND REFERENCED_TABLE_NAME IS NOT NULL;
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'cadastro' AND TABLE_NAME = 'estados' AND REFERENCED_TABLE_NAME IS NOT NULL;
mas não esta listando nada, ou seja esta tudo NULL na coluna REFERENCED_TABLE_NAME da tabela KEY_COLUMN_USAGE.
Porem pela estrutura da tabela no phpmyadmin percebo que existem os relacionamento: fk_cidades_estados_1 e fk_estados_paises_1.
Como abter corretamente os relacionamentos entre as tabelas?????

Anônimo disse...

Ok resolvido o problema acima, verifiquei que eram relacionamentos internos, exclui e fiz novos relacionamentos InnoDB e funcionou, listou direitinho.

Tiburcio Salgado disse...

Olá, ótimo blog!
Mas gostaria que me ajudasse em uma dúvida.

Como faço para traçar um caminho de uma tabela a outra em uma consulta, onde retorne cada tabela que esteja entre elas? Seria possível?

Grato desde já.

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Tiburcio
Isso me parece um pouco difícil de se fazer apenas com queries SQL.
Eu imagino que você precisaria fazer alguma lógica com software, afinal, podem existir diferentes "caminhos" entre duas tabelas.
Eu faria uma busca em largura, partindo da tabela de origem e percorrendo os relacionamentos dela e, se encontrar a tabela de destino, para, senão, faz a busca nos relacionamentos das tabelas relacionadas (recursivamente). Enfim, caso não conheça, dê uma estudada sobre busca em largura:
http://pt.wikipedia.org/wiki/Busca_em_largura

Outra opção é com busca em profundidade, mas aí você pode acabar caindo no maior caminho, que pode não ser o desejado.

Também precisa ter um cuidado especial para não entrar em loop. Então precisa bolar alguma forma de controlar o que já foi percorrido para não percorrer novamente.

Tiburcio Salgado disse...

Bela explicação Rubens,

realmente uma busca em largura com uma estrutura de repetição é o ideal, mas é um pouco complexo. Mas mesmo assim obrigado pela orientação.

Sucessos!

Cleber Alcasar disse...

Rubens,
Preciso de sua ajuda na questão a seguir:
Quero o tamanho parcial de uma tabela onde o GROUP BY de um campo COLUNA seja a referência.
Por exemplo:
Quero selecionar todas as linhas que contem o dado específico 123456 na COLUNA (codigo)
E saber o tamanho em bytes desse GROUP BY
Quero dizer que a TABELA poderá ter vários agrupamentos.
5000 linhas com valor 123456 na COLUNA (código)
7300 linhas com valor 124446 na COLUNA (código)
500 linhas com valor 888999 na COLUNA (código)
e assim por diante e quero o tamanho em bytes, separadamente, de cada agrupamento de linhas da COLUNA (código) pela referência do valor / conteúdo da coluna.
Pode me indicar a SELECT correta?
Tentei inserir o valor no exemplo do blog e não resultou. A select da tabela inteira está retornando o tamanho corretamente, mas preciso de apenas parte da tabela e não da tabela inteira.
Pode me ajudar? Obrigado pela atenção.

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Cleber

Creio que o MySQL não guarde o tamanho de cada registro, o que seria necessário para obter os valores que você quer.

Talvez seja mais simples obter uma estimativa, levando-se em conta a quantidade total de registros da tabela e o tamanho total da tabela (fazendo uma regra de 3).

Para obter o valor real, acho que precisaria consultar tudo e calcular manualmente, ou então jogar os registros desejados em uma tabela auxiliar e calcular o tamanho desta tabela auxiliar. Soluções bem inviáveis...

Alx Roveda disse...

Boa noite, Rubens, tenho a seguinte situação, em uma tabela MovementType existe o campo credit_type que se marcado siginifica que aquele tipo de movimento é crédito, e se o campo estiver desmarcado ele é débito. Pois bem, eu preciso selecionar os dados no banco, e com eles preencher um select.

Para isso preciso que seja mostrado para o usuário da seguinte maneira:

Crédito - Pagamento de fornecedor
Débito - Pagamento de fornecedor

Veja que para alguns tipos de movimentos existem os dois tipos, crédito e débito, e para isso, preciso que o campo credit_type seja substituido seu valor para crédito se ele for verdadeiro e débito se ele for falso. Como que faço isso?

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Alx Roveda

Eu não entendi muito bem. Se o campo credit_type só pode assumir dois valores, como é que "para alguns tipos de movimentos existem os dois tipos"?

Pelo que me parece, sua dúvida está relacionada ao funcionamento dos campos input checkbox de HTML, não?

Um campo checkbox só é enviado quando ele está marcado, logo, se você tem algum campo com algum nome e ele não chega no PHP ao submeter o formulário, é porque ele não foi marcado. Uma solução para sempre enviar um valor é criar um campo input hidden com o mesmo name do checkbox (e com valor "0") e coloca-lo antes do checkbox (com valor "1"). Assim, se o usuário não marca o checkbox, é enviado o valor "0", mas se ele marca, é enviado o valor "1".

Anônimo disse...

Bom dia,

Precisava de ajuda aqui num probleminha.
Tenho uma tabela marca_equipamento e outra modelo_equipamento,
ao qual a marca_equipamento tem as colunas mrc_id(primary key),mrc_codigo, mrc_designacao e a tabela
modelo_equipamento tem as colunas mdl_id (primary key),mdl_mrc_id(foreign key),mdl_codigo,mdl_designacao.
Eu tive de associar nova coluna na tabela modelo_equipamento para que apareça numa View (codigo asp.net mvc) a informação da marca.
Para isso precisava da relação mdl_designacao_marca ir retirar dados a coluna mrc_codigo.como posso fazer isso?

select mdl_id,mdl_mrc_id,mdl_codigo,mdl_designacao,mdl_designacao_marca
from modelo_equipamento
inner join marca_equipamento on mdl_mrc_id = mrc_id where mdl_id=13.

retorna mdl_designacao_marca a 0.

podem ajudar?!

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Anônimo
Sua dúvida não tem relação direta com o artigo. Em todo caso, não entendi o que você quis dizer com "associar nova coluna na tabela modelo_equipamento". Você se refere a criar um novo campo ou fazer uma consulta com JOIN para obter a informação de uma tabela relacionada?

No exemplo que você colocou, você fez um JOIN, mas só obteve dados da tabela de modelos. Se você faz um JOIN para obter o campo da tabela de marcas, é só pedir os campos desejados no SELECT. Só precisa se atentar quando existe um campo com mesmo nome nas duas tabelas (precisa desambiguar). Veja um exemplo:

SELECT
mdl_id, mdl_mrc_id, mdl_codigo, mdl_designacao, mrc_designacao
FROM
modelo_equipamento INNER JOIN marca_equipamento ON mdl_mrc_id = mrc_id
WHERE
mdl_id = 13

Bravo Consultor disse...

Boa tarde @Rubens Takiguti Ribeiro, como eu poderia obter os valores de todas as colunas obtidas pelo "Select * FROM table WHERe ID = 1", preciso dos registro de apenas a linha referente ao ID, mas são muitas colunas então não possu utilizar [campo], teria que ser para todas as colunas e naum uma a uma pelo nome. Agraço desde já.

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Bravo Consultor
Você quer que em uma única consulta você obtenha as informações de todos os campos de uma tabela. Não sei se isso seja possível.

Note que o artigo explica como obter informações sobre a estrutura das tabelas de um banco de dados e não é sobre como obter os dados que foram inseridos em uma tabela.

Jose Dias disse...

Bom Dia, Rubens.
Preciso de sua ajuda.
tenho esta query "SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'userger'"
obtenho o resultado de todas as tabelas que possui o "userger" como coluna. Agora gostaria de saber como faço pra ter o resultado, em qual tabela por exemplo "SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'userger'=2", sem precisar de fazer relacionamento?

Unknown disse...

Boa tarde Rubens, na paz? Excelente artigo. Dentro ainda deste conteúdo, veja se consegue me ajudar. Preciso fazer uma consulta em um banco, mas preciso mostrar todas as TABELAS com algum registro e que possua o CAMPO CDPROD.
Nesta sintaxe :

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS > 0 AND TABLE_SCHEMA = 'BANCO_DE_DADOS';

Me traz as tabelas, mas caso eu acrescente a condição :
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS > 0 AND TABLE_SCHEMA = 'BANCO_DE_DADOS'
AND column_name like '%cdprod%';

Retorna o erro: "Unknown column 'column_name' in 'where clause'.

É possível consultar tabelas e campos no mesmo comando? Se não, há alguma sugestão ?

Obrigado e parabéns pelo artigo.

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, Unknown

É possível fazer isso que você quer. Só que o campo "COLUMN_NAME" não fica na tabela "TABLES", mas sim na tabela "COLUMNS", então você precisa fazer um JOIN desta forma:

SELECT t.TABLE_NAME FROM TABLES t INNER JOIN COLUMNS c ON (t.table_name = c.table_name AND t.table_schema = c.table_schema) WHERE t.TABLE_ROWS > 0 AND t.TABLE_SCHEMA = 'tantodetrem' AND c.COLUMN_NAME LIKE '%cdprod%' GROUP BY t.TABLE_NAME;

Rubens Takiguti Ribeiro (autor do blog) disse...

Olá, José Dias

O banco "INFORMATION_SCHEMA" só possui meta informações sobre as tabelas, colunas e relacionamentos. Os dados ficam nas tabelas propriamente ditas. Ou seja, não é possível obter uma meta informação levando em conta um dado real sem fazer um relacionamento.

Lucas Henrard disse...

Bom dia, tenho uma tela de um sistema, na tela tenho um campo id e ao digitar um valor nele quero retornar do banco para minha aplicação todos os elementos que possuam esse id digitado. Como faço?

Rubens Takiguti Ribeiro (autor do blog) disse...

Lucas, pela sua dúvida, me parece que você está começando a aprender sobre banco de dados. Este artigo apresenta algo um pouco menos comum, que é extrair meta-informações sobre tabelas do banco, e não os dados propriamente.

Para obter dados de uma tabela, sugiro que leia por completo este outro artigo:
http://rubsphp.blogspot.com.br/2010/09/pdo.html