![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/fed60ef83e00a4a467e0e736889c1ec7.png)
一、事务
#include <iostream>
#include <mysql.h>
#include <thread>
#include <string>
#include <sstream>
#include <map>
#include <chrono>
using namespace std;
using namespace chrono;
int main()
{
MYSQL mysql;
mysql_init(&mysql);
const char *host = "127.0.0.1";
const char *user = "root";
const char *pass = "123456";
const char *db = "laoxiaketang";
if (!mysql_real_connect(&mysql,host, user, pass, db, 3306, 0, CLIENT_MULTI_STATEMENTS))
{
cout << "mysql connect failed!" << mysql_error(&mysql) << endl;
}
else
{
cout << "mysql connect success!" << endl;
}
string sql = "";
sql = "CREATE TABLE IF NOT EXISTS `t_video` ( \
`id` int AUTO_INCREMENT,\
`name` varchar(1024),\
`path` varchar(2046),\
`size` int,\
PRIMARY KEY(`id`)\
) ENGINE=InnoDB;";
int re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
sql = "truncate t_video";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
sql = "START TRANSACTION";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
sql = "set autocommit = 0";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
for (int i = 0; i < 3; i++)
{
sql = "insert into t_video (name) values('test three!')";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
}
sql = "ROLLBACK";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
for (int i = 0; i < 1000; i++)
{
sql = "insert into t_video (name) values('test three!')";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
}
sql = "COMMIT";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
sql = "set autocommit = 1";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
sql = "select count(*) from t_video";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
MYSQL_RES * res = mysql_store_result(&mysql);
if (res)
{
MYSQL_ROW row = mysql_fetch_row(res);
if (row)
{
cout << "t_video count(*) = " << row[0] << endl;
}
}
auto start = system_clock::now();
for (int i = 0; i < 1000; i++)
{
sql = "insert into t_video (name,path) values('single','0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789')";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
}
auto end = system_clock::now();
auto dur = duration_cast<milliseconds> (end - start);
cout << "1 单条语句插入1千条数据" << dur.count()/1000.<<"秒" << endl;
{
auto start = system_clock::now();
sql = "";
for (int i = 0; i < 1000; i++)
{
sql += "insert into t_video (name,path) values('single','0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789');";
}
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
do
{
cout << mysql_affected_rows(&mysql) << flush;
} while (mysql_next_result(&mysql) == 0);
auto end = system_clock::now();
auto dur = duration_cast<milliseconds> (end - start);
cout << "2 多条语句插入1千条数据" << dur.count() / 1000. << "秒" << endl;
}
{
sql = "START TRANSACTION";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
auto start = system_clock::now();
for (int i = 0; i < 1000; i++)
{
sql = "insert into t_video (name,path) values('single','0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789')";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
else
cout << mysql_affected_rows(&mysql) << flush;
}
sql = "set autocommit = 0";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
sql = "COMMIT";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
sql = "set autocommit = 1";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << "mysql_query failed! " << mysql_error(&mysql) << endl;
}
auto end = system_clock::now();
auto dur = duration_cast<milliseconds> (end - start);
cout << "3 事务插入1千条数据" << dur.count() / 1000. << "秒" << endl;
}
mysql_close(&mysql);
mysql_library_end();
std::cout << "Mysql 8.0 API!\n";
getchar();
}
二、向数据库写入二进制文件和从数据库取出二进制文件
#include <iostream>
#include <mysql.h>
#include <thread>
#include <string>
#include <sstream>
#include <map>
#include <chrono>
#include <fstream>
using namespace std;
using namespace chrono;
int main()
{
MYSQL mysql;
mysql_init(&mysql);
const char *host = "127.0.0.1";
const char *user = "root";
const char *pass = "123456";
const char *db = "laoxiaketang";
if (!mysql_real_connect(&mysql,host, user, pass, db, 3306, 0, CLIENT_MULTI_STATEMENTS))
{
cout << "mysql connect failed!" << mysql_error(&mysql) << endl;
}
else
{
cout << "mysql connect success!" << endl;
}
string sql = "";
sql = "CREATE TABLE IF NOT EXISTS `t_data` (\
`id` int AUTO_INCREMENT,\
`name` varchar(1024),\
`data` blob,\
`size` int,\
PRIMARY KEY(`id`))";
int re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cout << mysql_error(&mysql) << endl;
}
sql = "truncate t_data";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cerr << mysql_error(&mysql) << endl;
}
MYSQL_STMT *stmt = mysql_stmt_init(&mysql);
if (!stmt)
{
cerr << "mysql_stmt_init failed!" << mysql_error(&mysql) << endl;
}
sql = "INSERT INTO `t_data` (name,data,size) VALUES(?,?,?)";
if (mysql_stmt_prepare(stmt, sql.c_str(), sql.size()))
{
cerr << "mysql_stmt_prepare failed!" << mysql_stmt_error(stmt) << endl;
}
string filename = "mysql.jpg";
fstream in(filename, ios::in | ios::binary);
if (!in.is_open())
{
cerr << "file " << filename << " open failed!" << endl;
}
in.seekg(0, ios::end);
int filesize = in.tellg();
in.seekg(0, ios::beg);
char *data = new char[filesize];
int readed = 0;
while (!in.eof())
{
in.read(data+ readed, filesize- readed);
if (in.gcount() <= 0)
break;
readed += in.gcount();
}
in.close();
MYSQL_BIND bind[3] = { 0 };
bind[0].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = (char*)filename.c_str();
bind[0].buffer_length = filename.size();
bind[1].buffer_type = MYSQL_TYPE_BLOB;
bind[1].buffer = data;
bind[1].buffer_length = filesize;
bind[2].buffer_type = MYSQL_TYPE_LONG;
bind[2].buffer = &filesize;
if (mysql_stmt_bind_param(stmt, bind) != 0)
{
cerr << "mysql_stmt_bind_param failed! " << mysql_stmt_error(stmt) << endl;
}
if (mysql_stmt_execute(stmt) != 0)
{
cerr << "mysql_stmt_execute failed! " << mysql_stmt_error(stmt) << endl;
}
delete data;
mysql_stmt_close(stmt);
sql = "select * from t_data";
re = mysql_query(&mysql, sql.c_str());
if (re != 0)
{
cerr << "mysql query failed!" << mysql_error(&mysql) << endl;
}
MYSQL_RES *res = mysql_store_result(&mysql);
if (!res)
{
cerr << "mysql_store_result failed!" << mysql_error(&mysql) << endl;
}
MYSQL_ROW row = mysql_fetch_row(res);
if (!row)
{
cerr << "mysql_fetch_row failed!" << mysql_error(&mysql) << endl;
}
cout << row[0] << " " << row[1]<<" " << row[3] << endl;
unsigned long *lens = mysql_fetch_lengths(res);
int fnum = mysql_num_fields(res);
for (int i = 0; i < fnum; i++)
{
cout << "["<<lens[i] << "]";
}
filename = "out_";
filename += row[1];
fstream out(filename, ios::out|ios::binary);
if (!out.is_open())
{
cerr << "open file " << filename << " failed!" << endl;
}
out.write(row[2], lens[2]);
out.close();
mysql_close(&mysql);
mysql_library_end();
std::cout << "Mysql 8.0 API!\n";
getchar();
}