mysql num filed_mysql_num_fields与mysql_field_count的区别 | 学步园

MySQL字段计数函数区别

最新推荐文章于 2021-02-01 04:13:51 发布

原创最新推荐文章于 2021-02-01 04:13:51 发布 · 648 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql num filed

本文探讨了MySQL中mysql_num_fields与mysql_field_count的区别及应用场景。通过实例对比，揭示了这两种函数在处理不同类型的SQL语句（如INSERT、SELECT）时的表现差异。

mysql_num_fields和mysql_field_count都是获取结果中列的个数，但是这两个函数在使用的时候是存在区别的。

先看一段代码(上篇文章提供的数据库)：

#include

intmain()

{

charout[100];

MYSQL mysql;

MYSQL_RES* res;

intnum_fields;

intcount_fields;

intnum_rows;

mysql_init(&mysql);

mysql_real_connect(&mysql,NULL,"root","123","ta",0,NULL,0);

fprintf(stdout,"--insert------------------------/n");

mysql_real_query(&mysql,"INSERT INTO t1 VALUES(6,'n6')",29);

count_fields = mysql_field_count(&mysql);

sprintf(out,"insert----count_fields is %d/n",count_fields);

fprintf(stdout,out);

res = mysql_store_result(&mysql);

if(res == NULL)

{

fprintf(stdout,"insert---store return null/n");

}

else

{

num_fields = mysql_num_fields(res);

sprintf(out,"insert---num_fields is %d/n",num_fields);

fprintf(stdout,out);

}

fprintf(stdout,"---select-----------/n");

mysql_real_query(&mysql,"SELECT * FROM t1",16);

count_fields = mysql_field_count(&mysql);

sprintf(out,"select---count_fields is %d/n",count_fields);

fprintf(stdout,out);

res = mysql_store_result(&mysql);

if(res == NULL)

{

fprintf(stdout,"select---store return null/n");

}

else

{

num_fields = mysql_num_fields(res);

sprintf(out,"select---num_fields is %d/n",num_fields);

fprintf(stdout,out);

num_rows = mysql_num_rows(res);

sprintf(out,"select---num_rows is %d/n",num_rows);

fprintf(stdout,out);

}

fprintf(stdout,"---select 0------/n");

mysql_real_query(&mysql,"SELECT * FROM t1 where id = 10",30);

count_fields = mysql_field_count(&mysql);

sprintf(out,"select 0---count_fields is %d/n",count_fields);

fprintf(stdout,out);

res = mysql_store_result(&mysql);

if(res == NULL)

{

fprintf(stdout,"select 0---store return null/n");

}

else

{

num_fields = mysql_num_fields(res);

sprintf(out,"select 0---num_fields is %d/n",num_fields);

fprintf(stdout,out);

num_rows = mysql_num_rows(res);

sprintf(out,"select 0---num_rows is %d/n",num_rows);

fprintf(stdout,out);

}

mysql_free_result(res);

mysql_close(&mysql);

exit(0);

}

#include

int main()

{

char out[100];

MYSQL mysql;

MYSQL_RES* res;

int num_fields;

int count_fields;

int num_rows;

mysql_init(&mysql);

mysql_real_connect(&mysql,NULL,"root","123","ta",0,NULL,0);

fprintf(stdout,"--insert------------------------/n");

mysql_real_query(&mysql,"INSERT INTO t1 VALUES(6,'n6')",29);

count_fields = mysql_field_count(&mysql);

sprintf(out,"insert----count_fields is %d/n",count_fields);

fprintf(stdout,out);

res = mysql_store_result(&mysql);

if(res == NULL)

{

fprintf(stdout,"insert---store return null/n");

}

else

{

num_fields = mysql_num_fields(res);

sprintf(out,"insert---num_fields is %d/n",num_fields);

fprintf(stdout,out);

}

fprintf(stdout,"---select-----------/n");

mysql_real_query(&mysql,"SELECT * FROM t1",16);

count_fields = mysql_field_count(&mysql);

sprintf(out,"select---count_fields is %d/n",count_fields);

fprintf(stdout,out);

res = mysql_store_result(&mysql);

if(res == NULL)

{

fprintf(stdout,"select---store return null/n");

}

else

{

num_fields = mysql_num_fields(res);

sprintf(out,"select---num_fields is %d/n",num_fields);

fprintf(stdout,out);

num_rows = mysql_num_rows(res);

sprintf(out,"select---num_rows is %d/n",num_rows);

fprintf(stdout,out);

}

fprintf(stdout,"---select 0------/n");

mysql_real_query(&mysql,"SELECT * FROM t1 where id = 10",30);

count_fields = mysql_field_count(&mysql);

sprintf(out,"select 0---count_fields is %d/n",count_fields);

fprintf(stdout,out);

res = mysql_store_result(&mysql);

if(res == NULL)

{

fprintf(stdout,"select 0---store return null/n");

}

else

{

num_fields = mysql_num_fields(res);

sprintf(out,"select 0---num_fields is %d/n",num_fields);

fprintf(stdout,out);

num_rows = mysql_num_rows(res);

sprintf(out,"select 0---num_rows is %d/n",num_rows);

fprintf(stdout,out);

}

mysql_free_result(res);

mysql_close(&mysql);

exit(0);

}

执行结果是：

--insert------------------------

insert----count_fields is 0

insert---store return null

---select-----------

select---count_fields is 2

select---num_fields is 2

select---num_rows is 7

---select 0------

select 0---count_fields is 2

select 0---num_fields is 2

select 0---num_rows is 0

显然，当执行insert语句的时候，是没有结果返回的，因此列的个数为0，且mysql_store_result返回NULL。因此可以通过mysql_field_count()是否返回0来判断是否有结果返回，而不需要执行mysql_store_result来判断是否返回了NULL。我想，mysql_field_count()的效率肯定要比mysql_store_result()高。

在这种情况下，由于没有返回结果，因此mysql_store_result()返回NULL，也就是得不到res指针，于是mysql_num_fields()函数就无法执行，缺少必要的参数。

当执行第一条select语句的时候，返回了结果，因此mysql_field_count()和mysql_num_fields()都返回了正确的列的个数2，mysql_num_rows()返回了记录的条数7.

当执行第二条select语句，由于表中没有 id = 0 的记录，因此mysql_num_rows返回了0表示记录数为0，但是，我们发现mysql_store_result()并没有返回NULL，mysql_num_fields()和mysql_field_count()还是返回了2.

因此我们可以得出这样的结论：

执行结果有三种情况，第一是执行insert、update和delete这样的语句的时候，是不会有任何内容返回，因此mysql_store_result()会返回一个NULL。第二，执行select或show这样的语句时，一定会有内容返回，可以取得列信息，但是记录可以为0，也可以不为0。这就像一个表，表头一定存在，但是表中可以没有数据。

另外：

mysql_affected_rows()函数的作用是，当执行update insert delete 时，返回影响的行数。

mysql中文文档：