vc++/C++操作mysql数据库一些注意事项

参考：

http://blog.sina.com.cn/s/blog_6ec912d80101exmf.html   

http://blog.csdn.net/zalion/article/details/7329736

http://blog.csdn.net/nciaebupt/article/details/8653518

核心语句：

mysql_num_rows 与 mysql_affected_rows 函数

 

mysql_num_rows() 函数返回结果集中行的数目，用于select后使用；

mysql_affected_rows()用于insert、update、delete语句后使用；

为什么使用这两个函数原因:
 
mysql_query() 查询并不会判断数据是否存在 ， 它只负责mysql语句是否执行成功
select mysql_query();
如果查询的 id 在数据库不存在，它一样会返回资源型，但是里面返回的值却为空。
INSERT，UPDATE 或 DELETE mysql_query();
也一样的道理 ，delete不存在的id，一样语句执行成功。所以要用mysql_affected_rows判断实际影响的行数，这样才能真正判断数据是否存在且改变！

//1：mysql_num_rows 与select语句使用

CString strSQL;
strSQL.Format("select * from t_user where name='%s' and password = '%s' and isUse = 1",userinfo.name,userinfo.password);


int ret = mysql_query(&mydbcon,strSQL);
if(ret)
{
//LOG_ERROR(g_LogHandle,"查询USER表错误:%s",mysql_error(&mysql));
strErrInfo.Format("检查USER是否存在错误:%s",mysql_error(&mydbcon));
m_InfoReport.WriteInfo(m_InfoReport.Register("MibOperation"+strRptTime+".log"), "SQLCheckUserInfo", strErrInfo);


return MIB_OPERATION_ERROR;
}
MYSQL_RES *res = mysql_store_result(&mydbcon);
if (!res)
{
strErrInfo.Format("检查USER是否存在_记录集有问题:%s",mysql_error(&mydbcon));
m_InfoReport.WriteInfo(m_InfoReport.Register("MibOperation"+strRptTime+".log"), "SQLCheckUserInfo", strErrInfo);


return MIB_OPERATION_ERROR;
}
int numret = mysql_num_rows(res);
if (res)
mysql_free_result(res);
if (numret<=0)
{
//LOG_ERROR(g_LogHandle,"Insert USER表错误:%s",mysql_error(&mysql));
//strErrInfo.Format("check USER错误:%s",mysql_error(&mydbcon));
m_InfoReport.WriteInfo(m_InfoReport.Register("MibOperation"+strRptTime+".log"), "SQLCheckUserInfo", "无匹配的username和password");


return NOT_FOUND_RECODE;
}


return FOUND_RECODE;

//2:mysql_affected_rows 与insert语句

CString strSQL;
//strSQL.Format("insert into t_user set %s",sql);
strSQL.Format("insert into t_user(name,password,PriFlag,isUse,Remark) values ('%s','%s',%d,%d,'%s')"\
,userinfo.name,userinfo.password,userinfo.PriFlag,userinfo.isUse,userinfo.Remark);


int ret = mysql_query(&mydbcon,strSQL);
if (ret)
{
//LOG_ERROR(g_LogHandle,"Insert USER表错误:%s",mysql_error(&mysql));
strErrInfo.Format("插入 USER表错误:%s",mysql_error(&mydbcon));
m_InfoReport.WriteInfo(m_InfoReport.Register("MibOperation"+strRptTime+".log"), "SQLGetUserInfo", strErrInfo);


return MIB_OPERATION_ERROR;
}


int aftret = mysql_affected_rows(&mydbcon);
if (aftret<=0)
{
m_InfoReport.WriteInfo(m_InfoReport.Register("MibOperation"+strRptTime+".log"), "SQLGetUserInfo", "实际插入user失败");


return MIB_OPERATION_ERROR;
}


return FOUND_RECODE;

//3: mysql_affected_rows 与update语句

CString strSQL;
strSQL.Format("update t_user set password=\'%s\' where id=\'%d\'",userinfo.password,userinfo.ID);


int ret = mysql_query(&mydbcon,strSQL);
if (ret)
{
//LOG_ERROR(g_LogHandle,"修改USER表用户角色错误:%s",mysql_error(&mysql));
strErrInfo.Format("修改USER表错误:%s",mysql_error(&mydbcon));
m_InfoReport.WriteInfo(m_InfoReport.Register("MibOperation"+strRptTime+".log"), "SQLUpDateUserInfo", strErrInfo);


return FALSE;
}


int aftret = mysql_affected_rows(&mydbcon);
if (aftret<=0)
{
m_InfoReport.WriteInfo(m_InfoReport.Register("MibOperation"+strRptTime+".log"), "SQLUpDateUserInfo", "实际修改user失败");


return FALSE;
}


return TRUE;

//4:mysql_affected_rows 与delete语句

CString strSQL;
//strSQL.Format("insert into t_user set %s",sql);
strSQL.Format("delete from t_user where id=%d",userinfo.ID);


int ret = mysql_query(&mydbcon,strSQL);
if (ret)
{
//LOG_ERROR(g_LogHandle,"Insert USER表错误:%s",mysql_error(&mysql));
strErrInfo.Format("删除 USER错误:%s",mysql_error(&mydbcon));
m_InfoReport.WriteInfo(m_InfoReport.Register("MibOperation"+strRptTime+".log"), "SQLDelUserInfo", strErrInfo);


return MIB_OPERATION_ERROR;
}
int aftret = mysql_affected_rows(&mydbcon);
if (aftret<=0)
{
m_InfoReport.WriteInfo(m_InfoReport.Register("MibOperation"+strRptTime+".log"), "SQLDelUserInfo", "实际删除user失败");


return MIB_OPERATION_ERROR;
}


return FOUND_RECODE;