问题
由于在项目中需要在多个同样格式表中，查询同一条件结果。为此编写了一个存储过程，通过游标的方式获取指定命名格式的表名。然后编写动态SQL语句分别查询每个表，再使用UNION ALL合并结果一次性返回。通过Navicat工具调用存储过程可以正常获得结果，在C API直接通过SQL语句也可以直接查询，但通过预编译表达式的方法调用，则提示MYSQL_NO_DATA没有对应的结果。
- MySQL版本 5.6.37 (x64)
数据库表描述
在数据库test中，有两张表分别为personlist1、personlist2，表的内容分别为：
发生问题的存储过程写法
CREATE DEFINER=`root`@`localhost` PROCEDURE `QueryAllZhang`()
BEGIN
DECLARE stopflag INT DEFAULT 0;
DECLARE tablename varchar(255);
DECLARE SqlSelectResult varchar(1024) DEFAULT("");
DECLARE cur CURSOR FOR select table_name from information_schema.tables where table_name like "personlist%";
DECLARE CONTINUE HANDLER FOR not found SET stopflag = 1 ;
OPEN cur;
FETCH cur into tablename;
if stopflag = 0 then
SET SqlSelectResult = CONCAT("select name from ",tablename," where name like 'zhang%'");
FETCH cur into tablename;
end if;
while stopflag = 0 Do
SET SqlSelectResult = CONCAT(SqlSelectResult," UNION ALL select name from ",tablename," where name like 'zhang%'");
FETCH cur into tablename;
end WHILE;
close cur;
SET SqlSelectResult = CONCAT("",SqlSelectResult,";");
set @sql = SqlSelectResult;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE prepare stmt;
END
Navicat调用结果
SQL语句直接调用
如果通过mysql_real_query执行SQL语句，调用存储过程，可以正常获得结果，关键代码如下所示：
string query = "CALL QueryAllZhang();";
int ret= mysql_real_query(connection,query.c_str(),query.length());
if (ret)
{
printf("Error exec query: %s\n",mysql_error(connection));
}
else
{
printf("[%s] exec...\n", query.c_str());
}
result = mysql_store_result(connection);
while((row = mysql_fetch_row(result))) {
printf("[%s]\n", row[0]);
}
mysql_free_result(result);
对应的输出结果如下：
[CALL QueryAllZhang();] exec…
[zhang1]
[zhang3]
[zhang2]
[zhang4]
然而这种直接执行SQL语句的方法，当存储过程有参数时，直接拼接SQL语句会有注入问题，风险很大。
预编译表达式调用
参考MySQL官方例子，编写了如下的C代码：
MYSQL_STMT *stmt;
int status;
/* initialize and prepare CALL statement with parameter placeholders */
stmt = mysql_stmt_init(connection);
if (!stmt)
{
printf("Could not initialize statement\n");
return -1;
}
char *query = "call QueryAllZhang();";
status = mysql_stmt_prepare(stmt, query, strlen(query));
test_stmt_error(stmt, status);
if(status != 0)
{
return -9;
}
char name[256] = {0};
status = mysql_stmt_execute(stmt);
test_stmt_error(stmt, status);
if(status != 0)
{
return -9;
}
/* process results until there are no more */
do {
int i;
int num_fields; /* number of columns in result */
MYSQL_FIELD *fields; /* for result set metadata */
MYSQL_BIND *rs_bind; /* for output buffers */
/* the column count is > 0 if there is a result set */
/* 0 if the result is only the final status packet */
num_fields = mysql_stmt_field_count(stmt);
if (num_fields > 0)
{
/* there is a result set to fetch */
printf("Number of columns in result: %d\n", (int) num_fields);
/* what kind of result set is this? */
printf("Data: ");
if(connection->server_status & SERVER_PS_OUT_PARAMS)
printf("this result set contains OUT/INOUT parameters\n");
else
printf("this result set is produced by the procedure\n");
MYSQL_RES *rs_metadata = mysql_stmt_result_metadata(stmt);
test_stmt_error(stmt, rs_metadata == NULL);
fields = mysql_fetch_fields(rs_metadata);
rs_bind = (MYSQL_BIND *) malloc(sizeof (MYSQL_BIND) * num_fields);
if (!rs_bind)
{
printf("Cannot allocate output buffers\n");
return -2;
}
memset(rs_bind, 0, sizeof (MYSQL_BIND) * num_fields);
rs_bind[0].buffer_type = MYSQL_TYPE_VAR_STRING;
rs_bind[0].buffer = name;
rs_bind[0].buffer_length = 255;
status = mysql_stmt_bind_result(stmt, rs_bind);
test_stmt_error(stmt, status);
if(status != 0)
{
return -9;
}
/* fetch and display result set rows */
while (1)
{
status = mysql_stmt_fetch(stmt);
// 一打开游标就会在这里捕获到MYSQL_NO_DATA
if (status == 1 || status == MYSQL_NO_DATA)
{
break;
}
printf("[%s]\n",(char*)rs_bind[0].buffer);
}
mysql_free_result(rs_metadata); /* free metadata */
free(rs_bind); /* free output buffers */
}
else
{
/* no columns = final status packet */
printf("End of procedure output\n");
}
/* more results? -1 = no, >0 = error, 0 = yes (keep looking) */
status = mysql_stmt_next_result(stmt);
if (status > 0)
{
test_stmt_error(stmt, status);
if(status != 0)
{
return -9;
}
}
} while (status == 0);
mysql_stmt_close(stmt);
如上代码中的注释，无法获得结果，能获取到查询结果的列名，输出结果如下：
Number of columns in result: 1
Data: this result set is produced by the procedure
End of procedure output
为此，我对存储过程进行简化，修改为如下：
CREATE DEFINER=`root`@`localhost` PROCEDURE `QueryAllZhang`()
BEGIN
DECLARE stopflag INT DEFAULT 0;
DECLARE tablename varchar(255);
DECLARE SqlSelectResult varchar(1024) DEFAULT("");
DECLARE cur CURSOR FOR select table_name from information_schema.tables where table_name like "personlist%";
DECLARE CONTINUE HANDLER FOR not found SET stopflag = 1 ;
set @sql = "select table_name from information_schema.tables";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE prepare stmt;
OPEN cur;
FETCH cur into tablename;
if stopflag = 0 then
SET SqlSelectResult = CONCAT("select name from ",tablename," where name like 'zhang%'");
FETCH cur into tablename;
end if;
while stopflag = 0 Do
SET SqlSelectResult = CONCAT(SqlSelectResult," UNION ALL select name from ",tablename," where name like 'zhang%'");
FETCH cur into tablename;
end WHILE;
close cur;
SET SqlSelectResult = CONCAT("",SqlSelectResult,";");
END
将执行查询的代码提到OPEN cur之前，可以获得数据库中的表列表，输入结果如下：
Number of columns in result: 1
Data: this result set is produced by the procedure
[CHARACTER_SETS]
[COLLATIONS]
[COLLATION_CHARACTER_SET_APPLICABILITY]
[COLUMNS]
[COLUMN_PRIVILEGES]
[ENGINES]
…
如果将查询代码挪到close cur之后，则无法获得结果。为此，我查询了有关的资料，但还没有发现原因(暂时无解)。为此，我放弃使用游标，改用通过临时表的方式编译查询结果，再动态执行SQL语句进行查询。
修改后的存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `QueryAllZhang`()
BEGIN
DECLARE stopflag INT DEFAULT 0;
DECLARE tablename varchar(255);
DECLARE SqlSelectResult varchar(1024) DEFAULT("");
DECLARE temp_index int;
DECLARE temp_count int;
// 若不存在临时表，则创建
CREATE TEMPORARY TABLE if not exists tmp_table(
rowid int primary key,
rowname varchar(255)
);
// 清空临时表内容
truncate tmp_table;
// 没插入一条结果，对应ID自增，方便后续控制循环获取结果
insert into tmp_table(SELECT (@i := @i + 1),table_name from information_schema.tables,(SELECT @i := 0) b where table_name like "personlist%");
set temp_index = 1;
select count(*) from tmp_table into temp_count;
if temp_count != 0
then
select rowname from tmp_table where rowid = temp_index into tablename;
SET SqlSelectResult = CONCAT("select name from ",tablename," where name like 'zhang%'");
set temp_index = temp_index + 1;
while temp_index <= temp_count do
select rowname from tmp_table where rowid = temp_index into tablename;
SET SqlSelectResult = CONCAT(SqlSelectResult," UNION ALL select name from ",tablename," where name like 'zhang%'");
set temp_index = temp_index + 1;
END while;
SET SqlSelectResult = CONCAT("",SqlSelectResult,";");
set @sql = SqlSelectResult;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE prepare stmt;
end if;
END
再通过预编译表达式方式调用存储过程，得到如下结果：
Number of columns in result: 1
Data: this result set is produced by the procedure
[zhang1]
[zhang3]
[zhang2]
[zhang4]
End of procedure output
可以正常获得结果了。。。虽然通过临时表的方式，会有写入再读出的IO问题，但暂时实现了防止SQL注入，又能在存储过程内从多个指定格式表中查询结果。另外，MySQL中的临时表，当关闭连接时，会自动删除临时表，也可以手动执行删除。
782
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
