首页 › mysql › mysql 编程笔记

mysql 编程笔记

第七根弦 2014 年 10 月 8 日 mysql, UC 发表评论 (0)

要进行linux下的mysql的C编程，需要安装mysql及mysql的开发包，ubuntu下直接apt-get install libmysql++安装开发包。
#include <mysql.h>
相关函数:

C
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
MYSQL *mysql_init(MYSQL *);
//这里称之为载入函数吧，返回的MYSQL指针要用到后续的函数中
 
int mysql_options(MYSQL *connection, enum option_to_set,const char *argument);
//设置MYSQL*的一些属性，比如超时时间等
MYSQL *mysql_real_connect(MYSQL *connection,
                const char *server_host,
                const char *sql_user_name,
                const char *sql_password,
                const char *db_name,
                unsigned int port_number,//置0连接默认端口，一般为3306
                const char *unix_socket_name,//NULL
                unsigned int flags);//无另外属性时置0
//连接函数
 
void mysql_close(MYSQL *connection);
//关闭连接
 
unsigned int mysql_errno(MYSQL *connection);
//返回错误代码
 
char *mysql_error(MYSQL *connection);
 
//返回错误信息
 
int mysql_query(MYSQL *connection, const char *query);
//执行sql语句
 
my_ulonglong mysql_affected_rows(MYSQL *connection);
//返回执行语句过后受影响的行数
 
MYSQL_RES *mysql_store_result(MYSQL *connection);
//返回执行结果，适用于数据量较小时
 
my_ulonglong mysql_num_rows(MYSQL_RES *result);
//返回上面函数返回结果的行数
 
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
//抽取一条记录，返回NULL时表示抽取完记录或者错误
 
void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);
//调整数据位置，offset为0时，下次调用mysql_fetch_row将返回result第一条记录
 
MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result);
//返回当前的位置
 
MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset);
//移动数据位置，并返回先前的位置，可以和上一个函数结合使用
 
void mysql_free_result(MYSQL_RES *result);
//释放result空间
 
MYSQL_RES *mysql_use_result(MYSQL *connection);
//返回执行结果，适用于数据量较大时
 
unsigned int mysql_field_count(MYSQL *connection);
//返回查询结果中的列数（column数）
 
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result);
//获得查询结果中的列名等信息（表头信息）

例子：

创建测试数据库
mysql> create database test;
创建用户
mysql> grant all on test.* to test@’localhost’ identified by ‘test’;

sql文件：

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--
-- Create the table children
--
CREATE TABLE children (
childno int(11) NOT NULL auto_increment,
fname varchar(30),
age int(11),
PRIMARY KEY (childno)
);
--
-- Populate the table ‘children’
--
 
INSERT INTO children(childno,fname,age) VALUES(1,'Jenny',21);
INSERT INTO children(childno,fname,age) VALUES(2,'Andrew',17);
INSERT INTO children(childno,fname,age) VALUES(3,'Gavin',8);
INSERT INTO children(childno,fname,age) VALUES(4,'Duncan',6);
INSERT INTO children(childno,fname,age) VALUES(5,'Emma',4);
INSERT INTO children(childno,fname,age) VALUES(6,'Alex',15);
INSERT INTO children(childno,fname,age) VALUES(7,'Adrian',9);

导入sql文件：

mysql -u test –password=test test<mysqlchildren.sql

导入后的情况：

mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| children |
+—————-+
1 row in set (0.00 sec)

mysql> select * from children;
+———+——–+——+
| childno | fname | age |
+———+——–+——+
| 1 | Jenny | 21 |
| 2 | Andrew | 17 |
| 3 | Gavin | 8 |
| 4 | Duncan | 6 |
| 5 | Emma | 4 |
| 6 | Alex | 15 |
| 7 | Adrian | 9 |
+———+——–+——+
7 rows in set (0.00 sec)

C代码:

C
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>
 
MYSQL *mysql_main;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;
void display_header();
void display_row();
int main(int argc,char *argv[])
{
    int res;
    int first_row = 1;
 
    mysql_main = mysql_init(NULL);
 
    if(!mysql_main)
    {
        fprintf(stderr,"mysql_init failed\n");
        return EXIT_FAILURE;
    }
    mysql_main = mysql_real_connect(mysql_main,"localhost","test","test","test",0,NULL,0);
    if(mysql_main)
    {
        printf("Connection success:\n");
        res = mysql_query(mysql_main,"SELECT childno,fname,age FROM children WHERE age>5");
        if(res)
        {
            fprintf(stderr,"SELECT error: %s\n",mysql_error(mysql_main));
        }
        else
        {
            res_ptr = mysql_use_result(mysql_main);
            if(res_ptr)
            {
                while((sqlrow = mysql_fetch_row(res_ptr)))
                {
                    if(first_row)
                    {
                        display_header();
                        first_row = 0;
                    }
                    display_row();
                }
            }
        }
    }
    else
    {
        printf("Connection failed\n");
    }
    mysql_close(mysql_main);
    return EXIT_SUCCESS;
}
void display_header()
{
    MYSQL_FIELD *field_ptr;
    printf("Column details:\n");
    while((field_ptr = mysql_fetch_field(res_ptr))!=NULL)
    {
        printf("\t Name: %s\n",field_ptr->name);
        printf("\t Type: ");
        if(IS_NUM(field_ptr->type))
        {
            printf("Numeric filed\n");
        }
        else
        {
            switch(field_ptr->type)
            {
            case FIELD_TYPE_VAR_STRING:
                printf("VARCHAR\n");
                break;
            case FIELD_TYPE_LONG:
                printf("LONG\n");
                break;
            default:
                printf("Type is %d,check in mysql_com.h\n",field_ptr->type);
            }
        }
        printf("\t MAX width %ld\n",field_ptr->length);
        if(field_ptr->flags&AUTO_INCREMENT_FLAG)
            printf("\t Auto increments\n");
        printf("\n");
    }
}
void display_row()
{
    unsigned int field_count;
 
    field_count = 0;
    while(field_count<mysql_field_count(mysql_main))
    {
        if(sqlrow[field_count]) printf("%s ",sqlrow[field_count]);
        else printf("NULL");
        field_count++;
    }
    printf("\n");
}

gcc -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient -o mysqltest mysqltest.c

./test

结果如下：

Connection success:
Column details:
Name: childno
Type: Numeric filed
MAX width 11
Auto increments

Name: fname
Type: VARCHAR
MAX width 30

Name: age
Type: Numeric filed
MAX width 11

1 Jenny 21
2 Andrew 17
3 Gavin 8
4 Duncan 6
6 Alex 15
7 Adrian 9

|

发表评论