注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

老狗的博客

尽管每一步都很微小,但我确认我在进步

 
 
 

日志

 
 
关于我
sky

认真生活,努力工作 热爱技术,关注DB,存储,分布式,中间层,java,c++,php

mysql C API/JDBC  

2012-09-10 16:25:29|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
C API涉及到的结构体

1. MYSQL

a. 最近有个哥们 问了一个非常有意思的问题? 
mysql在执行一个 insert/delete/update语句后,如果要使用mysql_affected_rows(MYSQL*)获取影响的行数,是否要和mysqld再进行一次交互?
答案: 应该是不用,因为MYSQL这个结构中已经包含了affected_rows, 从中取出即可

typedef struct st_mysql
{
NET>-->---net;>--->--->---/* Communication parameters */
unsigned char>*connector_fd;>->---/* ConnectorFd for SSL */
char>->---*host,*user,*passwd,*unix_socket,*server_version,*host_info;
char *info, *db;
struct charset_info_st *charset;
MYSQL_FIELD>--*fields;
MEM_ROOT>-field_alloc;
my_ulonglong affected_rows;
my_ulonglong insert_id;>-->---/* id if insert on table with NEXTNR */
my_ulonglong extra_info;>->---/* Not used */
unsigned long thread_id;>->---/* Id for connection in server */
unsigned long packet_length;
unsigned int>-port;
unsigned long client_flag,server_capabilities;
unsigned int>-protocol_version;
unsigned int>-field_count;
unsigned int >server_status;
unsigned int server_language;
unsigned int>-warning_count;

struct st_mysql_options options;
enum mysql_status status;
my_bool>--free_me;>--->---/* If free in mysql_close */
my_bool>--reconnect;>->---/* set to 1 if automatic reconnect */

/* session-wide random string */
char>- scramble[SCRAMBLE_LENGTH+1];

/*
Set if this is the original connection, not a master or a slave we have
added though mysql_rpl_probe() or mysql_set_master()/ mysql_add_slave()
*/
my_bool rpl_pivot;
/*
Pointers to the master, and the next slave connections, points to
itself if lone connection.
*/
struct st_mysql* master, *next_slave;

struct st_mysql* last_used_slave; /* needed for round-robin slave pick */

/* needed for send/read/store/use result to work correctly with replication */
struct st_mysql* last_used_con;

LIST *stmts; /* list of all statements */
const struct st_mysql_methods *methods;
void *thd;
/*
Points to boolean flag in MYSQL_RES or MYSQL_STMT. We set this flag-
from mysql_stmt_close if close had to cancel result set of this object.
*/
my_bool *unbuffered_fetch_owner;
/* needed for embedded server - no net buffer to store the 'info' */
char *info_buffer;
void *extension;
} MYSQL;



2. MYSQL_RES

typedef struct st_mysql_res {
my_ulonglong row_count;
MYSQL_FIELD>--*fields;
MYSQL_DATA>---*data;
MYSQL_ROWS>---*data_cursor;
unsigned long *lengths;>-->---/* column lengths of current row */
MYSQL>>---*handle;>--->---/* for unbuffered reads */
const struct st_mysql_methods *methods;
MYSQL_ROW>row;>--->--->---/* If unbuffered read */
MYSQL_ROW>current_row;>--->---/* buffer to current row */
MEM_ROOT>-field_alloc;
unsigned int>-field_count, current_field;
my_bool>--eof;>--->--->---/* Used by mysql_fetch_row */
/* mysql_stmt_close() had to cancel this result */
my_bool unbuffered_fetch_cancelled;--
void *extension;
} MYSQL_RES;


3. MYSQL_ROWS

typedef struct st_mysql_rows {
struct st_mysql_rows *next;>-->---/* list of rows */
MYSQL_ROW data;
unsigned long length;
} MYSQL_ROWS;


4. MYSQL_ROW
这个结构代表了数据中的一行

typedef char **MYSQL_ROW;



