mysql create table, insert,select via c++ and lmysqlcppconn

复制代码
//create table t1

CREATE TABLE `t1` (
`id` bigint NOT NULL AUTO_INCREMENT,
`author` varchar(40) NOT NULL,
`comment` varchar(40) NOT NULL,
`content` varchar(40) NOT NULL,
`header` varchar(40) NOT NULL,
`isbn` varchar(40) NOT NULL,
`memory` varchar(40) NOT NULL,
`object` varchar(40) NOT NULL,
`result` varchar(40) NOT NULL,
`summary` varchar(40) NOT NULL,
`title` varchar(40) NOT NULL,
`topic` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



复制代码


复制代码


//main.cpp
#include <algorithm>
#include <chrono>
#include <ctime>
#include <fstream>
#include <iomanip>
#include <iostream>
#include <map>
#include <memory>
#include <mutex>
#include <pqxx/pqxx>
#include <random>
#include <set>
#include <thread>
#include <uuid/uuid.h>
#include <vector>
#include <cppconn/connection.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/datatype.h>
#include <cppconn/metadata.h>
#include <cppconn/parameter_metadata.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <cppconn/sqlstring.h>

template <typename T1, typename T2>
void print_mtx_map(const std::map<T1, T2> _mp, const int &interval);

std::string get_time_now(bool is_exact = true)
{
    std::chrono::time_point<std::chrono::high_resolution_clock> now = std::chrono::high_resolution_clock::now();
    time_t raw_time = std::chrono::high_resolution_clock::to_time_t(now);
    struct tm tm_info = *localtime(&raw_time);
    std::stringstream ss;
    ss << std::put_time(&tm_info, "%Y%m%d%H%M%S");
    if (is_exact)
    {
        std::chrono::seconds seconds = std::chrono::duration_cast<std::chrono::seconds>(now.time_since_epoch());
        std::chrono::milliseconds mills = std::chrono::duration_cast<std::chrono::milliseconds>(now.time_since_epoch());
        std::chrono::microseconds micros = std::chrono::duration_cast<std::chrono::microseconds>(now.time_since_epoch());
        std::chrono::nanoseconds nanos = std::chrono::duration_cast<std::chrono::nanoseconds>(now.time_since_epoch());
        ss << std::setw(3) << std::setfill('0') << (mills.count() - seconds.count() * 1000)
           << std::setw(3) << std::setfill('0') << (micros.count() - mills.count() * 1000)
           << std::setw(3) << std::setfill('0') << (nanos.count() - micros.count() * 1000);
    }
    return ss.str();
}

std::string get_time_span(std::chrono::time_point<std::chrono::high_resolution_clock> _start_time, std::chrono::time_point<std::chrono::high_resolution_clock> _end_time)
{
    std::stringstream ss;
    ss << std::chrono::duration_cast<std::chrono::seconds>(_end_time - _start_time).count() << " seconds,"
       << std::chrono::duration_cast<std::chrono::milliseconds>(_end_time - _start_time).count() << " mills,"
       << std::chrono::duration_cast<std::chrono::microseconds>(_end_time - _start_time).count() << " micros,"
       << std::chrono::duration_cast<std::chrono::nanoseconds>(_end_time - _start_time).count() << " nanos"
       << std::endl;
    return ss.str();
}
 
char uuid_value[37];
uint32_t rand32()
{
    return ((rand() & 0x3) << 30) | ((rand() & 0x7fff) << 15) | (rand() & 0x7fff);
}

char *gen_uuid4()
{
    int n = snprintf(uuid_value, sizeof(uuid_value), "%08x-%04x-%04x-%04x-%04x%08x",
                     rand32(),                       // Generates a 32-bit Hex number
                     rand32() & 0xffff,              // Generates a 16-bit Hex number
                     ((rand32() & 0x0fff) | 0x4000), // Generates a 16-bit Hex number of the form 4xxx (4 indicates the UUID version)
                     (rand32() & 0x3fff) + 0x8000,   // Generates a 16-bit Hex number in the range [0x8000, 0xbfff]
                     rand32() & 0xffff, rand32());   // Generates a 48-bit Hex number
    // return n >= 0 && n < len;             // Success only when snprintf result is a positive number and the provided buffer was large enough.
    return uuid_value;
}
 
void insert_into_mysql_table()
{     
    sql::Driver *driver = get_driver_instance(); 
    sql::Connection *conn = driver->connect("localhost", "username_value", "password_value");
    conn->setSchema("db");
    std::cout << std::boolalpha << conn->isValid() << std::endl;
    sql::Statement *stmt = conn->createStatement();
    std::string sql_str; 
    std::stringstream ss;
    srand(time(NULL));
    std::uint64_t num = 0;

    for (int loop = 0; loop < 1000; loop++)
    {
        ss = std::stringstream();
        ss << "insert into t1(author,comment,content,header,isbn,memory,object,result,summary,title,topic) values ";
        for (int i = 0; i < 1000000; i++)
        {
            ss << "('" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','"
               << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','"
               << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "'),";
            ++num;
        }
        sql_str = ss.str();
        int last_comma_idx = sql_str.find_last_of(",");
        sql_str = sql_str.substr(0, last_comma_idx);
        stmt->execute(sql_str);
        std::cout << get_time_now() << ",num:" << num <<",loop:"<<loop+1<< " thread id:" << std::this_thread::get_id() << std::endl;
    }
    conn->close();
    std::cout << get_time_now() << ",finished thread id:" << std::this_thread::get_id() << ",line " << __LINE__ << " of " << __FUNCTION__ << std::endl;
}



　　




void select_from_mysql()


{


sql::Driver *driver = get_driver_instance();


sql::Connection *conn = driver->connect("localhost", "fred", "Root0001!");


conn->setSchema("db");


sql::Statement *stmt = conn->createStatement();


std::string select_str = "select * from t1;";


sql::ResultSet *res = stmt->executeQuery(select_str);


sql::ResultSetMetaData *resMetadata = res->getMetaData();


int cols_count = resMetadata->getColumnCount();


int rows_count = res->rowsCount();


std::cout << "Rows count:" << rows_count << ",columns count:" << cols_count << std::endl;


std::cout << get_time_now() << ",finished in " << __LINE__ << " of " << __FUNCTION__ << std::endl;


}






int main(int args, char **argv)
{ 
    //insert_into_mysql_table();
    std::cout << get_time_now() << ",finished in " << __LINE__ << " of " << __FUNCTION__ << std::endl;
}


复制代码


 


Compile




g++-13 -std=c++23 -I. main.cpp -luuid -lpthread -lmysqlcppconn -o h1;




 


Run




nohup ./h1 >>insert.log |tail -f insert.log






 




show columns from db.t1;


select * from t1 order by id desc limit 1;




 


 




 


复制代码


#include <algorithm>
#include <chrono>
#include <ctime>
#include <fstream>
#include <iomanip>
#include <iostream>
#include <map>
#include <memory>
#include <mutex>
#include <pqxx/pqxx>
#include <random>
#include <set>
#include <thread>
#include <uuid/uuid.h>
#include <vector>
#include <cppconn/driver.h>
#include <cppconn/metadata.h>
#include <cppconn/parameter_metadata.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <cppconn/resultset_metadata.h>
#include <cppconn/statement.h>
#include "libpq-fe.h"

template <typename T1, typename T2>
void print_mtx_map(const std::map<T1, T2> _mp, const int &interval);

std::string get_time_now(bool is_exact = true)
{
    std::chrono::time_point<std::chrono::high_resolution_clock> now = std::chrono::high_resolution_clock::now();
    time_t raw_time = std::chrono::high_resolution_clock::to_time_t(now);
    struct tm tm_info = *localtime(&raw_time);
    std::stringstream ss;
    ss << std::put_time(&tm_info, "%Y%m%d%H%M%S");
    if (is_exact)
    {
        std::chrono::seconds seconds = std::chrono::duration_cast<std::chrono::seconds>(now.time_since_epoch());
        std::chrono::milliseconds mills = std::chrono::duration_cast<std::chrono::milliseconds>(now.time_since_epoch());
        std::chrono::microseconds micros = std::chrono::duration_cast<std::chrono::microseconds>(now.time_since_epoch());
        std::chrono::nanoseconds nanos = std::chrono::duration_cast<std::chrono::nanoseconds>(now.time_since_epoch());
        ss << std::setw(3) << std::setfill('0') << (mills.count() - seconds.count() * 1000)
           << std::setw(3) << std::setfill('0') << (micros.count() - mills.count() * 1000)
           << std::setw(3) << std::setfill('0') << (nanos.count() - micros.count() * 1000);
    }
    return ss.str();
}

std::string get_time_span(std::chrono::time_point<std::chrono::high_resolution_clock> _start_time, std::chrono::time_point<std::chrono::high_resolution_clock> _end_time)
{
    std::stringstream ss;
    ss << std::chrono::duration_cast<std::chrono::seconds>(_end_time - _start_time).count() << " seconds,"
       << std::chrono::duration_cast<std::chrono::milliseconds>(_end_time - _start_time).count() << " mills,"
       << std::chrono::duration_cast<std::chrono::microseconds>(_end_time - _start_time).count() << " micros,"
       << std::chrono::duration_cast<std::chrono::nanoseconds>(_end_time - _start_time).count() << " nanos"
       << std::endl;
    return ss.str();
}

char uuid_value[37];
uint32_t rand32()
{
    return ((rand() & 0x3) << 30) | ((rand() & 0x7fff) << 15) | (rand() & 0x7fff);
}

char *gen_uuid4()
{
    int n = snprintf(uuid_value, sizeof(uuid_value), "%08x-%04x-%04x-%04x-%04x%08x",
                     rand32(),                       // Generates a 32-bit Hex number
                     rand32() & 0xffff,              // Generates a 16-bit Hex number
                     ((rand32() & 0x0fff) | 0x4000), // Generates a 16-bit Hex number of the form 4xxx (4 indicates the UUID version)
                     (rand32() & 0x3fff) + 0x8000,   // Generates a 16-bit Hex number in the range [0x8000, 0xbfff]
                     rand32() & 0xffff, rand32());   // Generates a 48-bit Hex number
    // return n >= 0 && n < len;             // Success only when snprintf result is a positive number and the provided buffer was large enough.
    return uuid_value;
}

void insert_into_psql_batch(const int &loops)
{
    // create table t1(id bigserial not null primary key,author varchar(40) not null,comment varchar(40) not null,content varchar(40) not null,header varchar(40) not null,isbn varchar(40) not null,object varchar(40) not null,summary varchar(40) not null);
    std::uint64_t num = 0;
    srand(time(NULL));
    std::string conn_str = "host=localhost port=5432 user=fred password=Fred0001! dbname=db";
    const char *conn_char_ptr = conn_str.c_str();
    PGconn *conn = PQconnectdb(conn_char_ptr);
    PGresult *res;

    // std::cout << PQstatus(conn) << std::endl;
    if (PQstatus(conn) == CONNECTION_OK)
    {
        std::cout << "ConnStatusType:CONNECTION_OK" << std::endl;
    }
    else if (PQstatus(conn) == CONNECTION_BAD)
    {
        std::cout << "ConnStatusType:CONNECTION_BAD" << std::endl;
    }

    std::stringstream ss;
    std::chrono::time_point<std::chrono::high_resolution_clock> _start_time, _end_time;
    for (int loop = 0; loop < loops; loop++)
    {
        _start_time = std::chrono::high_resolution_clock::now();
        ss = std::stringstream();
        ss << "insert into t1(author,comment,content,header,isbn,object,summary) values ";
        for (int i = 0; i < 1000000; i++)
        {
            ++num;
            ss << "('" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','"
               << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "'),";
        }

        std::string insert_sql = ss.str();
        int last_comma_idx = insert_sql.find_last_of(",");
        insert_sql = insert_sql.substr(0, last_comma_idx);
        res = PQexec(conn, insert_sql.c_str());
        _end_time = std::chrono::high_resolution_clock::now();
        if (PGRES_COMMAND_OK == PQresultStatus(res))
        {
            std::cout << "PQresultStatus(res):PGRES_COMMAND_OK" << std::endl;
            PQclear(res);
            std::cout << "Loops:" << loop + 1 << ",num:" << num << ",time cost:" << get_time_span(_start_time, _end_time) << std::endl;
        }
    }
    PQfinish(conn);
    std::cout << get_time_now() << ",finished line:" << __LINE__ << " of " << __FUNCTION__ << std::endl;
}

void insert_into_mysql(const int &loops)
{
    sql::Driver *driver = get_driver_instance();
    sql::Connection *conn = driver->connect("localhost", "fred", "Root0001!");
    sql::ResultSet *res;
    sql::Statement *stmt = conn->createStatement();
    conn->setSchema("db");
    std::stringstream ss;
    srand(time(NULL));
    std::string sql_str;
    int last_comma_idx = -1;
    bool is_inserted = false;
    std::uint64_t num = 0;
    std::chrono::time_point<std::chrono::high_resolution_clock> _start_time, _end_time;
    for (int i = 0; i < loops; i++)
    {
        ss = std::stringstream();
        _start_time = std::chrono::high_resolution_clock::now();
        ss << "insert into t1(author,comment,content,header,isbn,memory,object,result,summary,title,topic) values ";
        for (int j = 0; j < 1000000; j++)
        {
            ss
                << "('" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4()
                << "','" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4()
                << "','" << gen_uuid4() << "','" << gen_uuid4() << "','" << gen_uuid4() << "'),";
            ++num;
        }
        sql_str = ss.str();
        last_comma_idx = sql_str.find_last_of(",");
        sql_str = sql_str.substr(0, last_comma_idx);
        is_inserted = stmt->execute(sql_str);
        _end_time = std::chrono::high_resolution_clock::now();
        std::cout << std::boolalpha << get_time_now() << "," << is_inserted << ",loops:" << i + 1 << ",num:" << num << ",time cost:"
                  << std::chrono::duration_cast<std::chrono::seconds>(_end_time - _start_time).count() << " seconds,"
                  << std::chrono::duration_cast<std::chrono::milliseconds>(_end_time - _start_time).count() << " mills,"
                  << std::chrono::duration_cast<std::chrono::microseconds>(_end_time - _start_time).count() << " micros,"
                  << std::chrono::duration_cast<std::chrono::nanoseconds>(_end_time - _start_time).count() << " nanos!" << std::endl;
    }
    conn->close();
    std::cout << std::boolalpha << get_time_now() << ",num:" << num << ",finished line:" << __LINE__ << " of " << __FUNCTION__ << std::endl;
}

void select_from_mysql()
{
    sql::Driver *driver = get_driver_instance();
    sql::Connection *conn = driver->connect("localhost", "fred", "Root0001!");
    conn->setSchema("db");
    sql::Statement *stmt = conn->createStatement();
    std::string select_str = "select * from t1;";
    sql::ResultSet *res = stmt->executeQuery(select_str);
    sql::ResultSetMetaData *resMetadata = res->getMetaData();
    int cols_count = resMetadata->getColumnCount();
    int rows_count = res->rowsCount();
    std::cout << "Rows count:" << rows_count << ",columns count:" << cols_count << std::endl;
    std::cout << get_time_now() << ",finished in " << __LINE__ << " of " << __FUNCTION__ << std::endl;
}

int main(int args, char **argv)
{
    // -I/usr/include/postgresq
    // g++-13 -std=c++23 -I. main.cpp -I/usr/include/postgresql -lpq -lmysqlcppconn -o h1;
    // insert_into_psql_batch(atoi(argv[1]));
    // insert_into_mysql(atoi(argv[1]));
    select_from_mysql();
    std::cout << get_time_now() << ",finished in " << __LINE__ << " of " << __FUNCTION__ << std::endl;
}


复制代码


复制代码


void select_from_mysql()
{
    sql::Driver *driver = get_driver_instance();
    sql::Connection *conn = driver->connect("localhost", "fred", "Root0001!");
    conn->setSchema("db");
    sql::Statement *stmt = conn->createStatement();
    std::string select_str = "select * from t1;";
    sql::ResultSet *res = stmt->executeQuery(select_str);
    sql::ResultSetMetaData *resMetadata = res->getMetaData();
    int cols_count = resMetadata->getColumnCount();
    int rows_count = res->rowsCount();
    std::cout << "Rows count:" << rows_count << ",columns count:" << cols_count << std::endl;
    std::cout << get_time_now() << ",finished in " << __LINE__ << " of " << __FUNCTION__ << std::endl;
}


复制代码


 


Compile




g++-13 -std=c++23 -I. main.cpp -I/usr/include/postgresql -lpq -lmysqlcppconn -o h1;




Run




./h1
fred@fred:~/C$ ./h1;
Rows count:10000000,columns count:12
20231118011626239986642,finished in 181 of select_from_mysql
20231118011626240021627,finished in 191 of main




 






            

            
posted @ 
 
FredGrit 
阅读(6) 
评论(0) 
编辑 
收藏 
举报


        

	    
	    
    









    
    

    
    



    

        
    

        

            
                
                
            
        

    



    相关博文：

    

            

                · 
                mysql c++ create table,insert,select
            

            

                · 
                cpp lmysqlcppconn insert multiple rows into mysql table via groups and remainder
            

            

                · 
                mysql存储过程
            

            

                · 
                C++ - 操作MySQL数据库
            

            

                · 
                肖sir__mysql__插入时间
            

    





    

    阅读排行：
    

 ·          分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
        

 ·          基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
        

 ·          ollama系列01：轻松3步本地部署deepseek，普通电脑可用
        

 ·          25岁的心里话
        

 ·          按钮权限的设计及实现
        



    

历史上的今天：



2019-10-31    C# System.Timers Demo
    



    



	

	

	
	

	

	

	





    

    
    








  

  
点击右上角即可分享

  微信分享提示

   
    
AI IDE Trae