C++ 操作MySql数据库实例讲解

 

1） 安装mysql数据库
使用Windows的朋友们安装过程就不用多说了。由于鄙人所使用的是Fedora，就以Feodra为例了。其余的安装过程类似，不多做解释，这不是重点。
因为鄙人对于msql而言是个新手，所以采用yum命令安装。
root@XXX# yum install mysql 
root@XXX# yum install mysql-server 
root@XXX# yum install mysql-devel 
root@XXX# chgrp -R mysql /var/lib/mysql 
root@XXX# chmod -R 770 /var/lib/mysql 
root@XXX# service mysqld start

2） 修改mysql 中root用户密码
安装mysql的时候默认的root是没有密码的，这也无所谓了，但是修改root用户密码的时候就出现：

解决办法：
修改mysql登录设置 vi /etc/my.cnf
在[mysqld]段中加入  skip-grant-tables
保存之后重启mysql服务
service mysqld restart
或者直接service mysqld stop
重启mysql服务之后修改mysql密码
[root@XXX]# mysql
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 3 to server version: 3.23.56

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

mysql> USE mysql ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user SET Password = password ( 'yourpassword' ) WHERE User = 'root' ; //更改密码
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> flush privileges ;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
然后还原my.cnf就行了！！！

3） 创建一个简单的表mytest
会点sql的就行了。
mysql> create database mytest;
mysql> use mytest;
mysql> create table student
>(
 id int primary key,
 Name nvarchar(20),
 Sex nvarchar(1),
 Score smallint
>);
接下来向数据表中插入一些数据。
Insert student(id, name, sex, score) values(1, ‘terry’, ‘男’, 90);
Insert student(id, name, sex, score) values(1, ‘hali’, ‘女’, 90);
4） 数据库操作类（mysql 版,该类整理自互联网)

/*    * MySQLManager.h    *    *    Created on: 8.18, 2012   *            Author: Terry*/#ifndef MYSQLMANAGER_H_    #define MYSQLMANAGER_H_     #include <mysql.h>    #include <string>    #include <iostream>    #include <vector>    #include <string.h>    using namespace std;    class MySQLManager    {    public:            /*             * Init MySQL             * @param hosts:         Host IP address             * @param userName:        Login UserName             * @param password:        Login Password             * @param dbName:        Database Name             * @param port:                Host listen port number             */            MySQLManager(std::string hosts, std::string userName, std::string password, std::string dbName, unsigned int port);            ~MySQLManager();            void initConnection();            /*             * Making query from database             * @param mysql:        MySQL Object             * @param sql:                Running SQL command             */            bool runSQLCommand(std::string sql);        /*         * 执行插入语句         * @param sql: 执行的SQL语句         * @return: 受影响的行数           */        unsigned int insert(std::string sql);        /**             * Destroy MySQL object             * @param mysql                MySQL object             */            void destroyConnection();            bool getConnectionStatus();            vector< vector<string> > getResult();    protected:            void setUserName(std::string userName);            void setHosts(std::string hosts);            void setPassword(std::string password);            void setDBName(std::string dbName);            void setPort(unsigned int port);    private:            bool IsConnected;            vector< vector<string> > resultList;            MYSQL mySQLClient;            unsigned int DEFAULTPORT;            char * HOSTS;            char * USERNAME;            char * PASSWORD;            char * DBNAME;    };    #endif /* MYSQLMANAGER_H_ */ 