4. MYSQL_FIELD
这个结构包含在MYSQL_QUERY结构中,代表一行的一列,使用mysql_fetch_field进行获取

typedef struct st_mysql_field {
char *name; /* Name of column */
char *org_name; /* Original column name, if an alias */
char *table; /* Table of column if column was a field */
char *org_table; /* Org table name, if table was an alias */
char *db; /* Database for table */
char *catalog;>--- /* Catalog for table */
char *def; /* Default value (set by mysql_list_fields) */
unsigned long length; /* Width of column (create length) */
unsigned long max_length; /* Max width for selected set */
unsigned int name_length;
unsigned int org_name_length;
unsigned int table_length;
unsigned int org_table_length;
unsigned int db_length;
unsigned int catalog_length;
unsigned int def_length;
unsigned int flags; /* Div flags */
unsigned int decimals; /* Number of decimals in field */
unsigned int charsetnr; /* Character set */
enum enum_field_types type; /* Type of field. See mysql_com.h for types */
void *extension;
} MYSQL_FIELD;



5. my_ulonglong
mysql_affected_rows(),mysql_num_rows(),mysql_insert_id(),返回此类型结果

typedef struct st_mysql_rows {
struct st_mysql_rows *next;>-->---/* list of rows */
MYSQL_ROW data;
unsigned long length;
} MYSQL_ROWS;


6. my_bool

typedef char my_bool;


7. my_socket

typedef int my_socket;


8. MYSQL_ROWS

typedef MYSQL_ROWS *MYSQL_ROW_OFFSET


9. MYSQL_DATA

typedef struct st_mysql_data {
MYSQL_ROWS *data;
struct embedded_query_result *embedded_info;
MEM_ROOT alloc;
my_ulonglong rows;
unsigned int fields;
/* extra info for embedded library */
void *extension;
} MYSQL_DATA;

10. MYSQL_MANAGER

typedef struct st_mysql_manager
{
NET net;
char *host, *user, *passwd;
char *net_buf, *net_buf_pos, *net_data_end;
unsigned int port;
int cmd_status;
int last_errno;
int net_buf_size;
my_bool free_me;
my_bool eof;
char last_error[MAX_MYSQL_MANAGER_ERR];
void *extension;
} MYSQL_MANAGER;


11. MYSQL_PARAMETERS

typedef struct st_mysql_parameters
{
unsigned long *p_max_allowed_packet;
unsigned long *p_net_buffer_length;
void *extension;
} MYSQL_PARAMETERS;


12. 如何编译
标准的mysql安装路径下,你会看到有一个include目录和一个lib目录,目录结构一般如下
/home/mydb/mysql51
include
mysql 
lib
mysql

#include<stdio.h>
#include "my_global.h"
#include "mysql.h"

int main(int argc, char** argv)
{
printf("mysql client version=%s\n", mysql_get_client_info());
return 0;
}


mysql client version=5.1.45

对于上面这段程序,静态编译如下:
gcc -o test_mysql test_mysql.c  -I../include/mysql/ -L ../lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -static

关于编译,可以使用mysql bin目录下的mysql_config --libs_r选项去寻找编译选项

[liudong2@hebe21 bin]$ ./mysql_config --libs_r
-rdynamic -L/usr/home/liudong2/myapp/mysql5145/lib/mysql -lmysqlclient_r -lz -lpthread -lcrypt -lnsl -lm -lpthread

如果没有-static, 则注意在运行的时候通过指定expot LD_LIBRARY_PATH=../lib/mysql/ 指定lib路径

13.  连接数据库并执行query

#include<stdio.h>
#include<stdint.h>

#include "my_global.h"
#include "my_sys.h"
#include "mysql.h"

const char host[] = "127.0.0.1";
const uint32_t port = 7777;
const char *db = NULL;
const char user[] = "root";
const char pwd[] = "123";

