Operate MySql in C Language

Http://andrew913.iteye.com/blog/433280

There are not many database operations using the C language. Generally, file operations are selected.
MySQL is used in a recent project, so I went to check the C API of MySQL.
It is basically the same. To put it bluntly, it is an application-layer protocol. Because there are not many opportunities to do it, you should write it down so that you do not forget it later.

1. First of all, it is a connection. The function prototype is as follows::

C code

1. MySQL * stdcall mysql_real_connect (MySQL * MySQL, constchar * host,
2. Const
   Char * user,
3. Const
   Char * passwd,
4. Const
   Char * dB,
5. Unsigned int port,
6. Const
   Char * unix_socket,
7. Unsigned long clientflag );

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 c api. It has a wide array of memory, including port, dbname, charset, and other connection basic parameters. It also contains a struct variable named st_mysql_methods, which stores many function pointers, which will be called in various data operations after the database connection is successful.
The parameters in the mysql_real_connect function are mostly deliberate.

After the connection is successful, you need to execute the SQL statement.

The function is prototype as follows: C code

1. Int stdcall mysql_query (MySQL * MySQL, constchar * q );

Intstdcall mysql_query (MySQL * MySQL, const char * q );

The first parameter has been described above, and the second parameter is the SQL statement to be executed.
This function takes two steps.
(1). When sending SQL statements, an SQL statement is actually sent using a socket, coupled with a fixed MySQL protocol header. When I read the source code, I caught the package as follows:
0000 19 00 00 00 03 73 65 6C 65 63 74 20 61 70 70 5f ...... select app _
0010 6e 61 6D 65 20 66 72 6f 6D 20 61 70 70 name from app

The red part is the protocol, and the first two are actually the package length. The specific protocol has not been studied.

(2). Then accept the result. Here, the read_query_result function pointer in st_mysql_methods in MySQL is called.

Obtain results
After the SQL statement is executed, if it is a query statement, we certainly need to read data. If it is an update or insert statement, check whether the operation is successful.
Let's take a look at how to get the query results:

If mysql_query returns a successful result, we can use the mysql_store_result function to read the result. The prototype is as follows: C code

1. Mysql_res * stdcall mysql_store_result (MySQL * MySQL)

Mysql_res * stdcall mysql_store_result (MySQL * MySQL)

This function calls the read_rows function pointer in st_mysql_methods of the MySQL variable to obtain the query result. At the same time, this function will return a variable such as mysql_res, which is mainly used to save the query results. At the same time, this function malloc stores a piece of memory space to store the queried data, so we must note the free (result), otherwise it will certainly cause memory leakage.

After mysql_store_result is executed, the data is already in the mysql_res variable. The following API basically reads data in mysql_res.

For example, the mysql_fetch_row function is a row that reads the query result. The function prototype is as follows: C code

1. Mysql_row stdcall mysql_fetch_row (mysql_res * result );

Mysql_rowstdcall mysql_fetch_row (mysql_res * result );

It will return a mysql_row variable, and mysql_row is actually char **. It will be used as a two-dimensional array.

Example: C code

1. SQL _row = mysql_fetch_row (result)
2. Printf ("% s \ n", SQL _row [0]);

SQL _row = mysql_fetch_row (result) printf ("% s \ n", SQL _row [0]);

There are also a lot of APIS, which are not described one by one. Most of the information is in the two structures of mysql_res MySQL.
For details, refer to the MySQL Official Website:

Http://dev.mysql.com/doc/refman/5.1/en/c.html

I suddenly found that the official website information is comprehensive and looks better than any other book.

Here is an example. C code

1. # Include <stdio. h>
2. # Include <stdlib. h>
3. # Include <string. h>
4. # Include <MySQL/MySQL. h>
   
5. # Define max_column_len 32
   
6. Int main (INT argc, char * argv [])
7. {
8. MySQL my_connection;
9. Mysql_res * result;
10. Mysql_row SQL _row;
11. Mysql_field * FD;
12. Char column [max_column_len] [max_column_len];
13. Int res;
14. Mysql_init (& my_connection );
15. If (mysql_real_connect (& my_connection, "127.0.0.1"
16. , "User", "password", "Data name", 3306, null, 0 ))
17. {
18. Perror ("Connect ");
19. Res = mysql_query (& my_connection, "select * from app"); // query
20. If (! Res)
21. {
22. Result = mysql_store_result (& my_connection); // Save the queried data to the result
23. If (result)
24. {
25. Int I, J;
26. Printf ("the result number is % lu \ n", (unsignedlong) mysql_num_rows (result ));
27. For (I = 0; FD = mysql_fetch_field (result); I ++) // obtain the column name
28. {
29. Bzero (column [I], sizeof (column [I]);
30. Strcpy (column [I], FD-> name );
31. }
32. J = mysql_num_fields (result );
33. For (I = 0; I <j; I ++)
    
34. {
35. Printf ("% s \ t", column [I]);
36. }
37. Printf ("\ n ");
    
38. While (SQL _row = mysql_fetch_row (result) // obtain specific data
39. {
40. For (I = 0; I <j; I ++)
41. {
42. Printf ("% s \ t", SQL _row [I]);
43. }
44. Printf ("\ n ");
    
45. }
47. }
48. }
49. Else
50. {
51. Perror ("select ");
    
52. }
53. }
54. Else
55. {
56. Perror ("Connect: error ");
    
57. }
58. Mysql_free_result (mysql_res * result); // release the result resource
59. Mysql_close (& my_connection); // disconnect
61. }

# Include <stdio. h> # include <stdlib. h> # include <string. h> # include <MySQL/MySQL. h> # define max_column_len32int main (INT argc, char * argv []) {MySQL my_connection; mysql_res * result; mysql_row SQL _row; mysql_field * FD; char column [max_column_len] [max_column_len] int res; mysql_init (& my_connection); If (mysql_real_connect (& my_connection, "127.0.0.1", "user", "password", "Data name", 3306, null, 0 )) {perror ("Connect"); Res = mysql_query (& M Y_connection, "select * from app"); // query if (! Res) {result = mysql_store_result (& my_connection); // Save the queried data to resultif (result) {int I, J; printf ("the result number is % lu \ n", (unsigned long) mysql_num_rows (result); for (I = 0; FD = mysql_fetch_field (result); I ++) // obtain the column name {bzero (column [I], sizeof (column [I]); strcpy (column [I], FD-> name );} j = mysql_num_fields (result); for (I = 0; I <j; I ++) {printf ("% s \ t", column [I]);} printf ("\ n"); While (SQL _row = mysql_fetch_row (result) // obtain specific data {for (I = 0; I <j; I ++) {printf ("% s \ t", SQL _row [I]);} printf ("\ n") ;}} else {perror ("select ");}} else {perror ("Connect: error");} mysql_free_result (mysql_res * result); // release the result resource mysql_close (& my_connection); // disconnect}

In the preceding example, data is queried from a table and then output.

To insert or update an SQL statement, you only need to modify the specific SQL statement.
The specific operation is done through the mysql_query function.

Now let's talk about the compilation method. Here we need the. h and. So libraries.
We can

Http://dev.mysql.com/downloads/connector/c/6.0.html

Download connector/C.
The simple method is:
Copy the include content to/usr/include/MySQL/so that-I is not required during compilation, then copy the items under lib to/usr/lib.

GCC compilation method: GCC ***. C-o ***-lmysqlclient