mysql直接执行和预处理执行的效率比较

最新推荐文章于 2023-04-25 22:05:08 发布

yg2362

最新推荐文章于 2023-04-25 22:05:08 发布

阅读量2.1k

点赞数

分类专栏：数据库文章标签： mysql null string query insert 测试

本文链接：https://blog.csdn.net/yg2362/article/details/7217687

版权

数据库专栏收录该内容

0 篇文章 0 订阅

订阅专栏

最近闲来无事，想看看mysql预处理执行效率和直接执行的效率差别有多大。先去网上查了查，两种声音都有，没办法，只好自己来验证验证。

我打算测试插入的效率，下来建一个表

CREATE TABLE `test` (

  `id` int ( 11 ) NOT NULL AUTO_INCREMENT,
  `col1` varchar ( 64 ) NOT NULL DEFAULT '' ,
  `col2` char ( 60 ) DEFAULT NULL ,
  `col3` blob,
  `col4` timestamp NULL DEFAULT NULL ,
   PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

接下来贴上两段分别用预处理插入的语句和直接插入的语句
预处理代码
bool insert()

{
     if (mysql_stmt_prepare(stmt, " INSERT test(col1,col2,col3,col4) values(?,?,?,?) " , strlen( " INSERT test(col1,col2,col3,col4) values(?,?,?,?) " )))
    {
        fprintf(stderr, " mysql_stmt_prepare(), insert failed\r\n " );
        fprintf(stderr, " %s\r\n " , mysql_stmt_error(stmt));
         return false ;
    }

     // 测试mysql_stmt_field_count()作用
     // printf("mysql_stmt_field_count return value[%d]\r\n",mysql_stmt_field_count(stmt));
    MYSQL_BIND    bind[ 4 ];
    unsigned long length[ 4 ];
    my_bool       is_null[ 4 ];

    memset(bind, 0 , sizeof (bind));
    memset(length, 0 , sizeof (length));
    memset(is_null, 0 , sizeof (is_null));

     char varchar_var[STRING_SIZE] = " hello yg " ;
     char char_var[STRING_SIZE] = " hello dl " ;

     struct st_stud st;
    st.id = 1 ;
    strncpy(st.name, " shine.yang " , 16 );
    st.score = 10.0f ;

    time_t cur_t = time(NULL);
     struct tm cur_tm;
    localtime_r( & cur_t, & cur_tm);
    MYSQL_TIME    ts;
    ts.year = cur_tm.tm_year + 1900 , ts.month = cur_tm.tm_mon + 1 , ts.day = cur_tm.tm_mday,ts.hour = cur_tm.tm_hour, ts.minute = cur_tm.tm_min, ts.second = cur_tm.tm_sec;

    bind[ 0 ].buffer_type = MYSQL_TYPE_STRING;
    bind[ 0 ].buffer = varchar_var;
    bind[ 0 ].buffer_length = STRING_SIZE;
    length[ 0 ] = strlen(varchar_var);
    bind[ 0 ].length = & length[ 0 ];
    bind[ 0 ].is_null = & is_null[ 0 ];

    bind[ 1 ].buffer_type = MYSQL_TYPE_STRING;
    bind[ 1 ].buffer = char_var;
    bind[ 1 ].buffer_length = STRING_SIZE;
    length[ 1 ] = strlen(char_var);
    bind[ 1 ].length = & length[ 1 ];
    bind[ 1 ].is_null = & is_null[ 1 ];

    bind[ 2 ].buffer_type = MYSQL_TYPE_BLOB;
    bind[ 2 ].buffer = ( char * ) & st;
    bind[ 2 ].buffer_length = sizeof (st);
    length[ 2 ] = sizeof (st);
    bind[ 2 ].length = & length[ 2 ];
    bind[ 2 ].is_null = & is_null[ 2 ];

    bind[ 3 ].buffer_type = MYSQL_TYPE_TIMESTAMP;
    bind[ 3 ].buffer = ( char * ) & ts;
    bind[ 3 ].buffer_length = sizeof (ts);
    length[ 3 ] = sizeof (ts);
    bind[ 3 ].length = & length[ 3 ];
    bind[ 3 ].is_null = & is_null[ 3 ];

    dword time1 = get_time();
     for ( int i = 0 ; i < INSERT_ITEM_CNT; ++ i )
    {
         if ( mysql_stmt_bind_param(stmt,bind) )
        {
            fprintf(stderr, " mysql_stmt_bind_param failed,err = %s\r\n " ,mysql_stmt_error(stmt));
             return false ;
        }

         if ( mysql_stmt_execute(stmt) )
        {
            fprintf(stderr, " mysql_stmt_excute failed,err = %s\r\n " ,mysql_stmt_error(stmt));
             return false ;
        }

         // printf("Total effect rows [%d]\r\n",(int)mysql_stmt_affected_rows(stmt));
    }
    dword time2 = get_time();
    fprintf(stdout, " stmt insert %d items,during time[%d]\r\n " ,INSERT_ITEM_CNT,time2 - time1);
     return true ;
}

直接插入的代码
bool insert()

{
     char varchar_var[STRING_SIZE] = " hello yg " ;
     char char_var[STRING_SIZE] = " hello dl " ;
     struct st_stud st;
    st.id = 1 ;
    strncpy(st.name, " shine.yang " , 16 );
    st.score = 10.0f ;

     char blob_var[ 128 ];
    mysql_real_escape_string(sql, blob_var, ( char * ) & st, sizeof (st));

     char query[ 1024 ] = { 0 };
     int len = snprintf(query, 1024 , " insert test(col1,col2,col3,col4) values('%s','%s','%s',NOW()) " ,varchar_var,char_var,blob_var);
    dword time1 = get_time();
     for ( int i = 0 ;i < INSERT_ITEM_CNT; ++ i )
    {
        mysql_real_query(sql,query,len);
    }
    dword time2 = get_time();
    fprintf(stdout, " direct insert %d items,during time[%d]\r\n " ,INSERT_ITEM_CNT,time2 - time1);

     return true ;
}

下面是测试结果