int main(int argc, char** argv)
{
int ret = -1;
MYSQL * conn = NULL;
//allocate and initialize a MYSQL object, freed when mysql_close() is called

conn = mysql_init(NULL);
if(conn == NULL)
{
printf("mysql_init fail\n");
exit(2);
}
//connect db
conn = mysql_real_connect(conn, host, user, pwd, db, port, NULL, 0);
if(conn == NULL)
{
printf("connect db fail\n");
mysql_close(conn);
exit(3);
}

ret = mysql_query(conn, "create database test1");
if(ret != 0)
{
printf("create db fail\n");
}

ret = mysql_query(conn, "create table test1.t1(a int,b int)engine=innodb");
if(ret != 0)
{
printf("create table t1 fail\n");
}

ret = mysql_query(conn, "insert into test1.t1(a,b) values(1,2),(3,4),(5,6)");
if(ret != 0)
{
printf("insert fail\n");
}
printf("affected rows=%ld\n", conn->affected_rows);

mysql_close(conn);
return 0;
}



gcc -o test_mysql test_mysql.c -I../include/mysql/ -L ../lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -static


结果:affected rows=3


#include<stdio.h>
#include<stdint.h>

#include "my_global.h"
#include "my_sys.h"
#include "mysql.h"

const char host[] = "127.0.0.1";
const uint32_t port = 7777;
const char *db = NULL;
const char user[] = "root";
const char pwd[] = "123";

int main(int argc, char** argv)
{
int ret = -1;
MYSQL * conn = NULL;
//allocate and initialize a MYSQL object, freed when mysql_close() is called
conn = mysql_init(NULL);
if(conn == NULL)
{
printf("mysql_init fail\n");
exit(2);
}
//connect db
conn = mysql_real_connect(conn, host, user, pwd, db, port, NULL, 0);
if(conn == NULL)
{
printf("connect db fail\n");
mysql_close(conn);
exit(3);
}

ret = mysql_query(conn, "create database test1");
if(ret != 0)
{
printf("create db fail\n");
}

ret = mysql_query(conn, "create table test1.t1(a int,b int)engine=innodb");
if(ret != 0)
{
printf("create table t1 fail\n");
}

ret = mysql_query(conn, "insert into test1.t1(a,b) values(1,2),(3,4),(5,6)");
if(ret != 0)
{
printf("insert fail\n");
}
printf("affected rows=%ld\n", conn->affected_rows);

mysql_close(conn);
return 0;
}


静态编译: gcc -o t1 t1.c -g -I../include/mysql/ -L ../lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -static


结果输出:

1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6



14. 多线程访问
a. 如果使用多线程来访问mysql,必须在编译的时候使用--with-thread-safe-client参数,连接的库应该是libmysqlclient_r.a/so
b.  请注意最好使用一个线程一个连接的形式进行使用,否则容易出core, 建议不要两个线程共享一个链接,一个连接不能同时执行两个query
c. 在 创建 线程前使用mysql_library_init()进行初始化,mysql_library_init()不能并发
d. 在线程中首先使用mysql_init()进行初始化后,然后再创建连接/获取连接,执行查询,返回,并且执行mysql_thread_end() 释放为线程分配的内存,避免内存泄露


#include<stdio.h>
#include<stdint.h>

#include "my_global.h"
#include "my_sys.h"
#include "mysql.h"

const char host[] = "127.0.0.1";
const uint32_t port = 7777;
const char *db = NULL;
const char user[] = "root";
const char pwd[] = "123";
void print_error(MYSQL* conn)
{
uint32_t myerrno = 0;
myerrno = mysql_errno(conn);
if(myerrno == 0)
{
return;
}

const char* myerrmsg = NULL;
myerrmsg = mysql_error(conn);
if(myerrmsg)
{
printf("errno=%u,errmsg=%s\n", myerrno, myerrmsg);
}
}

