Using C language to call MySQL database programming combat and tricks

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

The C language of MySQL database often uses interface API
1. First of all, of course the link database mysql_real_connect, prototypes such as the following:
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. The memory inside is very rich. There are port,dbname,charset and other connection basic parameters. It also includes a struct variable called st_mysql_methods, which holds a number of function pointers. These function pointers will be called in various data operations after the database connection is successful.

The Mysql_real_connect function in each of the parameters, the basic is as implies meaning.

2. Once the connection database is successful, you can run the SQL statement using mysql_query. Prototypes such as the following:
int stdcall mysql_query (MySQL *mysql, const char *q);
The first number of references has been described above. The second parameter is to run the SQL statement, the main is to run the SQL statement to add, delete, change, check and other functions.

This function is a two-step whole:
(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 assumes a query that includes binary data, to 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 run of Update,insert or delete queries.

#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 running 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, run the 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, and the details of which statement to use depend on how you want to extract the data. Next, a series of mysql_fetch_row will be used to process the data. At last. Use Mysql_free_result to free the memory resources that the query consumes.

A extract all data at once: Mysql_store_result
Assuming Mysql_query returns successfully, we will read the result by Mysql_store_result function. Prototypes such as the following:
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 running the Mysql_store_result, the data is actually already in the mysql_res variable.
Related functions:
This function is used after a successful call to Mysql_query, which will immediately save all the data returned in the client. It returns a pointer to the result set structure, assuming that the failure returns null
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. Sets the row that will be returned by the next mysql_fetch_row operation.

The parameter offset is a line number. It must be within the range of 0~ results in the total number of rows-1.

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 all the operations on the data are completed. This must always be called 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
Usage is exactly the same as Mysql_store_result. Change the mysql_store_result of the above code to Mysql_use_result.
Mysql_use_result has substantial advantages in resource management to better balance Network load. and reduces the storage overhead associated with potentially large data, but cannot be used with Mysql_data_seek, Mysql_row_seek, Mysql_row_tell, mysql_num_rows. Suppose the data is less and better with Mysql_store_result.

The

Processes 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 is used to override the current field number. The number will be added on its own with each Mysql_fetch_field call. Assuming that you pass 0 to OFFSET, you will jump back to the 1th column
Mysql_field_offset mysql_field_seek (MYSQL *result, Mysql_field_offset OFFSET); The
//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; Assume that you call Mysql_list_fields. It will include the default value for that column
Enum Enum_field_types type; Column type
unsigned int length; Column width
unsigned int max_length; Assuming that Mysql_store_result is used, it will include the length of the longest column value extracted in bytes, assuming that using mysql_use_result 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 pre-defined macro: 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 methods, for example.
Definition: Mysql_res *res=null;
Use: res = Mysql_store_result (m_sock)
Release: if (res)
{mysql_free_result (res); Res=null}
According to the above rules. We define a macro:
* #define MYSQLFREE (a) \
if (A! = NULL) \
{ \
Mysql_free_result (a); \
A=null;\
}
When used: Mysqlfree (RES)

Using C language to call MySQL database programming combat and tricks