Início / dba / Perguntas / 75532
Accepted
sjdh
Asked: 2014-09-03 16:42:55 +0800 CST

Consulta para comparar a estrutura de duas tabelas no MySQL

  • 772

Para automatizar o processo de backup de um dos meus bancos de dados MySQL, gostaria de comparar a estrutura de duas tabelas (versão atual vs versão antiga).

Você consegue pensar em uma consulta que possa comparar duas tabelas?

Aqui estão algumas tabelas de exemplo que você pode comparar.

CREATE TABLE product_today
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_yesterday
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_2days_back
(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

As duas primeiras tabelas têm estruturas idênticas. A última é diferente. Eu só preciso saber se duas tabelas têm estruturas diferentes ou não. Eu não estou interessado em como eles diferem.

mysql schema

7 respostas

  1. Best Answer

    DUAS TABELAS NO BANCO DE DADOS ATUAL

    Se você quiser saber se duas tabelas são diferentes, execute este

    SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

    Se você realmente precisa ver as diferenças, execute este

    SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

    DUAS TABELAS EM UM BANCO DE DADOS ESPECÍFICO

    Se você quiser saber se duas tabelas são diferentes no banco de dados mydb, execute este

    SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

    Se você realmente precisa ver as diferenças, execute este

    SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

    DUAS TABELAS EM DOIS BANCOS DE DADOS DIFERENTES

    Se você quiser saber se db1.tb1e db2.tb2são diferentes, execute este

    SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

    Se você realmente precisa ver as diferenças, execute este

    SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

    DE UMA CHANCE !!!

    • 50

  2. Você pode comparar a soma de verificação da saída de SHOW CREATE TABLE product_today

    # mysql -NBe "SHOW CREATE TABLE sakila.actor"| sed -r 's/AUTO_INCREMENT=[0-9]+/AUTO_INCREMENT=XXX/g' | md5sum
# 1bc0d72b294d1a93ce01b9a2331111cc  -
    • 3

  3. Expandindo a resposta do RolandoMySQLDBA:

    Para ver também o nome da tabela, consulte isto:

    SELECT table_name, column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        table_name, column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('table_1','table_2')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;
    • 1

  4. Dê uma olhada na tabela de colunas no information_schema - o campo column_type. Isso permitirá que você compare as estruturas da tabela.

    • 0

  5. Minha melhor maneira de comparar 2 bancos de dados (DB1, DB2) - apenas tabelas/visualizações, restrições e chave estrangeira não estão incluídas. No meu caso eu sempre uso o seguinte SQL para comparar PRODUCTION com UAT ou UAT com DEV.

    DB DIFF (comparar tabelas/visualizações)

    select x.* from (
SELECT a.table_name, a.column_name,
    max(IF(b.TS='S1',b.ordinal_position,null)) as S1_ordinal_position,
    max(IF(b.TS='S2',b.ordinal_position,null)) as S2_ordinal_position,
    max(IF(b.TS='S1',b.data_type       ,null)) as S1_data_type,
    max(IF(b.TS='S2',b.data_type       ,null)) as S2_data_type,
    max(IF(b.TS='S1',b.column_type     ,null)) as S1_column_type,
    max(IF(b.TS='S2',b.column_type     ,null)) as S2_column_type
FROM
(SELECT DISTINCT table_name, column_name
 FROM information_schema.columns
 WHERE table_schema IN ('DB1','DB2')
) a
INNER JOIN
(SELECT IF(table_schema='DB1','S1','S2') as TS,
    table_schema,table_name,column_name,ordinal_position,data_type,column_type
 FROM information_schema.columns
 WHERE table_schema IN ('DB1','DB2')
) b
on (a.table_name = b.table_name and a.column_name = b.column_name)
group by a.table_name, a.column_name
) x
where x.S1_ordinal_position != x.S2_ordinal_position or x.S1_ordinal_position is null or x.S2_ordinal_position is null
or    x.S1_data_type        != x.S2_data_type
or    x.S1_column_type      != x.S2_column_type
ORDER BY x.table_name;
    • 0

  6. Estou atrasado, mas estou postando minha resposta para que alguém possa ter mais opções para escolher o formulário

    Recebi um desafio para migrar dados de um banco de dados antigo para um novo. Uma coisa foi que as tabelas mantiveram o nome, mas a estrutura foi alterada. Os valores padrão foram dados a mim em cada tabela e onde não foi fornecido, foi o trabalho de outra pessoa para fazê-lo. O RDBMS era o MariaDb 10.1 e usei o seguinte script para obter a diferença nas colunas. Com Isso você pode adicionar informações adicionais sobre a estrutura dos campos. Espero não ter errado o alvo da pergunta.

    set @tem = cast('tabl1' as char(90));
set @db_new = cast('db_new' as char(90));
set @db_old = cast('db_old' as char(90));
select n.column_name newCol, (case when o.column_name is null then '''''' else
o.column_name end) oldCol from information_schema.columns as n 
left join information_schema.columns as o on (n.table_name = o.table_name and
n.column_name = o.column_name and o.table_schema = @db_old)
where 
n.table_name = @tem
and
n.table_schema = @db_new
    • 0

  7. para todas as alterações na estrutura da tabela de dois bancos de dados:

    SELECT table_schema, table_name, column_name,ordinal_position,data_type,column_type FROM (
    SELECT
        table_schema, table_name, column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema IN ('database1', 'database2')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1 ) A;

    Ref.: de RolandoMySQLDBA ans

    • -2

relate perguntas