[C语言实战]C语言操作MySQL数据库（八）

[C语言实战]C语言操作MySQL数据库（八）

MySQL作为最流行的开源关系型数据库，与C语言的结合在系统编程、嵌入式开发等领域应用广泛。本文将全面讲解如何使用C语言连接MySQL数据库并实现增删改查(CRUD)操作，包含详细代码示例和最佳实践。

一、环境配置与基础准备

1.1 开发环境搭建

Linux系统安装MySQL开发库（方案一）：

# Ubuntu/Debian
sudo apt-get update
sudo apt-get install libmysqlclient-dev

# CentOS/RHEL
sudo yum install mysql-devel

Linux系统安装MySQL开发库（方案二）：

1. 下载MySQL Connector/C

2.安装

dpkg-deb -x libmysqlclient-dev_9.2.0-1ubuntu24.04_amd64.deb .

3.将mysql的头文件和库文件放在系统环境下（可选）

#进入安装目录
cd usr
cp -r mysql/ /usr/include/
#进入安装后的lib目录下
cd /usr/lib/x86_64-linux-gnu
cp * -r /usr/lib/x86_64-linux-gnu/

1.2 项目基础配置

必要头文件：

#include <mysql/mysql.h>  // MySQL主头文件
#include <stdio.h>        // 标准输入输出
#include <stdlib.h>       // 标准库函数

编译命令：

#根据实际情况修改头文件和库位置:-I/home/c/mysqldemo/usr/include/mysql（可选）
gcc mysql_demo.c -o mysql_demo -lmysqlclient -I/home/c/mysqldemo/usr/include/mysql  -L/home/c/mysqldemo/usr/lib/x86_64-linux-gnu -lmysqlclient 

#假如mysql8版本是docker启动且是按方案二安装，则将docker中的mysql_native_password.so复制在宿主机（可选)
docker cp 94:/usr/lib/mysql/plugin/mysql_native_password.so 94:/usr/lib/mysql/plugin/

#实际编译Makefile,直接make


# 编译器和标志
CC = gcc
CFLAGS = -Wall -g -std=c99
CPPFLAGS = -I/home/c/mysqldemo/usr/include/mysql
LDFLAGS = -L/home/c/mysqldemo/usr/lib/x86_64-linux-gnu
LDLIBS = -lmysqlclient -lssl -lcrypto -lpthread -lstdc++ -lz

# 目标和源文件
TARGET = mysql_demo
SRCS = mysql_demo.c
OBJS = $(SRCS:.c=.o)

# 默认目标
all: $(TARGET)

# 链接目标文件
$(TARGET): $(OBJS)
	$(CC) $(CFLAGS) $(LDFLAGS) -o $@ $^ $(LDLIBS)

# 编译源文件
%.o: %.c
	$(CC) $(CFLAGS) $(CPPFLAGS) -c $< -o $@

# 清理生成文件
clean:
	rm -f $(OBJS) $(TARGET)

编译为可执行文件：

测试：

二、数据库连接管理

2.1 建立数据库连接

//结构体定义
typedef struct {
    MYSQL *conn;
    const char *host;
    const char *user;
    const char *passwd;
    const char *dbname;
    unsigned int port;
} Database;


int db_connect(Database *db) {
    db->conn = mysql_init(NULL);
    if (!db->conn) {
        handle_mysql_error(db->conn, "数据库初始化失败");
        return -1;
    }

    // 设置连接超时和认证插件（解决5.7/8.0兼容问题）
    unsigned int timeout = 5;
    mysql_options(db->conn, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);
    mysql_options(db->conn, MYSQL_DEFAULT_AUTH, "mysql_native_password");

    if (!mysql_real_connect(
        db->conn, db->host, db->user, db->passwd,
        db->dbname, db->port, NULL, CLIENT_MULTI_STATEMENTS
    )) {
        handle_mysql_error(db->conn, "数据库连接失败");
        mysql_close(db->conn);
        return -1;
    }

    // 强制设置字符集并检查错误
    if (mysql_set_character_set(db->conn, "utf8mb4")) {
        handle_mysql_error(db->conn, "字符集设置失败");
        mysql_close(db->conn);
        return -1;
    }

    return 0;
}

