参考:
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;