對比資料庫欄位不同的sql (mysql版)

標籤：name   engine   project   from   sts   mat   def   creat   orm   

-- 使用test庫 `test_project_management` `oel_project_management`USE test;-- 舊錶DROP TABLE old_column_info;CREATE TABLE `old_column_info` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `table_schema` VARCHAR(64) NOT NULL COMMENT ‘資料庫‘,  `table_name` VARCHAR(64) NOT NULL COMMENT ‘表名‘,  `table_comment` VARCHAR(128) DEFAULT NULL COMMENT ‘表備忘‘,  `column_name` VARCHAR(64) NOT NULL COMMENT ‘列名‘,  `column_type` VARCHAR(32) NOT NULL COMMENT ‘列類型‘,  `is_nullable` VARCHAR(8) NOT NULL COMMENT ‘是否可為空白‘,  `column_default` VARCHAR(32) DEFAULT NULL COMMENT ‘預設值‘,  `column_comment` VARCHAR(128) DEFAULT NULL COMMENT ‘列備忘‘,  PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;-- 舊錶DROP TABLE new_column_info;CREATE TABLE `new_column_info` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `table_schema` VARCHAR(64) NOT NULL COMMENT ‘資料庫‘,  `table_name` VARCHAR(64) NOT NULL COMMENT ‘表名‘,  `table_comment` VARCHAR(128) DEFAULT NULL COMMENT ‘表備忘‘,  `column_name` VARCHAR(64) NOT NULL COMMENT ‘列名‘,  `column_type` VARCHAR(32) NOT NULL COMMENT ‘列類型‘,  `is_nullable` VARCHAR(8) NOT NULL COMMENT ‘是否可為空白‘,  `column_default` VARCHAR(32) DEFAULT NULL COMMENT ‘預設值‘,  `column_comment` VARCHAR(128) DEFAULT NULL COMMENT ‘列備忘‘,  PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;-- 插入舊錶結構INSERT INTO `old_column_info` (  `table_schema`,  `table_name`,  `table_comment`,  `column_name`,  `column_type`,  `is_nullable`,  `column_default`,  `column_comment`) SELECT ‘project_management‘, tab.table_name, tab.table_comment, col.`COLUMN_NAME`, col.`COLUMN_TYPE`, col.`IS_NULLABLE`, col.`COLUMN_DEFAULT`, col.`COLUMN_COMMENT` FROM information_schema.`TABLES` tab, information_schema.columns colWHERE col.table_schema = tab.`TABLE_SCHEMA`AND col.table_name = tab.`TABLE_NAME`AND col.table_schema = ‘test_project_management‘;-- 插入舊錶結構INSERT INTO `new_column_info` (  `table_schema`,  `table_name`,  `table_comment`,  `column_name`,  `column_type`,  `is_nullable`,  `column_default`,  `column_comment`) SELECT ‘project_management‘, tab.table_name, tab.table_comment, col.`COLUMN_NAME`, col.`COLUMN_TYPE`, col.`IS_NULLABLE`, col.`COLUMN_DEFAULT`, col.`COLUMN_COMMENT` FROM information_schema.`TABLES` tab, information_schema.columns colWHERE col.table_schema = tab.`TABLE_SCHEMA`AND col.table_name = tab.`TABLE_NAME`AND col.table_schema = ‘oel_project_management‘;-- 查看欄位名稱未變單資料結構變了的SELECT o.table_schema, o.table_name, o.table_comment, o.`COLUMN_NAME` AS old_column_name, o.`COLUMN_TYPE` AS old_column_type,n.`COLUMN_NAME` AS new_column_name, n.`COLUMN_TYPE` AS new_column_typeFROM old_column_info o, new_column_info nWHERE o.`table_schema` = n.table_schemaAND o.`table_name` = n.table_nameAND o.`column_name` = n.column_nameAND (o.column_type != n.column_typeOR o.`is_nullable` != n.`is_nullable`);-- 查看欄位名稱未變單資料結構變了的SELECT o.table_schema, o.table_name, o.table_comment, o.`COLUMN_NAME` AS old_column_name, o.`COLUMN_TYPE` AS old_column_type,n.`COLUMN_NAME` AS new_column_name, n.`COLUMN_TYPE` AS new_column_typeFROM old_column_info o, new_column_info nWHERE o.`table_schema` = n.table_schemaAND o.`table_name` = n.table_nameAND o.`column_name` = n.column_nameAND o.column_type != n.column_type;-- 查詢老庫裡存在但新庫裡不存在的資料SELECTo.table_schema, o.table_name, o.table_comment, o.`COLUMN_NAME` AS old_column_name, o.`COLUMN_TYPE` AS old_column_typeFROM old_column_info oWHERE NOT EXISTS(SELECT 1 FROM new_column_info nWHERE  o.`table_schema` = n.table_schemaAND o.`table_name` = n.table_nameAND o.`column_name` = n.column_name);-- 查詢新庫裡存在但老庫裡不存在的資料SELECTn.table_schema, n.table_name, n.table_comment, n.`COLUMN_NAME` AS new_column_name, n.`COLUMN_TYPE` AS new_column_typeFROM new_column_info nWHERE NOT EXISTS(SELECT 1 FROM old_column_info oWHERE  o.`table_schema` = n.table_schemaAND o.`table_name` = n.table_nameAND o.`column_name` = n.column_name);

對比資料庫欄位不同的sql (mysql版)