C-language operation MySQL Database

Directory

1. mysql Data structure

2. mysql Common function

3. mysql Operation flow

4. Example

MySQL is an open source code of small relational database management system, small size, fast, low overall cost, open source. MySQL has the following features:

(1) Use C and C + + written, and use a variety of compilers to test, to ensure the portability of the source code.

(2) Support multi-threading, utilize CPU resources, support multi-user.

(3) A large database with thousands records can be processed.

(4) can be used as a separate application in the Client server network environment, but also as a library and embedded in other software.

1. mysql Data structure

Describes the commonly used MySQL-related c variable types. These types are used when defining variables behind us.

1. MYSQL

To connect to MySQL, a MySQL instance must be established and the connection can begin by Mysql_init initialization

Example:

MySQL MySQL; #创建MYSQL实例

Mysql_init (&mysql); #初始化开始进行连接

2, Mysql_res

This structure represents the result of a query that returns rows (SELECT, SHOW, DESCRIBE, EXPLAIN). The returned data is called a "dataset." The data is read from the database and finally the data is read from the mysql_res.

PS: Like the resultset variable inside Java, save the result set in this variable and read the data from it.

3, Mysql_row

This is a representation of the type safety (type-safe) of a row of data. A string array that is currently implemented as a count byte. Line is obtained by calling Mysql_fetch_row ()

4, Mysql_field

You can get the Mysql_field structure for each column by repeating the call to Mysql_fetch_field (). This structure contains field information, such as field names, types, and sizes.

PS: This variable defines a column-related property problem!

The following code block is used to connect to the database of the communication process, to connect to MySQL, you must establish a MySQL instance, through the Mysql_init initialization can start the connection.

typedef struct ST_MYSQL {

NET net; /* Communication Parameters */

Gptr connector_fd; /* CONNECTORFD for SSL */

Char *host,*user,*passwd,*unix_socket,

*server_version,*host_info,*info,*db;

unsigned int port,client_flag,server_capabilities;

unsigned int protocol_version;

unsigned int field_count;

unsigned int server_status;

unsigned long thread_id; /* Id for connection in server */

My_ulonglong affected_rows;

My_ulonglong insert_id; /* ID if insert on table with NEXTNR */

My_ulonglong Extra_info; /* Used by Mysqlshow */

unsigned long packet_length;

Enum Mysql_status status;

Mysql_field *fields;

Mem_root Field_alloc;

My_bool Free_me; /* If free in Mysql_close */

My_bool Reconnect; /* Set to 1 if automatic reconnect */

struct st_mysql_options options;

Char scramble_buff[9];

struct Charset_info_st *charset;

unsigned int server_language;

} MYSQL;

This structure represents the result of a query (,,,) that returns a row SELECT SHOW DESCRIBE EXPLAIN . The returned data is called the "DataSet", the friend who used the database should not be unfamiliar with the result set in the database, the corresponding in the C API is Mysql_res, reading from the database, and finally reading the data from the Mysql_res.

typedef struct ST_MYSQL_RES {

My_ulonglong Row_count;

unsigned int field_count, Current_field;

Mysql_field *fields;

Mysql_data *data;

Mysql_rows *data_cursor;

Mem_root Field_alloc;

Mysql_row ROW; /* If unbuffered Read */

Mysql_row Current_row; /* Buffer to current row */

unsigned long *lengths; /* column lengths of current row */

MYSQL *handle; /* for unbuffered reads */

My_bool EOF; /* Used my mysql_fetch_row */

} mysql_res;

2. mysql Common function

Required header files: #include <mysql/mysql.h>
Function: Get or initialize a MySQL structure
Function prototypes: MySQL *mysql_init (MySQL *mysql)
function return value: A mysql* handle that is initialized
Note: null is returned in case of low memory

Required header files: #include <mysql/mysql.h>
function function: Close a server connection and release the memory associated with the connection
Function prototype: void Mysql_close (MySQL *mysql);
function passed in value: MYSQL: pointer of type
function return value: None

Required header files: #include <mysql/mysql.h>
function function: Connect a MySQL server
Function prototype: MySQL * mysql_connect (mysql *mysql,const char *host,const char *user,const char *passwd);
function passed in value: MySQL represents an existing MySQL structure address
Host indicates the hostname or IP of the MySQL server
User name for Login
PASSWD indicates the password of the login
function return Value: If the connection succeeds, a MySQL * connection handle: NULL If the connection fails
Note: This function is not recommended, use Mysql_real_connect () instead

Required Documents: #include <mysql/mysql.h>
function function: MySQL *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 int client_flag);
function passed in value: MySQL represents an existing MySQL structure address
Host indicates the hostname or IP of the MySQL server
User name for Login
PASSWD indicates the password of the login
DB indicates the database to connect to
Port indicates the TCP/IP port of the MySQL server
Unix_socket indicates the connection type
Client_flag indicates that MySQL is running the ODBC database tag
function return Value: If the connection succeeds, a mysql* connection handle: NULL If the connection fails

Required header files: #include <mysql/mysql.h>
function function: Returns the number of rows affected by the most recent update,delete or insert query
function passed in value: MYSQL: Type pointer
function return value: An integer greater than 0 indicates the number of rows affected or retrieved. Zero indicates that there is no record of the WHERE clause in the lookup sequence or that no query has been executed yet;-1 indicates that the query returned an error, or for a select query

