c++使用Mysql的详解及常用操作示例

ubuntu14.04命令安装mysql
MYSQL 基础知识之API解析

运行环境：ubuntu14.04
c++版本： c++11
编译器：QT
QMake version 3.0
Using Qt version 5.2.1 in /usr/lib/x86_64-linux-gnu
Mysql版本：mysql Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (x86_64) using readline 6.3

1 pro文件

TEMPLATE = app
CONFIG += console c++11
CONFIG -= app_bundle
CONFIG -= qt

LIBS += -L/usr/local/lib -lmysqlclient

SOURCES += main.cpp

2 代码实例

2.1 实例1 —— 查询

• 示例表：
  
• 源码：

#include <iostream>
#include <vector>
#include "mysql/mysql.h"
using namespace std;

int main()
{
    string strSelect = "select * from repositories";

    MYSQL *pDatabase = mysql_init(nullptr);
    MYSQL *pConnect = mysql_real_connect(pDatabase, "127.0.0.1", "root", 
    "yourpassword", "vrsmgr",3306, nullptr, 0);

    int iQueryRet = mysql_query(pConnect, strSelect.c_str());
    cout << "iRet = " << iQueryRet << endl;

    MYSQL_RES *pRes = mysql_store_result(pConnect);
    if(nullptr != pRes)
    {
        int iRowCount = mysql_num_rows(pRes);
        cout << "row count = " << iRowCount << endl;

        int iColCount = mysql_num_fields(pRes);
        cout << "col count = " << iColCount << endl;

        MYSQL_FIELD *pField = nullptr;
        vector<string> vctFields;
        while (pField = mysql_fetch_field(pRes) )
        {
            cout << pField->name << "\t";
            vctFields.push_back(pField->name);
        }

        MYSQL_ROW pQueryRes;
        int iCurRow = 0;
        while (pQueryRes = mysql_fetch_row(pRes) )
        {
            cout << "\nrow " << ++iCurRow << " :" << endl;
            for(int i = 0; i < iColCount; ++i)
            {
                if(nullptr == pQueryRes[i])
                {
                    cout << vctFields[i] << " = " << "null" << endl;
                }
                else
                {
                    cout << vctFields[i] << " = " << pQueryRes[i] << endl;
                }
            }
        }

        mysql_free_result(pRes);
    }
    mysql_close(pConnect);
    
    getchar();
    return 0;
}

• 解析：
  1~4：头文件及使用std命名空间
  8：定义sql查询语句
  10：初始化MYSQL对象
  11~12：用root用户连接本地服务器的vrsmgr数据库
  14：查询数据库
  17：将查询的全部结果读取到客户端，将结果置于返回值pRes中
  20：获取结果集中数据的行数
  23：获取结果集中数据的列数
  28：循环获取结果集中数据的列信息
  31：将列的字段名存在vctFields中
  36：循环获取结果集中每一行的数据信息
  39：循环获取每一行的每一列数据内容
  52：释放为结果集分配的内存
  54：关闭前面打开的连接
• 运行结果：

iRet = 0
row count = 2
col count = 13
id	name	description	author	trunkId
status	version	date	tableId	revision
private_flag	delete_flag	property_file_id	
row 1 :
id = 1
name = test1
description = null
author = null
trunkId = 2
status = 3
version = 1.0.1
date = null
tableId = 4
revision = null
private_flag = 5
delete_flag = null
property_file_id = null

row 2 :
id = 11
name = test11
description = null
author = null
trunkId = 22
status = 33
version = 1.0.2
date = null
tableId = 44
revision = null
private_flag = 0
delete_flag = null
property_file_id = null