杨记

碎片化学习令人焦虑，系统化学习使人进步

0%

MySQL的C API

发表于 更新于 分类于

MySQL的C API https://www.mysqlzh.com/

01_hello.c 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#include <stdio.h> 
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include "mysql.h"

#define __HOST__    "127.0.0.1"
#define __USER__     "root"
#define __PASSWD__  "yanglinqi"
#define __DBNAME__  "fi11_tv"

int main(void)
{
    MYSQL* mysql = mysql_init(NULL);
    if (mysql == NULL) {
        printf("init err\n");
        exit(1);
    }

    mysql = mysql_real_connect(mysql, __HOST__, __USER__, __PASSWD__, __DBNAME__, 0, NULL, 0);
    if (mysql == NULL) {
        printf("connect err\n");
        exit(1);
    }
    
    printf("hello mysql\n");

    mysql_close(mysql);
    return 0;
}

02_insert.c 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#include <stdio.h> 
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include "mysql.h"

#define __HOST__    "127.0.0.1"
#define __USER__     "root"
#define __PASSWD__  "yanglinqi"
#define __DBNAME__  "fi11_tv"

int main(void)
{
    MYSQL* mysql = mysql_init(NULL);
    if (mysql == NULL) {
        printf("init err\n");
        exit(1);
    }

    mysql = mysql_real_connect(mysql, __HOST__, __USER__, __PASSWD__, __DBNAME__, 0, NULL, 0);
    if (mysql == NULL) {
        printf("connect err\n");
        exit(1);
    }
    printf("hello mysql\n");

    char rSql[256]={0};
    strcpy(rSql, "insert into video_location values(4, 'aliyun', '11111', 'houzhui')");
    if(mysql_query(mysql,rSql) != 0){
 	      printf("mysql_query err\n");
	      exit(1);
    }
    mysql_close(mysql);
    return 0;
}

03_select.c 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
#include <stdio.h>
#include <unistd.h>
#include <stdlib.h>
#include <string.h>
#include "mysql.h"

#define _HOST_ "127.0.0.1"
#define _USER_ "root"
#define _PASSWD_ "yanglinqi"
#define _DBNAME_ "fi11_tv"

void show_result(MYSQL_RES *result)
{
    // 打印表头
    unsigned int num_fields;
    unsigned int i;
    MYSQL_FIELD *fields;

    num_fields = mysql_num_fields(result);
    fields = mysql_fetch_fields(result);
    for(i = 0; i < num_fields; ++i)
    {
        printf("%s\t", fields[i].name);
    }
    printf("\n------------------------------------------------------------\n");

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result)) != NULL) 
    {
        for (i = 0; i < num_fields; ++i) 
        {
            printf("%s\t", row[i] ? row[i] : "NULL");
        }
        printf("\n");
    }
}

int main()
{
    // first:  init
    MYSQL *mysql = mysql_init(NULL);
    if (NULL == mysql) {
        printf("init err\n");
        exit(1);
    }
    // second: real_connect
    mysql = mysql_real_connect(mysql, _HOST_, _USER_, _PASSWD_, _DBNAME_, 0, NULL, 0);
    if (NULL == mysql) {
        printf("connect err\n");
        exit(1);
    }
    printf("hello mysql!\n");
    // third: query
    char rSql[256]={0};
    strcpy(rSql, "select * from video_location");
    if (mysql_query(mysql, rSql) != 0) {
        printf("mysql_query err\n");
        exit(1);
    }
    // 取结果集
    MYSQL_RES *result = mysql_store_result(mysql);
    if (result != NULL) {
        show_result(result);
        //释放结果集
        mysql_free_result(result);
    }

    // last : close
    mysql_close(mysql);
    return 0;
}

04_client.c 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
#include <stdio.h>
#include <unistd.h>
#include <stdlib.h>
#include <string.h>
#include "mysql.h"

#define _HOST_ "127.0.0.1"
#define _USER_ "root"  //数据库用户
#define _PASSWD_ "yanglinqi"
#define _DBNAME_ "fi11_tv"

#define _MAX_COLS_ 30

//定义格式化输出结构体
typedef struct _strFormat {
    int length; // 每个字段最终显示的长度
    char sfm[10]; // 每个列的展示串的格式化效果
    char Column[100]; // 列显示效果 | empno
    char splitLine[100]; // 分割线显示效果 +------
} strFormat;

/*
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
*/

void show_result(MYSQL_RES * result,MYSQL *mysql)
{
  	//打印表头
  	unsigned int num_fields;
  	unsigned int i;
  	MYSQL_FIELD *fields;
  	 
  	num_fields = mysql_num_fields(result);
  	fields = mysql_fetch_fields(result);
  	for(i = 0; i < num_fields; i++)
  	{
  	   printf("%s\t", fields[i].name);
  	}
  	printf("\n----------------------------------------------------------\n");//华丽分割线
  	
  	
  	MYSQL_ROW row;
  	while ((row = mysql_fetch_row(result)))//循环取一行
  	{
  	   for(i = 0; i < num_fields; i++)
  	   {
  	       printf("%s\t",  row[i] ? row[i] : "NULL");
  	   }
  	   printf("\n");
  	}
  	printf("\n----------------------------------------------------------\n");//华丽分割线
  	//3 rows in set (0.28 sec)
  	printf("%ld rows in set \n",mysql_affected_rows(mysql));//影响的数目
}