Required header files: #include <mysql/mysql.h>
function function: Execute a query on the specified connection
Function prototype: int mysql_query (mysql *mysql,const char *query);
function passed in value: query represents the SQL statement executed
function return value: If the query succeeds, zero, the error is nonzero.
Correlation function: Mysql_real_query

Required header files: #include <mysql/mysql.h>
function function: Obtain the result identifier for the unbuffered result set
Function prototype: Mysql_res *mysql_use_result (MYSQL *mysql);
function passed in value: MYSQL: pointer of type
function return value: A mysql_res result structure, if an error occurs, a null is sent

#incluee <mysql/mysql.h>
Retrieves the next row of a result collection
Mysql_row mysql_fetch_row (Mysql_res *result);
Mysql_res: Pointers to structures
A mysql_row structure for the next line. If there are no more rows to retrieve or if an error occurs, NULL

#include <mysql/mysql.h>
Returns the number of columns in the specified result set
unsigned int mysql_num_fields (mysql_res *res);
Pointers to Mysql_res structures
An unsigned integer of the number of fields in the result collection

#include <mysql/mysql.h>
Create a database
int mysql_create_db (MySQL *mysql,const char *db);
MYSQL: pointer of type
DB: The name of the database to be created
If the database was successfully created, zero is returned, and nonzero if an error occurs.

#include <mysql/mysql.h>
Select a database
int mysql_select_db (MySQL *mysql,const char *db);
MYSQL: pointer of type
DB: The name of the database to be created
If the database was successfully created, zero is returned, and nonzero if an error occurs.

3. mysql Operation flow

1. Initialize the MySQL library by calling Mysql_library_init (). The library can be either the Mysqlclient C client library or the Mysqld Embedded server library, depending on whether the application is linked to the "-libmysqlclient" or "-LIBMYSQLD" flag.

2. Initialize the connection handler by calling Mysql_init () and connect to the server by calling Mysql_real_connect ().

3. Issue the SQL statement and process its results. (in the following discussion, the method of using it is described in detail).

4. Close the connection to the MySQL server by calling Mysql_close ().

5. End the use of the MySQL library by calling Mysql_library_end ().

4. Simple example

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>

int main (int agrc,char *agrv[])
{
MYSQL *conn;
Mysql_res *res;
Mysql_row ROW;
int i;

conn = Mysql_init (NULL);//Initialize Connection
if (conn==null)
{
printf ("Mysql_init error\n");
return exit_failure;
}
conn = Mysql_real_connect (conn, "localhost", "root", "123456", "Test", 3306,null,0);//Connect to Database
if (conn==null
{
printf ("Mysql_real_connect error");
}

if (mysql_query (conn, "select *from test")//performs a database operation, which can be an insert Updata,delete operation
{
printf ("Select Error: %s\n ", MYSQL_ERROR (conn));
}

Res =mysql_store_result (conn);

int inum_rows = mysql_num_rows (res);//Get a few rows in the dataset
int inum_fields=mysql_num_fields (res);//Get a few columns of data set

printf ("rows=%d,fields=%d\n", inum_rows,inum_fields);

while ((Row=mysql_fetch_row (res)))//Get a row of results
{
for (i=0;i<inum_fields;i++)//Display data
{
printf (" %s\t ", Row[i]);
}
printf ("\ n");
}
Mysql_free_result (res);//Release result set contents
Mysql_close (conn);//Disconnect

return exit_success;

The results show:

}

5. Makefile Compilation

Mysqltest:mysqltest.c
GCC mysqltest.c-l/usr/lib/mysql-lmysqlclient-lstdc++-ldl-lpthread-lrt-o mysqltest

Clean
Rm-f Mysqltest

Compile Link:

Gcc-g-o MySQL mysql1.c-i/usr/include/mysql-l/usr/lib-lmysqlclient-lpthread-lm-lrt-ldl

can also: gcc-g-o mysql mysql.c $ (mysql_config--cflags) $ (mysql_config--libs)

Explain:

(1) Mysql_config--cflags search for MySQL compiler related header files and other information.

(2) Mysql_config--libs look for the library functions required for MySQL compilation.

(3)-l/usr/lib indicates the path that the static library is looking for. You can also not write here, because the system will go to the default/lib,/usr/lib to find the library function. If LIBMYSQLCLIENT.A is under/usr/lib/mysql, then-l/usr/lib/mysql this directory.

(4) Non-standard library functions, linked with-lmysqlclient. The original library is named Libmysqlclient.a minus Lib and. A.

(5)-I is a header file and other information, and include.

(6) The search order of dynamic Link libraries:

A. Ld_library_path settings in the directory.

B./etc/ld.so.conf add to the directory in this file.

C. The default/usr/lib,/lib directory.

(7) Search Order of header files

A.-I in the specified directory to find

The B.GCC environment variable specifies the directory C_include_path, Cplus_include_path, objc_include_pathc. /usr/include/usr/local/include, and other directories under search.

(8) Search Order of static libraries

A. Static library search path specified by-l

B.GCC environment variable Library_path

C./lib,/usr/lib,/usr/local/lib

C-language operation MySQL Database