ubantu数据库安装以及使用——mysql+redis

mysql

安装mysql

ubuntu 安装 MySql_ubuntu安装mysql-CSDN博客

Ubuntu 安装 MySQL 密码设置_ubuntu安装mysql后设置密码-CSDN博客

service mysql restart1

C/C++连接数据库

C/C++ 连接访问 MySQL数据库_c++ mysql-CSDN博客

ubuntu安装mysql的c++开发环境_ubuntu 搭建mysql c++开发环境-CSDN博客

安装C版本mysql编写工具

sudo apt install libmysqlclient-dev

编译命令

g++ main.cpp -lmysqlclient -o main

代码示例

#include
#include
#include
using namespace std;

int main(int argc,char* argv[]){
	MYSQL conn;
	int res;
	mysql_init(&conn);
	if(mysql_real_connect(&conn,"127.0.0.1","root","Yaoaolong111","testdb",0,NULL,CLIENT_FOUND_ROWS)){
		cout<<"connect success"<<endl;
 
		/*************select sql example start************************/
	       	string sql = string("select * from ").append("test_table");
    		mysql_query(&conn,sql.c_str());
    		//收集查询得到的信息
    		MYSQL_RES *result = NULL;
    		result = mysql_store_result(&conn);
    		//得到查询到的数据条数
    		int row_count = mysql_num_rows(result);
    		cout<<"all data number: "<< row_count << endl;
    		//得到字段的个数和字段的名字
    		int field_count = mysql_num_fields(result);
    		cout << "filed count: " <<field_count << endl;
    		//得到所有字段名
    		MYSQL_FIELD *field = NULL;
    		for(int i=0;i<field_count;++i){
        		field = mysql_fetch_field_direct(result,i);
        		cout<<field->name<<"\t";
   		}
    		cout<< endl;
    		MYSQL_ROW row = NULL;
    		row = mysql_fetch_row(result);
    		while(NULL != row){
        		for(int i=0; i<field_count;++i){
            			cout <<row[i]<<"\t";
       			}
        		cout<<endl;
       		 	row = mysql_fetch_row(result);
    		}
    		mysql_free_result(result);
		mysql_close(&conn);
	}
	else{
		cout<<"connect failed!"<<endl;
	}
	
	return 0;
}

安装Connector/C++

sudo apt-get install libmysqlcppconn-dev

编译命令

g++ your_program.cpp -o your_program -lmysqlcppconn

基本语法

1. 创建操作句柄

   具体使用

   sql::mysql::MySQL_Driver *driver = sql::mysql::get_mysql_driver_instance();
   sql::Connection *con = driver->connect("tcp://127.0.0.1:3306", "username", "password");
   con->setSchema("database");
   

2. 查询命令

   executeQuery 查询语句

   executeUpdate 数据更新

   execute 其他执行语句

   具体使用

   sql::Statement *stmt = con->createStatement();
   // 查询
   string sql = "select * from table_name";
   sql::ResultSet *res = stmt->executeQuery(sql);
   while(res->next()){
       // res 会自动更新
       // res 会从第一个开始，遍历到最后一个
       int id = res->getInt("id");// 输入字段名，get+类型一定要正确
       string name = res->getString("name");
   }
   
   
   // 更新
   string sql = "insert into table_name values(1,2,3...具体数据)";// sql语句
   stmt->executeUpdate(sql);
   
   

3. 获取数据

   形如getInt,getString

4. 开启事务

   bool SaveHtmlToMySql(const std::vector<DocInfo_t>& result,sql::Connection* con,sql::Statement* stmt)
   {
       con->setAutoCommit(false);
       try{
           int cnt = 0;
           for(auto& doc:result){
               // ...
               stmt->executeUpdate();
           }
           con->commit();
       }catch(sql::SQLException &e){
           con->setAutoCommit(true);
           con->rollback();
           std::cout << e.what() << std::endl;
           return false;
       }
   
       return true;
   }
   

代码样例

// 代码示例
#include 
#include 
#include 
#include 
#include 

int main() {
    try {
        // 创建驱动实例
        sql::mysql::MySQL_Driver *driver = sql::mysql::get_mysql_driver_instance();

        // 创建连接
        sql::Connection *con = driver->connect("tcp://127.0.0.1:3306", "root", "Yaoaolong111");

        // 连接到数据库
        con->setSchema("testdb");

        // 创建语句对象
        sql::Statement *stmt = con->createStatement();

        // 执行查询
        sql::ResultSet *res = stmt->executeQuery("SELECT * FROM test_table");

        // 处理结果集
        while (res->next()) {
            // 获取列数据
            int column1 = res->getInt("id");
            std::string column2 = res->getString("name");
            // 处理数据...
			std::cout << column1 << " " << column2 << std::endl;
        }

        // 清理
        delete res;
        delete stmt;
        delete con;
    } catch (sql::SQLException &e) {
        std::cerr << "# ERR: SQLException in " << __FILE__ << "(" << __FUNCTION__ << ") on line "
                  << __LINE__ << ", " << e.what() << std::endl;
        return 1;
    }

    return 0;
}

