(一) 前置条件:
(1) Linux 已经安装好 mysql 数据库;
(2) Linux 已经安装了 gcc 编译器;
(二)数据库准备:
为了便于描述,假设数据库的 root 用户密码为 root_pwd 。
(1) 以 root 用户登陆数据库
#mysql -uroot –proot_pwd
mysql>
(2) 创建数据 testdb
mysql> create database testdb;
(3) 创建表
mysql> use testdb;
mysql> create table t_users(userid int not null, username varchar(20) not null);
(4) 向表中插入一条记录
mysql> insert into t_users(userid, username) values(1, 'HuDennis');
(5) 创建用户
mysql> create user testdb_user;
(6) 对用户授权
mysql> grant select, insert, update, delete on *.* to 'testdb_user'@'localhost' identified by '123456';
mysql> flush privileges;
(7) 查看用户及该用户的权限
mysql> use mysql;
mysql> select user from user;
mysql> show grants for testdb_user@localhost;
(8) [ 备用 ] 删除数据库及用户
mysql> drop database testdb;
mysql> show databases;
mysql> drop user testdb_user;
mysql> drop user testdb_user@localhost;
(三) C 源代码 testdb.c 准备:
- #include <mysql.h>
- #include <stdlib.h>
- #include <stdio.h>
- static char *server_options[] = { "mysql_test", "--defaults-file=my.cnf" };
- int num_elements = sizeof(server_options)/ sizeof(char *);
- static char *server_groups[] = { "libmysqld_server", "libmysqld_client" };
- int main(void)
- {
- if (mysql_server_init(num_elements, server_options, server_groups))
- {
- exit(1);
- }
- MYSQL *conn;
- MYSQL_RES *res;
- MYSQL_ROW row;
- char *server = "localhost";
- char *user = "testdb_user";
- char *password = "123456";
- char *database = "testdb";
- conn = mysql_init(NULL);
- /* Connect to database */
- if (!mysql_real_connect(conn, server,
- user, password, database, 0, NULL, 0))
- {
- fprintf(stderr, "%s/n", mysql_error(conn));
- exit(1);
- }
- /* send SQL query */
- if (mysql_query(conn, "select userid, username from t_users"))
- {
- fprintf(stderr, "%s/n", mysql_error(conn));
- exit(1);
- }
- if ((res = mysql_use_result(conn)) == NULL)
- {
- fprintf(stderr, "%s/n", mysql_error(conn));
- exit(1);
- }
- int num = mysql_num_fields(res);
- while ((row = mysql_fetch_row(res)) != NULL)
- {
- int i=0;
- for (; i<num; i++)
- {
- printf("%s ", row[i]);
- }
- printf("/n");
- }
- /* close connection */
- mysql_free_result(res);
- mysql_close(conn);
- /* Use any MySQL API functions here */
- mysql_server_end();
- return EXIT_SUCCESS;
- }
(四) C 程序编译与运行:
(1) 编译 testdb.c
# gcc testdb.c -I/usr/local/mysql/include -L/usr/local/mysql/lib -lmysqlclient
注意: -I 和 -L 的路径应该根据 mysql 的安装路径不同而应该有所不同。同理,下面的 LD_LIBRARY_PATH 变量一样。
(2) 将库文件路径加入到 LD_LIBRARY_PATH 环境变量中去
# LD_LIBRARY_PATH =$LD_LIBRARY_PATH:/usr/local/mysql/lib
(3) 运行编译得到的可执行文件 a.out
#./a.out
1 HuDennis