MySQL查询以显示开发和生产模式架构之间的差异

Question

我希望能够使用MySQL中的模式数据库进行查询，以显示生产和开发两个数据库模式之间的列、触发器和存储过程的区别。

查询而非工具。 我看到了比较两个MySQL数据库，其中列出了可以执行此任务的工具，但我想知道的是是否有一个查询可以执行此任务。 请只建议查询，我真的不想知道有关工具、命令行技巧或类似内容。 我想查看生产数据库和开发数据库是否不同步。 哪些字段、程序等被添加或更改，以便在推出使用数据库的新客户端软件更新时，我可以更新生产数据库。

我正在使用MySQL 5.1最新版本。

- Johan

---

你是在谈论比较可能相似的数据库表（例如生产与开发），并且想要由于任何更改使它们同步，如果是这样，那么你想知道新的/更改的列、过程、索引等，否则，如果你试图比较例如地图数据和音乐或电影，那就没有意义了。 - DRapp

是的，开发数据库和生产数据库之间的对比。 - Johan

Answer 1

Johan，请尝试运行此脚本。请在脚本开头指定您想要比较的两个数据库。查询将返回数据集，并为表/视图列设置状态。

状态“仅在源中” - 对象仅存在于db1中； 状态“仅在目标中” - 对象仅存在于db2中； 状态“两个模式都有” - 对象存在于db1和db2中，但细节可能不同；例如：“varchar（255）/int（11）”表示源字段类型为“varchar（255）”，目标字段类型为“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;

这个脚本可以修改以查找触发器和例程之间的差异。

- Devart

---

哇，Devart，这就是我想要的。 - Johan

Answer 2

你需要的所有数据应该在information_schema数据库中的表中。

你可能可以通过某种只显示差异的连接来进行比较，但尝试使用查询或单个查询似乎是解决问题的一种过于复杂的方式，我认为这会给自己制造麻烦。

快速简便的解决方案是要么使用mysqldump --no-data提取每个数据库的内容进行diff，要么从信息模式中提取数据并进行比较。

- James C

---

我只想知道表格和存储过程之间是否有更改，这样我就不必为可能不同步的事情失眠了。 - Johan

Answer 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'; 

希望这能帮到你！

- neocanable

Answer 4

Johan，你已经通过说你想要一个“查询”来比较数据库，缩小了你的答案领域 :)

然而，我建议你考虑“二进制日志”。我已经成功地用它来实现类似的目的。

       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 

显然，您将在生产数据库上执行此操作。

其他方法可能包括：如何同步开发和生产数据库

- sakhunzai

Answer 5

我开发了一个工具，可以用来比较两个数据库。这个工具只适用于MySQL。该工具会生成目标数据库的SQL以同步数据库。这是一个基于CakePHP 2构建的Web应用程序工具，您需要将代码下载并放入xammp-> htdocs（在Windows情况下），然后在使用之前创建一个虚拟域。欲了解更多信息，请访问以下链接。

https://github.com/hardeepvicky/MySql-Schema-Compare

- Hardeep Singh

Answer 6

这是一个老掉牙的方法，但它确实有效。在devart的示例基础上，我继续构建了过程和函数的比较：

SET @source_db = 'qls_projects_for_comparison';
SET @target_db = 'qls_projects';

-- Pick one and comment out the other
-- SET @routine_type = 'FUNCTION';
SET @routine_type = 'PROCEDURE';

-- Get the ones only in the source
SELECT
  'Only in SOURCE' exist_type, C1.ROUTINE_NAME, C1.ROUTINE_SCHEMA,         
C1.ROUTINE_TYPE, C1.LAST_ALTERED, C1.DEFINER as 'Source Definer', C2.DEFINER 
as     'Target Definer', def_compare as 'Compare Definitions'  
FROM (    
(SELECT *,'' as def_compare FROM INFORMATION_SCHEMA.ROUTINES WHERE 
ROUTINE_TYPE = @routine_type AND ROUTINE_SCHEMA = @source_db) C1
LEFT JOIN (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @target_db) C2
ON C1.ROUTINE_NAME = C2.ROUTINE_NAME
)
WHERE C2.ROUTINE_NAME IS NULL

UNION ALL

-- Get the ones only in the target
SELECT
  'Only in TARGET' exist_type, C2.ROUTINE_NAME, C2.ROUTINE_SCHEMA,             
C2.ROUTINE_TYPE, C2.LAST_ALTERED, C1.DEFINER as 'Source Definer', C2.DEFINER 
as 'Target Definer', def_compare as 'Compare Definitions'  
FROM (    
(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @source_db) C1
RIGHT JOIN (SELECT *,'' as def_compare FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = @routine_type AND ROUTINE_SCHEMA = @target_db) C2
ON C1.ROUTINE_NAME = C2.ROUTINE_NAME
)
WHERE C1.ROUTINE_NAME IS NULL

UNION ALL

-- Get the ones in both and compare the bodies of the routines 

SELECT 
'In both schemas' exist_type
, C2.ROUTINE_NAME
, C2.ROUTINE_SCHEMA
, C2.ROUTINE_TYPE
, C2.LAST_ALTERED
, C1.DEFINER as 'Source Definer'
, C2.DEFINER as 'Target Definer', 
IF(C1.ROUTINE_DEFINITION=C2.ROUTINE_DEFINITION, 'Matches','Does Not Match') 
as 'Compare Definitions'
FROM (    
   (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @source_db) C1
  INNER JOIN (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 
@routine_type AND ROUTINE_SCHEMA = @target_db) C2
    ON C1.ROUTINE_NAME = C2.ROUTINE_NAME
)

- fitz