在 Ubuntu 12.04 下搭建 MySQL C 开发环境

准备工作

1、安装MYSQL

1
$ sudo apt-get install mysql-server mysql-workbench

2、安装MYSQL C开发包

1
$ sudo apt-get install libmysqlclient-dev

注:安装MySQL需要设置管理员root帐号密码(非Linux账户root密码)。

建立数据表

MySQL安装完毕后,使用以下命令操作:

1
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 36 
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu) 

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql>

1、添加一般用户

mysql> CREATE USER young@localhost IDENTIFIED BY 'young'; 
Query OK, 0 rows affected (0.02 sec) 

mysql> GRANT ALL ON *.* to young@localhost; 
Query OK, 0 rows affected (0.00 sec) 

mysql>

2、使用一般用户登录

1
$ mysql -u young -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 39 
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu) 

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql>

3、建立数据库

mysql> CREATE DATABASE testdb; 
Query OK, 1 row affected (0.00 sec)

察看数据库

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| test               | 
| testdb             | 
+--------------------+ 
5 rows in set (0.01 sec)

4、建立数据表

mysql> use testdb; 
Database changed 

mysql> CREATE TABLE student(stuid CHAR(11), name VARCHAR(20), sex CHAR(1), birth DATE, major VARCHAR(30), PRIMARY KEY(stuid)); 
Query OK, 0 rows affected (0.33 sec)

5、导入数据

编写一个student.txt的文件,文件使用TAB分隔,内容如下:

20111110001     Jason   m       1987-01-01      ComputerScience
20111110002     Peter   m       1989-10-23      Mathematics
20111110003     Marry   f       1989-11-11      Art

注:需将student.txt文件放置在/var/lib/mysql目录下,否则导入数据时无法找到文件。

将数据导入testdb数据库:

mysql> load data INFILE './student.txt' into table student; 
Query OK, 3 rows affected (0.18 sec) 
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0 

mysql> use testdb; 
Database changed 
mysql> select * from student;

+-------------+-------+------+------------+-----------------+ 
| stuid       | name  | sex  | birth      | major           | 
+-------------+-------+------+------------+-----------------+ 
| 20111110001 | Jason | m    | 1987-01-01 | ComputerScience | 
| 20111110002 | Peter | m    | 1989-10-23 | Mathematics     | 
| 20111110003 | Marry | f    | 1989-11-01 | Art             | 
+-------------+-------+------+------------+-----------------+ 
3 rows in set (0.00 sec)

测试连接Mysql

编辑一个db-demo.c程序(来源自mysqlcapitutorial):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#define USER "young" // mysql user name
#define PASSWD "young" // mysql password
int main(int argc, char **argv)
{
MYSQL *conn;
MYSQL_RES *result;
MYSQL_ROW row;
int num_fields;
int i;
// initilize mysql
conn = mysql_init(NULL);
if (conn == NULL)
{
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
exit(1);
}
// connect the database testdb
if (mysql_real_connect(conn, "localhost", USER,
PASSWD, "testdb", 0, NULL, 0) == NULL)
{
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
exit(1);
}
// query with SQL
if (mysql_query(conn, "select * from student"))
{
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
exit(1);
}
// store the query result
result = mysql_store_result(conn);
num_fields = mysql_num_fields(result);
// show the result
while ((row = mysql_fetch_row(result)))
{
for (i = 0; i &lt; num_fields; i++)
{
printf("%s ", row[i] ? row[i] : "NULL");
}
printf("\n");
}
mysql_free_result(result);
// close the database testdb
mysql_close(conn);
return 0;
}

使用以下命令编译:

1
$ gcc db-demo.c -lmysqlclient -o db-demo

运行:

1
$ ./db-demo
20111110001 Jason m 1987-01-01 ComputerScience 
20111110002 Peter m 1989-10-23 Mathematics 
20111110003 Marry f 1989-11-01 Art

数据库导出:

1
$ mysqldump -u user -p database > db.sql

eg:

1
$ mysqldump -u young -p testdb > /home/mutse/testdb.sql

数据表导出:

1
$ mysqldump -u user -p database table > table.sql

eg:

1
$ mysqldump -u young -p testdb student > /home/mutse/student.sql
参考资料: