驅動選型對比
| 驅動 | 類型 | 效能 | 相容性 | 推薦場景 |
| mysqlclient | C 擴展 (MySQL C API) | ⭐⭐⭐⭐⭐ | Django 官方推薦 | 高效能、Django 專案、生產環境 |
| PyMySQL | 純 Python | ⭐⭐⭐ | 純 Python 環境、無編譯工具鏈 | 開發測試、容器構建、無法編譯 C 擴展 |
| mysql-connector-python | 官方純 Python / C 擴展 | ⭐⭐⭐ | 官方維護 | 需官方支援的企業環境 |
| asyncmy | C 擴展 + AsyncIO | ⭐⭐⭐⭐⭐ | Python 3.7+ | FastAPI、Starlette 異步框架 |
安裝實戰
mysqlclient(推薦生產)
# Ubuntu/Debian
apt-get install -y default-libmysqlclient-dev build-essential pkg-config
pip install mysqlclient
# CentOS/RHEL
yum install -y mysql-devel gcc gcc-c++ python3-devel
pip install mysqlclient
# macOS
brew install mysql-client pkg-config
export PATH="/usr/local/opt/mysql-client/bin:$PATH"
pip install mysqlclient
# Windows
pip install mysqlclient # 提供預編譯 wheel
PyMySQL(無編譯環境)
pip install pymysql
# 兼容 MySQLdb API
import pymysql
pymysql.install_as_MySQLdb()
SQLAlchemy + mysqlclient(ORM 標準)
pip install sqlalchemy mysqlclient
# 連線字串
DATABASE_URL = "mysql+mysqldb://user:pass@host:3306/db?charset=utf8mb4"
engine = create_engine(DATABASE_URL, pool_pre_ping=True, pool_recycle=3600)
常見安裝錯誤與解決
| 錯誤 | 原因 | 解決 |
| mysql_config not found |
缺少 MySQL 開發庫 |
安裝 libmysqlclient-dev / mysql-devel |
| library not found: libssl / libcrypto |
OpenSSL 開發庫缺失 |
安裝 libssl-dev / openssl-devel |
| ImportError: libmysqlclient.so.21: cannot open shared object file |
運行時找不到共享庫 |
echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf && ldconfig |
| Authentication plugin 'caching_sha2_password' not supported |
驅動版本過舊 |
升級 mysqlclient≥2.0 / PyMySQL≥1.0,或改用 mysql_native_password |
連線池與最佳實踐
原生連線池
import mysql.connector.pooling
cnxpool = mysql.connector.pooling.MySQLConnectionPool(
pool_name="mypool",
pool_size=10,
host="localhost",
user="app",
password="pass",
database="mydb",
charset="utf8mb4",
autocommit=True
)
cnx = cnxpool.get_connection()
SQLAlchemy 引擎池
engine = create_engine(
DATABASE_URL,
pool_size=20,
max_overflow=10,
pool_pre_ping=True, # 檢測斷開連線
pool_recycle=3600, # 1小時回收,防 MySQL wait_timeout 斷開
pool_timeout=30
)
CRUD 範例
# mysqlclient 參數化查詢(防注入)
conn = MySQLdb.connect(host='localhost', user='app', passwd='pass', db='mydb', charset='utf8mb4')
cur = conn.cursor()
cur.execute("SELECT id, name FROM users WHERE email = %s AND status = %s", ('test@example.com', 'active'))
rows = cur.fetchall()
for row in rows:
print(row)
# 批次插入
cur.executemany("INSERT INTO logs (level, message) VALUES (%s, %s)",
[('INFO', 'start'), ('ERROR', 'failed')])
conn.commit()
異步操作
# asyncmy + asyncio
import asyncmy
pool = await asyncmy.create_pool(host='localhost', user='app', password='pass', db='mydb', minsize=5, maxsize=20)
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT 1")
result = await cur.fetchall()
總結
生產環境首選 mysqlclient + SQLAlchemy**,效能最佳、生態成熟。無編譯環境用 PyMySQL。異步框架用 asyncmy。關鍵配置:charset=utf8mb4、pool_pre_ping=True、pool_recycle 防斷連。安裝失敗 90% 因缺少 MySQL 開發庫與 OpenSSL 庫,補齊即可。