linux c 连接mysql数据库

作者：zieckey(zieckey@yahoo.com.cn)
All Right Reserved!


1. mysql在linux下的编译和安装[code="java"]
# mkdir /usr/local/mysql
# cp mysql-4.0.12.tar.gz /home/
# cd /home/[/code]
解压[code="java"]
# tar zxvf mysql-4.0.12.tar.gz
# cd mysql-4.0.12[/code]
配置，生成Makefile[code="java"]
# ./configure --prefix=/usr/local/mysql --without-debug --with-extra-charsets=gb2312 --enable-assembler --without-isam --without-innodb --with-pthread --enable-thread-safe-client[/code]
编译[code="java"]
# make[/code]
安装[code="java"]
# make install

# scripts/mysql_install_db
[/code]
创建一个数据库管理员[code="java"]
# groupadd mysql
# useradd -g mysql mysql
[/code]

改变权限。[code="java"]
# chown -R root /usr/local/mysql
# chown -R mysql /usr/local/mysql/var
# chgrp -R mysql /usr/local/mysql

[/code]

配置环境变量，以便于编程[code="java"]
# cd /usr/local/mysql/bin/
# export PATH=$PATH:/usr/local/mysql/bin/
# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/mysql/lib/mysql/
# env

[/code][code="java"]
启动mysql服务器
# cd /usr/local/mysql/bin/
# ./mysqld_safe -u mysql&
Starting mysqld daemon with databases from /usr/local/mysql/var[/code]
查看是否启动了[code="java"]
# pgrep mysql
15931
15950
15951[/code]


启动一个mysql客户端[code="java"]
# /usr/local/mysql/bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.12

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>[/code]
创建一个数据库

mysql> create database cusemysql;
Query OK, 1 row affected (0.00 sec)

并使用这个数据库

mysql> use cusemysql;
Database changed

在给数据库内创建一个表

mysql> create table children(childno int not null unique,fname varchar(20),age int);
Query OK, 0 rows affected (0.00 sec)

在该表内插入一项数据

