MySQL应用

1. mysql存储过程示例

DROP PROCEDURE IF EXISTS P_QueryRecord;
CREATE PROCEDURE P_QueryRecord(Para_Query VARCHAR(16),Para_Condition INTEGER)
BEGIN
DECLARE query VARCHAR(36);
SET query := '';
SET query := CONCAT(query,Para_Query,'%');
IF Para_Condition = 0 THEN
SELECT * FROM T_Record WHERE name LIKE query;
ELSEIF Para_Condition = 1 THEN
SELECT * FROM T_Record WHERE phone LIKE query;
ELSEIF Para_Condition = 2 THEN
SELECT * FROM T_Record WHERE room_no LIKE query;
ELSEIF Para_Condition = 3 THEN
SELECT * FROM T_Record WHERE address LIKE query;
ELSE
SELECT * FROM T_Record WHERE 1=2;
END IF;
END;

注意事项:赋值语句:=;传参数的方式;END IF结束后要加;号.

2. 删除索引存储过程

DROP PROCEDURE IF EXISTS P_DelIndex;

CREATE PROCEDURE P_DelIndex(IN para_table_name VARCHAR(200), IN para_index_name VARCHAR(200))
BEGIN
  DECLARE str VARCHAR(250);
  SET @str=CONCAT('DROP INDEX ',para_index_name,' ON ',para_table_name);
  SELECT COUNT(*) INTO @cnt FROM information_schema.statistics WHERE TABLE_NAME=para_table_name AND INDEX_NAME=para_index_name;
  IF @cnt >0 THEN 
        PREPARE stmt FROM @str;
        EXECUTE stmt ;
END IF;
END;

3. MySQL Connectors C++使用注意事项

PreparedStatement对象通过传入参数读取数据有问题,第二次调用就报错,读取数据集时用Statement代替,外部传入参数用其提供的escapeString转换即可.

OpenMySql();
string format = "CALL P_QueryRecord('%s',%d)";
string param1 = m_pMySqlConnection->escapeString(query);
char sql[128] = {0};
sprintf_s(sql,sizeof(sql),format.c_str(),param1.c_str(),condition);
auto_ptr<Statement>pState(m_pMySqlConnection->createStatement());  
auto_ptr<ResultSet>pSet;
pState->execute(sql);

do
{
string col_value = "";
pSet.reset(pState->getResultSet());
while(pSet->next())
{
string sValue = pSet->getString("name");
}
}while(pState->getMoreResults());