徐高翔的个人网站

C语言操作mysql数据库

2018-03-15

软硬件环境

  • ubuntu 18.04 64bit
  • mysql 5.7.22-0ubuntu18.04.1
  • gcc 7.3.0

安装mysql

为方便调试,把server也装上

1
2
sudo apt install mysql-server mysql-workbench
sudo apt install libmysqlclient-dev

增删改查操作

基本的注释都写在了代码里了,应该都很容易看懂,主要是几个重要的API的调用。

首先创建数据库db并建一张表, 表结构如下

mysql_in_c_02

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
/*
* =====================================================================================
*
* Filename: mysql_in_c.c
* Description:
* Version: 1.0
* Created: 2018年07月23日 13时58分15秒
* Revision: none
* Compiler: gcc
*
* Author: xugaoxiang , djstava@gmail.com
*
* =====================================================================================
*/

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

#define MYSQL_HOST "localhost"
#define MYSQL_USER "root"
#define MYSQL_PASSWD "toor"
#define DB_NAME "db"
#define TABLE_NAME "students"

/*
* 测试的数据表结构很简单, number, grade, sex, score 四个字段, 代表学号、年级、性别、分数
*/

/* 连接mysql */
void mysqldb_connect(MYSQL *mysql)
{
if(!mysql_real_connect(mysql, MYSQL_HOST, MYSQL_USER, MYSQL_PASSWD, DB_NAME, 0, NULL, 0)) {
printf("\nFailed to connect:%s\n", mysql_error(mysql));
} else {
printf("\nConnect sucessfully!\n");
}
}

/* 插入数据 */
void mysqldb_insert(MYSQL *mysql, int number, int grade, char *sex, int score)
{
int t;
char *head = "INSERT INTO ";
char query[120];
char field[48] = "number, grade, sex, score";
char *left = "(";
char *right = ") ";
char *values = "VALUES";
char message[100] = {0};

sprintf(message, "%d, %d, \"%s\", %d", number, grade, sex, score);

/* 拼接sql命令 */
sprintf(query, "%s%s%s%s%s%s%s%s%s", head, TABLE_NAME, left, field, right, values, left, message, right);
printf("%s\n", query);

t = mysql_real_query(mysql, query, strlen(query));
if (t) {
printf("Failed to query: %s\n", mysql_error(mysql));
}
else {
printf("\nInsert sucessfully!\n");
}

}

/* 删除数据 */
void mysqldb_delete(MYSQL *mysql, char *field_name, int number)
{
int t;
char *head = "DELETE FROM ";
char query[120];

sprintf(query, "%s%s where %s =%d", head, TABLE_NAME, field_name, number);
printf("%s\n", query);

t = mysql_real_query(mysql, query, strlen(query));
if (t) {
printf("\nFailed to query: %s\n", mysql_error(mysql));
} else {
printf("\nDelete data sucessfully!\n");
}

}

/* 更新数据 */
void mysqldb_update(MYSQL *mysql, char *field_name, int score)
{
int t;
char *head = "UPDATE ";
char query[100];

sprintf(query, "%s%s SET %s=%d", head, TABLE_NAME, field_name, score);
printf("%s\n", query);

t = mysql_real_query(mysql, query, strlen(query));
if (t) {
printf("Failed to update: %s\n", mysql_error(mysql));
return;
}
printf("\nUpdate data sucessfully!\n");
}

/* 查询数据 */
void mysqldb_query(MYSQL *mysql)
{
int t;
char *head = "SELECT * FROM ";
char query[50] = {0};
MYSQL_RES *res;
MYSQL_ROW row;

sprintf(query, "%s%s", head, TABLE_NAME);

t = mysql_real_query(mysql, query, strlen(query));

if (t) {
printf("Failed to query: %s\n", mysql_error(mysql));
return;
} else {
printf("\nQuery successfully!\n");
}

res = mysql_store_result(mysql);
while (row = mysql_fetch_row(res)) {
for(t = 0; t < mysql_num_fields(res); t++) {
printf("%s\t", row[t]);
}
printf("\n");
}
mysql_free_result(res);
}

/* 断开mysql连接 */
void close_connection(MYSQL *mysql)
{
mysql_close(mysql);
}

int main(int argc, char *argv[])
{
// 准备一组数据
int number = 1;
int grade = 1;
char sex[] = "male";
int score = 100;

// 初始化mysql
MYSQL *mysql = mysql_init(NULL);
if (!mysql) {
printf("\nMysql init failed.\n");
}

// 连接MYSQL
mysqldb_connect(mysql);

// 插入数据
mysqldb_insert(mysql, number, grade, sex, score);

// 更新数据
mysqldb_update(mysql, "score", 99);

// 查询数据
mysqldb_query(mysql);

// 删除数据
mysqldb_delete(mysql, "number", number);

// 断开连接
close_connection(mysql);

return 0;
}

四个操作,在执行前面的操作时,后面暂未执行的可以先注释掉,然后一步步查看结果,看是否和预期的一致。

编译

1
gcc -o mysql_in_c mysql_in_c.c -lmysqlclient

执行结果

增加一条记录,
mysql_in_c_03

更改一条记录
mysql_in_c_04

查询记录
mysql_in_c_05

删除记录
mysql_in_c_06

参考资料

  1. https://github.com/mysql

本文链接 https://xugaoxiang.com/2018/03/15/C语言操作mysql数据库/

推荐文章(由hexo文章推荐插件驱动)

使用支付宝打赏
使用微信打赏

请博主喝咖啡!