Запрос MySQL, чтобы показать разницу между схемой разработки и производства

Ответ 1

Johan, попробуйте запустить script. Укажите две базы данных, которые вы хотите сравнить в переменных в начале script. Запрос возвращает набор данных и устанавливает статусы столбцов таблицы/представления.

Статус "Только в источнике" - объект существует только в db1; Статус "Только в цели" - объект существует только в db2; Статус "В обеих схемах" - объект существует в db1 и db2, но детали могут быть разными; например: значение "varchar (255)/int (11)" говорит, что тип исходного поля - "varchar (255)", а target - "int (11)", значение "null" говорит, что детали равны;

SET @source_db = 'db1';
SET @target_db = 'db2';

SELECT 
  'Only in source' exist_type,
  c1.table_schema, c1.table_name, c1.column_name, c1.ordinal_position, c1.column_default, c1.is_nullable, c1.numeric_precision, c1.numeric_scale, c1.character_set_name, c1.collation_name, c1.column_type, c1.column_key, c1.extra, c1.column_comment
FROM
  (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @source_db) c1
  LEFT JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @target_db) c2
    ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name
WHERE c2.column_name is null

UNION ALL

SELECT
  'Only in target' exist_type,
  c2.table_schema, c2.table_name, c2.column_name, c2.ordinal_position, c2.column_default, c2.is_nullable, c2.numeric_precision, c2.numeric_scale, c2.character_set_name, c2.collation_name, c2.column_type, c2.column_key, c2.extra, c2.column_comment
FROM
  (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @source_db) c1
  RIGHT JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @target_db) c2
    ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name
WHERE c1.column_name is null

UNION ALL

SELECT 
  'In both schemas' exist_type,
  CONCAT(c1.table_schema, '/', c2.table_schema),
  c1.table_name, c1.column_name,
  IF(c1.ordinal_position = c2.ordinal_position OR c1.ordinal_position IS NULL AND c2.ordinal_position IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.ordinal_position, ''), IFNULL(c2.ordinal_position, ''))),
  IF(c1.column_default = c2.column_default OR c1.column_default IS NULL AND c2.column_default IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_default, ''), IFNULL(c2.column_default, ''))),
  IF(c1.is_nullable = c2.is_nullable OR c1.is_nullable IS NULL AND c2.is_nullable IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.is_nullable, ''), IFNULL(c2.is_nullable, ''))),
  IF(c1.numeric_precision = c2.numeric_precision OR c1.numeric_precision IS NULL AND c2.numeric_precision IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.numeric_precision, ''), IFNULL(c2.numeric_precision, ''))),
  IF(c1.numeric_scale = c2.numeric_scale OR c1.numeric_scale IS NULL AND c2.numeric_scale IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.numeric_scale, ''), IFNULL(c2.numeric_scale, ''))),
  IF(c1.character_set_name = c2.character_set_name OR c1.character_set_name IS NULL AND c2.character_set_name IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.character_set_name, ''), IFNULL(c2.character_set_name, ''))),
  IF(c1.collation_name = c2.collation_name OR c1.collation_name IS NULL AND c2.collation_name IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.collation_name, ''), IFNULL(c2.collation_name, ''))),
  IF(c1.column_type = c2.column_type OR c1.column_type IS NULL AND c2.column_type IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_type, ''), IFNULL(c2.column_type, ''))),
  IF(c1.column_key = c2.column_key OR c1.column_key IS NULL AND c2.column_key IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_key, ''), IFNULL(c2.column_key, ''))),
  IF(c1.extra = c2.extra OR c1.extra IS NULL AND c2.extra IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.extra, ''), IFNULL(c2.extra, ''))),
  IF(c1.column_comment = c2.column_comment OR c1.column_comment IS NULL AND c2.column_comment IS NULL, NULL, CONCAT_WS('/', IFNULL(c1.column_comment, ''), IFNULL(c2.column_comment, '')))
FROM
  (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @source_db) c1
  JOIN (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA = @target_db) c2
    ON c1.TABLE_name = c2.TABLE_name AND c1.column_name = c2.column_name;

Этот script можно изменить, чтобы найти различия между триггерами и подпрограммами.

Ответ 2

Все данные, которые вы должны, должны быть в таблицах базы данных information_schema.

Возможно, вам удастся выполнить сравнение с каким-то соединением, которое только показывает вам различия, но пытается сделать это в запросах, или один запрос кажется слишком сложным способом приближения к проблеме, я думаю, вы стреляя себе в ногу.

Быстрое и простое решение заключалось в том, чтобы diff либо содержимое mysqldump --no-data каждой базы данных, либо вытащить данные из информационной схемы и diff.

Ответ 3

t1 сравните с t2

select 
 (case t1.table_name=t2.table_name when 1 then concat(t1.table_name,"==",t2.table_name) else concat(t1.table_name,"!=",t2.table_name) end) as table_name,
 (case t1.column_name=t2.column_name when 1 then concat(t1.column_name,"==", t2.column_name) else concat(t1.column_name,"!=", t2.column_name) end) as column_name,
 (case t1.ORDINAL_POSITION=t2.ORDINAL_POSITION when 1 then concat(t1.ORDINAL_POSITION,"==", t2.ORDINAL_POSITION) else concat(t1.ORDINAL_POSITION,"!=", t2.ORDINAL_POSITION) end) as ORDINAL_POSITION
......--columns in information_schema
from columns t1 left join (select * from columns where table_schema='t2') t2 on t2.table_name=t1.table_name and t2.column_name=t1.column_name where t1.table_schema='t1'; 

надеюсь, что эта помощь!

Ответ 4

Йохан, вы уже сузили свой ответ на домен, заявив, что хотите, чтобы "запрос" сравнивал базы данных:)

Однако мое предложение для вас - подумать о "Binary Logging". Я использовал его для аналогичных целей.

       a) enable logs 
       b) Go through all binary logs files 
       c) grep desired statements
       d) at then end purge/reset binary logs  ie. RESET MASTER 

Очевидно, вы сделаете это на производстве db.

Другими способами могут быть: Как синхронизировать базу разработки и производства