很多次项目上线都会遗漏掉数据库的修改,因为本地开发好几个人做,开发环境都是在本地,项目是在云上跑的,总有人记不住自己本地修改了哪些表,导致每次上线完发现有问题,回过来查才发现是数据库忘记update了
需求:两个mysql数据库对比表结构,输出差异部分,用于上线前数据库修改
后端代码:
app.py
from flask import Flask, render_template, request
import mysql.connector
app = Flask(__name__)
# 配置MySQL连接信息
db1_config = {
'host': 'host1',
'user': 'user1',
'password': 'password1',
'database': 'database1',
'port': 'port1'
}
db2_config = {
'host': 'host2',
'user': 'user2',
'password': 'password2',
'database': 'database2',
'port': 'port2'
}
# 连接数据库并获取表结构信息
def get_table_structure(config):
conn = mysql.connector.connect(**config)
cursor = conn.cursor()
cursor.execute("SHOW TABLES")
tables = [table[0] for table in cursor.fetchall()]
table_structure = {}
for table in tables:
cursor.execute(f"SHOW CREATE TABLE {table}")
table_info = cursor.fetchone()
table_structure[table] = table_info[1]
cursor.close()
conn.close()
return table_structure
# 对比两个数据库的表结构差异
def compare_table_structure(db1_structure, db2_structure):
diff_result = {}
for table, structure in db1_structure.items():
if table in db2_structure:
if structure != db2_structure[table]:
diff_result[table] = {
'db1_structure': structure,
'db2_structure': db2_structure[table]
}
else:
diff_result[table] = {
'db1_structure': structure,
'db2_structure': 'Table not exists in db2'
}
for table, structure in db2_structure.items():
if table not in db1_structure:
diff_result[table] = {
'db1_structure': 'Table not exists in db1',
'db2_structure': structure
}
return diff_result
@app.route('/')
def index():
return render_template('index.html')
# 处理对比请求并展示结果页面
@app.route('/compare', methods=['POST'])
def compare():
db1_config['host'] = request.form['db1_host']
db1_config['user'] = request.form['db1_user']
db1_config['password'] = request.form['db1_password']
db1_config['database'] = request.form['db1_database']
db1_config['port'] = request.form['db1_port']
db2_config['host'] = request.form['db2_host']
db2_config['user'] = request.form['db2_user']
db2_config['password'] = request.form['db2_password']
db2_config['database'] = request.form['db2_database']
db2_config['port'] = request.form['db2_port']
db1_structure = get_table_structure(db1_config)
db2_structure = get_table_structure(db2_config)
diff_result = compare_table_structure(db1_structure, db2_structure)
return render_template('result.html', diff_result=diff_result)
if __name__ == '__main__':
app.run(host='0.0.0.0', port=3000, debug=True)
前端需要两个页面,在app.py根目录下创建templates目录和两个文件result.html index.html 分别用于数据库连接参数传递和差异结果展示
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>MySQL Table Structure Compare</title>
<style>
body {
font-family: Arial, sans-serif;
background-color: #f0f0f0;
margin: 0;
padding: 0;
}
.container {
max-width: 800px;
margin: 20px auto;
padding: 20px;
background-color: #fff;
border-radius: 5px;
box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
}
h1, h2 {
color: #333;
}
form {
margin-top: 20px;
}
input[type="text"],
input[type="password"] {
width: calc(100% - 20px);
padding: 10px;
margin-bottom: 10px;
border: 1px solid #ccc;
border-radius: 5px;
}
input[type="submit"] {
background-color: #4CAF50;
color: #fff;
padding: 10px 20px;
border: none;
border-radius: 5px;
cursor: pointer;
}
input[type="submit"]:hover {
background-color: #45a049;
}
</style>
</head>
<body>
<div class="container">
<h1>Enter Database Connection Information</h1>
<form action="/compare" method="post">
<h2>Database 1:</h2>
<input type="text" name="db1_host" placeholder="Host" required><br>
<input type="text" name="db1_user" placeholder="User" required><br>
<input type="password" name="db1_password" placeholder="Password" required><br>
<input type="text" name="db1_database" placeholder="Database" required><br>
<input type="text" name="db1_port" placeholder="Port" required><br>
<h2>Database 2:</h2>
<input type="text" name="db2_host" placeholder="Host" required><br>
<input type="text" name="db2_user" placeholder="User" required><br>
<input type="password" name="db2_password" placeholder="Password" required><br>
<input type="text" name="db2_database" placeholder="Database" required><br>
<input type="text" name="db2_port" placeholder="Port" required><br>
<input type="submit" value="Compare">
</form>
</div>
</body>
</html>
result.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Comparison Result</title>
<style>
body {
font-family: Arial, sans-serif;
background-color: #f0f0f0;
margin: 0;
padding: 20px;
}
.container {
max-width: 1200px;
margin: 20px auto;
background-color: #fff;
border-radius: 5px;
box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
overflow: hidden;
}
h1 {
color: #333;
text-align: center;
padding: 20px 0;
}
table {
width: 100%;
border-collapse: collapse;
}
th, td {
border: 1px solid #ccc;
padding: 10px;
text-align: left;
}
th {
background-color: #f2f2f2;
}
tr:nth-child(even) {
background-color: #f9f9f9;
}
tr:hover {
background-color: #e0e0e0;
}
</style>
</head>
<body>
<div class="container">
<h1>Table Structure Comparison Result</h1>
<table>
<thead>
<tr>
<th>Table Name</th>
<th>Database 1 Structure</th>
<th>Database 2 Structure</th>
</tr>
</thead>
<tbody>
{% for table, structures in diff_result.items() %}
<tr>
<td>{{ table }}</td>
<td>{{ structures['db1_structure'] }}</td>
<td>{{ structures['db2_structure'] }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</body>
</html>
效果展示:
运行app.sh,本地访问http://127.0.0.1:3000