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

| 我想使用MySQL中的模式数据库进行查询  该图显示了两个数据库模式（生产和开发）之间的列，触发器和存储过程之间的差异。 查询，不是工具 我看过比较两个MySQL数据库 其中列出了可以执行此任务的工具，但我想知道是否存在可以执行此任务的查询。 请只提出查询，我真的不想知道工具，命令行黑客之类的东西。 我正在查看生产数据库和开发数据库是否不同步。 以及添加或更改了哪些字段，过程等，因此，如果我发布使用该数据库的客户端软件的新更新，则可以更新生产数据库。 我正在使用MySQL 5.1最新版本。     
2020-03-27 6 条评论
分享

没有找到相关结果

    已邀请:

    抵舵

    Johan，尝试运行此脚本。在脚本开头，在变量中指定要比较的两个数据库。查询返回数据集，并设置表/视图列的状态。 状态“仅在源中”-对象仅存在于db1中； 状态“仅在目标中”-对象仅存在于db2中； 状态“在两个模式中”-对象存在于db1和db2中，但是细节可以不同；例如：value \'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;
    可以修改此脚本以查找触发器和例程之间的差异。     
    2020-03-27 0 0
    分享

    坛沤疲撑拆

    您所需要的所有数据都应在“ 1”数据库的表中。 您也许可以通过某种联接来进行比较，这种联接只能向您显示差异，但是尝试在查询或单个查询中进行联接似乎是解决问题的一种过于复杂的方式，我想您是在自欺欺人在脚下。 一种快速简便的解决方案是将每个数据库的“ 3”的内容加2或从信息模式中提取数据并将它们进行比较。     
    2020-03-27 0 0
    分享

    裸雷胜檀哭

    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\';
    希望这个帮助！     
    2020-03-27 0 0
    分享

    授巨

    约翰，您已经说出您想要一个“查询”来比较数据库，从而缩小了您的回答范围：） 但是我对您的建议是考虑“二进制日志记录”。我已经成功地将其用于类似目的。 
           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
    显然，您将在生产数据库上执行此操作。 其他方式可能是：如何同步开发和生产数据库     
    2020-03-27 0 0
    分享

    届甸衬丝蚕

    这是一个古老的东西，但是可以用。在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
)
        
    2020-03-27 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.037091016769409