CRUD
table
1create table if not exists `student` ( 2 `id` int auto_increment, 3 `name` varchar(16) not null, 4 `age` int not null, 5 `address` varchar(128) not null, 6 primary key (`id`) 7)ENGINE=InnoDB DEFAULT CHARSET=utf8;
code
1// 2// Created by zhangrongxiang on 2018/3/5 14:56 3// File main 4// 5 6#include <mysql/mysql.h> 7#include <stdio.h> 8#include <stdlib.h> 9#include <string.h> 10 11int main() { 12 MYSQL *mysql = NULL; 13// const char *host = "debian"; 14 const char *host = "localhost"; 15 const char *user = "root"; 16 const char *passwd = "root"; 17 const char *db = "fgap_config"; 18 unsigned int port = 3306; 19 const char *unix_socket = "/var/run/mysqld/mysqld.sock"; 20 int flag = 0, i = 0; 21 const char *db2 = "test"; 22 char sql[1024] = {0}; 23 24 if (mysql_library_init(0, NULL, NULL)) { 25 fprintf(stderr, "could not initialize MySQL client library\n"); 26 exit(EXIT_FAILURE); 27 } 28 mysql = mysql_init(mysql); 29 if (mysql == NULL) { 30 perror("mysql_init error"); 31 return EXIT_FAILURE; 32 } 33 printf("mysql_init success\n"); 34 mysql = mysql_real_connect(mysql, host, user, passwd, db, port, unix_socket, 0); 35 if (mysql == NULL) { 36 fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(mysql)); 37 return EXIT_FAILURE; 38 } 39 printf("mysql_real_connect success\n"); 40 int ping = mysql_ping(mysql); 41 if (ping == 0) { 42 printf("mysql is active !\n"); 43 } else { 44 return EXIT_FAILURE; 45 } 46 47 ////////////////////////////////////////////////////////////////// 48 const char *clientInfo = mysql_get_client_info(); 49 //5.5.58 50 printf("mysql_get_client_info : %s\n", clientInfo); 51 52 unsigned long version = mysql_get_client_version(); 53 //50558 = 5*10000 + 5*100 + 58 54 //major_version*10000 + release_level*100 + sub_version 55 printf("mysql_get_client_version : %ld\n", version); 56 57 const char *serverInfo = mysql_get_server_info(mysql); 58 //5.5.58-0+deb8u1 59 printf("mysql_get_server_info : %s\n", serverInfo); 60 61 const char *hostInfo = mysql_get_host_info(mysql); 62 //Localhost via UNIX socket 63 printf("mysql_get_host_info : %s\n", hostInfo); 64 65 unsigned int protoInfo = mysql_get_proto_info(mysql); 66 //10 67 printf("mysql_get_proto_info : %d\n", protoInfo); 68 69 unsigned long serverVersion = mysql_get_server_version(mysql); 70 //50558 71 printf("mysql_get_server_version : %ld\n", serverVersion); 72 73 const char *mysqlInfo = mysql_info(mysql); 74 //(null) 75 printf("mysql_info : %s\n", mysqlInfo); 76 77 const char *stat = mysql_stat(mysql); 78 //mysql_stat : Uptime: 70217 Threads: 2 Questions: 824 Slow queries: 0 Opens: 69 Flush tables: 1 Open tables: 59 Queries per second avg: 0.011 79 printf("mysql_stat : %s\n", stat); 80 ////////////////////////////////////////////////////////////////// 81 82 MYSQL_RES *pRes = mysql_list_dbs(mysql, NULL); 83 MYSQL_ROW row = NULL; 84 if (pRes) { 85 unsigned int fields = mysql_num_fields(pRes); 86 my_ulonglong rows = mysql_num_rows(pRes); 87 unsigned int count = mysql_field_count(mysql); 88 printf("mysql_num_fields : %d\n", fields);//1 89 printf("mysql_num_rows : %ld\n", (long) rows);//4 90 printf("mysql_field_count : %d\n", count);//1 91 while ((row = mysql_fetch_row(pRes)) != NULL) { // 打印结果集 92 // database: information_schema 93 // database: fgap_config 94 // database: mysql 95 // database: performance_schema 96 if (strcmp(row[fields - 1], db2) == 0) { 97 flag = 1; 98 } 99 printf("database: %-10s\n", row[fields - 1]); 100 } 101 mysql_free_result(pRes); 102 } 103 104 if (flag == 0) { 105 printf("no %s db -> %d\n", db2, flag); 106 //int mysql_create_db(MYSQL *mysql, const char *db) 107 /* 108 * //main.c:(.text+0x34f): undefined reference to `mysql_create_db' 109 if(mysql_create_db(mysql,"test") == 0){ 110 printf("create test success\n"); 111 } else{ 112 printf("create test error\n"); 113 } 114 */ 115 } 116 if (mysql_select_db(mysql, db2) == 0) { 117 printf("mysql_select_db(mysql, %s) success \n", db2); 118 MYSQL_RES *tables = mysql_list_tables(mysql, NULL); 119 unsigned int fields = mysql_num_fields(tables); 120 my_ulonglong rows = mysql_num_rows(tables); 121 printf("mysql_num_fields : %d\n", fields);//1 122 printf("mysql_num_rows : %d\n", (int) rows);//1 123 if (tables) { 124 flag = 0; 125 MYSQL_ROW fetchRow = NULL; 126 while ((fetchRow = mysql_fetch_row(tables)) != NULL) { 127 if (strcmp(fetchRow[fields - 1], "student") == 0) 128 flag = 1; 129 printf("table : %-10s\n", fetchRow[fields - 1]); 130 } 131 if (!flag) { 132 FILE *file = NULL; 133 file = fopen("student.sql", "r"); 134 if (file) { 135 while (!feof(file)) { 136 char str[64] = {0}; 137 fgets(str, sizeof(str), file); 138 strcat(sql, str); 139 } 140 printf("%s\n", sql); 141 } else { 142 fprintf(stderr, "fopen student.sql error\n"); 143 exit(EXIT_FAILURE); 144 } 145 fclose(file); 146 int r = mysql_real_query(mysql, sql, (unsigned long) strlen(sql)); 147 if (r == 0) { 148 printf("table student was created success !! \n"); 149 } else { 150 printf("table student was created error !! \n"); 151 exit(EXIT_FAILURE); 152 } 153 } else { 154 printf("table student already exists !! \n"); 155 } 156 } else { 157 printf("something wrong\n"); 158 } 159 } 160 161 ///////////////////////////////////////////////////////////////// 162 163 memset(sql, 0, sizeof(sql)); 164 sprintf(sql, "select count(id) from student"); 165 int count = 0; 166 if (mysql_real_query(mysql, sql, (unsigned int) strlen(sql)) == 0) { 167 MYSQL_RES *result = mysql_store_result(mysql); 168// my_ulonglong rows = mysql_num_rows(result); 169 unsigned int fields = mysql_num_fields(result); 170 MYSQL_ROW fetchRow = mysql_fetch_row(result); 171 count = atoi(fetchRow[fields - 1]); 172 printf("count %d\n", count); 173 mysql_free_result(result); 174 } 175 if (count < 50) { 176 memset(sql, 0, sizeof(sql)); 177 for (i = 0; i < 10; ++i) { 178 char name[16] = {0}; 179 char address[64] = {0}; 180 sprintf(name, "zing-%d", i); 181 sprintf(address, "jiangsu-wuxi-%d", i); 182 sprintf(sql, "INSERT INTO `student` (`name`,`age`,`address`) VALUES('%s','%d','%s')", name, 10 + i, 183 address); 184 if (mysql_real_query(mysql, sql, (unsigned int) strlen(sql)) == 0) { 185 my_ulonglong id = mysql_insert_id(mysql); 186 printf("insert num %ld success\n", (long int) id); 187 } 188 } 189 } 190 memset(sql, 0, sizeof(sql)); 191 sprintf(sql, "select * from student order by id asc limit 0,20"); 192 if (mysql_real_query(mysql, sql, (unsigned int) strlen(sql)) == 0) { 193 MYSQL_RES *result = mysql_store_result(mysql); 194 unsigned int fields = mysql_num_fields(result); 195 printf("fields %d\n", fields); 196 MYSQL_ROW fetchRow; 197 while ((fetchRow = mysql_fetch_row(result)) != NULL) { 198 printf("id:%s name:%s age:%s address:%s \n", fetchRow[0], fetchRow[1], fetchRow[2], fetchRow[3]); 199 } 200 mysql_free_result(result); 201 } 202 /////////////////////////////////////////////////////////////////////////////////////// 203 memset(sql, 0, sizeof(sql)); 204 sprintf(sql, "update student set name='%s',age=%d,address='%s' where id between %d and %d;", "zhangrongxiang", 24, 205 "jiangsu-xuzhou-1", 1, 10); 206 if (mysql_real_query(mysql, sql, (int) strlen(sql)) == 0) { 207 printf("mysql_affected_rows : %d\n", (int) mysql_affected_rows(mysql)); 208 } 209 210 mysql_close(mysql); 211 mysql_library_end(); 212 return EXIT_SUCCESS; 213} 214 215/* 216 * 217 * Initialize the MySQL client library by calling mysql_library_init(). 218 * This function exists in both the libmysqlclient C client library and the libmysqld embedded server library, 219 * so it is used whether you build a regular client program by linking with the -libmysqlclient flag, or an embedded server application by linking with the -libmysqld flag. 220 * 221 * Initialize a connection handler by calling mysql_init() and connect to the server by calling mysql_real_connect(). 222 * 223 * Issue SQL statements and process their results. (The following discussion provides more information about how to do this.) 224 * 225 * Close the connection to the MySQL server by calling mysql_close(). 226 * 227 * End use of the MySQL client library by calling mysql_library_end(). 228 */