mysql> insert into children values(5,"花儿",10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from children;
+---------+-------+------+
| childno | fname | age |
+---------+-------+------+
|       5 | 花儿 |   10 |
+---------+-------+------+
1 row in set (0.03 sec)

mysql>

2. 下面进行具体的操作

插入：insert

好的，我们现编辑一段c代码，取名为insert.c

///
/* insert.c */
#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"
/*注意哦，上面也可以是mysql.h的绝对地址，一般在mysql下的include目录下，仔细看看你的在哪里？*/

int main(int argc, char *argv[])
{
    MYSQL my_connection;

    int res;

    mysql_init(&my_connection);

    /*mysql_real_connect(&mysql,host,user,passwd,dbname,0,NULL,0) == NULL)*/
    if (mysql_real_connect(&my_connection, "localhost", "root", "","cusemysql",0,NULL,CLIENT_FOUND_ROWS))
    {
            printf("Connection success\n");
        res = mysql_query(&my_connection, "insert into children values(11,'Anny',5)");

        if (!res)
        {   
            printf("Inserted %lu rows\n",(unsigned long)mysql_affected_rows(&my_connection));
        /*里头的函数返回受表中影响的行数*/
        }
        else
        {
        //分别打印出错误代码及详细信息
        fprintf(stderr, "Insert error %d: %s\n",mysql_errno(&my_connection),mysql_error(&my_connection));
        }
        mysql_close(&my_connection);
    }

    else
    {
        fprintf(stderr, "Connection failed\n");

        if (mysql_errno(&my_connection))
        {
                fprintf(stderr, "Connection error %d: %s\n",mysql_errno(&my_connection),mysql_error(&my_connection));
        }
    }
    return EXIT_SUCCESS;
}

/
代码写完了，要编译哦[code="java"]
# gcc -o insert insert.c
/tmp/ccyHfsX2.o(.text+0x1e): In function `main':
: undefined reference to `mysql_init'
/tmp/ccyHfsX2.o(.text+0x47): In function `main':
: undefined reference to `mysql_real_connect'
/tmp/ccyHfsX2.o(.text+0x76): In function `main':
: undefined reference to `mysql_query'
/tmp/ccyHfsX2.o(.text+0x9a): In function `main':
: undefined reference to `mysql_affected_rows'
/tmp/ccyHfsX2.o(.text+0xbc): In function `main':
: undefined reference to `mysql_error'
/tmp/ccyHfsX2.o(.text+0xcf): In function `main':
: undefined reference to `mysql_errno'
/tmp/ccyHfsX2.o(.text+0xf5): In function `main':
: undefined reference to `mysql_close'
/tmp/ccyHfsX2.o(.text+0x11f): In function `main':
: undefined reference to `mysql_errno'
/tmp/ccyHfsX2.o(.text+0x135): In function `main':
: undefined reference to `mysql_error'
/tmp/ccyHfsX2.o(.text+0x148): In function `main':
: undefined reference to `mysql_errno'
collect2: ld returned 1 exit status
#[/code]
头文件和库文件位置没有指定[code="java"]
# gcc -o insert insert.c -I/usr/include/mysql/ -L/usr/lib/mysql/
/tmp/cc4gdmlp.o(.text+0x1e): In function `main':
: undefined reference to `mysql_init'
/tmp/cc4gdmlp.o(.text+0x47): In function `main':
: undefined reference to `mysql_real_connect'
/tmp/cc4gdmlp.o(.text+0x76): In function `main':
: undefined reference to `mysql_query'
/tmp/cc4gdmlp.o(.text+0x9a): In function `main':
: undefined reference to `mysql_affected_rows'
/tmp/cc4gdmlp.o(.text+0xbc): In function `main':
: undefined reference to `mysql_error'
/tmp/cc4gdmlp.o(.text+0xcf): In function `main':
: undefined reference to `mysql_errno'
/tmp/cc4gdmlp.o(.text+0xf5): In function `main':
: undefined reference to `mysql_close'
/tmp/cc4gdmlp.o(.text+0x11f): In function `main':
: undefined reference to `mysql_errno'
/tmp/cc4gdmlp.o(.text+0x135): In function `main':
: undefined reference to `mysql_error'
/tmp/cc4gdmlp.o(.text+0x148): In function `main':
: undefined reference to `mysql_errno'
collect2: ld returned 1 exit status
#
[/code]
gcc还是找不到头文件，下面我们可以这样,指定gcc专门找 mysqlclient 之类的库
[code="java"]
# gcc -o insert insert.c -lmysqlclient -I/usr/local/mysql/include/mysql/ -L/usr/local/mysql/lib/mysql
[/code]
我用用 -lmysqlclient 选项就可以了(前面我们生成的库文件是 libmysqlclient.so.12.0.0 等,
去掉前面的lib和后面的版本标志,就剩下 mysqlclient 了所以是 -lmysqlclient )。

ok,现在我们执行看看[code="java"]
# ./insert
Connection Success
Inserted 1 rows
[/code]
如果执行出错，就先执行以下三句脚本（15.0.0根据版本不同而不同）

ln -s /usr/local/mysql/lib/mysql/libmysqlclient.so.15.0.0 /usr/lib/libmysqlclient.so
ln -s /usr/local/mysql/lib/mysql/libmysqlclient.so.15.0.0 /usr/lib/libmysqlclient.so.15
ln -s /usr/local/mysql/lib/mysql/libmysqlclient.so.15.0.0 /usr/lib/libmysqlclient.so.6.0.0

year,果然可以，呵呵
不信到mysql下看看表children中是否多了刚才插入的那一行数据

mysql> select * from children;
+---------+-------+------+
| childno | fname | age |
+---------+-------+------+
|       5 | 花儿 |   10 |
|      11 | Anny |    5 |
+---------+-------+------+
2 rows in set (0.00 sec)

总结：这里我们了解了MySQL数据库在Linux下的安装，以及一些基本的操作，并且我们可以通过C语言来操作MySQL数据库，这真是太令人兴奋了。欢迎大家交流