2.2 连接参数详解

三、CRUD操作实战

3.1 创建数据表

    // 创建表
    const char *create_table_sql = 
        "CREATE TABLE IF NOT EXISTS products ("
        "id INT AUTO_INCREMENT PRIMARY KEY,"
        "name VARCHAR(100) NOT NULL,"
        "price DECIMAL(10,2) NOT NULL,"
        "stock INT DEFAULT 0"
        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

    if (mysql_query(db.conn, create_table_sql)) {
        handle_mysql_error(db.conn, "表创建失败");
        db_disconnect(&db);
        return 1;
    }

3.2 安全插入数据（防SQL注入）

int insert_user(MYSQL *conn, const char *username, 
               const char *password, const char *email) {
    MYSQL_STMT *stmt;
    MYSQL_BIND bind[3];
    
    stmt = mysql_stmt_init(conn);
    if (!stmt) {
        fprintf(stderr, "初始化STMT失败\n");
        return -1;
    }
    
    const char *query = "INSERT INTO users (username, password, email) "
                       "VALUES (?, ?, ?)";
    
    if (mysql_stmt_prepare(stmt, query, strlen(query))) {
        fprintf(stderr, "准备失败: %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }
    
    // 初始化绑定结构
    memset(bind, 0, sizeof(bind));
    
    // 绑定用户名
    bind[0].buffer_type = MYSQL_TYPE_STRING;
    bind[0].buffer = (char *)username;
    bind[0].buffer_length = strlen(username);
    bind[0].is_null = 0;
    
    // 绑定密码
    bind[1].buffer_type = MYSQL_TYPE_STRING;
    bind[1].buffer = (char *)password;
    bind[1].buffer_length = strlen(password);
    bind[1].is_null = 0;
    
    // 绑定邮箱
    bind[2].buffer_type = MYSQL_TYPE_STRING;
    bind[2].buffer = (char *)email;
    bind[2].buffer_length = strlen(email);
    bind[2].is_null = (email == NULL) ? 1 : 0;
    
    if (mysql_stmt_bind_param(stmt, bind)) {
        fprintf(stderr, "绑定参数失败: %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }
    
    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, "执行失败: %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }
    
    printf("插入成功，ID: %llu\n", mysql_stmt_insert_id(stmt));
    mysql_stmt_close(stmt);
    return 0;
}

3.3 查询数据与结果处理

typedef struct {
    int id;
    char username[50];
    char email[100];
    char created_at[20];
} User;

int get_users(MYSQL *conn, User **users, int *count) {
    if (mysql_query(conn, "SELECT id, username, email, created_at FROM users")) {
        fprintf(stderr, "查询失败: %s\n", mysql_error(conn));
        return -1;
    }
    
    MYSQL_RES *result = mysql_store_result(conn);
    if (!result) {
        fprintf(stderr, "获取结果失败: %s\n", mysql_error(conn));
        return -1;
    }
    
    int num_fields = mysql_num_fields(result);
    *count = mysql_num_rows(result);
    
    if (*count == 0) {
        mysql_free_result(result);
        return 0;
    }
    
    *users = (User *)malloc(sizeof(User) * (*count));
    if (!*users) {
        fprintf(stderr, "内存分配失败\n");
        mysql_free_result(result);
        return -1;
    }
    
    MYSQL_ROW row;
    int i = 0;
    while ((row = mysql_fetch_row(result))) {
        (*users)[i].id = atoi(row[0]);
        strncpy((*users)[i].username, row[1], 49);
        if (row[2]) strncpy((*users)[i].email, row[2], 99);
        if (row[3]) strncpy((*users)[i].created_at, row[3], 19);
        i++;
    }
    
    mysql_free_result(result);
    return 0;
}

3.4 更新与删除操作

// 更新用户邮箱
int update_user_email(MYSQL *conn, int user_id, const char *new_email) {
    MYSQL_STMT *stmt = mysql_stmt_init(conn);
    if (!stmt) return -1;
    
    const char *query = "UPDATE users SET email = ? WHERE id = ?";
    if (mysql_stmt_prepare(stmt, query, strlen(query))) {
        mysql_stmt_close(stmt);
        return -1;
    }
    
    MYSQL_BIND bind[2];
    memset(bind, 0, sizeof(bind));
    
    // 绑定新邮箱
    bind[0].buffer_type = MYSQL_TYPE_STRING;
    bind[0].buffer = (char *)new_email;
    bind[0].buffer_length = strlen(new_email);
    
    // 绑定用户ID
    bind[1].buffer_type = MYSQL_TYPE_LONG;
    bind[1].buffer = &user_id;
    
    if (mysql_stmt_bind_param(stmt, bind) || 
        mysql_stmt_execute(stmt)) {
        mysql_stmt_close(stmt);
        return -1;
    }
    
    int affected = mysql_stmt_affected_rows(stmt);
    mysql_stmt_close(stmt);
    
    if (affected == 0) {
        printf("未找到用户或数据未改变\n");
        return 0;
    }
    
    printf("成功更新%d条记录\n", affected);
    return 1;
}

// 删除用户
int delete_user(MYSQL *conn, int user_id) {
    char query[100];
    snprintf(query, sizeof(query), "DELETE FROM users WHERE id = %d", user_id);
    
    if (mysql_query(conn, query)) {
        fprintf(stderr, "删除失败: %s\n", mysql_error(conn));
        return -1;
    }
    
    int affected = mysql_affected_rows(conn);
    if (affected == 0) {
        printf("未找到指定用户\n");
        return 0;
    }
    
    printf("成功删除%d条记录\n", affected);
    return 1;
}

四、高级特性与最佳实践

4.1 事务处理

int transfer_money(MYSQL *conn, int from_id, int to_id, double amount) {
    // 开始事务
    if (mysql_query(conn, "START TRANSACTION")) {
        return -1;
    }
    
    // 执行转账操作
    char query[256];
    snprintf(query, sizeof(query), 
            "UPDATE accounts SET balance = balance - %.2f WHERE user_id = %d",
            amount, from_id);
    if (mysql_query(conn, query)) {
        mysql_query(conn, "ROLLBACK");
        return -1;
    }
    
    snprintf(query, sizeof(query), 
            "UPDATE accounts SET balance = balance + %.2f WHERE user_id = %d",
            amount, to_id);
    if (mysql_query(conn, query)) {
        mysql_query(conn, "ROLLBACK");
        return -1;
    }
    
    // 提交事务
    if (mysql_query(conn, "COMMIT")) {
        mysql_query(conn, "ROLLBACK");
        return -1;
    }
    
    return 0;
}

4.2 连接池实现

#define MAX_CONNECTIONS 10

typedef struct {
    MYSQL *conn;
    int in_use;
    time_t last_used;
} DBConnection;

DBConnection connection_pool[MAX_CONNECTIONS];
pthread_mutex_t pool_mutex = PTHREAD_MUTEX_INITIALIZER;

MYSQL *get_connection() {
    pthread_mutex_lock(&pool_mutex);
    
    MYSQL *conn = NULL;
    int oldest_index = -1;
    time_t oldest_time = time(NULL);
    
    for (int i = 0; i < MAX_CONNECTIONS; i++) {
        if (!connection_pool[i].in_use) {
            if (connection_pool[i].conn) {
                // 复用现有连接
                connection_pool[i].in_use = 1;
                connection_pool[i].last_used = time(NULL);
                conn = connection_pool[i].conn;
                break;
            } else {
                // 创建新连接
                MYSQL *new_conn = mysql_init(NULL);
                if (mysql_real_connect(new_conn, "localhost", "user", "pass", 
                                     "db", 0, NULL, 0)) {
                    connection_pool[i].conn = new_conn;
                    connection_pool[i].in_use = 1;
                    connection_pool[i].last_used = time(NULL);
                    conn = new_conn;
                    break;
                }
            }
        } else if (connection_pool[i].last_used < oldest_time) {
            oldest_index = i;
            oldest_time = connection_pool[i].last_used;
        }
    }
    
    pthread_mutex_unlock(&pool_mutex);
    return conn;
}

void release_connection(MYSQL *conn) {
    pthread_mutex_lock(&pool_mutex);
    
    for (int i = 0; i < MAX_CONNECTIONS; i++) {
        if (connection_pool[i].conn == conn) {
            connection_pool[i].in_use = 0;
            break;
        }
    }
    
    pthread_mutex_unlock(&pool_mutex);
}

4.3 性能优化技巧

1. 批量插入优化：

// 使用多值插入语法
mysql_query(conn, "INSERT INTO users (username, email) VALUES 
                  ('user1', 'u1@ex.com'), ('user2', 'u2@ex.com')");

2. 查询缓存：

// 设置查询缓存
mysql_query(conn, "SET SESSION query_cache_type = ON");

3. 索引优化：

// 添加合适索引
mysql_query(conn, "ALTER TABLE users ADD INDEX idx_username (username)");

五、错误处理与调试

5.1 全面的错误处理机制

void handle_mysql_error(MYSQL *conn) {
    if (mysql_errno(conn)) {
        fprintf(stderr, "MySQL错误 %d: %s\n", 
                mysql_errno(conn), mysql_error(conn));
        
        switch(mysql_errno(conn)) {
            case CR_CONNECTION_ERROR:
                // 处理连接错误
                break;
            case CR_SERVER_GONE_ERROR:
                // 处理连接断开
                break;
            case ER_DUP_ENTRY:
                // 处理唯一键冲突
                break;
            default:
                // 其他错误
                break;
        }
    }
}

5.2 日志记录

void log_mysql_operation(const char *operation, const char *query, 
                        int affected_rows, time_t duration) {
    FILE *log_file = fopen("mysql.log", "a");
    if (log_file) {
        fprintf(log_file, "[%ld] %s: %s\n\t影响行数: %d, 耗时: %ldms\n",
                time(NULL), operation, query, affected_rows, duration);
        fclose(log_file);
    }
}

六、安全防护措施

6.1 SQL注入防护

1. 永远使用预处理语句
2. 输入验证：

int is_valid_input(const char *input) {
    const char *danger_chars = "'\"\\;";
    return strpbrk(input, danger_chars) == NULL;
}

3. 最小权限原则：

// 使用只具有必要权限的数据库账号
mysql_real_connect(conn, "localhost", "app_user", "limited_pass", 
                  "app_db", 0, NULL, 0);

6.2 敏感数据保护

// 密码加密存储
void store_password(MYSQL *conn, const char *username, const char *password) {
    char hashed_pwd[61]; // bcrypt哈希长度
    bcrypt_hashpw(password, bcrypt_gensalt(12), hashed_pwd);
    
    // 使用预处理语句存储
    MYSQL_STMT *stmt = mysql_stmt_init(conn);
    const char *query = "UPDATE users SET password = ? WHERE username = ?";
    // ...绑定参数并执行
}

七、完整示例

 //mysql_demo.c
#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    MYSQL *conn;
    const char *host;
    const char *user;
    const char *passwd;
    const char *dbname;
    unsigned int port;
} Database;

void handle_mysql_error(MYSQL *conn, const char *msg) {
    if (mysql_errno(conn)) {
        fprintf(stderr, "%s: MySQL错误 %d: %s\n", 
                msg, mysql_errno(conn), mysql_error(conn));
    }
}

int db_connect(Database *db) {
    db->conn = mysql_init(NULL);
    if (!db->conn) {
        handle_mysql_error(db->conn, "数据库初始化失败");
        return -1;
    }

    // 设置连接超时和认证插件（解决5.7/8.0兼容问题）
    unsigned int timeout = 5;
    mysql_options(db->conn, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);
    mysql_options(db->conn, MYSQL_DEFAULT_AUTH, "mysql_native_password");

    if (!mysql_real_connect(
        db->conn, db->host, db->user, db->passwd,
        db->dbname, db->port, NULL, CLIENT_MULTI_STATEMENTS
    )) {
        handle_mysql_error(db->conn, "数据库连接失败");
        mysql_close(db->conn);
        return -1;
    }

    // 强制设置字符集并检查错误
    if (mysql_set_character_set(db->conn, "utf8mb4")) {
        handle_mysql_error(db->conn, "字符集设置失败");
        mysql_close(db->conn);
        return -1;
    }

    return 0;
}

void db_disconnect(Database *db) {
    if (db->conn) {
        mysql_close(db->conn);
        db->conn = NULL;
    }
}

void print_result_set(MYSQL_RES *result) {
    if (!result) return;

    int num_fields = mysql_num_fields(result);
    MYSQL_FIELD *fields = mysql_fetch_fields(result);
    MYSQL_ROW row;

    // 打印表头
    for (int i = 0; i < num_fields; i++) {
        printf("%-15s", fields[i].name);
    }
    printf("\n");

    // 打印分隔线
    for (int i = 0; i < num_fields; i++) {
        printf("---------------");
    }
    printf("\n");

    // 打印数据行（直接使用行数据，避免内存分配）
    while ((row = mysql_fetch_row(result))) {
        for (int i = 0; i < num_fields; i++) {
            printf("%-15s", row[i] ? row[i] : "NULL");
        }
        printf("\n");
    }
}

int main() {
    Database db = {
        .host = "127.0.0.1",
        .user = "root",
        .passwd = "123456",
        .dbname = "test_db",
        .port = 3306
    };

    printf("正在连接数据库 %s:%d...\n", db.host, db.port);
    if (db_connect(&db)) return 1;
    printf("数据库连接成功\n\n");

    // 创建表
    const char *create_table_sql = 
        "CREATE TABLE IF NOT EXISTS products ("
        "id INT AUTO_INCREMENT PRIMARY KEY,"
        "name VARCHAR(100) NOT NULL,"
        "price DECIMAL(10,2) NOT NULL,"
        "stock INT DEFAULT 0"
        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

    if (mysql_query(db.conn, create_table_sql)) {
        handle_mysql_error(db.conn, "表创建失败");
        db_disconnect(&db);
        return 1;
    }
    printf("表创建成功\n\n");

    // 插入数据（使用预处理语句避免SQL注入，这里简化演示）
    const char *insert_sql = 
        "INSERT INTO products (name, price, stock) VALUES "
        "('笔记本电脑', 9999.99, 10),"
        "('智能手机', 5999.99, 20),"
        "('平板电脑', 3999.99, 15)";

    if (mysql_query(db.conn, insert_sql)) {
        handle_mysql_error(db.conn, "数据插入失败");
        db_disconnect(&db);
        return 1;
    }
    printf("数据插入成功，影响行数: %lu\n\n", mysql_affected_rows(db.conn));

    // 查询数据
    if (mysql_query(db.conn, "SELECT * FROM products")) {
        handle_mysql_error(db.conn, "数据查询失败");
        db_disconnect(&db);
        return 1;
    }
    printf("数据查询成功\n");
    print_result_set(mysql_store_result(db.conn));

    // 安全释放资源
    db_disconnect(&db);
    printf("\n数据库连接已关闭\n");

    return 0;
}

希望本教程对您有帮助，请点赞❤️收藏⭐关注支持！欢迎在评论区留言交流技术细节！