void *thread_process(void *arg)
{

MYSQL * conn = NULL;
int ret = -1;
//allocate and initialize a MYSQL object, freed when mysql_close() is called
conn = mysql_init(NULL);
if(conn == NULL)
{
printf("mysql_init fail\n");
exit(2);
}
//connect db
conn = mysql_real_connect(conn, host, user, pwd, db, port, NULL, 0);
if(conn == NULL)
{
printf("connect db fail\n");
mysql_close(conn);
exit(3);
}

char *stmt = "select * from test1.t1";
ret = mysql_real_query(conn, stmt, strlen(stmt));
if(ret != 0)
{
printf("execute sql=[%s] fail\n",stmt);
print_error(conn);
exit(4);
}

MYSQL_RES* res = mysql_store_result(conn);
if(res == NULL)
{
printf("store result fail\n");
exit(5);
}

uint64_t row_num = mysql_num_rows(res);
uint32_t field_num = mysql_num_fields(res);

MYSQL_ROW row = NULL;
while(row = mysql_fetch_row(res))
{
int i = 0;
for(i = 0; i < field_num; i++)
{
if(i != 0) printf(",");
printf("%s", row[i]);
if(i == field_num - 1) printf("\n");
}
}

mysql_free_result(res);
mysql_close(conn);

mysql_thread_end();
}
int main(int argc, char** argv)
{
int ret = -1;
ret = mysql_library_init(0, NULL, NULL);
if(ret != 0)
{
printf("library init fail\n");
}

pthread_t tid[3];
int i = 0;
for(i = 0; i < 3; i++)
{
pthread_create(&tid[i], NULL, thread_process, NULL);
}

for(i = 0; i < 3; i++)
{
pthread_join(tid[i], NULL);
}


mysql_library_end();

return 0;
}


gcc -o t2 t2.c -g -I../include/mysql/ -L ../lib/mysql -lmysqlclient_r -lz -lcrypt -lnsl -lm -lpthread -static


//执行结果

1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6
1,2
3,4
5,6


15. api详解
1. MYSQL * STDCALL mysql_init(MYSQL *mysql)

MYSQL * STDCALL
mysql_init(MYSQL *mysql)
{
if (mysql_server_init(0, NULL, NULL))
return 0;
if (!mysql)
{
if (!(mysql=(MYSQL*) my_malloc(sizeof(*mysql),MYF(MY_WME | MY_ZEROFILL))))
{
set_mysql_error(NULL, CR_OUT_OF_MEMORY, unknown_sqlstate);
return 0;
}
mysql->free_me=1;
}
else
bzero((char*) (mysql), sizeof(*(mysql)));
mysql->options.connect_timeout= CONNECT_TIMEOUT;
mysql->last_used_con= mysql->next_slave= mysql->master = mysql;
mysql->charset=default_client_charset_info;
strmov(mysql->net.sqlstate, not_error_sqlstate);

mysql->options.methods_to_use= MYSQL_OPT_GUESS_CONNECTION;
mysql->options.report_data_truncation= TRUE; /* default */

mysql->reconnect= 0;
return mysql;
}

2. mysql_library_init
/usr/home/liudong2/myapp/mysql-5.1.45/include/mysql.h:392:#define mysql_library_init mysql_server_init

int STDCALL mysql_server_init(int argc __attribute__((unused)),
char **argv __attribute__((unused)),
char **groups __attribute__((unused)))
{
int result= 0;
if (!mysql_client_init)
{
mysql_client_init=1;
org_my_init_done=my_init_done;
if (my_init()) /* Will init threads */
return 1;
init_client_errs();
if (!mysql_port)
{
mysql_port = MYSQL_PORT;
{
struct servent *serv_ptr;
char *env;

if ((env = getenv("MYSQL_TCP_PORT")))
mysql_port =(uint) atoi(env);
}
}
if (!mysql_unix_port)
{
char *env;
mysql_unix_port = (char*) MYSQL_UNIX_ADDR;
if ((env = getenv("MYSQL_UNIX_PORT")))
mysql_unix_port = env;
}
mysql_debug(NullS);
(void) signal(SIGPIPE, SIG_IGN);

}
#ifdef THREAD
else
result= (int)my_thread_init(); /* Init if new thread */
#endif
return result;
}


3. my_thread_init()



  评论这张
 
阅读(231)| 评论(1)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018