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

Author Avatar
Mutse Young 9月 09, 2012

准备工作

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
参考资料：
expand_less