C language to connect to Mysql database (Windows && Linux)

Foreword:

A while ago, I received a list of C language data structure and algorithmic final comprehensive assignments. The customer requested to make a Lianliankan mini game, requiring the use of at least four algorithms to achieve functions, and after the player completed the game, the score was recorded in the MySQL database. I have never used the C language to connect to the database before, only Java and Python can connect to the database. I ran to ask the embedded seniors how to connect to the database in C language, but he did not know how to...

Insert picture description here

Then I can only Baidu, Baidu, but Baidu others wrote is still not detailed enough, I introduced mysql.h, libmysql.lib according to the requirements, and still reported various errors, which made my mentality a little broken, hereby write down the detailed implementation of different operating systems C language connects to MySQL database operation.


Windows environment:

1. Prerequisite preparation:

MySQL 5.7 (64-bit)
Visual Studio 2019 (other compilers are also OK, use 64-bit compilation, which corresponds to the number of MySQL bits)

Second, the introduction of documents

C language is more cumbersome to connect to the database, and you need to introduce header files and dependencies about connecting to the database.
Insert picture description here

The question is: where do these files come from? Where can I find these header files and dependencies. please watch the following part:

Find the location where MySql was installed at that time: (My MySQL installation path: C:\Program Files\MySQL\MySQL Server 5.7) , you will see the following directory:

Insert picture description here


The required header files and dependencies are in the include and lib folders and can be copied to the same level directory as the main project file.

Copy all the include folder, not just copy mysql.h, because mysql.h needs to rely on other header files. Copy libmysql.dll and libmysql.lib in the lib folder.

Three, test code writing:

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
/*引入连接Mysql的头文件和lib包*/
#include "mysql/mysql.h"
#pragma comment(lib,"libmysql")

/*定义一些数据库连接需要的宏*/
#define HOST "localhost" /*MySql服务器地址*/
#define USERNAME "root" /*用户名*/
#define PASSWORD "123456" /*数据库连接密码*/
#define DATABASE "rank" /*需要连接的数据库*/

// 执行sql语句的函数
void exeSql(char* sql) {
    MYSQL my_connection; /*数据库连接*/ 
    int res;  /*执行sql语句后的返回标志*/ 
    MYSQL_RES* res_ptr; /*执行结果*/ 
    MYSQL_ROW result_row; /*按行返回查询信息*/ 
    int row, column; /* 定义行数,列数*/
    mysql_init(&my_connection);
    if (mysql_real_connect(&my_connection, HOST, USERNAME, PASSWORD, DATABASE, 0, NULL, CLIENT_FOUND_ROWS)) {
        printf("数据库连接成功!");
        /*设置查询编码为 utf8, 支持中文*/
        mysql_query(&my_connection, "set names utf8");
        res = mysql_query(&my_connection, sql);      
        if (res) {
            /*现在就代表执行失败了*/
            printf("Error: mysql_query !\n");
            /*不要忘了关闭连接*/
            mysql_close(&my_connection);
        } else {
            /*现在就代表执行成功了*/
            /*mysql_affected_rows会返回执行sql后影响的行数*/
            printf("%d 行受到影响!\n", mysql_affected_rows(&my_connection));
            // 把查询结果装入 res_ptr
            res_ptr = mysql_store_result(&my_connection);
            // 存在则输出
            if (res_ptr) {
                // 获取行数,列数
                row = mysql_num_rows(res_ptr);
                column = mysql_num_fields(res_ptr);
                // 执行输出结果,从第二行开始循环(第一行是字段名)
                for (int i = 1; i < row + 1; i++) {
                    // 一行数据
                    result_row = mysql_fetch_row(res_ptr);
                    for (int j = 0; j < column; j++) {
                    	printf("%s", result_row[j]);
                    }
                }
            }
            /*不要忘了关闭连接*/
            mysql_close(&my_connection);
        }
    } else {
        printf("数据库连接失败!");
    }
}

Linux environment:

1. Prerequisite preparation:

MySql 5.7 (64-bit)
c/c++ environment (gcc) compilation environment

Start the Mysql service: sudo service start mysql


2. Test code writing:

note:
  • If mysql can't find mysql.h, you can install it apt-get install libmysqlclient-dev. After installation, re-enter the mysql directory and there will be corresponding header files.
  • When Linux introduces mysql.h, don't put it directly in the same level directory as the main file as in the windows environment, but find the location of mysql.h:/usr/include/mysql/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
/*引入连接Mysql的头文件*/
#include "/usr/include/mysql/mysql.h"

/*定义一些数据库连接需要的宏*/
#define HOST "localhost" /*MySql服务器地址*/
#define USERNAME "root" /*用户名*/
#define PASSWORD "123456" /*数据库连接密码*/
#define DATABASE "rank" /*需要连接的数据库*/

// 执行sql语句的函数
void exeSql(char* sql) {
    MYSQL my_connection; /*数据库连接*/ 
    int res;  /*执行sql语句后的返回标志*/ 
    MYSQL_RES* res_ptr; /*执行结果*/ 
    MYSQL_ROW result_row; /*按行返回查询信息*/ 
    int row, column; /* 定义行数,列数*/
    mysql_init(&my_connection);
    if (mysql_real_connect(&my_connection, HOST, USERNAME, PASSWORD, DATABASE, 0, NULL, CLIENT_FOUND_ROWS)) {
        printf("数据库连接成功!");
        /*设置查询编码为 utf8, 支持中文*/
        mysql_query(&my_connection, "set names utf8");
        res = mysql_query(&my_connection, sql);      
        if (res) {
            /*现在就代表执行失败了*/
            printf("Error: mysql_query !\n");
            /*不要忘了关闭连接*/
            mysql_close(&my_connection);
        } else {
            /*现在就代表执行成功了*/
            /*mysql_affected_rows会返回执行sql后影响的行数*/
            printf("%d 行受到影响!\n", mysql_affected_rows(&my_connection));
            // 把查询结果装入 res_ptr
            res_ptr = mysql_store_result(&my_connection);
            // 存在则输出
            if (res_ptr) {
                // 获取行数,列数
                row = mysql_num_rows(res_ptr);
                column = mysql_num_fields(res_ptr);
                // 执行输出结果,从第二行开始循环(第一行是字段名)
                for (int i = 1; i < row + 1; i++) {
                    // 一行数据
                    result_row = mysql_fetch_row(res_ptr);
                    for (int j = 0; j < column; j++) {
                    	printf("%s", result_row[j]);
                    }
                }
            }
            /*不要忘了关闭连接*/
            mysql_close(&my_connection);
        }
    } else {
        printf("数据库连接失败!");
    }
}

Compile the program : gcc -g mysqlconnectDemo.c -lmysqlclient -o test( must bring -lmysqlclient )