Mysql的C程序接口配置与使用，在Ubuntu下

1. 安装相关包

先安装mysql
sudo apt-get install mysql-server mysql-client

再装开发包
sudo apt-get install libmysqlclient15-dev

2. 测试安装结果

编译方法：
gcc $(mysql_config --cflags) xxx.c -o xxx $(mysql_config --libs)

/* Simple C program that connects to MySQL Database server*/
#include <mysql.h>
#include <stdio.h>

main() {
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;

   char *server = "localhost";
   char *user = "root";
   char *password = ""; /* 此处改成你的密码 */
   char *database = "mysql";

    conn = mysql_init(NULL);

   /* Connect to database */
   if (!mysql_real_connect(conn, server,
          user, password, database, 0, NULL, 0)) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
   }

   /* send SQL query */
   if (mysql_query(conn, "show tables")) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
   }

    res = mysql_use_result(conn);

   /* output table name */
   printf("MySQL Tables in mysql database:\n");
   while ((row = mysql_fetch_row(res)) != NULL)
      printf("%s \n", row[0]);

   /* close connection */
    mysql_free_result(res);
    mysql_close(conn);
}

3. 操作数据库

a 创建表并插入数据

conn = mysql_init(NULL);
  mysql_real_connect(conn, "localhost", "zetcode", "passwd", "testdb", 0, NULL, 0);
  mysql_query(conn, "CREATE TABLE writers(name VARCHAR(25))");

  char sql[100];
  sprintf(sql,"INSERT INTO ZFile VALUES(%u,%u,%u)",inta,intb,intc);
			
				  mysql_query(conn, sql);
  mysql_query(conn, "INSERT INTO writers VALUES('Jack London')");
  mysql_query(conn, "INSERT INTO writers VALUES('Honore de Balzac')");

b 查询数据

int main(int argc, char **argv)
{
  MYSQL *conn;
  MYSQL_RES *result;
  MYSQL_ROW row;
  int num_fields;
  int i;

  conn = mysql_init(NULL);
  mysql_real_connect(conn, "localhost", "zetcode", "passwd", "testdb", 0, NULL, 0);
  mysql_query(conn, "SELECT * FROM writers");
  result = mysql_store_result(conn);
  num_fields = mysql_num_fields(result);
  while ((row = mysql_fetch_row(result)))
  {
      for(i = 0; i < num_fields; i++)
      {
          printf("%s ", row[i] ? row[i] : "NULL");
      }
      printf("\n");
  }
  mysql_free_result(result);
  mysql_close(conn);
}

c 获取列头

#include <mysql.h>
int main(int argc, char **argv)
{
  MYSQL *conn;
  MYSQL_RES *result;
  MYSQL_ROW row;
  MYSQL_FIELD *field;

  int num_fields;
  int i;
  conn = mysql_init(NULL);
  mysql_real_connect(conn, "localhost", "zetcode", "passwd", "testdb", 0, NULL, 0);
  mysql_query(conn, "SELECT * FROM friends");
  result = mysql_store_result(conn);
  num_fields = mysql_num_fields(result);
  while ((row = mysql_fetch_row(result)))
  {
      for(i = 0; i < num_fields; i++)
      {
          if (i == 0) {
             while(field = mysql_fetch_field(result)) {
                printf("%s ", field->name);
             }
          printf("\n");
          }
         printf("%s  ", row[i] ? row[i] : "NULL");
      }
  }
  printf("\n");
  mysql_free_result(result);
  mysql_close(conn);
}

d 使用事务

如果插入的数据比较多，可以使用事务来批量处理数据库操作。

   if (mysql_autocommit(conn_ddt, 0)) {
      fprintf(stderr, "%s\n", mysql_error(conn_ddt));
      exit(1);
   }

   while(1){
      char * sql = "some operation";
      mysql_query(conn_ddt,sql );
   }

   if (mysql_commit(conn_ddt)) {
      fprintf(stderr, "%s\n", mysql_error(conn_ddt));
      exit(1);
   }

一段C代码，初始化一段内存，然后动态扩大。

unsigned int nMax = 1024;
unsigned int *w, *d;

void init() {
    w = malloc(nMax * sizeof(*w));
    d = malloc(nMax * sizeof(*d));
// memset(w,0,nMax * sizeof(unsigned int));
    for (p = 0; p < nMax; p++) {
        w[p] = 0;
        d[p] = 0;
    }
}

void resizeNarrays() {
    int i, nMaxOld;
    nMaxOld = nMax;
    nMax = N*2;
    if (verbose)
        printf("Increasing NMAX to %d.\n", nMax);
    w = realloc(w, nMax * sizeof(*w));
    d = realloc(d, nMax * sizeof(*d));
    for (i = nMaxOld; i < nMax; i++) {
        w[i] = -1;
        d[i] = -1;
    }
}

void main() {
    while(something){
        d[N-1] = some;
        if (N >= nMax)
            resizeNarrays();
    }
}