在大型软件编程中,数据库是必不可少的,通常需要通过各种编程语言访问和操作数据库。本文介绍了在Linux 下使用C语言去操作Mysql数据库的方法。还有很重要的一点是: 在编程中,使用基础API操作数据库会比较复杂,不实用,软件开发者通常会在原有基础上抽象出一组使用更方便的API接口,本文也作了简单的抽象API(函数),如top_XX_XX()函数,仅供学习。
1. 首先在命令行通过SQL语句在mysql数据库中创建一个student表,并插入两条数据(1, zhangsan)和(2, lisi), 过程如下:
$mysql -h 127.0.0.1 -uroot -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kamailio |
| mysql |
| opensips |
| performance_schema |
| student |
+--------------------+
6 rows in set (0.12 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
.....
mysql> create table student(id int(4) not null primary key auto_increment, name char(20) not null);
Query OK, 0 rows affected (0.52 sec)
mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.19 sec)
mysql> insert into student(id, name) values(1, "zhangsan");
Query OK, 1 row affected (0.07 sec)
mysql> select * from student;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
mysql> insert into student(name) values("lisi");
Query OK, 1 row affected (0.09 sec)
mysql> select * from student;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
2 rows in set (0.00 sec)
2. 实例
Linux C mysql 在线API文档:http://www.yesky.com/imagesnew/software/mysql/manual_Clients.html
在Linux下使用C语言访问Mysql,需要安装额外的开发包:
sudo apt-get install libmysqlclient15-dev
mysql_test.c:
#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
#define debug() \
printf("%s, %d\n", __FUNCTION__, __LINE__)
#define MAX_QUERY_CMD_LEN 1024
MYSQL *conn = NULL;
char table_name[] = "student";
void top_log(const char *log)
{
printf("%s\n", log);
}
MYSQL_RES *top_query_database(MYSQL *conn)
{
MYSQL_RES *res = NULL;
if (!conn) {
top_log("param is NULL");
return NULL;
}
if (mysql_query(conn, "show databases"))
{
top_log(mysql_error(conn));
return NULL;
}
res = mysql_store_result(conn);
if (!res) {
top_log(mysql_error(conn));
return NULL;
}
return res;
}
MYSQL_RES *top_query_table(MYSQL *conn)
{
MYSQL_RES *res = NULL;
if (!conn) {
top_log("param is NULL");
return NULL;
}
if (mysql_query(conn, "show tables"))
{
top_log(mysql_error(conn));
return NULL;
}
res = mysql_store_result(conn);
if (!res) {
top_log(mysql_error(conn));
return NULL;
}
return res;
}
MYSQL_RES *top_insert_into_table(MYSQL *conn, char *table, char *fields, char *values)
{
MYSQL_RES *res = NULL;
int len = -1;
char sql_cmd[MAX_QUERY_CMD_LEN];
if (!conn || !table || !fields || !values) {
top_log("param is NULL");
return NULL;
}
if (fields) {
len = snprintf(sql_cmd, MAX_QUERY_CMD_LEN, "insert into %s%s values%s",
table, fields, values);
} else {
len = snprintf(sql_cmd, MAX_QUERY_CMD_LEN, "insert into %s values%s",
table, values);
}
printf("sql_cmd: %s, len: %d\n", sql_cmd, len);
if (mysql_query(conn, sql_cmd))
{
top_log(mysql_error(conn));
return NULL;
}
res = mysql_store_result(conn);
if (!res) {
top_log(mysql_error(conn));
return NULL;
}
return res;
}
MYSQL_RES *top_select_from_table(MYSQL *conn, char *table, char *condition)
{
MYSQL_RES *res = NULL;
int len = -1;
char sql_cmd[MAX_QUERY_CMD_LEN];
if (!conn || !table) {
top_log("param is NULL");
return NULL;
}
len = snprintf(sql_cmd, MAX_QUERY_CMD_LEN, "select * from %s", table);
printf("sql_cmd: %s, len: %d\n", sql_cmd, len);
if (condition) {
snprintf(sql_cmd + len, MAX_QUERY_CMD_LEN, " where %s", condition);
}
printf("sql_cmd: %s, len: %d\n", sql_cmd, len);
if (mysql_query(conn, sql_cmd))
{
top_log(mysql_error(conn));
return NULL;
}
res = mysql_store_result(conn);
if (!res) {
top_log(mysql_error(conn));
return NULL;
}
return res;
}
int main()
{
MYSQL_RES *res;
MYSQL_ROW row;
int ret = -1;
int i, j;
char server[] = "localhost";
char user[] = "root";
char password[] = "hello";
char database[] = "mysql";
conn = mysql_init(NULL);
if (!mysql_real_connect(conn, server,user, password, database, 0, NULL, 0))
{
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
res = top_query_database(conn);
if (!res) {
printf("error\n");
exit(1);
}
printf("MySQL num_rows: %lu, num_fileds: %d\n",
(unsigned long)mysql_num_rows(res), mysql_num_fields(res));
while ((row = mysql_fetch_row(res)) != NULL)
{
printf("%s \n", row[0]);
}
/* select db*/
ret = mysql_select_db(conn, "mysql");
if (ret) {
printf("select db error: %s\n", mysql_error(conn));
}
res = top_query_table(conn);
if (!res) {
printf("error\n");
exit(1);
}
printf("MySQL num_rows: %lu, num_fileds: %d\n",
(unsigned long)mysql_num_rows(res), mysql_num_fields(res));
while ((row = mysql_fetch_row(res)) != NULL)
{
// print all tables in mysql database
printf("%s \n", row[0]);
}
mysql_free_result(res);
top_insert_into_table(conn, table_name, "(name)", "(\"wangwu\")");
res = top_select_from_table(conn, table_name, NULL);
printf("MySQL num_rows: %lu, num_fileds: %d\n",
(unsigned long)mysql_num_rows(res), mysql_num_fields(res));
int field_count = res->field_count;
printf("field_count: %d\n", field_count);
while ((row = mysql_fetch_row(res)) != NULL)
{
for(i = 0; i < field_count; i++) {
printf("%s\n", row[i]);
}
}
mysql_free_result(res);
mysql_close(conn);
return 0;
}
Makefile:
CFLAGS=-g
all:
gcc mysql_test.c -o a.out -lmysqlclient -g
结果:
$ ./a.out
MySQL num_rows: 6, num_fileds: 1
information_schema
kamailio
mysql
opensips
performance_schema
student
MySQL num_rows: 25, num_fileds: 1
columns_priv
db
event
func
general_log
help_category
help_keyword
help_relation
help_topic
host
ndb_binlog_index
plugin
proc
procs_priv
proxies_priv
servers
slow_log
student
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
sql_cmd: insert into student(name) values("wangwu"), len: 42
sql_cmd: select * from student, len: 21
sql_cmd: select * from student, len: 21
MySQL num_rows: 3, num_fileds: 2
field_count: 2
1
zhangsan
2
lisi
3
wangwu