要进行linux下的mysql的C编程,需要安装mysql及mysql的开发包,ubuntu下直接apt-get install libmysql++安装开发包。
#include
相关函数:
MYSQL *mysql_init(MYSQL *);//这里称之为载入函数吧,返回的MYSQL指针要用到后续的函数中
int mysql_options(MYSQL *connection, enum option_to_set,const char *argument);//设置MYSQL*的一些属性,比如超时时间等
MYSQL*mysql_real_connect(MYSQL *connection,const char *server_host,const char *sql_user_name,const char *sql_password,const char *db_name,
unsignedint port_number,//置0连接默认端口,一般为3306
const char *unix_socket_name,//NULL
unsigned int flags);//无另外属性时置0//连接函数
void mysql_close(MYSQL *connection);//关闭连接
unsignedint mysql_errno(MYSQL *connection);//返回错误代码
char *mysql_error(MYSQL *connection);//返回错误信息
int mysql_query(MYSQL *connection, const char *query);//执行sql语句
my_ulonglong mysql_affected_rows(MYSQL*connection);//返回执行语句过后受影响的行数
MYSQL_RES*mysql_store_result(MYSQL *connection);//返回执行结果,适用于数据量较小时
my_ulonglong mysql_num_rows(MYSQL_RES*result);//返回上面函数返回结果的行数
MYSQL_ROW mysql_fetch_row(MYSQL_RES*result);//抽取一条记录,返回NULL时表示抽取完记录或者错误
void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);//调整数据位置,offset为0时,下次调用mysql_fetch_row将返回result第一条记录
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES*result);//返回当前的位置
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES*result, MYSQL_ROW_OFFSET offset);//移动数据位置,并返回先前的位置,可以和上一个函数结合使用
void mysql_free_result(MYSQL_RES *result);//释放result空间
MYSQL_RES*mysql_use_result(MYSQL *connection);//返回执行结果,适用于数据量较大时
unsignedint mysql_field_count(MYSQL *connection);//返回查询结果中的列数(column数)
MYSQL_FIELD*mysql_fetch_field(MYSQL_RES *result);//获得查询结果中的列名等信息(表头信息)
例子:
创建测试数据库
mysql> create database test;
创建用户
mysql> grant all on test.* to [email protected]‘localhost‘ identified by ‘test‘;
sql文件:
--
--Create the table children--CREATE TABLEchildren (
childnoint(11) NOT NULLauto_increment,
fnamevarchar(30),
ageint(11),PRIMARY KEY(childno)
);--
--Populate the table ‘children’--
INSERT INTO children(childno,fname,age) VALUES(1,‘Jenny‘,21);INSERT INTO children(childno,fname,age) VALUES(2,‘Andrew‘,17);INSERT INTO children(childno,fname,age) VALUES(3,‘Gavin‘,8);INSERT INTO children(childno,fname,age) VALUES(4,‘Duncan‘,6);INSERT INTO children(childno,fname,age) VALUES(5,‘Emma‘,4);INSERT INTO children(childno,fname,age) VALUES(6,‘Alex‘,15);INSERT INTO children(childno,fname,age) VALUES(7,‘Adrian‘,9);
导入sql文件:
mysql -u test --password=test test
导入后的情况:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| children |
+----------------+
1 row in set (0.00 sec)
mysql> select * from children;
+---------+--------+------+
| childno | fname | age |
+---------+--------+------+
| 1 | Jenny | 21 |
| 2 | Andrew | 17 |
| 3 | Gavin | 8 |
| 4 | Duncan | 6 |
| 5 | Emma | 4 |
| 6 | Alex | 15 |
| 7 | Adrian | 9 |
+---------+--------+------+
7 rows in set (0.00 sec)
C代码:
#include #include#includeMYSQL*mysql_main;
MYSQL_RES*res_ptr;
MYSQL_ROW sqlrow;voiddisplay_header();voiddisplay_row();int main(int argc,char *argv[])
{intres;int first_row = 1;
mysql_main=mysql_init(NULL);if(!mysql_main)
{
fprintf(stderr,"mysql_init failed\n");returnEXIT_FAILURE;
}
mysql_main= mysql_real_connect(mysql_main,"localhost","test","test","test",0,NULL,0);if(mysql_main)
{
printf("Connection success:\n");
res= mysql_query(mysql_main,"SELECT childno,fname,age FROM children WHERE age>5");if(res)
{
fprintf(stderr,"SELECT error: %s\n",mysql_error(mysql_main));
}else{
res_ptr=mysql_use_result(mysql_main);if(res_ptr)
{while((sqlrow =mysql_fetch_row(res_ptr)))
{if(first_row)
{
display_header();
first_row= 0;
}
display_row();
}
}
}
}else{
printf("Connection failed\n");
}
mysql_close(mysql_main);returnEXIT_SUCCESS;
}voiddisplay_header()
{
MYSQL_FIELD*field_ptr;
printf("Column details:\n");while((field_ptr = mysql_fetch_field(res_ptr))!=NULL)
{
printf("\t Name: %s\n",field_ptr->name);
printf("\t Type:");if(IS_NUM(field_ptr->type))
{
printf("Numeric filed\n");
}else{switch(field_ptr->type)
{caseFIELD_TYPE_VAR_STRING:
printf("VARCHAR\n");break;caseFIELD_TYPE_LONG:
printf("LONG\n");break;default:
printf("Type is %d,check in mysql_com.h\n",field_ptr->type);
}
}
printf("\t MAX width %ld\n",field_ptr->length);if(field_ptr->flags&AUTO_INCREMENT_FLAG)
printf("\t Auto increments\n");
printf("\n");
}
}voiddisplay_row()
{
unsignedintfield_count;
field_count= 0;while(field_count
{if(sqlrow[field_count]) printf("%s",sqlrow[field_count]);else printf("NULL");
field_count++;
}
printf("\n");
}
gcc -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient -o mysqltest mysqltest.c
./test
结果如下:
Connection success:
Column details:
Name: childno
Type: Numeric filed
MAX width 11
Auto increments
Name: fname
Type: VARCHAR
MAX width 30
Name: age
Type: Numeric filed
MAX width 11
1 Jenny 21
2 Andrew 17
3 Gavin 8
4 Duncan 6
6 Alex 15
7 Adrian 9