2020.05.30
环境： centos 8 + mysql 5.7.30
1 . 使用普通方式
MYSQL *mysql = mysql_init(NULL);// 初始化，获得句柄
if(!mysql)
{
std::cout<<"mysql init error"<<std::endl;
return -1;
}
if(NULL == (mysql=mysql_real_connect(mysql, "localhost", "root", "", "test", 3306, NULL, 0))) //连接
{
std::cout<< "connect error"<<std::endl;
return 0;
}
if( mysql_query(mysql, "select * from class")) // 执行sql语句
{
std::cout<<"query error"<<std::endl;
return -1;
}
MYSQL_RES *res = mysql_store_result(mysql);// 获取结果集
if(NULL == res)
{
std::cout<<"mysql_store_reslut error"<< mysql_error(mysql) <<std::endl;
return -1;
}
unsigned int lie = mysql_num_fields(res); // 结果集的列总数
MYSQL_ROW row;
while(row = mysql_fetch_row(res)) // 循环获取结果集一行，最后一行获取后 row=null
{
for(int i=0 ;i<lie; i++)
{
long unsigned int *len = mysql_fetch_lengths(res); // 获取一行中所有列的长度，放在 len [i] 中
std::cout<<"len=" <<len[i] <<"row[i]=" <<row[i] <<std::endl;
}
}
while(field = mysql_fetch_field(res)) // 获取列信息
{
std::cout<<"field->name: " << field->name <<std::endl;
}
sprintf(sql,"insert into class value(%s,\‘%s\‘,%s,\‘%s\‘,\‘%s\‘)", s1.c_str(), qq", s2.c_str(), "xxxx", s3.c_str() );
mysql_query(mysql, sql) // 插入数据
mysql_free_result(res); // 释放结果集，必须释放，否则内存泄漏
2.使用预处理方式
MYSQL_STMT * stmt = mysql_stmt_init(mysql); // 初始化获取 句柄
string strmysql = "insert into class(id,name,age,depname,time) values(?,?,?,?,?)"; // 注意插入格式
mysql_stmt_prepare(stmt,strmysql.c_str(),strmysql.length()) // 预处理准备
MYSQL_BIND bind[5]={0};
bind[0].buffer_type = MYSQL_TYPE_LONG;
bind[0].buffer = &id;
bind[0].is_null= 0;
bind[0].length= 0;
long unsigned int namelen = 2;
bind[1].buffer_type = MYSQL_TYPE_STRING;
bind[1].buffer = (char*)name.c_str();
bind[1].is_null= 0;
bind[1].length= &namelen;
bind[1].buffer_length= STRING_SIZE;
bind[2].buffer_type = MYSQL_TYPE_LONG;
bind[2].buffer = &age;
bind[2].is_null= 0;
bind[2].length= 0;
long unsigned int len1 = dep.length();
bind[3].buffer_type = MYSQL_TYPE_STRING;
bind[3].buffer = (char*)dep.c_str();
bind[3].is_null= 0;
bind[3].length= &len1;
bind[3].buffer_length= STRING_SIZE;
long unsigned int len2 = t.length();
bind[4].buffer_type = MYSQL_TYPE_STRING;
bind[4].buffer = (char*)t.c_str();
bind[4].is_null= 0;
bind[4].length= &len2;
bind[4].buffer_length= STRING_SIZE;
if(mysql_stmt_bind_param(stmt, bind)) // 绑定
{
cout<<"mysql_stmt_bind_param error: " << mysql_error(mysql) <<endl;
}
mysql_stmt_execute(stmt); // 执行