mysql编程C语言vs_mysql

包含头文件：

#inclde “mysql.h”

编译时指定头文件目录和连接库的目录：

-I/usr/include/mysql -L/usr/lib64/mysql

几种结构类型

MYSQL结构体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

42typedef 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;

const 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];

my_bool unused1;

void *unused2, *unused3, *unused4, *unused5;

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;

MYSQL_RES1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17typedef 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;

MYSQL_ROW1typedef char **MYSQL_ROW;

MYSQL_FIELD1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23typedef 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;

编程步骤

初始化

mysql_init(MYSQL *connection)

mysql的初始化函数，如果传入的是NULL,它会返回一个指向新分配的连接句柄指针

如果传递一个已有的结构，它将被重新初始化。如果出错，返回NULL

连接1

2

3

4

5

6

7

8MYSQL *mysql_real_connect(MYSQL *connection,

const char *server_host,

const char *sql_user_name,

const char *sql_password,

const char *db_name,

unsigned int port_number,

const char *unix_socket_name,

unsigned int client_flag);

server_host 可以是主机名，也可以是ip，如果是本机，直接是localhost

sql_user_name 和sql_password 分别为用户名和密码

port_number 和 unix_socket_name 分别默认为0 和NULL 一般是不用变得

client_flag 一般为0，但也可以传入宏值来实现特定的功能

执行mysql语句

int mysql_query(MYSQL *connection, const char *query)

int mysql_real_query(MYSQL *connection, const char *query, unsigned long length)

mysql_query() 预期的语句是指定的，由NULL终结的字符串

mysql_real_query() 预期的是计数字符串

如果字符串中包含的二进制数据其中可能包含NULL字节，就必须使用mysql_real_query();

获得上次执行结果

MYSQL_RES *mysql_store_result(MYSQL *connection)

mysql_store_result是用来存储结果，并返回结构体指针

MYSQL_ROW *mysql_fetch_row(MYSQL_RES *res)

mysql_fetch_row每次调用返回结果的一行，每行也有多个结果，分别为row[0],row[1].

关闭连接

mysql_close(MYSQL *connection)

程序示例

查询表,将结果打印出来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#include "mysql.h"

#define USER "root"

#define PASSWD ""

#define SQLNAME "westos"

#define HOST "localhost"

#define TABNAME "student"

#define CMD_LEN 256

typedef struct person{

char name[16];

char number[16];

char email[32];

}_person;

int (MYSQL *connect_handle, _person *person, int n)

{

char cmd[CMD_LEN];

int i = 0;

for(i = 0; i < n; ++i){

sprintf(cmd, "insert into %s value("%s", "%s", "%s")", TABNAME,

person[i].name, person[i].number, person[i].email);

mysql_query(connect_handle, cmd);

}

return 0;

}

int mysql_show_all(MYSQL *connect_handle)

{

MYSQL_RES *res;

MYSQL_ROW row;

mysql_query(connect_handle, "select * from student");

res = mysql_store_result(connect_handle);

while(row = mysql_fetch_row(res)){

printf("%s %s %sn", row[0], row[1], row[2]);

}

return 0;

}

int main(int argc, char **argv)

{

_person person[10];

MYSQL connect_handle;

MYSQL_RES *res;

MYSQL_ROW row;

int i = 0;

mysql_init(&connect_handle);

mysql_real_connect(&connect_handle, HOST, USER, PASSWD, SQLNAME, 0, NULL, 0);

for(i = 0; i < 2; ++i){

scanf("%s%s%s", person[i].name, person[i].number, person[i].email);

}

mysql_insert(&connect_handle, person, 2);

mysql_show_all(&connect_handle);

mysql_close(&connect_handle);

return 0;

}