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);
}