MySQL连接池C语言实现（c mysql 有连接池）

MySQL连接池：C语言实现

在数据库应用中，连接池是一种可使多个客户端共享数据库连接的技术手段，其主要作用是减少了重复连接数据库和断开连接的次数，从而减少了数据库的负担，提升了系统性能和响应速度。本文将介绍一个基于C语言实现的MySQL连接池。

实现步骤：

1. 需要定义一些宏，并且引入MySQL C API库头文件。代码如下：

#include 
#include 
#include 
#include 
 
#define HOST "localhost"   // MySQL server的ip地址
#define USER "root"        // 登录MySQL server的用户名
#define PASSWORD "password"   // 登录MySQL server的密码
#define PORT 3306         // MySQL server的端口号
#define DATABASE "testdb"  // 操作的数据库名称

2. 接下来，需要声明连接池的数据结构，包括连接池大小、已经分配的连接数、使用的连接数、可用连接池数组等。代码如下：

typedef struct MYSQL_POOL_MEMBER {
    MYSQL *conn;      // MYSQL连接结构体
    int used;         // 连接是否使用(0:没使用, 1:使用)
} MysqlPoolMember;
 
typedef struct MYSQL_POOL {
    MysqlPoolMember *pool;     // MySQL连接池数组
    int pool_size;             // 连接池大小
    int pool_current;          // 当前连接池大小
    int pool_max_use_count;    // 连接池最大使用的次数
} MysqlPool;

3. 实现连接池初始化操作。该操作需要初始化MySQL连接，将创建连接的SQL语句，将连接放入连接池中，将连接池中的当前容量设为1。代码如下：

MysqlPool * create_mysql_pool(int pool_size, int pool_max_use_count)
{
    MYSQL *conn;
    MysqlPoolMember *member;
    int i;
    MysqlPool *pool;
    char sql[100];
 
    //1. Allocate memory for pool structure.
    pool = (MysqlPool*) malloc(sizeof (MysqlPool));
 
    //2. Allocate memory for database connection structure for pool 
    //    initialized from the value provided with pool_size parameter
    pool->pool = (MysqlPoolMember*) calloc(pool_size, sizeof (MysqlPoolMember));
 
    //3. Initialize the variables of the created connection pool
    pool->pool_size = pool_size;
    pool->pool_current = 1;
    pool->pool_max_use_count = pool_max_use_count;
 
    //4. Create a new DB connection for each MysqlPoolMember array elements
    for (i = 0; i 
        member = &pool->pool[i];
        conn = mysql_init(NULL);
 
        if (conn == NULL) {
            printf("mysql_init() fled: %s\n", mysql_error(conn));
            return NULL;
        }
 
        /* Connect to database */
        if (!mysql_real_connect(conn, HOST, USER, PASSWORD,
                DATABASE, PORT, NULL, 0)) {
            printf("mysql_real_connect() fled: %s\n", mysql_error(conn));
            mysql_close(conn);
            return NULL;
        }
 
        /* Set character set to utf8 */
        if (mysql_set_character_set(conn, "utf8")) {
            printf("mysql_set_character_set() fled: %s\n", mysql_error(conn));
            mysql_close(conn);
            return NULL;
        }
 
        member->conn = conn;
        member->used = 0;
    }
 
    return pool;
}

4. 实现从连接池中获取连接的操作。此操作将查找连接池，查找当前处于空闲状态中的连接，找到后将其占用并返回。代码如下：

MYSQL * pool_get_connection(MysqlPool *pool)
{
    MysqlPoolMember *member = NULL;
    int i;
    
    //Iterate through the entire pool and find a member that is not already in use, and then return it to the function that called this function 
    for (i = 0; i pool_size; i++) {
        member = &pool->pool[i];
        if (member->used == 0) {
            member->used = 1;
            return member->conn;
        }
    }
    //If no member is avlable then return NULL
    return NULL;
}

5. 实现将连接还回连接池的操作。该操作将连接设为已用”(used=1)并放回连接池中，以备后续连接使用。代码如下：

void pool_release_connection(MysqlPool *pool, MYSQL *conn)
{
    MysqlPoolMember *member;
    int i;
    
    //find the connection and mark as not use
    for (i = 0; i pool_size; i++) {
        member = &pool->pool[i];
        if (member->conn == conn) {
            member->used = 0;
            break;
        }
 
    }
}

6. 实现连接池销毁操作。该操作将释放连接池中的每个连接，然后再释放连接池。代码如下：

void pool_destroy(MysqlPool *pool)
{
    int i;
    MysqlPoolMember *member;
    for (i = 0; i pool_size; i++) {
        member = &pool->pool[i];
        mysql_close(member->conn);
    }
    free(pool);
}

7.使用mn函数测试连接池模块代码的正确性和可用性。代码如下：

int mn(int argc, char** argv) {
    MYSQL *conn;
    MysqlPool *pool;
    int i;
    pool = create_mysql_pool(5, 10);
    if (!pool) {
        printf("MySQL pool initialization fled, quit.\n");
        exit(EXIT_FLURE);
    }
 
    for (i = 0; i 
        printf("(%d)Fetching connection\n", i);
        conn = pool_get_connection(pool);
        if (!conn) {
            printf("Fled to obtn connection, quit.\n");
            exit(EXIT_FLURE);
        }
        printf("Running SQL: select now();...\n");
        if (mysql_query(conn, "select now();")) {
            printf("mysql_query() fled: %s\n", mysql_error(conn));
        }
        mysql_ping(conn);
        pool_release_connection(pool, conn);
        printf("(%d)Released connection\n\n", i);
    }
 
    pool_destroy(pool);
    return EXIT_SUCCESS;
}

通过这样的实现，我们就可以使用连接池，加快与MySQL数据库的连接时间和增强性能。