还是我的抓取小程序需要用到mysql
本来想用mysql++的,我在xcode上没有配置好~~
所以简单的使用下mysql库,实现sql的增删查
c的api支持的数据类型
MYSQL
该结构代表1个数据库连接的句柄。几乎所有的MySQL函数均使用它。不应尝试拷贝MYSQL结构。不保证这类拷贝结果会有用。
MYSQL_RES
该结构代表返回行的查询结果(SELECT, SHOW, DESCRIBE, EXPLAIN)。在本节的剩余部分,将查询返回的信息称为“结果集”。
MYSQL_ROW
这是1行数据的“类型安全”表示。它目前是按照计数字节字符串的数组实施的。(如果字段值可能包含二进制数据,不能将其当作由Null终结的字符串对待,这是因为这类值可能会包含Null字节)。行是通过调用mysql_fetch_row()获得的。
MYSQL_FIELD
该结构包含关于字段的信息,如字段名、类型和大小。这里详细介绍了其成员。通过重复调用mysql_fetch_field(),可为每个字段获得MYSQL_FIELD结构。字段值不是该结构的组成部份,它们包含在MYSQL_ROW结构中。
MYSQL_FIELD_OFFSET
这是MySQL字段列表偏移量的“类型安全”表示(由mysql_field_seek()使用)。偏移量是行内的字段编号,从0开始。
my_ulonglong
用于行数以及mysql_affected_rows()、mysql_num_rows()和mysql_insert_id()的类型。该类型提供的范围为0~1.84e19。
在某些系统上,不能打印类型my_ulonglong的值。要想打印这类值,请将其转换为无符号长整数类型并使用%lu打印格式,例如:
printf ("Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));
详细的函数说明大家可以看看官网的说明
http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/apis.html#c-api-functions
当然c的mysql api也支持预处理数据,这里我暂时没用用到
mysql_stmt_init()初始化函数返回的MYSQL_STMT语句处理程序数据结构。对于多次执行的语句,预处理执行是一种有效的方式。首先对语句进行解析,为执行作好准备。接下来,在以后使用初始化函数返回的语句句柄执行一次或多次。
对于多次执行的语句,预处理执行比直接执行快,主要原因在于,仅对查询执行一次解析操作。在直接执行的情况下,每次执行语句时,均将进行查询。此外,由于每次执行预处理语句时仅需发送参数的数据,从而减少了网络通信量。
预处理语句的函数
MYSQL_STMT *mysql_stmt_init(MYSQL *mysql)
创建MYSQL_STMT句柄。对于该句柄,应使用mysql_stmt_close(MYSQL_STMT *)释放
int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length)
给定mysql_stmt_init()返回的语句句柄,准备字符串查询指向的SQL语句,并返回状态值。字符串长度应由“length”参量给出
my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind)
用于为SQL语句中的参数标记符绑定数据
my_bool mysql_stmt_bind_result(MYSQL_STMT *stmt, MYSQL_BIND *bind)
mysql_stmt_bind_result()用于将结果集中的列与数据缓冲和长度缓冲关联(绑定)起来
int mysql_stmt_execute(MYSQL_STMT *stmt)
mysql_stmt_execute()执行与语句句柄相关的预处理查询
int mysql_stmt_store_result(MYSQL_STMT *stmt)
以便后续的mysql_stmt_fetch()调用能返回缓冲数据
int mysql_stmt_fetch(MYSQL_STMT *stmt)
mysql_stmt_fetch()返回结果集中的下一行
my_bool mysql_stmt_close(MYSQL_STMT *)
关闭预处理语句
以下我用两种方式实现了插入数据和获取数据
#include "pcre.h"
#include <stdio.h>
#include <string.h>
#include <regex.h>
#include <iostream>
#include <fstream>
#include <time.h>
namespace mymysql {
#include <mysql.h>
}
#define HOST "localhost"
#define PORT 3306
#define USER "root"
#define PASSWORD "123456"
#define DATABASE "test"
#define CHARSET "utf8"
#define OVECCOUNT 256
using namespace std;
int tpcre();
int tmysql();
int tmysqlpre();
void printMysqlError(mymysql::MYSQL *con,const char*msg){
cout << msg << mymysql::mysql_errno(con)<<":"<< mymysql::mysql_error(con)<< endl;
}
int main(int argc, char ** argv)
{
//tpcre();
//tmysql();
tmysqlpre();
return 0;
}
int tmysql(){
//实现mysql的增删改查
mymysql::MYSQL con;
//初始化
mymysql::mysql_init(&con);
//基础设置
int timeout = 3;
//超时设置
if(mymysql::mysql_options(&con, mymysql::MYSQL_OPT_CONNECT_TIMEOUT, &timeout)!=0){
printMysqlError(&con,"设置超时失败");
exit(1);
}
//压缩设置
if(mymysql::mysql_options(&con,mymysql::MYSQL_OPT_COMPRESS,0)!=0){
printMysqlError(&con,"设置压缩失败");
exit(1);
}
//连接数据库
if(!mymysql::mysql_real_connect(&con, HOST, USER, PASSWORD, DATABASE, PORT, NULL, 0)){
printMysqlError(&con,"连接失败");
exit(1);
}
//编码
if(mymysql::mysql_set_character_set(&con, CHARSET)!=0){
printMysqlError(&con,"设置编码失败");
exit(1);
}
time_t t1;
time(&t1);
//添加数据
char insertSql[200];
sprintf(insertSql, "insert into message(name,msg,email,mtime)values('tom','xx000xx0x0','123@tom.com',%d)",(int) t1);
;
if(mymysql::mysql_query(&con, insertSql) == 0){
cout << "插入成功"<<endl;
}else{
cout << "插入失败"<<endl;
}
//获取插入后的id
long msgId;
if( (msgId=(long)mymysql::mysql_insert_id(&con)) != 0){
cout << "msg_id="<<msgId<<endl;
}
//删除数据
char deleteSql[100];
sprintf(deleteSql, "delete from message where id=%ld",msgId);
if( mymysql::mysql_query(&con, deleteSql)==0){
cout << "删除成功"<<endl;
}else{
cout << "删除成功"<<endl;
}
//获取数据
char selectSql[100] = "select * from message";
mymysql::MYSQL_RES *result = NULL;
mymysql::MYSQL_ROW row;
uint i,num_fields;
if(mymysql::mysql_query(&con, selectSql) == 0){
result = mymysql::mysql_store_result(&con);
if(result == NULL){
printMysqlError(&con, "结果集为空");
}else{
num_fields = mysql_num_fields(result);
while((row = mymysql::mysql_fetch_row(result))){
for(i=0;i<num_fields;i++){
if(row[i] != NULL){
cout << row[i];
}else{
cout << "NULL";
}
cout << ",";
}
cout << endl;
}
if(mymysql::mysql_error(&con) != 0){
cout << "fetch data:"<< mymysql::mysql_num_rows(result)<<endl;
}
mymysql::mysql_free_result(result);
}
}else{
printMysqlError(&con, "查询失败");
}
//查询数据
mysql_close(&con);
return 0;
}
int tmysqlpre(){
//实现mysql的增删改查
mymysql::MYSQL con;
//初始化
mymysql::mysql_init(&con);
//基础设置
int timeout = 3;
//超时设置
if(mymysql::mysql_options(&con, mymysql::MYSQL_OPT_CONNECT_TIMEOUT, &timeout)!=0){
printMysqlError(&con,"设置超时失败");
exit(1);
}
//压缩设置
if(mymysql::mysql_options(&con,mymysql::MYSQL_OPT_COMPRESS,0)!=0){
printMysqlError(&con,"设置压缩失败");
exit(1);
}
//连接数据库
if(!mymysql::mysql_real_connect(&con, HOST, USER, PASSWORD, DATABASE, PORT, NULL, 0)){
printMysqlError(&con,"连接失败");
exit(1);
}
//编码
if(mymysql::mysql_set_character_set(&con, CHARSET)!=0){
printMysqlError(&con,"设置编码失败");
exit(1);
}
//初始化
mymysql::MYSQL_STMT *stmt = mymysql::mysql_stmt_init(&con);
//增加
char insetSql[200]= "insert into message(name,msg,email,mtime)values(?,?,?,?)";
if(mymysql::mysql_stmt_prepare(stmt, insetSql, strlen(insetSql)) != 0){
printMysqlError(&con,"stmt pre失败");
exit(1);
}
time_t t1;
time(&t1);
long mtime = (long) t1;
char name[10] = "tom";
long nameLen = strlen(name);
char msg[20] = "xx000xx0x0";
long msgLen = strlen(msg);
char email[20] = "123@tom.com";
long emailLen = strlen(email);
mymysql::MYSQL_BIND bindData[4];
memset(bindData, 0, sizeof(bindData));
bindData[0].buffer_type =mymysql::MYSQL_TYPE_STRING;
bindData[0].buffer = (char *)name;
bindData[0].buffer_length = nameLen;
bindData[0].is_null = 0;
bindData[0].length = (unsigned long*)&nameLen;
bindData[1].buffer_type =mymysql::MYSQL_TYPE_STRING;
bindData[1].buffer = (char *)msg;
bindData[1].buffer_length = msgLen;
bindData[1].is_null = 0;
bindData[1].length = (unsigned long*)&msgLen;
bindData[2].buffer_type =mymysql::MYSQL_TYPE_STRING;
bindData[2].buffer = (char *)email;
bindData[2].buffer_length = emailLen;
bindData[2].is_null = 0;
bindData[2].length = (unsigned long*)&emailLen;
bindData[3].buffer_type =mymysql::MYSQL_TYPE_LONG;
bindData[3].buffer = &mtime;
bindData[3].is_null = 0;
if(mymysql::mysql_stmt_bind_param(stmt, bindData)!= 0){
printMysqlError(&con, "绑定数据失败");
exit(1);
}else{
cout << "绑定成功"<<endl;
}
mymysql::mysql_stmt_execute(stmt);
char selectSql[100] = "select id,name,msg,email from message";
//char selectSql[100] = "select id,name from message";
if(mymysql::mysql_stmt_prepare(stmt, selectSql, strlen(selectSql)) != 0){
printMysqlError(&con,"stmt pre失败");
exit(1);
}
long lId;
char cName[30];
char cMsg[30];
char cEmail[50];
mymysql::MYSQL_BIND selectBindData[4];
memset(selectBindData, 0, sizeof(selectBindData));
selectBindData[0].buffer_type = mymysql::MYSQL_TYPE_LONG;
selectBindData[0].buffer = &lId;
selectBindData[1].buffer_type = mymysql::MYSQL_TYPE_STRING;
selectBindData[1].buffer = cName;
selectBindData[1].buffer_length = sizeof(cName);
selectBindData[2].buffer_type = mymysql::MYSQL_TYPE_STRING;
selectBindData[2].buffer = cMsg;
selectBindData[2].buffer_length = sizeof(cMsg);
selectBindData[3].buffer_type = mymysql::MYSQL_TYPE_STRING;
selectBindData[3].buffer = cEmail;
selectBindData[3].buffer_length = sizeof(cEmail);
if(mymysql::mysql_stmt_bind_result(stmt, selectBindData)!=0){
printMysqlError(&con,"stmt bind result失败");
exit(1);
}
if(mymysql::mysql_stmt_execute(stmt)!=0){
printMysqlError(&con,"stmt execute失败");
exit(1);
}
if(mymysql::mysql_stmt_store_result(stmt)!=0){
printMysqlError(&con,"stmt store失败");
exit(1);
}else{
cout << "store成功" <<endl;
}
while(mymysql::mysql_stmt_fetch(stmt) ==0){
cout << lId << ","<<cName << ","<<cMsg << ","<<cEmail<< endl;
}
mymysql::mysql_stmt_close(stmt);
mymysql::mysql_close(&con);
cout << "end";
return 0;
}
对mysql++有兴趣可以查看http://blog.csdn.net/laoyang360/article/details/52335669