Mysql，检查表和C的行数

I try to make function to check if table exists and to get total number of rows but I get result 1 instead of 99999. So, what should I do here to work properly?

我尝试使函数检查表是否存在并获得总行数，但我得到结果1而不是99999.那么，我应该在这里做什么才能正常工作？

int sql_table_length(char* database, char* dtable, char* mysql_user_name, char* mysql_password)
{
    int retval = 0;
    MYSQL *conn;
    conn = mysql_init(NULL);
    if (conn)
    {
        if (mysql_real_connect(conn, "localhost", mysql_user_name, mysql_password, database, 0, NULL, 0)!=0)
        {
            char chktable[512] = {0};
            sprintf(chktable,"%s%s%s", "SHOW TABLES LIKE '", dtable, "'");
            mysql_query(conn, chktable);
            if (mysql_store_result(conn))
            {
                MYSQL_RES *result;
                char lngtable[512] = {0};
                sprintf(lngtable, "%s%s", "SELECT COUNT(*) FROM ", dtable);
                if (!(mysql_query(conn, lngtable)))
                {
                    result = mysql_store_result(conn);
                    retval = mysql_num_rows(result); // here I get 1
                    mysql_free_result(result);
                }
                else retval = -4;   //no rows
            }
            else retval = -3;       //don't exist
        }
        else retval = -2;           //can't connect
    }
    else retval = -1;               //no connection
    mysql_close(conn);
    return retval;
}

Thanks!

谢谢！

2 个解决方案

#1

Your query, SELECT COUNT(*) FROM table returns a single row containing the number of rows in the table. Instead of counting the number of rows in the result, you want to query the number that is returned.

您的查询SELECT COUNT（*）FROM表返回包含表中行数的单行。您想要查询返回的数字，而不是计算结果中的行数。

You will want to do something like (untested):

你会想做一些像（未经测试的）：

MYSQL_STMT *stmt;
MYSQL_BIND bind;
long rows;
my_bool error; /* Omit at your own risk */

stmt = mysql_stmt_init(mysql)
mysql_stmt_prepare(stmt, lngtable, strlen(lngtable));
mysql_stmt_execute(stmt);

memset(&bind, 0, sizeof(bind));
bind.buffer_type = MYSQL_TYPE_LONG;
bind.buffer = (char *)&rows;
bind.error = &error; /* Omit at your own risk */

mysql_stmt_bind_result(stmt, &bind);

mysql_stmt_fetch(stmt);
printf("Number of rows: %ld\n", rows);

You should add error checking to all the mysql calls. See http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-fetch.html for more examples about binding parameters.

您应该为所有mysql调用添加错误检查。有关绑定参数的更多示例，请参见http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-fetch.html。

Alternatively, you can change your query to something like SELECT * FROM table and keep the rest of your code, but this is asking MySQL to do a lot more work, and the result will likely take longer.

或者，您可以将查询更改为SELECT * FROM table之类的内容并保留其余代码，但这要求MySQL做更多工作，结果可能需要更长时间。

#2

After David's suggestion I find simpler and safer way to get result. Maybe someone will need this.

在大卫的建议之后，我找到了更简单，更安全的方法来获得结果。也许有人需要这个。

int sql_table_length(char* database, char* dtable, char* mysql_user_name, char* mysql_password)
{
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW rowdata;
    int retval = 0;
    conn = mysql_init(NULL);
    if (conn)
    {
        if (mysql_real_connect(conn, "localhost", mysql_user_name, mysql_password, database, 0, NULL, 0)!=0)
        {
            char chktable[512] = {0};
            sprintf(chktable,"%s%s%s", "SHOW TABLES LIKE '", dtable, "'");
            if (!(mysql_query(conn, chktable)))
            {
                result = mysql_store_result(conn);
                if (mysql_num_rows(result))
                {
                    char lngtable[512] = {0};
                    sprintf(lngtable, "%s%s", "SELECT COUNT(*) FROM ", dtable);
                    if (!(mysql_query(conn, lngtable)))
                    {
                        result = mysql_store_result(conn);
                        if (result)
                        {
                            rowdata = mysql_fetch_row(result);
                            if (rowdata)
                                retval = atoi(rowdata[0]);
                            else retval = -7;   // data error
                        }
                        else retval = -6;       // result error
                        mysql_free_result(result);
                    }
                    else retval = -5;           // can't select
                }
                else retval = -4;               // table don't exist
            }
            else retval = -3;                   // can't query
        }
        else retval = -2;                       // can't connect
    }
    else retval = -1;                           // no server connection
    mysql_close(conn);
    return retval;
}

Any remark on possible memory leak (or other) is welcome!

欢迎任何关于可能的内存泄漏（或其他）的评论！

#1