def get_user_by_id(user_id):
conn = get_db_connection()
try:
with conn.cursor() as cursor:
sql = "SELECT id, username, email, created_at FROM users WHERE id = %s"
cursor.execute(sql, (user_id,))
result = cursor.fetchone()
return result # 返回字典或None
finally:
conn.close()
一次性获取所有结果,适用于小数据集:
def get_all_users():
conn = get_db_connection()
try:
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM users")
return cursor.fetchall() # 返回列表[dict, ...]
finally:
conn.close()
def update_user_email(user_id, new_email):
conn = get_db_connection()
try:
with conn.cursor() as cursor:
sql = "UPDATE users SET email = %s WHERE id = %s"
affected = cursor.execute(sql, (new_email, user_id))
conn.commit()
return affected > 0 # 返回是否更新成功
except MySQLdb.Error as e:
conn.rollback()
print(f"更新失败: {e}")
raise
finally:
conn.close()
删除操作需谨慎验证条件:
def delete_user(user_id):
conn = get_db_connection()
try:
with conn.cursor() as cursor:
# 先检查是否存在
cursor.execute("SELECT id FROM users WHERE id = %s", (user_id,))
if not cursor.fetchone():
return False
cursor.execute("DELETE FROM users WHERE id = %s", (user_id,))
conn.commit()
return True
except MySQLdb.Error as e:
conn.rollback()
print(f"删除失败: {e}")
raise
finally:
conn.close()
生产环境建议使用逻辑删除(添加is_deleted字段)而非物理删除,避免数据不可恢复。
事务处理最佳实践
事务确保操作原子性,关键点在于正确使用try/except/finally:
def transfer_funds(from_id, to_id, amount):
conn = get_db_connection()
try:
with conn.cursor() as cursor:
# 检查余额
cursor.execute("SELECT balance FROM accounts WHERE id = %s", (from_id,))
result = cursor.fetchone()
if not result or result['balance'] < amount:
raise ValueError("余额不足")
# 扣款
cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_id))
# 加款
cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_id))
conn.commit()
return True
except (MySQLdb.Error, ValueError) as e:
conn.rollback()
print(f"转账失败: {e}")
raise
finally:
conn.close()
def bulk_update_users():
batch_size = 1000
last_id = 0
while True:
conn = get_db_connection()
try:
with conn.cursor() as cursor:
# 获取本批数据
cursor.execute(
"SELECT id, email FROM users WHERE id > %s ORDER BY id LIMIT %s",
(last_id, batch_size)
)
users = cursor.fetchall()
if not users:
break
# 批量更新
for user in users:
if '@' not in user['email']:
cursor.execute(
"UPDATE users SET email = %s WHERE id = %s",
(f"{user['id']}@default.com", user['id'])
)
conn.commit()
last_id = users[-1]['id']
finally:
conn.close()
优化技巧:1) 使用WHERE id > last_id实现无锁扫描;2) 每批提交减少锁持有时间;3) 添加LIMIT避免单次操作过大。
# 危险!用户输入直接拼接
keyword = request.args.get('q', '')
sql = f"SELECT * FROM products WHERE name LIKE '%{keyword}%'"
安全实现:
keyword = request.args.get('q', '')
sql = "SELECT * FROM products WHERE name LIKE %s"
cursor.execute(sql, (f"%{keyword}%",))
场景2:动态排序字段注入
问题:ORDER BY子句无法使用参数化
# 错误:直接拼接
order = request.args.get('sort', 'id')
sql = f"SELECT * FROM users ORDER BY {order} DESC"
安全方案:白名单校验
order = request.args.get('sort', 'id')
allowed_columns = ['id', 'username', 'created_at']
if order not in allowed_columns:
order = 'id'
sql = f"SELECT * FROM users ORDER BY {order} DESC"
场景3:IN子句注入
问题:IN列表参数数量不固定
# 错误:直接拼接
ids = request.args.get('ids', '1,2,3')
sql = f"SELECT * FROM users WHERE id IN ({ids})"
安全方案:动态占位符
ids = request.args.get('ids', '1,2,3').split(',')
ids = [int(x) for x in ids if x.isdigit()]
placeholders = ','.join(['%s'] * len(ids))
sql = f"SELECT * FROM users WHERE id IN ({placeholders})"
cursor.execute(sql, ids)
CREATE INDEX idx_name ON users (name);
-- 查询:WHERE name LIKE '%john%' 无法使用索引
原因:前导通配符导致索引失效
正确索引设计
-- 方案1:避免前导通配符
WHERE name LIKE 'john%' -- 可用索引
-- 方案2:全文索引(MySQL 5.6+)
ALTER TABLE users ADD FULLTEXT idx_name (name);
WHERE MATCH(name) AGAINST('john');
复合索引最佳实践
CREATE INDEX idx_user_status ON users (status, created_at);
-- 高效查询:WHERE status = 'active' AND created_at > '2023-01-01'
-- 效率低下:WHERE created_at > '2023-01-01' -- 违反最左前缀原则
执行计划分析
使用EXPLAIN分析查询性能:
EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
# 生成CSV文件
with open('users.csv', 'w') as f:
for user in users:
f.write(f"{user['id']},{user['username']},{user['email']}\n")
# 导入数据
cursor.execute("""
LOAD DATA INFILE '/path/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, username, email)
""")
Q2:连接时出现“Access denied for user 'root'@'localhost'”?
常见原因与修复方案:
• 密码错误:检查mysql.user表中对应用户的密码
• 用户权限不足:执行GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
• MySQL 5.7+默认使用auth_socket插件:修改用户认证方式
修复步骤:
# 以安全模式登录MySQL
sudo mysqld_safe --skip-grant-tables &
# 修改root认证方式
mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
FLUSH PRIVILEGES;
conn = MySQLdb.connect(autocommit=False)
try:
with conn.cursor() as cursor:
cursor.execute("UPDATE accounts SET balance=balance-100 WHERE id=1")
cursor.execute("UPDATE accounts SET balance=balance+100 WHERE id=2")
conn.commit() # 关键步骤
finally:
conn.close()
Q4:如何处理“MySQL server has gone away”错误?
常见原因与解决方案:
原因
解决方案
连接超时(wait_timeout)
设置connect_timeout=10,连接前执行SELECT 1测试
数据包过大(max_allowed_packet)
增加MySQL配置max_allowed_packet=64M
长事务占用连接
拆分事务,避免长事务操作
连接健康检查代码:
def check_connection(conn):
try:
with conn.cursor() as cursor:
cursor.execute("SELECT 1")
return True
except MySQLdb.OperationalError:
return False
Q5:如何安全处理用户输入的SQL片段?
严格禁止直接拼接,必须使用白名单校验:
# 错误做法
order = request.GET.get('order', 'id')
sql = f"SELECT * FROM products ORDER BY {order}"
# 正确做法
allowed = ['id', 'name', 'price', 'created_at']
order = request.GET.get('order', 'id')
if order not in allowed:
order = 'id'
sql = f"SELECT * FROM products ORDER BY {order}"
对于WHERE条件中的动态值,必须使用参数化查询:
# 安全处理
status = request.GET.get('status', 'active')
if status not in ['active', 'inactive', 'pending']:
status = 'active'
cursor.execute("SELECT * FROM products WHERE status = %s", (status,))
📌 实用工具推荐:1) 使用mysqltuner.pl分析MySQL配置;2) 开发环境使用pt-query-digest分析慢查询;3) 生产环境部署Percona Monitoring and Management监控系统。