linux 环境 c 连接mysql笔记

最新推荐文章于 2025-03-30 16:45:26 发布

原创最新推荐文章于 2025-03-30 16:45:26 发布 · 820 阅读

1 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

7 篇文章

订阅专栏

本文提供了一个使用C语言操作MySQL数据库的示例程序，包括连接数据库、执行SQL语句、处理查询结果等核心功能。

#include <stdio.h>
#include "/usr/include/mysql/mysql.h"

int main(){
   int i , j , num_fields;
   unsigned long *lengths;
   MYSQL *conn;
   MYSQL_RES *result;
   MYSQL_ROW row;
   MYSQL_FIELD *field;
   const char* sql;
   MY_CHARSET_INFO cs;

MYSQL_ROW_OFFSET row_offset;

//初始化mysql (mysql_init)

conn = mysql_init(NULL);

//连接mysql (mysql_real_connect)

    if (mysql_real_connect(conn , "localhost" , "root" , "123456" , NULL , 0 , NULL , 0) == NULL){
       fprintf(stderr , "sorroy , no database connection...\n");
       return 1;
   }

//列出所有数据库 (mysql_list_dbs 或者使用sql语句show database [like wild])

    result = mysql_list_dbs(conn , NULL);
   num_fields = mysql_num_fields(result);
   while ((row = mysql_fetch_row(result))!=NULL){
       for(i = 0 ; i < num_fields ; i++){
           if (row[i] == NULL)
                    printf("list dbs:[NULL]\n");
          else
                    printf("list dbs:%s\n" , row[i]);
       }
   }

mysql_free_result(result);

#if 0
   //mysql_list_tables 为什么会报错?
   result = mysql_list_tables(conn , NULL);
   num_fields = mysql_num_fields(result);
        while ((row = mysql_fetch_row(result))!=NULL){
                for(i = 0 ; i < num_fields ; i++){
                        if (row[i] == NULL)
                                printf("list tables:[NULL]\n");
                        else
                                printf("list tables:%s\n" , row[i]);
                }
        }
        mysql_free_result(result);
#endif

//列出一个数据库里的所有表 (sql语句 show tables [like wild][from database])

//执行sql语句 (mysql_query)

    sql = "show tables from `yuliying`";
   if (mysql_query(conn , sql)){
                fprintf(stderr , "%s\n" , mysql_error(conn));
                return 1;
    }
   result = mysql_store_result(conn);
   num_fields = mysql_num_fields(result);
   while ((row = mysql_fetch_row(result))!=NULL)   {
       for(i = 0 ; i < num_fields; i++){
           if (row[i] == NULL)
                    printf("list tables:[NULL]\n");
            else
                    printf("list tables:%s\n" , row[i]);
       }
   }
   mysql_free_result(result);

   //mysql_get_client_info
   printf("client info:%s\n" , mysql_get_client_info());

   //mysql_get_client_version
   printf("client version:%i\n" , (int)mysql_get_client_version());

   //mysql_get_host_info
   printf("host info:%s\n" , mysql_get_host_info(conn));

   //mysql_get_proto_info
   printf("proto info:%i\n" , (int)mysql_get_proto_info(conn));

   //mysql_get_server_info
   printf("server info:%s\n" , mysql_get_server_info(conn));

   //mysql_get_server_version
   printf("server version:%i\n" , (int)mysql_get_server_version(conn));

//设置数据库字符编码 (mysql_set_character_set)

mysql_set_character_set(conn , "utf8");

//获取数据库字符编码信息 (mysql_get_character_set_info)

mysql_get_character_set_info(conn , &cs);
printf("character set name: %s\n" , cs.csname);

//获取数据库字符编码 (mysql_character_set_name)

printf("mysql charset name:%s\n" , mysql_character_set_name(conn));

//创建数据库 (sql语句create database)

    sql = "CREATE DATABASE IF NOT EXISTS `yuliying`";
   if (mysql_query(conn , sql)){
       fprintf(stderr , "%s\n" , mysql_error(conn));
       return 1;
   }

//选择一个数据库 (mysql_select_db)

    if(mysql_select_db(conn , "yuliying")){
       fprintf(stderr , "%s\n" , mysql_error(conn));
       return 1;
   }

//创建表 (sql语句create table)

    sql = "CREATE TABLE IF NOT EXISTS `test`(\
       id int(11) primary key auto_increment,\
       name varchar(100) not null\
   ) ENGINE=Innodb default charset=UTF8";

   if (mysql_query(conn , sql)){
       fprintf(stderr , "%s\n" , mysql_error(conn));
                fprintf(stderr , "%s\n" , sql);
                return 1;
   }

//列出列名 (mysql_list_fields)(sql语句 show columns from tal_name [like wild])

    sql = "show columns from `test`";
   if (mysql_query(conn , sql)){
                fprintf(stderr , "%s\n" , mysql_error(conn));
                fprintf(stderr , "%s\n" , sql);
                return 1;
        }
   result = mysql_store_result(conn);

   num_fields = mysql_num_fields(result);
   while ((row = mysql_fetch_row(result))!=NULL){
       for(i = 0 ; i < num_fields ; i++){
           printf("list fileds:%s\n" , row[i]);
       }
   }

mysql_free_result(result);

#if 0

//插入数据 (sql语句insert)

    sql = "INSERT INTO `test` (`name`) VALUES (floor(rand()*100))";
   if (mysql_query(conn , sql)){
                fprintf(stderr , "%s\n" , mysql_error(conn));
                fprintf(stderr , "%s\n" , sql);
                return 1;
        }

#endif

//获取insert或者update影响的最后一个自增列ID (mysql_insert_id)

printf("insert id:%i\n" , (int)mysql_insert_id(conn) );

//更新表 (sql语句 update)

    sql = "UPDATE `test` SET `name` = floor(rand()*100)";

   if (mysql_query(conn , sql)){
                fprintf(stderr , "%s\n" , mysql_error(conn));
                fprintf(stderr , "%s\n" , sql);
                return 1;
        }

//insert、update、delete影响的行数 (mysql_affected_rows)

printf("%i rows affected\n" , (int)mysql_affected_rows(conn));

//查询表 (sql语句 select)

    sql = "SELECT * FROM `test`";
   if (mysql_query(conn , sql)){
       fprintf(stderr , "%s\n" , mysql_error(conn));
       fprintf(stderr , "%s\n" , sql);
       return 1;
   }

//将数据库查找结果集保存到客户端 (mysql_store_result)

//列数量 (mysql_field_count)

//mysql_field_count 返回作用在连接上的最近查询的列数。该函数的正常使用是在mysql_store_result()返回NULL时。在这种情况下，可调用mysql_field_count()来判定mysql_store_result()是否应生成非空结果。这样，客户端就能采取恰当的动作，而无需知道查询是否是SELECT（或类似SELECT的）语句.

   result = mysql_store_result(conn);
   if(result == NULL){
       if (mysql_field_count(conn) == 0){
           printf("affected rows %i" , (int)mysql_affected_rows(conn));
       } else {
           fprintf(stderr , "Error:%s\n" , mysql_error(conn));
       }

}

//从结果集获取列数 (mysql_num_fields)

num_fields = mysql_num_fields(result);

//获取结果集某列的信息 (mysql_fetch_field_direct)

    for (i = 0 ; i < num_fields ; i++){
       field = mysql_fetch_field_direct(result , i);
       printf("Field %u is %s\n" , i , field->name);
   }

//获取结果集所有列的信息 (mysql_fetch_fields)

    field = mysql_fetch_fields(result);
   for( i = 0 ; i < num_fields ; i++){
       printf("field name %s\n" , field[i].name);

}

    //获取结果集当前列并将游标移动到下一列 (mysql_fetch_field)
   while ((field = mysql_fetch_field(result))){
       printf("field name %s\n" , field->name);
   }

//将列的结果集游标移动到某一列位置 (mysql_field_seek)

    mysql_field_seek(result , 0);
   field = mysql_fetch_field(result);
   printf("Field 0's name: %s\n" , field->name);

//列的结果集中游标当前位置 (mysql_field_tell)

printf("filed_tell %i\n" , (int)mysql_field_tell(result));

//结果集的行数 (mysql_num_rows)

printf("%i records found \n" , (int)mysql_num_rows(result));

//获取结果集当前行并将游标移动到下一行 (mysql_fetch_row)

    while((row = mysql_fetch_row(result))!=NULL){
       for(i = 0 ; i < mysql_num_fields(result); i++){
           if (row[i] == NULL)
               printf("[NULL]\t");
           else
               printf("%s\t" , row[i]);

       }
       printf("\n");

}

//获取结果集中当前行的偏移量 (mysql_row_tell)

//将结果集游标移动到某偏移量(mysql_row_seek) offset是偏移量 MYSQL_ROW_OFFSET类型

   //这两个函数在已知点之间跳转很有用
   mysql_data_seek(result , 0);
   row_offset = mysql_row_tell(result);
   printf("row tell:%i\n" , (int)row_offset);
   mysql_row_seek(result , row_offset);
   row = mysql_fetch_row(result);
   printf("row field one:%s\n" , row[0]);

//将结果集游标移动到某行 (mysql_data_seek) (offset是行编号)

    for(i = 0 ; i < mysql_num_rows(result) ; i++){
       mysql_data_seek(result , i);
       if ((row = mysql_fetch_row(result))!=NULL){
           for( j = 0 ; j < mysql_num_fields(result) ; j++){
                       if (row[i] == NULL)
                           printf("[NULL]\t");
                           else
                           printf("%s\t" , row[j]);
                      }
       }

       printf("\n");
   }

//获取某行中的所有列数据的大小 (mysql_fetch_lengths)

    mysql_data_seek(result , 0);
   row = mysql_fetch_row(result);
   if (row) {
       lengths = mysql_fetch_lengths(result);
       for( j = 0 ; j < num_fields ; j++){
           printf("Column %u is %lu bytes in length.\n" , j , lengths[j]);
       }

   }

    //释放结果集 (mysql_free_result)

mysql_free_result(result);

//关闭连接 (mysql_close)

mysql_close(conn);
return 0;

}

最后使用 gcc -I/usr/include/mysql -lmysqlclient mysql_test.c 命令编译.