/*    * MySQLManager.cpp    *    *    Created on:8. 18, 2012    *            Author: Terry*/    #include "MySQLManager.h"    MySQLManager::MySQLManager(string hosts, string userName, string password, string dbName, unsigned int port)    {            IsConnected = false;            this ->setHosts(hosts);            //    设置主机IP地址            this ->setUserName(userName);            //    设置登录用户名            this ->setPassword(password);            //    设置登录密码            this ->setDBName(dbName);            //    设置数据库名            this ->setPort(port);            //    设置端口号    }    MySQLManager::~MySQLManager()    {            this ->destroyConnection();    }    void MySQLManager::setDBName(string dbName)    {            if ( dbName.empty() )            {//        用户没有指定数据库名                    std::cout << "DBName is null! Used default value: mysql" << std::endl;                    this ->DBNAME = new char[5];                    strcpy(this ->DBNAME, "mysql");            }            else            {                    this ->DBNAME = new char[dbName.length()];                    strcpy(this ->DBNAME, dbName.c_str());            }    }    void MySQLManager::setHosts(string hosts)    {            if ( hosts.empty() )            {//    用户没有指定数据库IP地址                    std::cout << "Hosts is null! Used default value: localhost" << std::endl;                    this ->HOSTS = new char[9];                    strcpy(this ->HOSTS, "localhost");            }            else            {                    this ->HOSTS = new char[hosts.length()];                    strcpy(this ->HOSTS, hosts.c_str());            }    }    void MySQLManager::setPassword(string password)    {//    用户没有指定密码            if ( password.empty() )            {                    std::cout << "Password is null! Used default value: " << std::endl;                    this ->PASSWORD = new char[1];                    strcpy(this ->PASSWORD, "");            }            else            {                    this ->PASSWORD = new char[password.length()];                    strcpy(this ->PASSWORD, password.c_str());            }    }    void MySQLManager::setPort(unsigned int port)    {//    用户没有指定端口号，使用默认端口号            if ( port <= 0 )            {                    std::cout << "Port number is null! Used default value: 0" << std::endl;                    this->DEFAULTPORT = 0;        }            else            {                    this ->DEFAULTPORT = port;            }    }    void MySQLManager::setUserName(string userName)    {//    用户没有指定登录用户名            if ( userName.empty() )            {                    std::cout << "UserName is null! Used default value: root" << std::endl;                    this ->USERNAME = new char[4];                    strcpy(this ->USERNAME, "root");            }            else            {                    this ->USERNAME = new char[userName.length()];                    strcpy(this ->USERNAME, userName.c_str());            }    }    void MySQLManager::initConnection()    {            if ( IsConnected )            {//    已经连接到服务器                    std::cout << "Is connected to server!" <<std::endl;                    return;            }         mysql_init(&mySQLClient);//    初始化相关对象            if ( !mysql_real_connect( &mySQLClient, HOSTS, USERNAME, PASSWORD, DBNAME, DEFAULTPORT, NULL, 0) )            {//    连接到服务器                cout << "HHHHHHHHHHHHH"<<endl;                std::cout << "Error connection to database: \n" << mysql_error(&mySQLClient) << std::endl;            }            IsConnected = true;//    修改连接标识    }    bool MySQLManager::runSQLCommand(string sql)    {            if ( !IsConnected )            {//    没有连接到服务器                    std::cout << "Not connect to database!" << std::endl;                    return false;            }            if ( sql.empty() )            {//    SQL语句为空                    std::cout << "SQL is null!" << std::endl;                    return false;            }            MYSQL_RES *res;            MYSQL_ROW row;            unsigned int i,j = 0;            i = mysql_real_query(&mySQLClient,sql.c_str(),(unsigned int)strlen(sql.c_str()));//    执行查询            if ( i < 0 )            {                    std::cout << "Error query from database: \n" << mysql_error(&mySQLClient) << std::endl;                    return false;            }            res = mysql_store_result(&mySQLClient);            vector<string> objectValue;            while( (row = mysql_fetch_row(res)) )            {//    遍历结果集                    objectValue.clear();                    for ( j = 0 ; j < mysql_num_fields(res) ; j++ )                    {                            objectValue.push_back(row[j]);                    }                    this ->resultList.push_back(objectValue);            }            mysql_free_result(res);         //free result after you get the result            return true;    }unsigned int MySQLManager::insert(std::string sql){    if(!isConnected) {        cout << "" << endl;        return -1;    }    if(sql.empty()){        cout << "sql is null " << endl;        return -1;    }    int rows = -1;    int res = mysql_query(&mySQLClient, sql.c_str());    if(res >= 0){        // 返回受影响的行数        rows = mysql_affected_rows(&mySQLClient);        cout << "Inserted "<< rows << " rows\n";        return rows;    } else {        cout << "Insert error " << mysql_errno(&mySQLClient) << "," << mysql_error(&mySQLClient) << endl;        return -1;    }}vector< vector<string> > MySQLManager::getResult()    {            return resultList;    }    void MySQLManager::destroyConnection()    {            mysql_close(&mySQLClient);            this ->IsConnected = false;    }    bool MySQLManager::getConnectionStatus()    {            return IsConnected;    } 

　下面是测试主程序main.cc：

?

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

#include "MySQLManager.h"

#include <iostream>

#include <vector>

usingnamespace std;

int main()

{

    MySQLManager *mysql =new MySQLManager("127.0.0.1","root", "xufeiyang","mytest", (unsignedint)3306);

    mysql->initConnection();

    if(mysql->getConnectionStatus())

    {

        if(mysql->runSQLCommand("select * from student"))

        {

            vector<vector<std::string> > result = mysql->getResult();

            for(auto & vec : result)

            {

                for(auto &str : vec) {

                                    cout << str.c_str() << " ";

                   }

                cout << endl;

            }

        }

        else

            cout << "执行失败" << endl;

    }

    else

        cout <<"连接未建立" << endl;

       

    return0;

}

　　

程序结果是：

不幸的是：修改了数据库中的默认编码是utf8，C++中的使用的string，结果书出来遇见汉字就是个？，尝试了C++11中的宽字符和本地化解决问题，但是效果不好，依然显示不出来，希望有办法解决的前辈留个话，谢谢了。

期待下一次尝试操作Oracle。