void show_result_format(MYSQL_RES *result, MYSQL *mysql)
{
	//展示result的结果
    if (result->row_count == 0)
    {
        printf("Empty set\n");
        return;
    }
    //打印表头
    unsigned int num_fields;
    strFormat fm[_MAX_COLS_];
    bzero(&fm, sizeof(strFormat)*_MAX_COLS_);
    unsigned int i,j ;
    MYSQL_FIELD *fields;
    num_fields = mysql_num_fields(result);
    fields = mysql_fetch_fields(result);//取回表头,即各个列名
    for(i = 0; i < num_fields; ++i)
    {
        //每个字段有多个长度,取名字长度和内容最长 两者之间的最大值
        fm[i].length = fields[i].name_length > fields[i].max_length ? fields[i].name_length : fields[i].max_length;
        fm[i].splitLine[0] = '+';
        for (j = 1; j < fm[i].length+3; ++j) 
        {
            fm[i].splitLine[j] = '-';
        }
        if (i == num_fields-1)
        {
            sprintf(fm[i].sfm, "| %c-%ds |", '%', fm[i].length); // 得到"| %-10s |"
            fm[i].splitLine[j] = '+';
        }
        else
        {
            sprintf(fm[i].sfm, "| %c-%ds ", '%', fm[i].length);
        }
        sprintf(fm[i].Column, fm[i].sfm, fields[i].name);
    }
    for(j = 0; j < 3; ++j)
    {
        if(j == 1)
        {
            for(i = 0; i < num_fields; ++i)
            {
                printf("%s", fm[i].Column);
            }
        }
        else
        {
            for(i = 0; i < num_fields; ++i)
            {
                printf("%s", fm[i].splitLine);
            }
        }
        printf("\n");
    }
    
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result)))//循环展示每一行的的内容
    {
        for(i = 0; i < num_fields; i++)//针对每一行,展示每一列,使用之前得到的输出格式化串
        {
            printf(fm[i].sfm, row[i] ? row[i] : "NULL");
        }
        printf("\n");
    }
    for(i = 0; i < num_fields; i++)
    {
        printf("%s",fm[i].splitLine);
    }
    printf("\n%ld rows in set \n",(long) mysql_affected_rows(mysql));
}

int main()
{
	//1. init 
    MYSQL*mysql = mysql_init(NULL);
    if(mysql == NULL){
        printf("init err\n");
        exit(1);
    }
    //2. real_connect
    mysql = mysql_real_connect(mysql, _HOST_, _USER_, _PASSWD_, _DBNAME_ ,0, NULL, 0);
    if(mysql == NULL){
        printf("connect err\n");
        exit(1);
    }
    printf("welcome to mysql!\n");
	
  	char rSql[1024] = {0};
  	const char tip[] = "yoursql> ";
  	while(1) {
    	write(STDOUT_FILENO, tip, strlen(tip));
  		memset(rSql, 0x00, sizeof(rSql));
        read(STDIN_FILENO, rSql, sizeof(rSql));
    	if (strncmp(rSql, "quit", 4) == 0) {
            printf("bye bye!\n");
            break;
        }
        //执行sql
        if(mysql_query(mysql,rSql) != 0) {
            printf("mysql_query err\n");
            continue;
        }
        //取回结果集
        MYSQL_RES *result = mysql_store_result(mysql);
        if (result != NULL) {
            //打印结果集
            show_result_format(result, mysql);
            //释放结果集
            mysql_free_result(result);
        } else {
            printf("Query OK, %ld row affected \n", mysql_affected_rows(mysql));
        }
	  }
    //3. close
    mysql_close(mysql);
    return 0;
}

05_tran.c 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
//mysql中的事务
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "mysql.h"

#define SET_TRAN	"SET AUTOCOMMIT=0"  		//手动commit	————手动commit
#define UNSET_TRAN	"SET AUTOCOMMIT=1"			//自动commit

#define _HOST_ "127.0.0.1"
#define _USER_ "root"
#define _PASSWD_ "yanglinqi"
#define _DBNAME_ "fi11_tv"

// 设置事务为手动提交
int mysql_OperationTran(MYSQL *mysql)
{
    // --设置事务为手动提交
    int ret = mysql_query(mysql, SET_TRAN);
    if (ret != 0) {
        printf("%s query set err: %s\n", __FUNCTION__, mysql_error(mysql));
        return ret;
    }
    return ret;
}

//设置事务为自动提交
int mysql_AutoTran(MYSQL *mysql)
{
	//--设置事务为自动提交
	int ret = mysql_query(mysql, UNSET_TRAN);  //"set autocommit = 1"
	if (ret != 0) {
		printf("%s query set err: %s\n", __FUNCTION__, mysql_error(mysql));
		return ret;
	}
	return ret;		
}

