C++ 操作数据库 查询mysql_query select from where中字段名等于变量的问题

[flybird70]

代码如下:
#include <fstream.h>
#include <windows.h>
#include <cstdlib>
#include <cstdio>
#include "mysql.h"
#pragma comment(lib, "libmysql.lib")
MYSQL * conn;
#include <algorithm>
using namespace std;
int main()
{
//------连接数据库----------------
char host[] = "localhost";
char username[] = "root";
char password[] = "123456";
char database[] = "tt";

conn = mysql_init(NULL);

if(mysql_real_connect(conn,
host,
username,
password,
database,
0, NULL, 0) != NULL)
//-----------定义---------------
MYSQL_RES * res_set;
MYSQL_ROW row;
unsigned int i, ret;
MYSQL_FIELD * field;
unsigned int num_fields;
//----------查询并显示结果---------
int ty=9;//设置变量tt

mysql_query(conn,"select rt2 from dt where rt1 =ty" );//出问题的地方:rt1 =ty
res_set = mysql_store_result(conn);
row=mysql_fetch_row(res_set);
cout < <row[0] < <endl;

return 0;

}
//数据库tt的表dt中有两列数据,rt1,rt2,都是整数型数据,mediumint(m) tinyint(m)
//如果把mysql_query中的ty改为任何一个正整数,都可以运行并返回一个正确结果
//如果字段名=变量ty,则可以编译,不能运行,请高手具体指正,谢谢!

[Xshl5]

mysql_query(conn,"select rt2 from dt where rt1 =ty" );//出问题的地方:rt1 =ty

相当于在mysql中执行 select rt2 from dt where rt1=ty ，很明显SQL语句错误，

如果rt1为整形，则应该rt1=整形常量（不能是ty变量）
如果rt1为字符串型，则应该rt1='ty'（字符串在单引号里面）

这里问题属于前者，可以尝试：

#include <string>   // for c++ std string
...

std::string sql="select rt2 from dt where rt1=";

int ty=9;
char int_to_str[5]="";
sprintf(int_to_str,"%d",ty);

sql+= sql + int_to_str;

mysql_query(conn,sql);    // maybe it work...

...

没有测试，有问题请跟贴。。

[资深码农多年]

"select rt2 from dt where rt1 =ty"
这是一个字符串，而大哥你的程序中ty是变量，
你的意思是想让查询根据ty变量值不同而不同吧！

std::string ty("9");
std::string sql("select rt2 from dt where rt1 =");
sql = sql+ty;

[flybird70]

to :Xshl5


编译时出错;

error C2664: 'mysql_query' : cannot convert parameter 2 from 'class std::basic_string<char,struct std::char_traits<char>,class std::allocator<char> >' to 'const char *'
No user-defined-conversion operator available that can perform this conversion, or the operator cannot be called

[flybird70]

to:yi_jun_jun


我的意思确实是想让查询根据ty变量值不同而不同.

[偶像罗斯福]

#include <fstream.h>
#include <windows.h>
#include <cstdlib>
#include <cstdio>
#include "mysql.h"
#pragma comment(lib, "libmysql.lib")
MYSQL * conn;
#include <algorithm>
using namespace std;
int main()
{
	char    host[] = "localhost";
	char username[] = "root";
	char password[] = "123456";
	char database[] = "tt";

	conn = mysql_init(NULL);

	if(!mysql_real_connect(conn,host,username,password,database,0, NULL, 0))
	{	
		cout<<"cannot connect mysql"<<endl;
		return 0;

	}	

	MYSQL_RES *res_set;
	MYSQL_ROW row;
	unsigned int i, ret;
	MYSQL_FIELD *field;
	unsigned int num_fields;
	
	int ty = 9;
	char sql[1024];
	memset(sql, 0x00, 1024);
	sprintf(sql, "select rt2 from dt where rt1 = %d", ty );
	//size_t len = strlen(sql);
	//sql[strlen(sql)] = '\0';
	mysql_real_query(conn, sql );
	res_set = mysql_store_result(conn);
	row = mysql_fetch_row(res_set);
	cout <<row[0] <<endl;

	return 0;

} 

[Xshl5]

[Quote=引用 3 楼 flybird70 的回复:]
to :Xshl5


编译时出错;

error C2664: 'mysql_query' : cannot convert parameter 2 from 'class std::basic_string <char,struct std::char_traits <char>,class std::allocator <char> >' to 'const char *'
No user-defined-conversion operator available that can perform this conversion, or the operator cannot be called
[/Quote]

昨天大意了,应该将sql转化成c-style字符串

sql+= int_to_str;
mysql_query(conn,sql.c_str());    // now it works...

另外,在SQL查询时可以将整形转换成十六进制,

#include <string>   // for c++ std string
...

std::string sql="select rt2 from dt where rt1=";

int ty=9;
char int_to_str[9]="";
sprintf(int_to_str,"%x",ty);      // int_to_str中能存放一个任意4字节int型

sql+= "0x" + int_to_str;

mysql_query(conn,sql.c_str());    // now it works?

...

[flybird70]

TO :epeaktop
编译时:
error C2660: 'mysql_real_query' : function does not take 2 parameters

[Xshl5]

sql+= "0x" + int_to_str;修改成

sql+= std::string("0x") + int_to_str;

5楼的
sprintf(sql, "select rt2 from dt where rt1 = %d", ty );
方法不错,不过这时候
char sql[1024];

sql一般是够用了，不过有浪费空间嫌疑; 太小又怕不够用,不好控制

[SearchLife]

mysql_query(conn,"select rt2 from dt where rt1 =ty" );//出问题的地方:rt1 =ty

-----------------
sprintf(sql, "select rt2 from dt where rt1 = %d", ty );
同意

[flybird70]

我想再问一下,这里返回的row[0]是什么数据类型,怎么不是整数,好像也不是字符?

[Xshl5]

mysql.h中查看函数原型：

...
typedef struct st_mysql_res {
my_ulonglong row_count;
MYSQL_FIELD *fields;
MYSQL_DATA *data;
MYSQL_ROWS *data_cursor;
unsigned long *lengths; /* column lengths of current row */
MYSQL *handle; /* for unbuffered reads */
MEM_ROOT field_alloc;
unsigned int field_count, current_field;
MYSQL_ROW row; /* If unbuffered read */
MYSQL_ROW current_row; /* buffer to current row */
my_bool eof; /* Used by mysql_fetch_row */
/* mysql_stmt_close() had to cancel this result */
my_bool unbuffered_fetch_cancelled;
const struct st_mysql_methods *methods;
} MYSQL_RES;
...
typedef char **MYSQL_ROW; /* return data as array of strings */
...
MYSQL_ROW STDCALL mysql_fetch_row(MYSQL_RES *result);
...

可知道mysql_fetch_row函数返回类型是char**，即char*[]指针数组，每个元素为指针(指向一个字符串)

[flybird70]

谢谢Xshl5,epeaktop,问题解决,散分每人100分.

麻烦Xshl5到http://topic.csdn.net/u/20080716/19/ae60571c-7715-403a-a33a-646d7e213cda.html

这个贴另有100分给你.再次谢谢!

[code_study]

学习