526互联

mysql c++ create table,insert,select

发布时间 2023-11-21 22:53:46作者: FredGrit
CREATE TABLE `t1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT primary key,
  `author` varchar(40) NOT NULL DEFAULT '',
  `comment` varchar(40) NOT NULL DEFAULT '',
  `content` varchar(40) NOT NULL DEFAULT '',
  `header` varchar(40) NOT NULL DEFAULT '',
  `isbn` varchar(40) NOT NULL DEFAULT '',
  `memory` varchar(40) NOT NULL DEFAULT '',
  `object` varchar(40) NOT NULL DEFAULT '',
  `result` varchar(40) NOT NULL DEFAULT '',
  `summary` varchar(40) NOT NULL DEFAULT '',
  `title` varchar(40) NOT NULL DEFAULT '',
  `topic` varchar(40) NOT NULL DEFAULT '' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

#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> 

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(const int &loops)
{
    /*CREATE TABLE `t1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT primary key,
  `author` varchar(40) NOT NULL DEFAULT '',
  `comment` varchar(40) NOT NULL DEFAULT '',
  `content` varchar(40) NOT NULL DEFAULT '',
  `header` varchar(40) NOT NULL DEFAULT '',
  `isbn` varchar(40) NOT NULL DEFAULT '',
  `memory` varchar(40) NOT NULL DEFAULT '',
  `object` varchar(40) NOT NULL DEFAULT '',
  `result` varchar(40) NOT NULL DEFAULT '',
  `summary` varchar(40) NOT NULL DEFAULT '',
  `title` varchar(40) NOT NULL DEFAULT '',
  `topic` varchar(40) NOT NULL DEFAULT '' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;*/
    sql::Driver *driver = get_driver_instance();
    sql::Connection *conn = driver->connect("localhost", "sam01", "Sam0001!");
    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", "sam01", "Sam0001!");
    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::uint64_t rows_idx = 0;
    while (res->next())
    {
        if (++rows_idx % 1000000 == 0)
        {
            for (int j = 1; j < cols_count - 1; j++)
            {
                std::cout << res->getString(j) << ",";
            }
            std::cout << res->getString(cols_count - 1) << std::endl;
        }
    }
    std::cout << get_time_now() << ",finished in " << __LINE__ << " of " << __FUNCTION__ << std::endl;
}

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

 

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

 

//run
nohup ./h1 100 >> insertmysql.txt |tail -f insertmysql.txt;

 

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

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

//run
nohup ./h1 >>select.txt |tail -f select.txt;

 

 

//insert snippet
        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);

 

 

 

//select snippet
    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::uint64_t rows_idx = 0;
    while (res->next())
    {
        if (++rows_idx % 1000000 == 0)
        {
            for (int j = 1; j < cols_count - 1; j++)
            {
                std::cout << res->getString(j) << ",";
            }
            std::cout << res->getString(cols_count - 1) << std::endl;
        }
    }