关于向数据库中写入大型文件的操作

当需要向数据库中写入长度很长的文件时，可以使用TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB(容量有小向大，我使用的是LONGBLOB)

创建数据库语句

create table FileSource (file LONGBLOB);

将文件写入数据库

bool SaveHtmlToMySql(const std::vector<DocInfo_t>& result,sql::Connection* con)
{
    std::string table_name("FileSource");
#define SEP '\3'
    // 将所有文件的内容写到一行中
    con->setAutoCommit(false);
    try{
        for(auto& doc:result){
            std::string out_str;
            out_str += doc.title;
            out_str += SEP;
            out_str += doc.content;
            out_str += SEP;
            out_str += doc.url;

            char sql[1024]={0};
            snprintf(sql,sizeof(sql),"insert into %s values(?)",table_name.c_str()); // ? 是一个占位符
            // 使用字符串进行替换，写入会自动转化为二进制数据
            sql::PreparedStatement* pstmt(con->prepareStatement(sql));

            std::istringstream in_sql(out_str);

            // 绑定BLOB数据
            pstmt->setBlob(1, &in_sql);
            pstmt->executeUpdate();
        }
        con->commit();
    }catch(sql::SQLException &e){
        con->setAutoCommit(true);
        con->rollback();
        std::cout << e.what() << std::endl;
        return false;
    }

    return true;
}

读取文件中数据

bool BuildIndexMysql()
{
    const std::string table_name = "FileSource";
    // 从数据库中获取数据
    sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
    sql::Connection* con = driver->connect("tcp://127.0.0.1:3306","root","Yaoaolong111");
    con->setSchema("SearchBoost");
    sql::Statement* stmt = con->createStatement();
    std::string sql("select * from ");
    sql.append(table_name);
    sql::ResultSet *res = stmt->executeQuery(sql);
    srand(time(nullptr));
    while(res->next()){
        std::istream *blobStream = res->getBlob("file");  // 步骤1
        std::stringstream ss;
        ss << blobStream->rdbuf();// rdbuf返回流缓冲区指针，将数据给到ss中
        std::string data = ss.str();// data就是转化成功的字符串

        delete blobStream;
    }
    return true;
}

扩展——使用占位符一次输入多个值

#include 
#include 
using namespace std;

#define MYSQL 1
#ifdef MYSQL
#include 
#include 
#include 
#include 


struct Info{
    int id;
    string name;
    int age;
};
#endif

int main()
{
#ifdef MYSQL
    sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
    sql::Connection* con = driver->connect("tcp://127.0.0.1:3306","root","Yaoaolong111");
    con->setSchema("SearchBoost");
    // 希望一次输入几个就有几个占位符
    string sql("insert into test_argument values(?,?,?)");
    sql::PreparedStatement* pstmt(con->prepareStatement(sql));
    
    // 保佑多个位置插入数据
    vector<Info> v;
    v.push_back({1,"yao",23});
    v.push_back({2,"chen",32});

    for(auto& e:v){
        pstmt->setInt(1,e.id);
        pstmt->setString(2,e.name);
        pstmt->setInt(3,e.age);
        
        pstmt->executeUpdate();
    }


#endif

    return 0;
}

redis

安装redis

ubuntu安装redis_ubuntu下安装redis-CSDN博客

sudo apt install redis-server

配置redis

vim /etc/redis/redis.conf

protected-mode yes----->no 可以远程访问

bind 0.0.0.0 改ip

deamonize yes 表示使用守护进程方式执行

C/C++操作redis

安装diredis——C语言

sudo apt install libhiredis-dev

代码示例

#include 
#include 
#include 

int main(int argc, char **argv) {
    redisContext *c;
    redisReply *reply;

    c = redisConnect("127.0.0.1", 6379);
    if (c == NULL || c->err) {
        printf("Connection error: %s\n", c ? c->errstr : "Can't allocate redis context");
        return 1;
    }

    reply = redisCommand(c, "SET key value");
    if (reply == NULL || reply->type != REDIS_REPLY_STATUS) {
        printf("SET error: %s\n", reply ? reply->str : "Unknown error");
        freeReplyObject(reply);
        redisFree(c);
        return 1;
    }

    printf("SET command succeeded.\n");

    reply = redisCommand(c, "GET key");
    if (reply == NULL || reply->type != REDIS_REPLY_STRING) {
        printf("GET error: %s\n", reply ? reply->str : "Unknown error");
        freeReplyObject(reply);
    } else {
        printf("The value of 'key' is: %s\n", reply->str);
    }
    freeReplyObject(reply);

    redisFree(c);
    return 0;
}

