使用C++操作mysql数据库

话不多说&＃xff0c;直接进入主题。

一&＃xff0e;环境搭建

本文所述是在32位win7&＃xff0c;VS2013&＃xff0c;mysql5.5下的实验情况&＃xff0c;其它环境应该也是大同小异。

首先&＃xff0c;需要安装VS2013和mysql数据库。

然后&＃xff0c;打开VS2013开始配置mysql使用环境。

1、新建win32控制台程序&＃xff0c;此处就不多说了。

2、在VS2013的菜单栏选择“项目——>属性”

3、配置属性——>VC&＃43;&＃43;目录——>包含目录&＃xff08;Include&＃xff09;&＃xff0c;选择MySQL安装路径下的“include”文件夹

——>库目录&＃xff08;Lib&＃xff09;&＃xff0c;选择MySQL安装目录下的lib文件夹

“包含目录”在mysql安装路径的截图如下&＃xff1a;

“库目录”的截图如下&＃xff1a;

4、配置属性——>链接器——>输入——>附加依赖项&＃xff0c;添加“libmysql.lib”

5、把\MySQL\MySQLServer 5.5\lib下的libmysql.dll复制并拷贝到C:\Windows\System32下。(这步如果不设置&＃xff0c;运行时会报错)。

此时环境就配置好了&＃xff01;

二.Mysql.h头文件中常用类型简介

下面就看一下MYSQL_RES&＃xff0c;MYSQL &＃xff0c;MYSQL_ROW &＃xff0c;MYSQL_FIELD 的定义&＃xff1a;

typedef structst_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 structst_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 thisresult */

  my_bool      unbuffered_fetch_cancelled; 

  void *extension;

} MYSQL_RES;

用于保存执行结果的结构体。

typedef structst_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&＃43;1];

  my_bool unused1;

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

  LIST  *stmts;                    /* list of all statements */

  const structst_mysql_methods *methods;

  void *thd;

  /*

    Points toboolean flag in MYSQL_RES  or MYSQL_STMT.We set this flag

    frommysql_stmt_close if close had to cancel result set of this object.

  */

  my_bool *unbuffered_fetch_owner;

  /* needed for embedded server - no netbuffer to store the &＃39;info&＃39; */

  char *info_buffer;

  void *extension;

} MYSQL;

用于保存数据库连接的结构体。

typedef char **MYSQL_ROW;             /* return data as array of strings */

用于保存某一行返回结果的值。

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

包含字段信息的结构&＃xff0c;例如字段名、类型和大小。

三&＃xff0e;函数调用流程及简介

1.      连接mysql数据库

MYSQL *             STDCALL mysql_real_connect(MYSQL *mysql,const char *host,

                                const char *user,

                                const char *passwd,

                                const char *db,

                                unsigned int port,

                                const char *unix_socket,

                                unsigned long clientflag);

2.   执行sql语句

int           STDCALL mysql_query(MYSQL *mysql,const char *q);

3.   获取执行结果

MYSQL_RES *    STDCALL mysql_store_result(MYSQL *mysql);

保存结果集到一个变量。

my_ulonglong STDCALL mysql_num_rows(MYSQL_RES *res);

typedef unsignedlong my_ulonglong;

获得数据的行数

unsigned intSTDCALL mysql_num_fields(MYSQL_RES *res);

获得一行数据中字段的数目。

MYSQL_FIELD *       STDCALL mysql_fetch_field(MYSQL_RES *result);

通过重复调用mysql_fetch_field() 可以对每一列获得MYSQL_FIELD 结构

MYSQL_ROW    STDCALL mysql_fetch_row(MYSQL_RES *result);

typedef char **MYSQL_ROW;             /* return data as array of strings */

从结果集中获取某一行的内容&＃xff0c;可以取到每一行的内容。

4.   释放资源

void              STDCALL mysql_free_result(MYSQL_RES *result);

释放结果集

void STDCALL mysql_close(MYSQL *sock);

关闭数据库连接。

四&＃xff0e;简单应用举例

在第一步建好的工程中加入一个cpp文件&＃xff0c;并添加如下代码&＃xff1a;

#include

#include

#include

#include

#include

using namespace std;

//全局变量

int res &＃61; 0;

MYSQL_RES *result &＃61; NULL;

MYSQL mysql;

MYSQL_ROW sql_row;

MYSQL_FIELD *fd &＃61; NULL;

char column[20][20] &＃61; { 0 };

//用于查询数据

int selectData(char * SQL,char * Msg);

int main()

{

       mysql_init(&mysql); //初始化mysql结构

       if(!mysql_real_connect(&mysql, "localhost", "用户名","密码","数据库名", 3306,NULL, 0))

       {

              cout <<"连接数据库时发生错误!"<

       }

       else

       {

              cout <<"连接数据库成功!" <

              //设置编码格式,否则在cmd下无法显示中文&＃xff1b;并且插入的中文也会显示乱码

              mysql_query(&mysql, "SET NAMES GBK");

              char *msg &＃61; NULL;

              //查询数据

              char select[] &＃61; "select * from user";

              if (selectData(select, msg) !&＃61; 0)

              {

                     cout <

              }

       }

       if(result !&＃61; NULL)mysql_free_result(result);//释放结果资源

       mysql_close(&mysql); //释放数据库

       system("pause");

       return0;

}

//查询数据实现

int selectData(char *SQL, char * Msg)

{

       res &＃61; mysql_query(&mysql, SQL);//查询

       if(!res)

       {

              result &＃61; mysql_store_result(&mysql);//保存查询到的数据到result

              if (result)

              {

                     int i, j;

                     cout <<"number of result:"<<(unsignedlong)mysql_num_rows(result)<

                     //获取列名

                     for (i &＃61; 0; fd &＃61; mysql_fetch_field(result); i&＃43;&＃43;)

                     {

                            strcpy(column[i], fd->name);

                     }

                     j &＃61; mysql_num_fields(result);

                     //输出列名

                     for (i &＃61; 0; i

                     {

                            cout <ios::left) <

                            //printf("%s\t", column[i]);

                     }

                     cout <

                     //获取具体的数据

                     while (sql_row &＃61; mysql_fetch_row(result))

                     {

                            for (i &＃61; 0; i

                            {

                                   cout <ios::left) <

                                   //printf("%s\t",sql_row[i]);

                            }

                            cout <

                     }

              }

              return 0;

       }

       else

       {

              Msg &＃61;  "query sqlfailed!";

       }

       return1;

}

执行结果如下图所示&＃xff1a;

说明&＃xff1a;我所连接的数据库中相应数据表的数据如下&＃xff1a;

相信小伙伴们应该都学会了吧&＃xff01;