C语言连接MySQL数据库实现增删改查

使用函数对增删改查进行封装

#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>

void init_conn(MYSQL* mysql) {
    mysql_init(mysql); // 初始化 MySQL 连接
    if (!mysql_real_connect(mysql, "127.0.0.1", "root", "root", "student_management", 3306, NULL, CLIENT_IGNORE_SIGPIPE)) {
        printf("连接数据库时发生错误!\n");
        printf("%s\n", mysql_error(mysql));
    }
}

void close_conn(MYSQL* mysql) {
    mysql_close(mysql); // 关闭数据库连接
}

typedef int (*query_callback_t)(MYSQL_ROW row, int num_fields);

int db_operate(const char* query, int operation_type, query_callback_t callback) {
    MYSQL mysql;
    int num_fields,ret = 0;

    // 连接 MySQL 数据库
    init_conn(&mysql);

    // 执行查询
    if (mysql_query(&mysql, query)) {
        fprintf(stderr, "MySQL operation error: %s\n", mysql_error(&mysql));
        close_conn(&mysql);
        return -1;
    }

    // 根据操作类型处理结果
    switch (operation_type) {
        case 0: // 查询
            {
                MYSQL_RES* result_set = mysql_use_result(&mysql);
				MYSQL_ROW row;
                if (!result_set) {
                    fprintf(stderr, "MySQL result error: %s\n", mysql_error(&mysql));
                    close_conn(&mysql);
                    return -1;
                }

                // 将结果传递给回调函数
                num_fields = mysql_num_fields(result_set);
				while ((row = mysql_fetch_row(result_set))) {
					ret = callback(row, num_fields);
					if (ret != 0) {
						break;
					}
				}

                close_conn(&mysql);
                return ret;
            }break;
        case 1: // 更新
            {
                int affected_rows = (int)mysql_affected_rows(&mysql);
                close_conn(&mysql);
                return affected_rows;
            }break;
        case 2: // 删除
            {
                int affected_rows = (int)mysql_affected_rows(&mysql);
                close_conn(&mysql);
                return affected_rows;
            }break;
        case 3: // 插入
            {
                int last_insert_id = (int)mysql_insert_id(&mysql);
                close_conn(&mysql);
                return last_insert_id;
            }break;
        default:
            fprintf(stderr, "Invalid operation type.\n");
            close_conn(&mysql);
            return -1;
    }
}

int db_query(const char* query, query_callback_t callback) {
    return db_operate(query, 0, callback);
}

int db_update(const char* query) {
    return db_operate(query, 1, NULL);
}

int db_delete(const char* query) {
    return db_operate(query, 2, NULL);
}

int db_insert(const char* query) {
    return db_operate(query, 3, NULL);
}

int my_callback(MYSQL_ROW row, int num_fields) {
    // 处理每一行的结果
    for (int i = 0; i < num_fields; i++) {
        printf("%s ", row[i] ? row[i] : "NULL");
    }
    printf("\n");
    return 0; // 返回 0 表示继续执行
}

int main() {
	//db_query("SELECT * FROM students  LIMIT 1", my_callback);

	//db_query("SELECT * FROM courses LIMIT 1", my_callback);

	//printf("%d\n", db_delete("DELETE FROM students WHERE student_id = 10"));
	
	//printf("%d\n", db_update("UPDATE students SET student_id = 10 WHERE student_id=9"));

	//printf("%d\n", db_insert("INSERT INTO students (name, gender, date_of_birth, email, phone)\
	//	VALUES('John Smith', 'Male', '1998-05-15', 'john.smith@example.com', '123-456-7890')"));
    system("pause");
	return 0;
}

数据库SQL

CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  gender VARCHAR(10),
  date_of_birth DATE,
  email VARCHAR(50),
  phone VARCHAR(20)
);

CREATE TABLE courses (
  course_id INT AUTO_INCREMENT PRIMARY KEY,
  course_name VARCHAR(100) NOT NULL,
  course_description TEXT,
  credits INT,
  instructor VARCHAR(50)
);

CREATE TABLE grades (
  grade_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  score FLOAT,
  semester VARCHAR(10),
  year INT
);

INSERT INTO students (name, gender, date_of_birth, email, phone)
VALUES
  ('John Smith', 'Male', '1998-05-15', 'john.smith@example.com', '123-456-7890'),
  ('Emily Johnson', 'Female', '1999-09-22', 'emily.johnson@example.com', '987-654-3210'),
  ('Michael Brown', 'Male', '2000-03-01', 'michael.brown@example.com', '456-789-0123'),
  ('Sarah Davis', 'Female', '2001-11-08', 'sarah.davis@example.com', '321-654-9876'),
  ('David Lee', 'Male', '1997-07-27', 'david.lee@example.com', '789-123-4567');
	
INSERT INTO courses (course_name, course_description, credits, instructor)
VALUES
  ('Data Structures', 'Common data structures and algorithms', 3, 'Professor Smith'),
  ('Operating Systems', 'Principles and applications of operating systems', 4, 'Professor Johnson'),
  ('Linear Algebra', 'Vector spaces and matrix operations', 3, 'Professor Brown'),
  ('Software Engineering', 'Software development lifecycle', 4, 'Professor Davis'),
  ('Machine Learning', 'Machine learning algorithms and applications', 3, 'Professor Lee');
	
INSERT INTO grades (student_id, course_id, score, semester, year)
VALUES
  (1, 1, 85.5, 'Spring', 2022),
  (1, 2, 92.0, 'Fall', 2022),
  (2, 1, 78.0, 'Spring', 2022),
  (2, 3, 87.3, 'Fall', 2022),
  (3, 2, 90.5, 'Spring', 2022),
  (3, 4, 82.0, 'Fall', 2022),
  (4, 3, 91.2, 'Spring', 2022),
  (4, 5, 88.7, 'Fall', 2022),
  (5, 4, 84.3, 'Spring', 2022),
  (5, 5, 86.1, 'Fall', 2022);