安装redis-plus-plus——C++

# 网页连接
https://github.com/sewenew/redis-plus-plus

# 如果想要进行clone，自己必须先配置自己的config
# 安装
git clone https://github.com/sewenew/redis-plus-plus.git
cd redis-plus-plus
mkdir build
cd build
cmake ..
make
make install
cd ..

编译命令

g++ -o $@ $^ -std=c++17 /usr/local/lib/libredis++.a /usr/lib/x86_64-linux-gnu/libhiredis.a -lpthread -ljsoncpp -g

代码示例

#include 
#include 
using namespace std;
using namespace sw::redis;

int main()
{
    try
    {
        // Create an Redis object, which is movable but NOT copyable.
        auto redis = Redis("tcp://127.0.0.1:6379");

        // ***** STRING commands *****

        redis.set("key", "val");
        auto val = redis.get("key"); // val is of type OptionalString. See 'API Reference' section for details.
        if (val)
        {
            // Dereference val to get the returned value of std::string type.
            std::cout << *val << std::endl;
        } // else key doesn't exist.

        // ***** LIST commands *****

        // std::vector to Redis LIST.
        std::vector<std::string> vec = {"a", "b", "c"};
        redis.rpush("list", vec.begin(), vec.end());

        // std::initializer_list to Redis LIST.
        redis.rpush("list", {"a", "b", "c"});

        // Redis LIST to std::vector.
        vec.clear();
        redis.lrange("list", 0, -1, std::back_inserter(vec));

        // ***** HASH commands *****

        redis.hset("hash", "field", "val");

        // Another way to do the same job.
        redis.hset("hash", std::make_pair("field", "val"));

        // std::unordered_map to Redis HASH.
        std::unordered_map<std::string, std::string> m = {
            {"field1", "val1"},
            {"field2", "val2"}};
        redis.hmset("hash", m.begin(), m.end());

        // Redis HASH to std::unordered_map.
        m.clear();
        redis.hgetall("hash", std::inserter(m, m.begin()));

        // Get value only.
        // NOTE: since field might NOT exist, so we need to parse it to OptionalString.
        std::vector<OptionalString> vals;
        redis.hmget("hash", {"field1", "field2"}, std::back_inserter(vals));

        // ***** SET commands *****

        redis.sadd("set", "m1");

        // std::unordered_set to Redis SET.
        std::unordered_set<std::string> set = {"m2", "m3"};
        redis.sadd("set", set.begin(), set.end());

        // std::initializer_list to Redis SET.
        redis.sadd("set", {"m2", "m3"});

        // Redis SET to std::unordered_set.
        set.clear();
        redis.smembers("set", std::inserter(set, set.begin()));

        if (redis.sismember("set", "m1"))
        {
            std::cout << "m1 exists" << std::endl;
        } // else NOT exist.

        // ***** SORTED SET commands *****

        redis.zadd("sorted_set", "m1", 1.3);

        // std::unordered_map to Redis SORTED SET.
        std::unordered_map<std::string, double> scores = {
            {"m2", 2.3},
            {"m3", 4.5}};
        redis.zadd("sorted_set", scores.begin(), scores.end());

        // Redis SORTED SET to std::vector>.
        // NOTE: The return results of zrangebyscore are ordered, if you save the results
        // in to `std::unordered_map`, you'll lose the order.
        std::vector<std::pair<std::string, double>> zset_result;
        redis.zrangebyscore("sorted_set",
                            UnboundedInterval<double>{}, // (-inf, +inf)
                            std::back_inserter(zset_result));

        // Only get member names:
        // pass an inserter of std::vector type as output parameter.
        std::vector<std::string> without_score;
        redis.zrangebyscore("sorted_set",
                            BoundedInterval<double>(1.5, 3.4, BoundType::CLOSED), // [1.5, 3.4]
                            std::back_inserter(without_score));

        // Get both member names and scores:
        // pass an back_inserter of std::vector> as output parameter.
        std::vector<std::pair<std::string, double>> with_score;
        redis.zrangebyscore("sorted_set",
                            BoundedInterval<double>(1.5, 3.4, BoundType::LEFT_OPEN), // (1.5, 3.4]
                            std::back_inserter(with_score));

        // ***** SCRIPTING commands *****

        // Script returns a single element.
        auto num = redis.eval<long long>("return 1", {}, {});

        // Script returns an array of elements.
        std::vector<std::string> nums;
        redis.eval("return {ARGV[1], ARGV[2]}", {}, {"1", "2"}, std::back_inserter(nums));

        // mset with TTL
        auto mset_with_ttl_script = R"(
        local len = #KEYS
        if (len == 0 or len + 1 ~= #ARGV) then return 0 end
        local ttl = tonumber(ARGV[len + 1])
        if (not ttl or ttl <= 0) then return 0 end
        for i = 1, len do redis.call("SET", KEYS[i], ARGV[i], "EX", ttl) end
        return 1
    )";

        // Set multiple key-value pairs with TTL of 60 seconds.
        auto keys = {"key1", "key2", "key3"};
        std::vector<std::string> args = {"val1", "val2", "val3", "60"};
        redis.eval<long long>(mset_with_ttl_script, keys.begin(), keys.end(), args.begin(), args.end());

        // ***** Pipeline *****

        // Create a pipeline.
        auto pipe = redis.pipeline();

        // Send mulitple commands and get all replies.
        auto pipe_replies = pipe.set("key", "value")
                                .get("key")
                                .rename("key", "new-key")
                                .rpush("list", {"a", "b", "c"})
                                .lrange("list", 0, -1)
                                .exec();

        // Parse reply with reply type and index.
        auto set_cmd_result = pipe_replies.get<bool>(0);

        auto get_cmd_result = pipe_replies.get<OptionalString>(1);

        // rename command result
        pipe_replies.get<void>(2);

        auto rpush_cmd_result = pipe_replies.get<long long>(3);

        std::vector<std::string> lrange_cmd_result;
        pipe_replies.get(4, back_inserter(lrange_cmd_result));

        // ***** Transaction *****

        // Create a transaction.
        auto tx = redis.transaction();

        // Run multiple commands in a transaction, and get all replies.
        auto tx_replies = tx.incr("num0")
                              .incr("num1")
                              .mget({"num0", "num1"})
                              .exec();

        // Parse reply with reply type and index.
        auto incr_result0 = tx_replies.get<long long>(0);

        auto incr_result1 = tx_replies.get<long long>(1);

        std::vector<OptionalString> mget_cmd_result;
        tx_replies.get(2, back_inserter(mget_cmd_result));

        // ***** Generic Command Interface *****

        // There's no *Redis::client_getname* interface.
        // But you can use *Redis::command* to get the client name.
        val = redis.command<OptionalString>("client", "getname");
        if (val)
        {
            std::cout << *val << std::endl;
        }

        // Same as above.
        auto getname_cmd_str = {"client", "getname"};
        val = redis.command<OptionalString>(getname_cmd_str.begin(), getname_cmd_str.end());

        // There's no *Redis::sort* interface.
        // But you can use *Redis::command* to send sort the list.
        std::vector<std::string> sorted_list;
        redis.command("sort", "list", "ALPHA", std::back_inserter(sorted_list));

        // Another *Redis::command* to do the same work.
        auto sort_cmd_str = {"sort", "list", "ALPHA"};
        redis.command(sort_cmd_str.begin(), sort_cmd_str.end(), std::back_inserter(sorted_list));

        // ***** Redis Cluster *****

        // Create a RedisCluster object, which is movable but NOT copyable.
        auto redis_cluster = RedisCluster("tcp://127.0.0.1:7000");

        // RedisCluster has similar interfaces as Redis.
        redis_cluster.set("key", "value");
        val = redis_cluster.get("key");
        if (val)
        {
            std::cout << *val << std::endl;
        } // else key doesn't exist.

        // Keys with hash-tag.
        redis_cluster.set("key{tag}1", "val1");
        redis_cluster.set("key{tag}2", "val2");
        redis_cluster.set("key{tag}3", "val3");

        std::vector<OptionalString> hash_tag_res;
        redis_cluster.mget({"key{tag}1", "key{tag}2", "key{tag}3"},
                           std::back_inserter(hash_tag_res));
    }
    catch (const Error &e)
    {
        // Error handling.
    }
}

redis使用文档

redis-plus-plus:Redis client written in C++ - GitCode，文档

扩展——set中string数据和json，格式进行相互转化

将json数据转化成string，并保存在redis中

Json::Value root;
Json::Valut elem;
elem["title"] = "title";
elem["id"] = 6;
elem["url"] = "url";
root.append(elem);
redis->sadd(word,elem.toStyledString());// redis无法很好的管理结构化数据，有时候将数据转化为string可能是一种选择

从json中取数据，转化为json

// 传入的数据结构要是能类似与数组的数据结构
template <typename Output>
void Redis::smembers(const StringView &key, Output output) {
    auto reply = command(cmd::smembers, key);

    reply::to_array(*reply, output);
}

// 从redis中取出类型为set的members
Json::Value root;
string word = "key";
std::vector<std::string> val_list;
redis->smembers(word, val_list);
// redis->smembers(word, std::back_inserter(val_list));// 也是正确的

if(val_list.size()){
    for(auto& e:val_list){
        Json::Reader reader;
        Json::Value tem;
        // redis中保存的是一个形如json的string
        if(reader.parse(e,tem)){
            root.append(tem);
        }
    }
}