Ubuntu系统MySQL嵌入式开发入门（C++）

最近在学Ubuntu软件开发，需要嵌入式数据库，作为备忘录，也希望对初学者有所帮助

首先安装MySQL

sudo apt-get install mysql-server mysql-client

再安装G++依赖的mysql.h的依赖包，见另一篇博客：fatal error： mysql.h:没有那个文件或目录

sudo apt-get install libmysqlclient-dev

运行MySQL

mysql -u root -p

创建一个简单的数据库

mysql>create database test; mysql>use test;    //切换到test数据库中mysql> create table test(name varchar(255),num int(10) ); //创建一个叫test的表mysql>show create table test;  //显示刚才创建的表信息mysql> select * from test; 　　//查询test表中数据mysql>quit

用vim简单写一个test.cpp 的测试文件

#include <stdio.h>#include <mysql.h>int main(int argc,char *argv[]){    MYSQL conn;    int res;    mysql_init(&conn);    if(mysql_real_connect(&conn,"localhost","root","8520","test",0,NULL,CLIENT_FOUND_ROWS))    //"root":数据库管理员 "":root密码 "test":数据库的名字    {        printf("connect success!\n");        res=mysql_query(&conn,"insert into test values('user','123456')");        if(res)        {            printf("error\n");        }        else        {            printf("OK\n");        }        mysql_close(&conn);    }    else    {        printf("Database can not connect!!!!");    }    return 0;}

编译命令：注意`是跟波浪线在一起的那个符号

g++ test.cpp `mysql_config --cflags --libs` -o test

运行test

./test

就可以把这条数据存储进MySQL了

/×××××××××××××××××××××××××分割线×××××××××××××××××××××××××××××××××××××/

接下来是多文件，多线程访问数据库。目前还有bug。。。数据库总报错，连接不上

/*****************main.cc******************/#include "MySQLManager.h"#include <iostream>#include <vector>using namespace std;int main(){    MySQLManager *mysql = new MySQLManager("127.0.0.1", "root", "xufeiyang", "mytest", (unsigned int)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;    return 0;}

/*    
* MySQLManager.h    
*/
#ifndef MYSQLMANAGER_H_    
#define MYSQLMANAGER_H_     
#include <mysql.h>
#include <string>
#include <iostream>
#include <vector>
#include <string.h>
using namespace std;
class MySQLManager    
{
public:
        MySQLManager(std::string hosts, std::string userName, std::string password, std::string dbName, unsigned int port);
        ~MySQLManager();
        void initConnection();
        bool runSQLCommand(std::string sql);
        unsigned int insert(std::string sql);
        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.cc***************************************/#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;    }

编译

g++ main.cc MySQLManager.cc -o main -std=c++11 -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient