Using C language to call MySQL database programming combat and tricks

今天编写使用C语言调用mysql数据库编程实战以及技巧，为其他IT同行作为参考，当然有错误可以留言，共同学习。

First, the MySQL database C language Common interface API
1. First of all, the link database Mysql_real_connect, the prototype is as follows:
MYSQL * stdcall Mysql_real_connect (
MYSQL *mysql, const char *host,
const Char *user,
const Char *PASSWD,
const Char *DB,
unsigned int port,
const Char *unix_socket,
unsigned long clientflag);
The first parameter MySQL is a very important variable in the C language API, which is very rich in memory, with Port,dbname,charset and other connection basic parameters. It also contains a struct variable called st_mysql_methods, which holds a number of function pointers, which are called in various data operations after the database connection succeeds. Mysql_real_connect function of the parameters, the basic is as implies meaning.

2. Once the connection database is successful, you can use Mysql_query to execute the SQL statement as follows:
int stdcall mysql_query (MySQL *mysql, const char *q);
The first parameter is described above, the second parameter is to execute the SQL statement, the main is to execute the SQL statement of the increase, delete, change, check and other functions.
This function is generally two-step:
(1) Send SQL statements, in fact, a socket to send SQL statements, plus MySQL fixed protocol header.
(2) Then the result is accepted, and this will call the Read_query_result function pointer in st_mysql_methods in the MySQL variable

A If you have a query that contains binary data, use Mysql_real_query.

b Check the number of rows affected by the query:
My_ulonglong mysql_affected_rows (MySQL *connection);
My_ulonglong is unsigned long shaped, for%lu format
This function returns the number of rows affected by the previous execution of the Update,insert or delete query.

#include <stdio.h>#include <string.h>#include <stdlib.h>#include <math.h>#include "/usr/local/mysql/include/mysql.h"StaticMySQL MySQL, *sock;Charsql[1024x768]; Mysql_res *res=null; Mysql_row ROW;intNum_fields;intNum_rows =0;intMain () {memset(SQL,0x00,sizeof(SQL)); Mysql_init (&mysql);if(! (sock = Mysql_real_connect (&mysql, (Char*)"localhost", (Char*)"Ebipcs",         (Char*)"Dcep2vunax", (Char*)"Ebipcs",0Null0))){printf("couldn ' t connect to db!\n\n%s\n\n", Mysql_error (&mysql));return 0; }if(sock) {printf("success\n"); }Else{printf("Fail\n");return 0; }sprintf(SQL,"Update cisaddressinfo set cisaddressinfo.addressseqno = ' 2 ' where cisaddressinfo.customid= ' 199999900000000000015 '; ');if(mysql_query (sock, SQL)) {printf("mysql_query[%d" [%s]!\n], Mysql_errno (sock), Mysql_error (sock));return-1; }if( ! (Mysql_affected_rows (sock))) {printf("Update ok\n"); }Else{printf("Update fail\n"); }if(Mysql_errno (sock)) {printf("mysql_affected_rows[%d" [%s]!\n], Mysql_errno (sock), Mysql_error (sock)); }return 0;}

3. Store execution Results
The most common use of SQL is to extract data instead of inserting or updating data. The data is extracted using the SELECT statement
C Application Extraction data generally requires 4 steps:
1. Execute Query
2. Extracting data
3. Processing data
4, the necessary cleaning work
Just like the previous insert and update, use mysql_query to send SQL statements, and then use Mysql_store_result or Mysql_use_result to extract the data, depending on how you want to extract the data. Next, a series of mysql_fetch_row will be used to process the data. Finally, use Mysql_free_result to release the memory resources that the query consumes.

A extract all data at once: Mysql_store_result
If Mysql_query returns successfully, then we will read the result through the Mysql_store_result function. The prototype is as follows:
Mysql_res * stdcall mysql_store_result (MySQL *mysql);
The function calls the Read_rows function pointer in st_mysql_methods in the MySQL variable to get the result of the query. At the same time, the function returns a variable such as mysql_res, which is used primarily to hold the results of a query. At the same time, the function malloc a memory space to store the query data, so we must remember the free (result), otherwise it will definitely cause a memory leak. After executing the Mysql_store_result, the data are already in the mysql_res variable.
Related functions:
This function is used after a successful call to Mysql_query, which saves all data returned in the client immediately. It returns a pointer to the result set structure and returns NULL if it fails
Mysql_res *mysql_store_result (MySQL *connection);
This function accepts the result structure set returned by Mysql_store_result and returns the number of rows in the structure set.
My_ulonglong mysql_num_rows (Mysql_res *result);
This function extracts a row from the resulting structure using mysql_store_result and puts it into a row structure. Returns NULL when the data is exhausted or an error occurs.
Mysql_row mysql_fetch_row (Mysql_res *resutl);
This function is used to jump in the result set, setting the rows that will be returned by the next mysql_fetch_row operation. The parameter offset is a line number, which must be within the range of the total number of rows-1 of the 0~ result. Passed
0 will cause the next Mysql_fetch_row call to return the first row in the result set.
void Mysql_data_seek (Mysql_res *result, my_ulonglong offset);
Returns an offset value that is used to represent the current position in the result set. It is not a line number, it cannot be used for Mysql_data_seek
Mysql_row_offset Mysql_row_tell (Mysql_res *result);
This will move the current position in the result set and return to the previous position
Mysql_row_offset Mysql_row_seek (mysql_res *result, Mysql_row_offset OFFSET);
After you have done all the data, you must always call this to deal with the aftermath.
void Mysql_free_result (Mysql_res *result);

#include <stdio.h>#include <stdlib.h>#include <string.h>#include "mysql.h"#include "errmsg.h"#include "mysqld_error.h"MYSQL Conn; Mysql_res *res_ptr; Mysql_row Sqlrow;voidConnectionConst Char* Host,Const Char* User,Const Char* Password,Const Char* database) {mysql_init (&conn);//Take note of the address character &    if(Mysql_real_connect (&conn, host, user, password, database,0Null0)) {printf("Connection success!\n"); }Else{fprintf(stderr,"Connection failed!\n");if(Mysql_errno (&conn)) {fprintf(stderr,"Connection error%d:%s\n", Mysql_errno (&conn), Mysql_error (&conn)); }Exit(exit_failure); }}intMain (intargcChar*argv[]) {connection ("localhost","Root","Shuang","Shuangde");intres = mysql_query (&conn,"SELECT * FROM Student");if(RES) {fprintf(stderr,"Select error:%s\n", Mysql_error (&conn)); }Else{res_ptr = Mysql_store_result (&conn);if(RES_PTR) {printf("Retrieved%lu rows\n", (unsigned Long) mysql_num_rows (res_ptr)); while((Sqlrow = Mysql_fetch_row (res_ptr))) {printf("fetched data...\n") ; }if(Mysql_errno (&conn)) {fprintf(stderr,"retrive error:%s\n", Mysql_error (&conn));        } mysql_free_result (RES_PTR); }} mysql_close (&conn);Exit(exit_success);}

b fetch one row of data at a time: Mysql_use_result
Using the same method as Mysql_store_result, change the mysql_store_result of the above code to Mysql_use_result.
Mysql_use_result has substantial resource management benefits, better balance of network load, and reduced storage overhead for potentially very large data, but not with Mysql_data_seek, Mysql_row_seek, Mysql_row_ Tell, mysql_num_rows use together. If the data is relatively small, use mysql_store_result better.

Handles the returned data-related functions and definitions:
Returns the number of fields (columns) in the result set
unsigned int mysql_field_count (MySQL *connection);
Extract metadata and data into a new structure
Mysql_field *mysql_fetch_field (MySQL *result);
This function overrides the current field number, which is automatically incremented with each Mysql_fetch_field call. If you pass 0 to offset, you will jump back to column 1th
Mysql_field_offset Mysql_field_seek (MySQL *result, Mysql_field_offset OFFSET);
Mysql_field is defined in Sql.h, which is a pointer to the field structure data, with information about the column. Have members:
Char *name; Column name, as String
Char *table; Column belongs to table name
Char *def; If Mysql_list_fields is called, it will contain the default value for that column
Enum Enum_field_types type; Column type
unsigned int length; Column width
unsigned int max_length; If you use Mysql_store_result, it will contain the length of the longest column value extracted in bytes, and if you use Mysql_use_result, it will not be set
unsigned int flags; A flag about a column definition, regardless of the data that is obtained. Common signs have the following meanings:
Not_null_flag
Pri_key_flag
Unsigned_flag
Auto_increment_flag
Binary_flag, etc.
unsigned int decimals; Number of digits after the decimal point.
The column types are quite extensive and the complete list is seen in the header file mysql_com.h, which is common:
Field_type_decimal
Field_type_long
Field_type_string
Field_type_var_string
A particularly useful booking macros: Is_num, returns True when the field type is a number

#include <stdio.h>#include <stdlib.h>#include <string.h>#include "mysql.h"#include "errmsg.h"#include "mysqld_error.h"MYSQL Conn; Mysql_res *res_ptr; Mysql_row Sqlrow;voidConnectionConst Char* Host,Const Char* User,Const Char* Password,Const Char* database) {mysql_init (&conn);//Take note of the address character &    if(Mysql_real_connect (&conn, host, user, password, database,0Null0)) {printf("Connection success!\n"); }Else{fprintf(stderr,"Connection failed!\n");if(Mysql_errno (&conn)) {fprintf(stderr,"Connection error%d:%s\n", Mysql_errno (&conn), Mysql_error (&conn)); }Exit(exit_failure); }}voidDisplay_row () {unsigned intField_count = Mysql_field_count (&conn);inti =0; while(I < Field_count) {if(Sqlrow[i])printf('%s ', Sqlrow[i]);Else printf("NULL");    i++; }printf("\ n");}voidDisplay_header () {Mysql_field *field_ptr;printf("Column details:\n"); while((Field_ptr = Mysql_fetch_field (res_ptr)) = NULL) {printf("\ t Name:%s\n", field_ptr->name);printf("\ t Table:%s\n", field_ptr->table);printf("\ t Type:");if(Is_num (Field_ptr->type)) {printf("Numeric field\n"); }Else{Switch(Field_ptr->type) { CaseField_type_var_string:printf("varchar\n"); Break; CaseField_type_long:printf("LONG"); Break;default:printf("Type is%d, check in msyql_com.h\n", Field_ptr->type); }           }printf("\ t Max width%ld\n", field_ptr->length);if(Field_ptr->flags & Auto_increment_flag)printf("\ t Auto increments\n");printf("\ n"); }}intMain (intargcChar*argv[]) {connection ("localhost","Root","Shuang","Shuangde");intres = mysql_query (&conn,"SELECT * FROM Student");if(RES) {fprintf(stderr,"Select error:%s\n", Mysql_error (&conn)); }Else{res_ptr = Mysql_use_result (&conn);if(RES_PTR) {intFirst =1; while((Sqlrow = Mysql_fetch_row (res_ptr))) {if(first)                    {Display_header (); First =0;            } display_row (); }if(Mysql_errno (&conn)) {fprintf(stderr,"retrive error:%s\n", Mysql_error (&conn));        } mysql_free_result (RES_PTR); }} mysql_close (&conn);Exit(exit_success);}

Tips for use:
A about the MYSQL mysql_res type variable definition and free problem.
It would be better to use the following method.
Definition: Mysql_res *res=null;
Use: res = Mysql_store_result (m_sock)
Release: if (res)
{mysql_free_result (res); Res=null}
Based on the above rules, we define a macro:
* #define MYSQLFREE (a) \
if (A! = NULL) \
{ \
Mysql_free_result (a); \
A=null;\
}
When used: Mysqlfree (RES)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Using C language to call MySQL database programming combat and tricks