mysql对比两个数据库中不同的表和列字段

查询数据库中有多少个表：

SELECT COUNT(*) TABLES, 
table_schema FROM information_schema.TABLES 
WHERE table_schema ='soc_common' GROUP BY table_schema;

　　以下代码亲测：（比较两个数据库中不同）

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

-- 1.将mysql分隔符从;设置为&
DELIMITER &
 
-- 2.如果存在存储过程getdatabaseCount则删除
DROP PROCEDURE IF EXISTS `getdatabaseCount` &
-- 3.定义存储过程，获取特定数据库的数量
-- (传入参数database_name字符串类型，为数据库名；传出参数count_date整数类型，为数量)
CREATE DEFINER=`oms`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT)
    BEGIN
    -- 4.声明变量
    DECLARE $sqltext VARCHAR(1000);
    -- 5.动态sql，把sql返回值放到@count_date中
    SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\';');
    SET @sqlcounts := $sqltext;
    -- 6.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 7.执行SQL语句
    EXECUTE stmt;
    -- 8.释放资源
    DEALLOCATE PREPARE stmt;
    -- 9.获取动态SQL语句返回值
    SET count_date = @count_date;
    END
-- 10.定义存储过程结束
&
 
-- 11.如果存在存储过程getTableCount则删除
DROP PROCEDURE IF EXISTS `getTableCount` &
-- 12.定义存储过程，获取特定数据库表的数量
-- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传出参数count_date整数类型，为数量)
CREATE DEFINER=`oms`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT)
    BEGIN
    -- 13.声明变量
    DECLARE $sqltext VARCHAR(1000);
    -- 14.动态sql，把sql返回值放到@count_date中
    SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\';');
    SET @sqlcounts := $sqltext;
    -- 15.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 16.执行SQL语句
    EXECUTE stmt;
    -- 17.释放资源
    DEALLOCATE PREPARE stmt;
    -- 18.获取动态SQL语句返回值
    SET count_date = @count_date;
    END
-- 19.定义存储过程结束
&
 
 
-- 20.如果存在存储过程getColumnCount则删除
DROP PROCEDURE IF EXISTS `getColumnCount` &
-- 21.定义存储过程，获取特定数据库表列的数量
-- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传出参数count_date整数类型，为数量)
CREATE DEFINER=`oms`@`localhost` PROCEDURE getColumnCount(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), OUT count_date INT)
    BEGIN
    -- 22.声明变量
    DECLARE $sqltext VARCHAR(1000);
    -- 23.动态sql，把sql返回值放到@count_date中
    SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
    SET @sqlcounts := $sqltext;
    -- 24.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 25.执行SQL语句
    EXECUTE stmt;
    -- 26.释放资源
    DEALLOCATE PREPARE stmt;
    -- 27.获取动态SQL语句返回值
    SET count_date = @count_date;
    END
