#include<stdio.h>#include<stdlib.h>#include"mysql/mysql.h"intmain(){// mysql初始化
MYSQL *mysql =mysql_init(NULL);if(mysql==NULL){printf("mysql init error\n");return-1;}printf("mysql init ok\n");// 连接mysql
MYSQL *conn =mysql_real_connect(mysql,"127.0.0.1","root","123456","cpp",0,NULL,0);if(conn==NULL){printf("mysql_real_connect error,[%s]\n",mysql_error(mysql));return-1;}printf("connect mysql OK, [%p], [%p]\n", mysql, conn);//关闭数据库连接mysql_close(conn);return0;}
CRUD
#include<stdio.h>#include<stdlib.h>#include<mysql/mysql.h>#define_HOST_"127.0.0.1"#define_USER_"root"#define_PASSWORD_"123456"#define_PORT_3306#define_DBNAME_"cpp"//创建表结构#defineCREATE_TABLE"create table user(id int primary key auto_increment, \
name char(8), \
age int)"// 删除表#defineDROP_TABLE"DROP TABLE IF EXISTS user"// 插入数据#defineINSERT"insert into user('name','age') values('time', 18)"// 读取数据#defineSELECT"select * from user"// 修改数据#defineUPDATE"update user set name='haha' where id = 1"// 删除数据#defineDELETE"delete from user where id = 1"intmain(){int ret =0;
MYSQL *mysql =mysql_init(NULL);
mysql =mysql_real_connect(mysql,_HOST_,_USER_,_PASSWORD_,_DBNAME_,_PORT_,NULL,0);if(mysql ==NULL){
ret =mysql_errno(mysql);printf("func mysql_real_connect() err:%d\n", ret);return ret;}printf(" --- connect ok......\n");if(mysql_query(mysql,DROP_TABLE)){fprintf(stderr," DROP TABLE failed\n");fprintf(stderr," %s\n",mysql_error(mysql));exit(0);}if(mysql_query(mysql,CREATE_TABLE)){fprintf(stderr," CREATE TABLE failed\n");fprintf(stderr," %s\n",mysql_error(mysql));exit(0);}
ret =mysql_query(mysql, INSERT);//向表中插入第一行数据if(ret !=0){printf("mysql_query() err:%d\n", ret);return ret;}
ret =mysql_query(mysql, SELECT);//查询数据if(ret !=0){printf("mysql_query() err:%d\n", ret);return ret;}
ret =mysql_query(mysql, UPDATE);//修改数据if(ret !=0){printf("mysql_query() err:%d\n", ret);return ret;}
ret =mysql_query(mysql, DELETE);//删除一条数据if(ret !=0){printf("mysql_query() err:%d\n", ret);return ret;}mysql_close(mysql);return0;}
mysql事务
#include<stdio.h>#include<stdlib.h>#include<mysql/mysql.h>#defineSET_TRANC"SET AUTOCOMMIT=0"#defineUNSET_TRANC"SET AUTOCOMMIT=1"#define_HOST_"127.0.0.1"#define_USER_"root"#define_PASSWORD_"123456"#define_PORT_3306#define_DBNAME_"cpp"intset_mutual_submit(MYSQL *mysql){int ret =mysql_query(mysql,"start transaction");if(ret !=0){printf("mysql_OperationTran query start err: %s\n",mysql_error(mysql));return ret;}
ret =mysql_query(mysql,SET_TRANC);if(ret !=0){printf("mysql mutual submit set err: %s\n",mysql_error(mysql));return ret;}return ret;}intset_auto_submit(MYSQL *mysql){int ret =mysql_query(mysql,"start transaction");if(ret !=0){printf("mysql auto submit start err: %s\n",mysql_error(mysql));return ret;}
ret =mysql_query(mysql,UNSET_TRANC);if(ret !=0){printf("mysql auto submit set err: %s\n",mysql_error(mysql));return ret;}return ret;}intmsyql_commit(MYSQL *mysql){int ret =mysql_query(mysql,"COMMIT");if(ret !=0){printf("mysql commit start err: %s\n",mysql_error(mysql));return ret;}return ret;}intmsyql_rollback(MYSQL *mysql){int ret =mysql_query(mysql,"ROLLBACK");if(ret !=0){printf("mysql rollback start err: %s\n",mysql_error(mysql));return ret;}return ret;}#defineCREATE_TABLE"create table user(id int primary key auto_increment, \
name char(8), \
age int)"#defineDROP_TABLE"DROP TABLE IF EXISTS user"#defineINSERT1"INSERT INTO test_table(name,age) VALUES('t1',18)"#defineINSERT2"INSERT INTO test_table(name,age) VALUES('t2',19)"#defineINSERT3"INSERT INTO test_table(name,age) VALUES('t3',20)"#defineINSERT4"INSERT INTO test_table(name,age) VALUES('t4',21)"intmain(){int ret =0;
MYSQL *mysql =mysql_init(NULL);
mysql =mysql_real_connect(mysql,_HOST_,_USER_,_PASSWORD_,_DBNAME_,_PORT_,NULL,0);if(mysql ==NULL){
ret =mysql_errno(mysql);printf("func mysql_real_connect() err:%d\n", ret);return ret;}printf(" --- connect ok......\n");if(mysql_query(mysql,DROP_TABLE)){fprintf(stderr," DROP TABLE failed\n");fprintf(stderr," %s\n",mysql_error(mysql));exit(0);}if(mysql_query(mysql,CREATE_TABLE)){fprintf(stderr," CREATE TABLE failed\n");fprintf(stderr," %s\n",mysql_error(mysql));exit(0);}
ret =set_auto_submit(mysql);if(ret !=0){printf("set_mutual_submit err:%d\n", ret);return ret;}
ret =mysql_query(mysql, INSERT1);//向表中插入第一行数据if(ret !=0){printf("mysql_query() err:%d\n", ret);return ret;}
ret =mysql_query(mysql, INSERT2);//向表中插入第二行数据if(ret !=0){printf("mysql_query() err:%d\n", ret);return ret;}
ret =mysql_commit(mysql);//手动提交事务if(ret !=0){printf("mysql Commit() err:%d\n", ret);return ret;}#if1
ret =set_auto_submit(mysql);if(ret !=0){printf("set_auto_submit err:%d\n", ret);return ret;}#else
ret =set_mutual_submit(mysql);if(ret !=0){printf("set_mutual_submit err:%d\n", ret);return ret;}#endif
ret =mysql_query(mysql, INSERT3);//向表中插入第三行数据if(ret !=0){printf("mysql_query() err:%d\n", ret);return ret;}
ret =mysql_query(mysql, INSERT4);//向表中插入第四行数据if(ret !=0){printf("mysql_query() err:%d\n", ret);return ret;}
ret =mysql_rollback(mysql);if(ret !=0){printf("mysql_Rollback() err:%d\n", ret);return ret;}mysql_close(mysql);return0;}
mysql预处理
#include<stdio.h>#include<stdlib.h>#include<string.h>#include<mysql/mysql.h>#define_HOST_"127.0.0.1"#define_USER_"root"#define_PASSWORD_"123456"#define_PORT_3306#define_DBNAME_"cpp"#defineSTRING_SIZE50#defineCREATE_TABLE"create table user(id int primary key auto_increment, \
name char(8), \
age int)"#defineDROP_TABLE"DROP TABLE IF EXISTS user"#defineINSERT"INSERT INTO user(name,age) VALUES(?,?)"voidprepare_insert(MYSQL *mysql);intmain(){int ret =0;
MYSQL *mysql =mysql_init(NULL);
mysql =mysql_real_connect(mysql,_HOST_,_USER_,_PASSWORD_,_DBNAME_,_PORT_,NULL,0);if(mysql ==NULL){
ret =mysql_errno(mysql);printf("func mysql_real_connect() err:%d\n", ret);return ret;}printf(" --- connect ok......\n");prepare_insert(mysql);mysql_close(mysql);return0;}voidprepare_insert(MYSQL *mysql){
MYSQL_STMT *stmt;unsignedlong param_count;
MYSQL_BIND bind[3];
my_ulonglong affected_row;int int_data;char str_data[STRING_SIZE];short small_data;
my_bool is_null;unsignedlong str_length;if(mysql_query(mysql, DROP_TABLE))//删除表{fprintf(stderr," DROP TABLE failed\n");fprintf(stderr," %s\n",mysql_error(mysql));exit(0);}if(mysql_query(mysql, CREATE_TABLE))//创建表{fprintf(stderr," CREATE TABLE failed\n");fprintf(stderr," %s\n",mysql_error(mysql));exit(0);}// MYSQL_STMT *mysql_stmt_init(MYSQL *mysql)
stmt =mysql_stmt_init(mysql);if(!stmt){fprintf(stderr," mysql_stmt_init(), out of memory\n");exit(0);}// int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length) if(mysql_stmt_prepare(stmt,INSERT_SAMPLE,sizeof(INSERT_SAMPLE))){fprintf(stderr," mysql_stmt_prepare(), INSERT failed\n");fprintf(stderr," %s\n",mysql_stmt_error(stmt));exit(0);}fprintf(stdout," prepare, INSERT successful\n");// unsigned long mysql_stmt_param_count(MYSQL_STMT *stmt)
param_count =mysql_stmt_param_count(stmt);fprintf(stdout," total parameters in INSERT: %ld\n", param_count);if(param_count !=3)/* validate parameter count */{fprintf(stderr," invalid parameter count returned by MySQL\n");exit(0);}memset(bind,0,sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_LONG;
bind[0].buffer =(char*)&int_data;
bind[0].is_null =0;
bind[0].length =0;
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer=(char*)str_data;//char 100
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null=0;
bind[1].length=&str_length;
bind[2].buffer_type= MYSQL_TYPE_SHORT;
bind[2].buffer=(char*)&small_data;
bind[2].is_null=&is_null;//是否为null的指示器
bind[2].length=0;// my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind) if(mysql_stmt_bind_param(stmt,bind)){fprintf(stderr," mysql_stmt_bind_param() failed\n");fprintf(stderr," %s\n",mysql_stmt_error(stmt));exit(0);}
int_data=10;/* integer */strncpy(str_data,"MySQL", STRING_SIZE);/* string */
str_length=strlen(str_data);
is_null=1;//指示插入的第三个字段是否为null // int mysql_stmt_execute(MYSQL_STMT *stmt) if(mysql_stmt_execute(stmt)){fprintf(stderr," mysql_stmt_execute(), 1 failed\n");fprintf(stderr," %s\n",mysql_stmt_error(stmt));exit(0);}// my_ulonglong mysql_stmt_affected_rows(MYSQL_STMT *stmt)
affected_row =mysql_stmt_affected_rows(stmt);fprintf(stdout," total affected rows(insert 1): %lu\n",(unsignedlong) affected_row);if(affected_row !=1)/* validate affected rows */{fprintf(stderr," invalid affected rows by MySQL\n");exit(0);}
int_data=1000;strncpy(str_data,"The most popular Open Source database", STRING_SIZE);
str_length=strlen(str_data);
small_data=1000;/* smallint */
is_null=0;/* reset *//* Execute the INSERT statement - 2*/if(mysql_stmt_execute(stmt))//第二次执行{fprintf(stderr," mysql_stmt_execute, 2 failed\n");fprintf(stderr," %s\n",mysql_stmt_error(stmt));exit(0);}/* Get the total rows affected */
affected_row=mysql_stmt_affected_rows(stmt);fprintf(stdout," total affected rows(insert 2): %lu\n",(unsignedlong) affected_row);if(affected_row !=1)/* validate affected rows */{fprintf(stderr," invalid affected rows by MySQL\n");exit(0);}if(mysql_stmt_close(stmt)){fprintf(stderr," failed while closing the statement\n");fprintf(stderr," %s\n",mysql_stmt_error(stmt));exit(0);}printf("closed....\n");}