C mySQL编程 linux

1 先看看效果

输入相关设置，就能显示查询结果。

2 注意的几个问题

2.1 汉字的显示 （参考http://my.oschina.net/mjRao/blog/100661）

今天在linux下用C++封装了一下mysql数据库的连接，在执行 mysql_query(connection,  sql_str.c_str())时，查询得到的汉字是乱码。 最后在网上查阅资料发现需要设置读去数据库的编码格式mysql_query(connection, "set names utf8") 即在执行数据查询之前，要设置一下编码方式！

2.2 数据库字符集设置以及建立

SET character_set_client = utf8 ;
SET character_set_connection = utf8 ;
SET character_set_database = utf8 ;
SET character_set_results = utf8 ;
SET character_set_server = utf8 ;
SET collation_connection = utf8 ;
SET collation_database = utf8 ;
SET collation_server = utf8 ;
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
SHOW CHARACTER SET;
drop database if exists db_school;
create database db_school;
use db_school;
create table tb_student(
stu_ID int auto_increment primary key,
stu_name varchar(20),
stu_sex varchar(5));
insert into tb_student(stu_ID,stu_name,stu_sex) values ('2001','申宇','男'),('2002','王丽蒙','女');
insert into tb_student( stu_name,stu_sex) values ( '申2宇','男'),( '王3蒙','女');
select * from tb_student;

3 再附上代码（用了curses库来优化界面显示）

Makefile文件：
make run:
gcc -std=c99 -I/usr/include/mysql -I/usr/include/ncurses mysql1.c -L/usr/lib/mysql -lmysqlclient -lncurses -o mysql1.exe
./mysql1.exe

#include<unistd.h>
#include<stdio.h>
#include<stdlib.h>
#include<mysql.h>
#include<ncurses.h>
void error(char * msg)
{
	printf("ERROR: %s\n",msg); exit(1);
}
int main(int argc, char * argv[])
{
	initscr();
	MYSQL * connection = mysql_init(NULL);
	if(!connection) error("init failed");
	//get user name and password
	char username[256]={'\0'};
	char password[256]={'\0'};
	char hostname[256]={'\0'};
	char dbname[256]  ={'\0'};
	char sqlquery[256]={'\0'};
	move(LINES/2-3,COLS/2-10);
	printw("UserName: "); getnstr(username,256);
	move(LINES/2-2,COLS/2-10);
	printw("PassWord: ");
	noecho();
	int count=0;char c=0;
	while( (c=getch())!='\n')
	{
		password[count++]=c;
		addch('*');
	}
	echo();

	move(LINES/2-1,COLS/2-10);
	printw("HostName: "); getnstr(hostname,256);
	move(LINES/2,COLS/2-10);
	printw("Database: "); getnstr(dbname,256);
	move(LINES/2+1,COLS/2-10);
	printw("SQLquery: "); getnstr(sqlquery,256);
	// connect to db
 	clear();
	move(LINES/2-1,COLS/2-10);
	printw("Connecting...\n");
	refresh();

	connection = mysql_real_connect( connection, hostname ,username,password,dbname,0,NULL,0);
	if(!connection)
	{
		printw("failed to connect to db\n");

	}
	else
		printw("db connected\n");
	move(0,0);
	refresh();
	endwin();
	//To do
        int res=mysql_query(connection, "set names utf8");
	if(res) error("query [set names utf8] failed!");
	MYSQL_RES *res_ptr;
	MYSQL_ROW sqlrow;

    printf("Excuting query %s ...\n",sqlquery);
	res= mysql_query(connection,sqlquery);
	if(res) error("query failed!");

	res_ptr=mysql_use_result(connection);
	if(res_ptr)
	{
		while((sqlrow=mysql_fetch_row(res_ptr)))
		{
			int field_count=0;
			while(field_count<mysql_field_count(connection))
			{
				if(sqlrow[field_count]) printf("%s ",sqlrow[field_count]);
				else printf("NULL");
				field_count++;
			}
			printf("\n");
		}
		mysql_free_result(res_ptr);
	}



	mysql_close(connection);
	printf("Bye\n");
	exit(0);
}