-- 28.定义存储过程结束
&
 
 
-- 29.如果存在存储过程getColumnInfo则删除
DROP PROCEDURE IF EXISTS `getColumnInfo` &
-- 30.定义存储过程，获取特定数据库表列的信息
-- (传入参数database_name字符串类型，为数据库名；传入参数table_name字符串类型，为表名；传入参数column_name字符串类型，为列名；传入参数column_info字符串类型，列信息；传出参数result_data字符串类型，信息)
CREATE DEFINER=`oms`@`localhost` PROCEDURE getColumnInfo(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), IN column_info CHAR(50), OUT result_data CHAR(20))
    BEGIN
    -- 31.声明变量
    DECLARE $sqltext VARCHAR(1000);
    -- 32.动态sql，把sql返回值放到@count_date中
    SET $sqltext = CONCAT('SELECT t.', column_info,' into @column_info FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
    SET @sqlcounts := $sqltext;
    -- 33.预编释，stmt预编释变量的名称
    PREPARE stmt FROM @sqlcounts;
    -- 34.执行SQL语句
    EXECUTE stmt;
    -- 35.释放资源
    DEALLOCATE PREPARE stmt;
    -- 36.获取动态SQL语句返回值
    SET result_data = @column_info;
    END
-- 37.定义存储过程结束
&
 
-- 38.如果存在存储过程comparisonTableExist则删除
DROP PROCEDURE IF EXISTS `comparisonTableExist` &
-- 39.定义存储过程，对比表是否存在
-- (传入参数database_1字符串类型，数据库名1；传入参数database_2字符串类型，数据库名2；传入参数info字符串类型，库表信息)
CREATE DEFINER=`oms`@`localhost` PROCEDURE comparisonTableExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
    BEGIN
    -- 40.声明变量。database_name查询出来的数据库，table_name查询出来的表名
    DECLARE database_name, table_name CHAR(200);
    -- this_info表不存在的记录，database_table_no表不存在的记录跳过重复查询
    DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
    -- database_count_1统计数据库1存在的数量，database_count_2统计数据库2存在的数量，resoult_count统计表存在的数量。如果为0表示不存在
    DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
    -- 41.定义游标结束标识，默认为0
    DECLARE stopflag INT DEFAULT 0;
    -- 42.定义游标，其实就是临时存储sql返回的集合
    DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.COLUMNS t;
    -- 43.游标结束就设置为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
    -- 44.调用存储过程getdatabaseCount，查看两个数据库是否存在，都存在则继续
    CALL getdatabaseCount(database_1, database_count_1);
    CALL getdatabaseCount(database_2, database_count_2);
    IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
        -- 45.打开游标
        OPEN sql_resoult;
            -- 46.读取游标中数据，存储到指定变量
            FETCH sql_resoult INTO database_name, table_name;
            -- 47.没有结束继续往下走
            WHILE (stopflag=0) DO
                BEGIN
                -- 48.判断数据库是否为输入的数据库名称1，去除已经比较过数据库2的表不存在
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
                    -- 49.调用存储过程getTableCount，查看表是否存在
                    CALL getTableCount(database_2, table_name, resoult_count);
                    -- 50.如果数量等于0，那么表不存在
                    IF (resoult_count = 0) THEN
                        -- 51.把不存在的表记录下来
                        IF (this_info IS NULL OR this_info='') THEN
                            SET this_info=CONCAT(database_2, '的', table_name, '表不存在;\n');
                        ELSE
                            SET this_info=CONCAT(this_info, database_2, '的', table_name, '表不存在;\n');
                        END IF;
                        SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
                    END IF;
                ELSE
                    -- 52.判断数据库是否为输入的数据库名称2，去除已经比较过数据库1的表不存在
                    IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
                        CALL getTableCount(database_1, table_name, resoult_count);
                        IF (resoult_count = 0) THEN
                            IF (this_info IS NULL OR this_info='') THEN
                                SET this_info=CONCAT(database_1, '的', table_name, '表不存在;\n');
                            ELSE
                                SET this_info=CONCAT(this_info, database_1, '的', table_name, '表不存在;\n');
                            END IF;
                            SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
                        END IF;
                    END IF;
                END IF;
                -- 53.读取游标中数据，存储到指定变量。（和46一样）
                FETCH sql_resoult INTO database_name, table_name;
                END;
            END WHILE;
        -- 54.关闭游标
        CLOSE sql_resoult;
    ELSE
        IF (database_count_1 = 0 AND database_count_2 = 0) THEN
            SET this_info = CONCAT(database_1, '和', database_2, '数据库不存在或为空数据库');
        ELSE
            IF (database_count_1 = 0) THEN
                SET this_info = CONCAT(database_1, '数据库不存在或为空数据库');
            ELSE
                SET this_info = CONCAT(database_2, '数据库不存在或为空数据库');
            END IF;
        END IF;
    END IF;
    -- 55.把数据放到传出参数
    SET info=this_info;
    END
-- 56.定义存储过程结束
&
 
 
-- 57.如果存在存储过程comparisonColumnExist则删除
DROP PROCEDURE IF EXISTS `comparisonColumnExist` &
-- 58.定义存储过程，对比列是否存在
-- (传入参数database_1字符串类型，数据库名1；传入参数database_2字符串类型，数据库名2；传入参数info字符串类型，库表信息)
CREATE DEFINER=`oms`@`localhost` PROCEDURE comparisonColumnExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
    BEGIN
    -- 59.声明变量。database_name查询出来的数据库，table_name查询出来的表名，column_name查询出来的列名
    DECLARE database_name, table_name, column_name CHAR(200);
    -- this_info表不存在的记录，database_table_no表不存在的记录跳过重复查询
    DECLARE this_info, database_table_no TEXT DEFAULT '';
    -- database_count_1统计数据库1存在的数量，database_count_2统计数据库2存在的数量，resoult_count统计表存在的数量。如果为0表示不存在
    DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
    -- 60.定义游标结束标识，默认为0
    DECLARE stopflag INT DEFAULT 0;
    -- 61.定义游标，其实就是临时存储sql返回的集合
    DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS t;
    -- 62.游标结束就设置为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
    -- 63.调用存储过程getdatabaseCount，查看两个数据库是否存在，都存在则继续（同44）
    CALL getdatabaseCount(database_1, database_count_1);
    CALL getdatabaseCount(database_2, database_count_2);
    IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
        -- 64.打开游标
        OPEN sql_resoult;
            -- 65.读取游标中数据，存储到指定变量
            FETCH sql_resoult INTO database_name, table_name, column_name;
            -- 66.没有结束继续往下走
            WHILE (stopflag=0) DO
                BEGIN
                -- 67.判断数据库是否为输入的数据库名称1，去除已经比较过数据库2的表不存在（同48）
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
                    -- 68.调用存储过程getTableCount，查看表是否存在（同49）
                    CALL getTableCount(database_2, table_name, resoult_count);
                    -- 69.如果数量不等于0，则继续
                    IF (resoult_count <> 0) THEN
                        -- 70.调用存储过程getColumnCount，查看列是否存在。为0说明不存在
                        CALL getColumnCount(database_2, table_name, column_name, resoult_count);
                        IF (resoult_count = 0) THEN
                            IF (this_info IS NULL OR this_info='') THEN
                                SET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;\n');
                            ELSE
                                SET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;\n');
                            END IF;
                        END IF;
                    ELSE
                        SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
                    END IF;
                ELSE
                    -- 71.判断数据库是否为输入的数据库名称2，去除已经比较过数据库1的表不存在（同52）
                    IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
                        CALL getTableCount(database_1, table_name, resoult_count);
                        IF (resoult_count <> 0) THEN
                            CALL getColumnCount(database_1, table_name, column_name, resoult_count);
                            IF (resoult_count = 0) THEN
                                IF (this_info IS NULL OR this_info='') THEN
                                    SET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;\n');
                                ELSE
                                    SET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;\n');
                                END IF;
                            END IF;
                        ELSE
                            SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
                        END IF;
                    END IF;
                END IF;
                -- 72.读取游标中数据，存储到指定变量。（和65一样）
                FETCH sql_resoult INTO database_name, table_name, column_name;
                END;
            END WHILE;
        -- 73.关闭游标
        CLOSE sql_resoult;
    END IF;
    -- 74.把数据放到传出参数
    SET info=this_info;
    END
-- 75.定义存储过程结束
&
 
 
-- 76.如果存在存储过程comparisonColumnInfo则删除
DROP PROCEDURE IF EXISTS `comparisonColumnInfo` &
-- 77.定义存储过程，对比列的不同
-- (传入参数database_1字符串类型，数据库名1；传入参数database_2字符串类型，数据库名2；传入参数info字符串类型，库表信息)
CREATE DEFINER=`oms`@`localhost` PROCEDURE comparisonColumnInfo(IN database_1 CHAR(20), IN database_2 CHAR(20),OUT info MEDIUMTEXT)
    BEGIN
    -- 78.声明变量。database_name查询出来的数据库，table_name查询出来的表名，column_name查询出来的列名，collation_name查询出来的具体编码类型
    -- result_data_1数据库1的列信息，result_data_2数据库2的列信息，column_info对比的列（现在只比较DATA_TYPE、CHARACTER_SET_NAME）
    DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2, column_info CHAR(200);
    -- this_info表不存在的记录，database_table_no表不存在的记录跳过重复查询
    DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
    -- database_count_1统计数据库1存在的数量，database_count_2统计数据库2存在的数量，resoult_count统计表存在的数量。如果为0表示不存在
    DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
    -- 79.定义游标结束标识，默认为0
    DECLARE stopflag INT DEFAULT 0;
    -- 80.定义游标，其实就是临时存储sql返回的集合
    DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t;
    -- 81.游标结束就设置为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
    -- 82.调用存储过程getdatabaseCount，查看两个数据库是否存在，都存在则继续（同63）
    CALL getdatabaseCount(database_1, database_count_1);
    CALL getdatabaseCount(database_2, database_count_2);
    IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
        -- 83.打开游标
        OPEN sql_resoult;
            -- 84.读取游标中数据，存储到指定变量
            FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
            -- 85.没有结束继续往下走
            WHILE (stopflag=0) DO
                BEGIN
                -- 86.判断数据库是否为输入的数据库名称1，去除已经比较过数据库2的表不存在（同67）
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
                    -- 87.调用存储过程getTableCount，查看表是否存在（同68）
                    CALL getTableCount(database_2, table_name, resoult_count);
                    -- 88.如果数量不等于0，则继续
                    IF (resoult_count <> 0) THEN
                        -- 89.调用存储过程getColumnCount，查看列是否存在。为0说明不存在（同70）
                        CALL getColumnCount(database_2, table_name, column_name, resoult_count);
                        IF (resoult_count <> 0) THEN
                            -- 90.对比DATA_TYPE是否相同
                            SET column_info = 'DATA_TYPE';
                            CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
                            CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
                            IF (result_data_1 <> result_data_2) THEN
                                IF (this_info IS NULL OR this_info='') THEN
                                    SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n');
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');
                                END IF;
                            END IF;
                            -- 91.对比CHARACTER_SET_NAME是否相同
                            SET column_info = 'CHARACTER_SET_NAME';
                            CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
                            CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
                            IF (result_data_1 <> result_data_2) THEN
                                IF (this_info IS NULL OR this_info='') THEN
                                    SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n');
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');
                                END IF;
                            END IF;
                        END IF;
                    ELSE
                        SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
                    END IF;
                ELSE
                    IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
                        CALL getTableCount(database_1, table_name, resoult_count);
                        IF (resoult_count = 0) THEN
                            SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
                        END IF;
                    END IF;
                END IF;
                -- 92.读取游标中数据，存储到指定变量。（和84一样）
                FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
                END;
            END WHILE;
        -- 93.关闭游标
        CLOSE sql_resoult;
    END IF;
    -- 94.把数据放到传出参数
    SET info=this_info;
    END
-- 95.定义存储过程结束
&
-- 96.将mysql分隔符从&设置为;
DELIMITER ;
-- 97.设置变量
SET @database_1='test_common';
SET @database_2='soc_common';
SET @tableExistInfo='';
SET @columnExistInfo='';
SET @columnInfo='';
-- 98.调用存储过程
CALL comparisonTableExist(@database_1, @database_2, @tableExistInfo);
CALL comparisonColumnExist(@database_1, @database_2, @columnExistInfo);
CALL comparisonColumnInfo(@database_1, @database_2, @columnInfo);
SET @info=CONCAT(@tableExistInfo, '\n', @columnExistInfo, '\n', @columnInfo);
-- 99.打印
SELECT @info;
-- 100.如果存在存储过程则删除
DROP PROCEDURE IF EXISTS `comparisonColumnInfo`;
DROP PROCEDURE IF EXISTS `comparisonColumnExist`;
DROP PROCEDURE IF EXISTS `comparisonTableExist`;
DROP PROCEDURE IF EXISTS `getColumnInfo`;
DROP PROCEDURE IF EXISTS `getColumnCount`;
DROP PROCEDURE IF EXISTS `getTableCount`;
DROP PROCEDURE IF EXISTS `getdatabaseCount`;

　　无注释：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

DELIMITER &
 
DROP PROCEDURE IF EXISTS `getdatabaseCount` &
CREATE DEFINER=`oms`@`localhost` PROCEDURE getdatabaseCount(IN database_name CHAR(20), OUT count_date INT)
    BEGIN
    DECLARE $sqltext VARCHAR(1000);
    SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\';');
    SET @sqlcounts := $sqltext;
    PREPARE stmt FROM @sqlcounts;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET count_date = @count_date;
    END
