使用c语言访问mysql数据库

首先在linux上安装mysql

 1 jason@t61:~$ mysql -u root
 2 Welcome to the MySQL monitor.  Commands end with ; or g.
 3 Your MySQL connection id is 9
 4 Server version: 5.6.24-0ubuntu2 (Ubuntu)
 5 
 6 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
 7 
 8 Oracle is a registered trademark of Oracle Corporation and/or its
 9 affiliates. Other names may be trademarks of their respective
10 owners.
11 
12 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
13 
14 mysql> create database foo;
15 Query OK, 1 row affected (0.00 sec)
16 
17 mysql> use foo ;
18 Database changed
19 mysql> exit
20 Bye

上面的代码是进入mysql创建数据库foo供测试

 1 -这是create_children.sql的源码
 2 create table children(
 3     childno int(11) not null auto_increment,
 4     fname varchar(30),
 5     age int(11),
 6     primary key (childno));
 7     
 8 INSERT INTO children VALUES (1,'Jenny',17);
 9 INSERT INTO children VALUES (2,'Andrew',13);
10 INSERT INTO children VALUES (3,'Gavin',4);
11 INSERT INTO children VALUES (4,'Duncan',2);
12 INSERT INTO children VALUES (5,'Emma',0);
13 INSERT INTO children VALUES (6,'Alex',11);
14 INSERT INTO children VALUES (7,'Adrian',5);

在mysql中调用执行这段源码

 1 mysql> source /home/jason/c_program/544977-blp3e/chapter08/create_children.sql
 2 Query OK, 0 rows affected (0.48 sec)
 3 
 4 Query OK, 1 row affected (0.08 sec)
 5 
 6 Query OK, 1 row affected (0.03 sec)
 7 
 8 Query OK, 1 row affected (0.12 sec)
 9 
10 Query OK, 1 row affected (0.11 sec)
11 
12 Query OK, 1 row affected (0.05 sec)
13 
14 Query OK, 1 row affected (0.06 sec)
15 
16 Query OK, 1 row affected (0.05 sec)
17 
18 mysql> show tables;
19 +---------------+
20 | Tables_in_foo |
21 +---------------+
22 | children      |
23 +---------------+
24 1 row in set (0.00 sec)

执行之后查看执行的效果：

 1 mysql> select * from  children;
 2 +---------+--------+------+
 3 | childno | fname  | age  |
 4 +---------+--------+------+
 5 |       1 | Jenny  |   17 |
 6 |       2 | Andrew |   13 |
 7 |       3 | Gavin  |    4 |
 8 |       4 | Duncan |    2 |
 9 |       5 | Emma   |    0 |
10 |       6 | Alex   |   11 |
11 |       7 | Adrian |    5 |
12 +---------+--------+------+
13 7 rows in set (0.00 sec)

这样就完成数据库的准备工作

 1 #include <stdlib.h>
 2 #include <stdio.h>
 3 
 4 #include "mysql.h"
 5 
 6 int main(int argc, char *argv[]) 
 7 {
 8    MYSQL *conn_ptr;
 9 
10    conn_ptr = mysql_init(NULL);
11    if (!conn_ptr) {
12       fprintf(stderr, "mysql_init failed
");
13       return EXIT_FAILURE;
14    }
15    
16    conn_ptr = mysql_real_connect(conn_ptr, "localhost", "root", "","foo", 0, NULL, 0);
17 
18    if (conn_ptr) {
19       printf("Connection success
");
20    } else {
21       printf("Connection failed
");
22    }
23 
24    mysql_close(conn_ptr);
25 
26    return EXIT_SUCCESS;
27 }

上面是初始化链接和建立链接的函数的使用

 1 jason@t61:~/c_program/544977-blp3e/chapter08$ gcc -I/usr/include/mysql connect1.c -L/usr/lib/mysql -lmysqlclient -o connect1
 2 connect1.c:4:19: fatal error: mysql.h: 没有那个文件或目录
 3  #include "mysql.h"
 4                    ^
 5 compilation terminated.
 6 //缺少头文件
 7 jason@t61:~/c_program/544977-blp3e/chapter08$ sudo apt-get install mysql-devel
 8 [sudo] password for jason: 
 9 正在读取软件包列表... 完成
10 正在分析软件包的依赖关系树       
11 正在读取状态信息... 完成  
12 E: 未发现软件包 mysql-devel
13 //不匹配uubuntu情况
14 jason@t61:/$ sudo apt-get install libmysqlclient-dev
15 正在读取软件包列表... 完成
16 正在分析软件包的依赖关系树       
17 正在读取状态信息... 完成       
18 下列【新】软件包将被安装：
19   libmysqlclient-dev
20 //成功

下面是编译这个代码：

1 jason@t61:~/c_program/544977-blp3e/chapter08$ gcc -I/usr/include/mysql connect1.c -L/usr/lib/mysql -lmysqlclient -o connect1
2 jason@t61:~/c_program/544977-blp3e/chapter08$ ./connect1
3 Connection failed

这个失败是因为原来的.c文件中的权限设置给的不合适

 1 mysql> select user, host, password from mysql.user;
 2 +------------------+-----------+-------------------------------------------+
 3 | user             | host      | password                                  |
 4 +------------------+-----------+-------------------------------------------+
 5 | root             | localhost |                                           |
 6 | root             | t61       |                                           |
 7 | root             | 127.0.0.1 |                                           |
 8 | root             | ::1       |                                           |
 9 | debian-sys-maint | localhost | *16AEF7D29C488DB8597DCBF25DCE8097E57558B7 |
10 +------------------+-----------+-------------------------------------------+
11 5 rows in set (0.00 sec)

修改相应的账号或者密码：

1 jason@t61:~/c_program/544977-blp3e/chapter08$ gcc -I/usr/include/mysql connect1.c -L/usr/lib/mysql -lmysqlclient -o connect1
2 jason@t61:~/c_program/544977-blp3e/chapter08$ ./connect1 
3 Connection success
4 jason@t61:~/c_program/544977-blp3e/chapter08$

 1 #include <stdlib.h>
 2 #include <stdio.h>
 3 
 4 #include "mysql.h"
 5 
 6 int main(int argc, char *argv[]) {
 7    MYSQL my_connection;
 8 
 9    mysql_init(&my_connection);  
10    if (mysql_real_connect(&my_connection, "localhost", "root", "", "foo", 0, NULL, 0)) {
11       printf("Connection success
");
12       mysql_close(&my_connection);
13    } else {
14       fprintf(stderr, "Connection failed
");
15       if (mysql_errno(&my_connection)) {
16           fprintf(stderr, "Connection error %d: %s
", mysql_errno(&my_connection), mysql_error(&my_connection));
17       }
18    }
19 
20    return EXIT_SUCCESS;
21 }

这段代码有错误处理的能力可以把相应的错误信息打印出来：

1 //首先给错误的账号密码
2 jason@t61:~/c_program/544977-blp3e/chapter08$ gcc -I/usr/include/mysql connect2.c -L/usr/lib/mysql -lmysqlclient -o connect2
3 jason@t61:~/c_program/544977-blp3e/chapter08$ ./connect2
4 Connection failed
5 Connection error 1045: Access denied for user 'rick'@'localhost' (using password: YES)
6 jason@t61:~/c_program/544977-blp3e/chapter08$ gcc -I/usr/include/mysql connect2.c -L/usr/lib/mysql -lmysqlclient -o connect2
7 jason@t61:~/c_program/544977-blp3e/chapter08$ ./connect2
8 Connection success

//2015年06月28日星期日 14时54分19秒

万事走心精益求美