// 执行commit，手动提交事务
int mysql_Commit(MYSQL *mysql)
{
    int ret = mysql_query(mysql, "COMMIT");
    if (ret != 0) {
        printf("commit err: %s\n", mysql_error(mysql));
        return ret;
    }
    return ret;
}

//执行rollback，回滚事务		
int mysql_Rollback(MYSQL *mysql)
{
	int ret = mysql_query(mysql, "ROLLBACK");
	if (ret != 0) {
		printf("rollback err: %s\n", mysql_error(mysql));
		return ret;
	}
	return ret;
	
}

#define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test_table"
#define CREATE_SAMPLE_TABLE "CREATE TABLE test_table(col1 INT,\
                                                 col2 VARCHAR(10),\
                                                 col3 VARCHAR(10))"
                                                                                       
#define sql01 "INSERT INTO test_table(col1,col2,col3) VALUES(10, 'AAA', 'A1')"
#define sql02 "INSERT INTO test_table(col1,col2,col3) VALUES(20, 'BBB', 'B2')"
#define sql03 "INSERT INTO test_table(col1,col2,col3) VALUES(30, 'CCC', 'C3')"
#define sql04 "INSERT INTO test_table(col1,col2,col3) VALUES(40, 'DDD', 'D4')"

int main(void)
{
	int ret = 0;

	MYSQL *mysql = mysql_init(NULL);
	
	mysql = mysql_real_connect(mysql, _HOST_, _USER_, _PASSWD_, _DBNAME_, 0, NULL, 0);
	if (mysql == NULL) {
		ret = mysql_errno(mysql);
		printf("func mysql_real_connect() err：%d\n", ret);
		return ret;
	} 	
	printf(" --- connect ok......\n");	
	//执行删除表
	if (mysql_query(mysql, DROP_SAMPLE_TABLE)) {
        fprintf(stderr, " DROP TABLE failed\n");
        fprintf(stderr, " %s\n", mysql_error(mysql));
        exit(0);
	}
	//执行创建表
	if (mysql_query(mysql, CREATE_SAMPLE_TABLE)) {
	  fprintf(stderr, " CREATE TABLE failed\n");
	  fprintf(stderr, " %s\n", mysql_error(mysql));
	  exit(0);
	}	
	
	ret = mysql_OperationTran(mysql); 	//开启事务，并修改事务属性为手动commit 
	if (ret != 0) {
		printf("mysql_OperationTran() err:%d\n", ret);
		return ret;
	}
	
	ret = mysql_query(mysql, sql01);	//向表中插入第一行数据 ‘AAA’
	if (ret != 0) {
		printf("mysql_query() err:%d\n", ret);
		return ret;
	}
	
	ret = mysql_query(mysql, sql02);	//向表中插入第二行数据 ‘BBB’
	if (ret != 0) {
		printf("mysql_query() err:%d\n", ret);
		return ret;
	}
	
	ret = mysql_Commit(mysql); 			//手动提交事务
	if (ret != 0) {
		printf("mysql_Commit() err:%d\n", ret);
		return ret;
	}
	//////////AAA BBB  进去了。
	
#if 0
	ret = mysql_AutoTran(mysql); 		// =再次= 修改事务属性为【自动】commit
	if (ret != 0) {
		printf("mysql_OperationTran() err:%d\n", ret);
		return ret;
	}
#else 
	ret = mysql_OperationTran(mysql); 	// =再次= 修改事务属性为【手动】commit
	if (ret != 0) {
		printf("mysql_OperationTran() err:%d\n", ret);
		return ret;
	}
#endif

	ret = mysql_query(mysql, sql03);	//向表中插入第三行数据 ‘CCC’
	if (ret != 0) {
		printf("mysql_query() err:%d\n", ret);
		return ret;
	}
	
	ret = mysql_query(mysql, sql04);	//向表中插入第四行数据 ‘DDD’
	if (ret != 0) {
		printf("mysql_query() err:%d\n", ret);
		return ret;
	}
	
	ret = mysql_Rollback(mysql);		//直接rollback操作
	if (ret != 0) {
		printf("mysql_Rollback() err:%d\n", ret);
		return ret;
	}
	
	//rollback操作是否能回退掉CCC、DDD的值，取决于事务属性。
	
	mysql_close(mysql);
	
	return 0;	
}

Makefile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SrcFiles=$(wildcard *.c)
TargetFiles=$(patsubst %.c,%,$(SrcFiles))

IncPath=/usr/include/mysql
LibPath=/usr/lib64/mysql
PubLib=-lmysqlclient -ldl -lpthread -lrt 

all:$(TargetFiles)

%:%.c
	gcc -o $@ $^ -I$(IncPath) -L$(LibPath) $(PubLib)

clean:
	-rm -f $(TargetFiles)

欢迎关注我的其它发布渠道