&
 
DROP PROCEDURE IF EXISTS `getTableCount` &
CREATE DEFINER=`oms`@`localhost` PROCEDURE getTableCount(IN database_name CHAR(20), IN table_name CHAR(200), OUT count_date INT)
    BEGIN
    DECLARE $sqltext VARCHAR(1000);
    SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\';');
    SET @sqlcounts := $sqltext;
    PREPARE stmt FROM @sqlcounts;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET count_date = @count_date;
    END
&
 
 
DROP PROCEDURE IF EXISTS `getColumnCount` &
CREATE DEFINER=`oms`@`localhost` PROCEDURE getColumnCount(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), OUT count_date INT)
    BEGIN
    DECLARE $sqltext VARCHAR(1000);
    SET $sqltext = CONCAT('SELECT COUNT(*) into @count_date FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
    SET @sqlcounts := $sqltext;
    PREPARE stmt FROM @sqlcounts;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET count_date = @count_date;
    END
&
 
 
DROP PROCEDURE IF EXISTS `getColumnInfo` &
CREATE DEFINER=`oms`@`localhost` PROCEDURE getColumnInfo(IN database_name CHAR(20), IN table_name CHAR(200), IN column_name CHAR(200), IN column_info CHAR(50), OUT result_data CHAR(20))
    BEGIN
    DECLARE $sqltext VARCHAR(1000);
    SET $sqltext = CONCAT('SELECT t.', column_info,' into @column_info FROM information_schema.COLUMNS t where t.`TABLE_SCHEMA` = \'', database_name, '\' and t.`TABLE_NAME` = \'', table_name, '\' and t.`COLUMN_NAME` = \'', column_name, '\';');
    SET @sqlcounts := $sqltext;
    PREPARE stmt FROM @sqlcounts;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET result_data = @column_info;
    END
&
 
DROP PROCEDURE IF EXISTS `comparisonTableExist` &
CREATE DEFINER=`oms`@`localhost` PROCEDURE comparisonTableExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
    BEGIN
    DECLARE database_name, table_name CHAR(200);
    DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
    DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
    DECLARE stopflag INT DEFAULT 0;
    DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.COLUMNS t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
    CALL getdatabaseCount(database_1, database_count_1);
    CALL getdatabaseCount(database_2, database_count_2);
    IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
        OPEN sql_resoult;
            FETCH sql_resoult INTO database_name, table_name;
            WHILE (stopflag=0) DO
                BEGIN
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
                    CALL getTableCount(database_2, table_name, resoult_count);
                    IF (resoult_count = 0) THEN
                        IF (this_info IS NULL OR this_info='') THEN
                            SET this_info=CONCAT(database_2, '的', table_name, '表不存在;\n');
                        ELSE
                            SET this_info=CONCAT(this_info, database_2, '的', table_name, '表不存在;\n');
                        END IF;
                        SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
                    END IF;
                ELSE
                    IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
                        CALL getTableCount(database_1, table_name, resoult_count);
                        IF (resoult_count = 0) THEN
                            IF (this_info IS NULL OR this_info='') THEN
                                SET this_info=CONCAT(database_1, '的', table_name, '表不存在;\n');
                            ELSE
                                SET this_info=CONCAT(this_info, database_1, '的', table_name, '表不存在;\n');
                            END IF;
                            SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
                        END IF;
                    END IF;
                END IF;
                FETCH sql_resoult INTO database_name, table_name;
                END;
            END WHILE;
        CLOSE sql_resoult;
    ELSE
        IF (database_count_1 = 0 AND database_count_2 = 0) THEN
            SET this_info = CONCAT(database_1, '和', database_2, '数据库不存在或为空数据库');
        ELSE
            IF (database_count_1 = 0) THEN
                SET this_info = CONCAT(database_1, '数据库不存在或为空数据库');
            ELSE
                SET this_info = CONCAT(database_2, '数据库不存在或为空数据库');
            END IF;
        END IF;
    END IF;
    SET info=this_info;
    END
&
 
 
DROP PROCEDURE IF EXISTS `comparisonColumnExist` &
CREATE DEFINER=`oms`@`localhost` PROCEDURE comparisonColumnExist(IN database_1 CHAR(20), IN database_2 CHAR(20), OUT info MEDIUMTEXT)
    BEGIN
    DECLARE database_name, table_name, column_name CHAR(200);
    DECLARE this_info, database_table_no TEXT DEFAULT '';
    DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
    DECLARE stopflag INT DEFAULT 0;
    DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
    CALL getdatabaseCount(database_1, database_count_1);
    CALL getdatabaseCount(database_2, database_count_2);
    IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
        OPEN sql_resoult;
            FETCH sql_resoult INTO database_name, table_name, column_name;
            WHILE (stopflag=0) DO
                BEGIN
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
                    CALL getTableCount(database_2, table_name, resoult_count);
                    IF (resoult_count <> 0) THEN
                        CALL getColumnCount(database_2, table_name, column_name, resoult_count);
                        IF (resoult_count = 0) THEN
                            IF (this_info IS NULL OR this_info='') THEN
                                SET this_info=CONCAT(database_2, '的', table_name, '表的', column_name, '列不存在;\n');
                            ELSE
                                SET this_info=CONCAT(this_info, database_2, '的', table_name, '表的', column_name, '列不存在;\n');
                            END IF;
                        END IF;
                    ELSE
                        SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
                    END IF;
                ELSE
                    IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
                        CALL getTableCount(database_1, table_name, resoult_count);
                        IF (resoult_count <> 0) THEN
                            CALL getColumnCount(database_1, table_name, column_name, resoult_count);
                            IF (resoult_count = 0) THEN
                                IF (this_info IS NULL OR this_info='') THEN
                                    SET this_info=CONCAT(database_1, '的', table_name, '表的', column_name, '列不存在;\n');
                                ELSE
                                    SET this_info=CONCAT(this_info, database_1, '的', table_name, '表的', column_name, '列不存在;\n');
                                END IF;
                            END IF;
                        ELSE
                            SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
                        END IF;
                    END IF;
                END IF;
                FETCH sql_resoult INTO database_name, table_name, column_name;
                END;
            END WHILE;
        CLOSE sql_resoult;
    END IF;
    SET info=this_info;
    END
&
 
 
DROP PROCEDURE IF EXISTS `comparisonColumnInfo` &
CREATE DEFINER=`oms`@`localhost` PROCEDURE comparisonColumnInfo(IN database_1 CHAR(20), IN database_2 CHAR(20),OUT info MEDIUMTEXT)
    BEGIN
    DECLARE database_name, table_name, column_name, collation_name, result_data_1, result_data_2, column_info CHAR(200);
    DECLARE this_info, database_table_no MEDIUMTEXT DEFAULT '';
    DECLARE database_count_1, database_count_2, resoult_count INT DEFAULT 0;
    DECLARE stopflag INT DEFAULT 0;
    DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME, t.COLLATION_NAME FROM information_schema.COLUMNS t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
    CALL getdatabaseCount(database_1, database_count_1);
    CALL getdatabaseCount(database_2, database_count_2);
    IF (database_count_1 <> 0 AND database_count_2 <> 0) THEN
        OPEN sql_resoult;
            FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
            WHILE (stopflag=0) DO
                BEGIN
                IF (database_name=database_1 AND INSTR(database_table_no, CONCAT(database_2, '_', table_name)) = 0) THEN
                    CALL getTableCount(database_2, table_name, resoult_count);
                    IF (resoult_count <> 0) THEN
                        CALL getColumnCount(database_2, table_name, column_name, resoult_count);
                        IF (resoult_count <> 0) THEN
                            SET column_info = 'DATA_TYPE';
                            CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
                            CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
                            IF (result_data_1 <> result_data_2) THEN
                                IF (this_info IS NULL OR this_info='') THEN
                                    SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n');
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');
                                END IF;
                            END IF;
                            SET column_info = 'CHARACTER_SET_NAME';
                            CALL getColumnInfo(database_1, table_name, column_name, column_info, result_data_1);
                            CALL getColumnInfo(database_2, table_name, column_name, column_info, result_data_2);
                            IF (result_data_1 <> result_data_2) THEN
                                IF (this_info IS NULL OR this_info='') THEN
                                    SET this_info=CONCAT(table_name, '表的', column_name, '列的', column_info, '不一样;\n');
                                ELSE
                                    SET this_info=CONCAT(this_info, table_name, '表的', column_name, '列的', column_info, '不一样;\n');
                                END IF;
                            END IF;
                        END IF;
                    ELSE
                        SET database_table_no=CONCAT(database_table_no, ';', database_2, '_', table_name, ';');
                    END IF;
                ELSE
                    IF (database_name=database_2 AND INSTR(database_table_no, CONCAT(database_1, '_', table_name)) = 0) THEN
                        CALL getTableCount(database_1, table_name, resoult_count);
                        IF (resoult_count = 0) THEN
                            SET database_table_no=CONCAT(database_table_no, ';', database_1, '_', table_name, ';');
                        END IF;
                    END IF;
                END IF;
                FETCH sql_resoult INTO database_name, table_name, column_name, collation_name;
                END;
            END WHILE;
        CLOSE sql_resoult;
    END IF;
    SET info=this_info;
    END
&
DELIMITER ;
SET @database_1='test_common';
SET @database_2='soc_common';
SET @tableExistInfo='';
SET @columnExistInfo='';
SET @columnInfo='';
CALL comparisonTableExist(@database_1, @database_2, @tableExistInfo);
CALL comparisonColumnExist(@database_1, @database_2, @columnExistInfo);
CALL comparisonColumnInfo(@database_1, @database_2, @columnInfo);
SET @info=CONCAT(@tableExistInfo, '\n', @columnExistInfo, '\n', @columnInfo);
SELECT @info;
DROP PROCEDURE IF EXISTS `comparisonColumnInfo`;
DROP PROCEDURE IF EXISTS `comparisonColumnExist`;
DROP PROCEDURE IF EXISTS `comparisonTableExist`;
DROP PROCEDURE IF EXISTS `getColumnInfo`;
DROP PROCEDURE IF EXISTS `getColumnCount`;
DROP PROCEDURE IF EXISTS `getTableCount`;
DROP PROCEDURE IF EXISTS `getdatabaseCount`;