以下代码实现功能:如果账号对应的记录存在,更新账号出现的个数(update),如果不存在插入记录(insert)
stringstream strSelectStream;
strSelectStream << "select * from `account`.`Server_Role_Info` where Account = ";
strSelectStream << nCgAccount << ")";CDBResult* pDBResult = Df_AccountDBPtr->SelectSQL(strSelectStream.str().c_str());
if(pDBResult->Count() > 0)
{
stringstream strUpdateStream;
strUpdateStream << "update `account`.`Server_Role_Info` set RoleCount = RoleCount +1 where Account = " ;
strUpdateStream << nCgAccount << ")";
int bExeRsz =Df_AccountDBPtr->ExeSQL(strUpdateStream.str().c_str());
GS_ASSERT(bExeRsz == 0);
}
else
{
stringstream strInsertStream;
strInsertStream << "insert into `account`.`Server_Role_Info`(ServerId,Account,RoleCount) values (";
strInsertStream << CUR_SERVER_ID << "," << nCgAccount << ","<< 0 << " )";
int bExeRsz =Df_AccountDBPtr->ExeSQL(strInsertStream.str().c_str());
GS_ASSERT(bExeRsz == 0);
}数据库:mysql5.1
这个程序的问题在于,我觉得找个三个SQL语句可以合并起来,比如用存储过程阿,什么的,但是不会用,所以请教高手,代码完整给高分。
stringstream strSelectStream;
strSelectStream << "select * from `account`.`Server_Role_Info` where Account = ";
strSelectStream << nCgAccount << ")";CDBResult* pDBResult = Df_AccountDBPtr->SelectSQL(strSelectStream.str().c_str());
if(pDBResult->Count() > 0)
{
stringstream strUpdateStream;
strUpdateStream << "update `account`.`Server_Role_Info` set RoleCount = RoleCount +1 where Account = " ;
strUpdateStream << nCgAccount << ")";
int bExeRsz =Df_AccountDBPtr->ExeSQL(strUpdateStream.str().c_str());
GS_ASSERT(bExeRsz == 0);
}
else
{
stringstream strInsertStream;
strInsertStream << "insert into `account`.`Server_Role_Info`(ServerId,Account,RoleCount) values (";
strInsertStream << CUR_SERVER_ID << "," << nCgAccount << ","<< 0 << " )";
int bExeRsz =Df_AccountDBPtr->ExeSQL(strInsertStream.str().c_str());
GS_ASSERT(bExeRsz == 0);
}数据库:mysql5.1
这个程序的问题在于,我觉得找个三个SQL语句可以合并起来,比如用存储过程阿,什么的,但是不会用,所以请教高手,代码完整给高分。
insert into `Server_Role_Info`
select Account from dual on DUPLICATE KEY UPDATE `Server_Role_Info`.RoleCount=`Server_Role_Info`.RoleCount+1;
-> ON DUPLICATE KEY UPDATE c=c+1;
mysql> UPDATE table SET c=c+1 WHERE a=1;
如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。