Mysql函数使用例子

dagun

摘在网上，对于编写程序可以作为参考。
首先，包括头文件。
#include <mysql/mysq.h>
编译方法：
$ gcc -I/usr/include/mysql -L/usr/lib/mysql myapp.c -o myapp  -lmysqlclient

程序：
connect_simplest.c
#include <stdio.h>
#include <mysql.h>

MYSQL *conn; /* pointer to connection handler */

int main ( int argc, char *argv[] )
{
//初始化mysql的句柄conn
conn = mysql_init ( NULL );
//连接mysql数据库
mysql_real_connect (
conn, /* pointer to connection handler */
"localhost", /* host to connect to */
"user_name", /* user name */
"password", /* password */
"test", /* database to use */
0, /* port (default 3306) */
NULL, /* socket or /var/lib/mysql.sock */
0 ); /* flags (none) */

//关闭数据库，句柄
  mysql_close ( conn );
return 0;
}
compilation: gcc -Wall connect_simplest.c -lmysqlclient
If mysql.h is not in a standard place: -I/path/to/the/mysql.
If libmysqlclient.so or libmysqlclient.a is not in a standard
place: -L/path/to/the/mysqlclient/library.
(match better use Makefile)

For more secure connection you should use struct option from <getopt.h>
conn.hMYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
const char* groups[] = {"client",NULL};
struct option long_options[] =
{
{"host",required_argument,NULL,'h'},
{"user",required_argument,NULL,'u'},
{"password",optional_argument,NULL,'p'},
{"port",required_argument,NULL,'P'},
{"socket",required_argument,NULL,'S'},
{0,0,0,0}
};

unsigned long how_rows (void);
void print_error(char *);

conn.c#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>
#include <getopt.h> // for getopt_long
#include "conn.h"


int main (int argc, char* argv[])
{
int i, c, option_index;
char databases[15][30];


char *pass = NULL;
char *user = NULL;
char *host = NULL;
char *sock = NULL;
unsigned int port = 0;

// needs for operations required by load_defaults().
my_init();
// reads /etc/.my.cnf or ${HOME}/.my.cnf file
load_defaults("my",groups,&argc,&argv);

// allows assigning values for host, pass...
while((c=getopt_long(argc,argv,"h:p:u:P:S:",long_options,
&option_index)) != EOF)
{
switch(c)
{
case 'h':
host = optarg;
break;
case 'u':
user = optarg;
break;
case 'p':
pass = optarg;
break;
case 'P':
port = (unsigned int) atoi(optarg);
break;
case 'S':
sock = optarg;
break;
}
}

//=================================================
if ( (conn = mysql_init(NULL)) == NULL)
{
print_error("mysql_init()");
exit (EXIT_FAILURE);
}
if ( (mysql_real_connect(conn,host,user,pass,"",0,NULL,0))
== NULL)
{
print_error("mysql_real_connect()");
exit (EXIT_FAILURE);
}
mysql_query(conn,"show databases");
res = mysql_store_result(conn);

i=0;
system("clear");
printf("\nThere are %lu databases\n",how_rows());
puts("=====================");

while ( row = mysql_fetch_row(res) )
{
strcpy(databases[i],row[0]);
puts(databases[i]);
i++;
}
mysql_close(conn);
return 0;

}
//----------------------------------------------------------
unsigned long how_rows (void)
//----------------------------------------------------------
{
return (unsigned long) mysql_num_rows(res);
}
//----------------------------------------------------------
void print_error(char* msg)
//----------------------------------------------------------
{
fprintf(stderr,"%s\n",msg);
if ( conn != NULL )
{
fprintf(stderr,"Error: %u %s\n",mysql_errno(conn),
mysql_error(conn));
}
}
OUTPUT:
// There are 16 databases
// =====================
// School
// Student
// TransAct
// ana
// bank
// billing
// helpfile
// helpfile1
// mysql
// phone
// sales_analis
// samp_db
// student
// studentdb
// test
// user1
Of course, you have to hide your password from the direct viewing.
I usually use <password.hh> that I created
for similar purposes.
simp_conn_pass.c#include <mysql.h>
#include <password.hh>

MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;

int main ()
{
conn = mysql_init ( NULL );
mysql_real_connect ( conn,"localhost","ana",
password(),"bank",0,NULL,0);

mysql_query(conn,"show tables");
res = mysql_store_result(conn);

while ( row = mysql_fetch_row(res) )
{
fprintf (stdout,"%s\n", row[0]);
}
fprintf (stdout,"\n%lu rows affected\n",
(unsigned long) mysql_num_rows(res));

mysql_close(conn);
exit(0);
}
OUTPUT:
// BANK_WORKER
// BRANCHES
// CHECKING_ACCT
// CHECKING_REP
// CLOSE_CHECK_ACCT
// CLOSE_CUSTOMERS
// CLOSE_SAVINGS_ACCT
// CUSTOMERS
// LOAN_ACCT
// SAVINGS_ACCT
// SAVINGS_REP
//
// 11 rows affected
Some Usage some_usage.c #include <mysql.h>
#include <stdlib.h>
#include <stdio.h>
#include <password.hh>

MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;

unsigned long how_rows (void);
void print_data(void);
char* to_upper (char*);
void print_table(void);
void print_line (void);

//----------------------------------------------------------

int main (int argc, char* argv[])
{
unsigned long ul;
int i;
char databases[25][30];
char query[30];
char temp[30], table[30];

conn = mysql_init ( NULL );
mysql_real_connect (conn,"","ana",password(),
"",3306,NULNULL;

mysql_query(conn,"show databases");
res = mysql_store_result(conn);

ul = how_rows();
i=0;
system("clear");
printf("\nThere are %lu databases\n",ul);
puts("=====================");

while ( row = mysql_fetch_row(res) )
{
strcpy(databases[i],row[0]);
puts(databases[i]);
i++;
}
for ( i = 0; i < ul; i++ )
{
if ( strcmp(databases[i],"mysql") == 0 )
continue;
sprintf(query,"use %s",databases[i]);
mysql_query(conn,query);
strcpy(temp,databases[i]);
fprintf(stdout,"\nThe database %s consists tables:\n",
to_upper(temp));
puts("==========================================");
print_data();
getchar();
// usleep(900000);
}
while(1)
{
fprintf(stdout,"What database do you want to use? (0-quit): ");
fgets(temp,29,sstdin;
temp[strlen(temp)-1] = '\0';

if ( strcmp(temp,"0") == 0 )
break;
for ( i = 0; i < ul; i++ )
{
if ( strcmp(temp,databases[i]) == 0 )
{
while(1)
{
fprintf(stdout,"What table do you want to see? ");
fprintf(stdout,"(0-quit): ");
fgets(table,29,sstdin;
table[strlen(table)-1] = '\0';

if ( strcmp(table,"0") == 0 )
break;
sprintf(query,"select * from %s.%s",temp,table);
mysql_query(conn,query);
print_table();
}
}
}
}
mysql_close(conn);
exit(0);
}
//----------------------------------------------------------
unsigned long how_rows (void)
//----------------------------------------------------------
{
return (unsigned long) mysql_num_rows(res);
}
//----------------------------------------------------------
void print_data(void)
//----------------------------------------------------------
{
mysql_query(conn,"show tables");
res = mysql_store_result(conn);
while ( row = mysql_fetch_row(res) )
{
fprintf(stdout,"%s\n",row[0]);
}
}
//----------------------------------------------------------
char* to_upper (char* name)
//----------------------------------------------------------
{
char *p = name;
while (*p)
{
*p = toupper(*p);
p++;
}
return name;
}
//----------------------------------------------------------
void print_table(void)
//----------------------------------------------------------
{
MYSQL_FIELD *field;
unsigned int i, j, column_length;

res = mysql_store_result(conn);

mysql_field_seek(res,0);
for ( i = 0; i < mysql_num_fields(res); i++ )
{
field = mysql_fetch_field(res);
column_length = strlen(field->name);
if ( column_length < field->max_length )
column_length = field->max_length;
if ( column_length < 4 && !IS_NOT_NULL(field->flags) )
column_length = 4;
field->max_length = column_length;
}
print_line();
fputc('\n',stdstdout
mysql_field_seek(res,0);
for ( i = 0; i < mysql_num_fields(res); i++ )
{
field = mysql_fetch_field(res);
fprintf(stdout," %-*s ",field->max_length,field->name);
}
fputc('\n',stdstdout print_line();
fputc('\n',stdstdout
while ((row = mysql_fetch_row(res)) != NULL)
{
mysql_field_seek(res,0);

for ( i = 0; i < mysql_num_fields(res); i++ )
{
field = mysql_fetch_field(res);
if ( row[i] == NULL )
fprintf(stdout," %-*s ",field->max_length,"NULL");
else
if ( IS_NUM(field->type) )
fprintf(stdout," %*s ",field->max_length,row[i]);
else
fprintf(stdout," %-*s ",field->max_length,row[i]);
}
fputc('\n',stdstdout }
fputc('\n',stdstdout mysql_free_result(res);
}
//----------------------------------------------------------
void print_line (void)
//----------------------------------------------------------
{
MYSQL_FIELD *field;
unsigned int i, j;

mysql_field_seek(res,0);
for ( i = 0; i < mysql_num_fields(res); i++ )
{
field = mysql_fetch_field(res);
for ( j = 0; j < field->max_length + 2; j++ )
fputc('=',ststdout
}
}
OUTPUT:
There are 16 databases
=====================
School
Student
TransAct
ana
bank
billing
helpfile
helpfile1
mysql
phone
sales_analis
samp_db
student
studentdb
test
user1

The database SCHOOL consists tables:
==========================================
school_student

The database STUDENT consists tables:
==========================================
students

The database TRANSACT consists tables:
==========================================
destination
purpose
test
transkepper

The database ANA consists tables:
==========================================
addition_info
cases
student_info
student_records
teacher
teacher_room
temp
temp_student
temp_teacher
temp_teacher_room

The database BANK consists tables:
==========================================
BANK_WORKER
BRANCHES
CHECKING_ACCT
CHECKING_REP
CLOSE_CHECK_ACCT
CLOSE_CUSTOMERS
CLOSE_SAVINGS_ACCT
CUSTOMERS
LOAN_ACCT
SAVINGS_ACCT
SAVINGS_REP

The database BILLING consists tables:
==========================================

The database HELPFILE consists tables:
==========================================
datafile
helpfile

The database HELPFILE1 consists tables:
==========================================
datafile

The database PHONE consists tables:
==========================================
address
phone

The database SALES_ANALIS consists tables:
==========================================
departments
quote
sales

The database SAMP_DB consists tables:
==========================================
absence
event
member
president
score
student

The database STUDENT consists tables:
==========================================
student
subjects

The database STUDENTDB consists tables:
==========================================
student
subjects

The database TEST consists tables:
==========================================
t
t1
test
test1
test2

The database USER1 consists tables:
==========================================

What database do you want to use? (0-quit): bank
What table do you want to see? (0-quit): BRANCHES
================================================================
BRANCH_NAME BRANCH_NUM BRANCH_ADDRESS
================================================================
BROOKLYN_BRANCH 001 718 Ave N, Brooklyn, NY 11234
MANHATTAN_BRANCH 002 291 5th Ave, New York, NY, 10070

What table do you want to see? (0-quit): BANK_WORKER
=============================================================
B_SSN B_FNAME B_LNAME B_ID B_PHONE B_EXT
=============================================================
277-65-8877 John Smith 001 718-999-6655 313
665-66-4423 Kevin Walters 001 718-999-6655 444
775-99-7543 Peter Kats 002 212-999-8655 111
777-65-6654 Anna Katsnelson 001 718-999-6655 222
765-87-6540 Sam Jefferson 002 212-999-8655 123

What table do you want to see? (0-quit): 0
What database do you want to use? (0-quit): 0