Skip to content

MySQL 连接池(极简版) ​

cpp
// 固定连接数量的 MySQL 连接池
#include<mysql/mysql.h>
#include<atomic>
#include<chrono>
#include<condition_variable>
#include<memory>
#include<mutex>
#include<queue>
#include<string>

class Connection
{
public:
    Connection() = default;

    ~Connection()
    {
        close();
    }

    // 一条连接同一时刻只能被一个线程使用,禁止拷贝
    Connection(const Connection&) = delete;
    Connection& operator=(const Connection&) = delete;

    bool connect(const std::string& ip, int port, const std::string& user,
                 const std::string& password, const std::string& dbname)
    {
        close();                        // 重连前先释放旧句柄
        conn_ = mysql_init(nullptr);

        if (!mysql_real_connect(conn_, ip.c_str(), user.c_str(), password.c_str(),
                                dbname.c_str(), port, nullptr, 0))
        {
            close();                    // 失败也要释放,否则内存泄漏
            return false;
        }
        return true;
    }

    // 增删改(任意非 SELECT 语句)
    bool update(const std::string& sql)
    {
        if (mysql_query(conn_, sql.c_str()) != 0)
        {
            return false;
        }
        return true;
    }

    // 查询:结果已用 mysql_store_result 拷到客户端,连接归还后结果集依然有效
    // 调用方负责 mysql_free_result
    MYSQL_RES* query(const std::string& sql)
    {
        if (mysql_query(conn_, sql.c_str()) != 0)
        {
            return nullptr;
        }
        return mysql_store_result(conn_);
    }

    // 探活:连接被服务端 wait_timeout 断开时返回 false
    bool ping()
    {
        return conn_ && mysql_ping(conn_) == 0;
    }

    void close()
    {
        if (conn_)
        {
            mysql_close(conn_);
            conn_ = nullptr;
        }
    }

private:
    MYSQL* conn_ = nullptr;
};

class ConnectionPool
{
public:
    ~ConnectionPool()
    {
        {
            std::lock_guard<std::mutex> lock(mtx_);
            isRunning_ = false;
        }
        cv_.notify_all();             // 唤醒所有等连接的线程,让它们拿到 nullptr

        std::lock_guard<std::mutex> lock(mtx_);
        while (!connectionQue_.empty())
        {
            delete connectionQue_.front();
            connectionQue_.pop();
        }
    }

    ConnectionPool(const ConnectionPool&) = delete;
    ConnectionPool& operator=(const ConnectionPool&) = delete;

    static ConnectionPool& getConnectionPool()
    {
        static ConnectionPool instance;   // 函数内 static:懒加载 + 线程安全
        return instance;
    }

    // 初始化:
    bool init(int initnum, const std::string& ip, int port,
              const std::string& username, const std::string& password,
              const std::string& dbname)
    {
        if (initnum <= 0)
            return false;

        std::lock_guard<std::mutex> lock(mtx_);
        if (isRunning_)
            return true;              // 幂等:已初始化直接返回

        initnum_ = initnum;
        ip_ = ip;
        port_ = port;
        username_ = username;
        password_ = password;
        dbname_ = dbname;

        for (int i = 0; i < initnum_; i++)
        {
            auto* conn = new Connection();
            if (!conn->connect(ip_, port_, username_, password_, dbname_))
            {
                delete conn;
                while (!connectionQue_.empty())
                {
                    delete connectionQue_.front();
                    connectionQue_.pop();
                }
                return false;
            }
            connectionQue_.push(conn);
        }

        isRunning_ = true;
        return true;
    }

    // 取连接:池空时阻塞等待,超时或池已停止返回 nullptr
    std::shared_ptr<Connection> getConnection(int timeout_ms = 3000)
    {
        Connection* conn = nullptr;
        {
            std::unique_lock<std::mutex> lock(mtx_);
            // 带超时等待,避免连接耗尽时调用线程永久阻塞
            if (!cv_.wait_for(lock, std::chrono::milliseconds(timeout_ms),
                              [this] { return !isRunning_ || !connectionQue_.empty(); }))
                return nullptr;
            if (!isRunning_)
                return nullptr;

            conn = connectionQue_.front();
            connectionQue_.pop();
        }

        // 探活:空闲连接可能已被服务端 wait_timeout 断开,取出时先 ping 一次
        if (!conn->ping())
        {
            conn->close();
            if (!conn->connect(ip_, port_, username_, password_, dbname_))
            {
                returnConnection(conn);   // 放回池中,固定数量的槽位不能少一个
                return nullptr;
            }
        }

        // 关键:自定义删除器,shared_ptr 析构时自动把连接还回池中
        return std::shared_ptr<Connection>(
            conn, [this](Connection* c) { returnConnection(c); });
    }

    // 归还连接
    void returnConnection(Connection* conn)
    {
        {
            std::lock_guard<std::mutex> lock(mtx_);
            if (!isRunning_)
            {
                delete conn;          // 池已停止,由最后一个归还者负责释放
                return;
            }
            connectionQue_.push(conn);
        }
        cv_.notify_one();             // 唤醒一个等待者
    }

private:
    ConnectionPool() = default;

    std::string ip_;
    int port_ = 0;
    std::string username_;
    std::string password_;
    std::string dbname_;
    int initnum_ = 0;

    std::atomic_bool isRunning_{false};
    std::queue<Connection*> connectionQue_;

    std::mutex mtx_;
    std::condition_variable cv_;              // 取连接 / 停止时唤醒等待者
};

/*  编译:
    g++ -std=c++17 -pthread "4. MySQL连接池设计(极简).cpp" -o cp $(mysql_config --libs)

int main()
{
    auto& pool = ConnectionPool::getConnectionPool();
    if (!pool.init(4, "127.0.0.1", 3306, "root", "123456", "test"))
    {
        printf("init pool failed\n");
        return -1;
    }

    auto conn = pool.getConnection();          // 离开作用域自动归还
    if (!conn)
        return -1;

    if (MYSQL_RES* res = conn->query("SELECT 1"))
    {
        while (MYSQL_ROW row = mysql_fetch_row(res))
            printf("%s\n", row[0]);
        mysql_free_result(res);
    }
    return 0;
}
*/