Linux-（C）利用Mysql相关API实现类似mysql的操作

程序比较简单，不过也有趣，练习C mysql相关API。

程序大概实现流程：

1、通过主函数传递参数登陆数据库： 程序名  -u  数据库用户名  -p;

./mysql1 -u dbuser1 -p

2、初始化连接数据库。

3、处理输入sql语句。程序分显示跟不显示两路处理。（显示的意思是执行了sql命令之后时候会输出控制台，eg，show、desc、select等命令执行完后会打印信息到控制台  

insert、delete、update则不会）

4、循环输入执行sql语句，直到程序退出。

缺点：

1、 必须事先指定一个要登录的用户登录的数据库。正常的mysql是可以登录用户之后再选择相应的数据库。

2、退格键、左右上下移动不自由，都需要手动设置。（这个程序只实现了退格键）

mysql.c

/**/#include<stdio.h>#include<stdlib.h>#include<string.h>#include<unistd.h>#include<errno.h>#include<termios.h>#include<mysql/mysql.h>MYSQL *connection = NULL;MYSQL mysql;char host[20]="localhost";char dbtable[20]="db1";void init_db(){mysql_init(&mysql);}void disconnect(){if(connection!=NULL){mysql_close(&mysql);connection=NULL;}}void showsql(char *sql){if(mysql_query(connection,sql)!=0){printf("mysql query error: %s\n",mysql_error(connection));return ;}MYSQL_RES *res=mysql_store_result(connection);if(res==NULL){printf("mysql store result error: %s",mysql_error(connection));return ;}/*遍历一张表信息，先统计列数后遍历每一行*/int fieldcnt=0;MYSQL_FIELD *sqlField=NULL;while(1) //统计field(段){//mysql_fetch_field() is reset to return information//每返回一个语句，下一条语句将会重置，换行吧sqlField=mysql_fetch_field(res);if(sqlField==NULL)break;printf("%s\t",sqlField->name);fieldcnt++;}printf("\n");MYSQL_ROW sqlRow=NULL;while(1){sqlRow=mysql_fetch_row(res);if(sqlRow==NULL)break;int i=0;for(i=0;i<fieldcnt;++i){if(sqlRow[i] == NULL)printf("NULL\t");else    printf("%s\t", (const char *) sqlRow[i]);//屏幕打印为字段内容}printf("\n");}printf("query is ok, %u rows affected\n", (unsigned int)mysql_affected_rows(connection));    mysql_free_result(res);printf("show success\n");}void execsql(char *sql){printf("%s\n",sql);if(mysql_query(connection,sql)!=0){printf("mysql query error: %s\n",mysql_error(connection));return ;}printf("query is ok, %u rows affected\n", (unsigned int)mysql_affected_rows(connection));}void dealsql(char *sql){if( strncmp(sql,"select",6)==0||strncmp(sql,"SELECT",6)==0||strncmp(sql,"show",4)==0||strncmp(sql,"SHOW",4)==0||strncmp(sql,"desc",4)==0||strncmp(sql,"DESC",4)==0){showsql(sql); // show command}else{execsql(sql); //execute command}}void work(char *dbuser,const char * dbuserpawd){init_db();//需要 先绑定一个数据表名 dbtableconnection=mysql_real_connect(&mysql,host,dbuser,dbuserpawd,dbtable,0,NULL,0);if(connection==NULL){fprintf(stderr, "Failed to connect to database: Error: %s\n",          mysql_error(&mysql));}printf("connect success db1(table)\n");while(1){char sql[1024];memset(sql,0,sizeof(sql));write(STDOUT_FILENO,"mysql1>",strlen("mysql1>"));read(STDIN_FILENO,sql,sizeof(sql));if (strncmp(sql, "quit", 4) == 0)break;dealsql(sql);}disconnect();}struct termios tmp, old;//设置退格键“backspace”为退格键。void setstty(){    //得到系统termion的状态    if(tcgetattr(STDIN_FILENO, &tmp) == -1)    {        printf("tcgetattr error： %s\n", strerror(errno));        return ;    }    old = tmp;    //'\b'为退格键的ASCII码 因此下面这个语句表示退格键被修改为erase功能,即能擦除    tmp.c_cc[VERASE] = '\b';    //设置系统termion的状态，TCSANOW表示设置完成效果立即生效    if(tcsetattr(STDIN_FILENO, TCSANOW, &tmp) == -1)    {        printf("tcsetattr error： %s\n", strerror(errno));        return ;    }}//恢复之前系统默认的状态，回显^Hvoid renew(){    if(tcsetattr(STDIN_FILENO, TCSANOW, &old) == -1)    {        printf("tcsetattr error： %s\n", strerror(errno));        return ;    }}int main(int argc,char * argv[]){if(argc<4){printf("argument: Program -u dbuername -p");return EXIT_SUCCESS;}if(strcmp(argv[1],"-u")!=0){printf("the second argument is wrong,should be assigned -u  \n");return EXIT_SUCCESS;}if(strcmp(argv[3],"-p")!=0){printf("the fouth argument is wrong,should be assign -p  \n");return EXIT_SUCCESS;}//outputs the string prompt,//turns off echoing, reads one line (the "password"),//restores the terminal state//The function getpass() returns a pointer to a static bufferconst char *dbpassword = getpass("Enter password:");//write(STDOUT_FILENO,dbpassword,strlen(dbpassword));setstty();work(argv[2],dbpassword);renew();return 0;}

执行效果：

stu@ubuntu:~/test1$ makegcc -Wall -g -o mysql1.o -c mysql1.cgcc -o mysql1 mysql1.o -lmysqlclient ----------------ok------------stu@ubuntu:~/test1$ ./mysql1 -u dbuser1 -pEnter password:connect success db1(table)mysql1>show tables;Tables_in_db1table1table3query is ok, 2 rows affectedshow successmysql1>select * from table1;name    sex     age     classname2   nan     20      bname1   boy     10      javaname1   boy     10      javaname3   boy     10      javaname4   boy     10      javaquery is ok, 5 rows affectedshow successmysql1>delete from table1 where name = 'name4';delete from table1 where name = 'name4';query is ok, 1 rows affectedmysql1>select * from table1;name    sex     age     classname2   nan     20      bname1   boy     10      javaname1   boy     10      javaname3   boy     10      javaquery is ok, 4 rows affectedshow successmysql1>

退格键回显具体可以参考：http://blog.csdn.net/qq_33850438/article/details/52184786

另外一个mysql 小程序：http://blog.csdn.net/qq_33850438/article/details/52156224