【MySQL】使用C++连接数据库

前置工作

1. 创建数据库并选中

mysql> create database conn;
Query OK, 1 row affected (0.01 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| conn               |
+--------------------+
2 rows in set (0.00 sec)mysql> use conn
Database changed

2. 创建表并描述表结构

mysql> create table user( id int primary key auto_increment, name varchar(32) not null, age int not null, telphone varchar(32) unique);
Query OK, 0 rows affected (0.06 sec)mysql> show tables;
+----------------+
| Tables_in_conn |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32) | NO   |     | NULL    |                |
| age      | int(11)     | NO   |     | NULL    |                |
| telphone | varchar(32) | YES  | UNI | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

代码

#include <iostream>
#include <string>
#include <mysql/mysql.h>const std::string host = "127.0.0.1";
const std::string user = "connector";
const std::string password = "123456";
const std::string db = "conn";
const unsigned int port = 3306;int main()
{// std::cout<<"mysql client version: "<<mysql_get_client_info()<<std::endl;MYSQL *my = mysql_init(nullptr);if (nullptr == my){std::cerr << " init MYSQL error" << std::endl;return 1;}if (mysql_real_connect(my, host.c_str(), user.c_str(), password.c_str(), db.c_str(), port, nullptr, 0) == nullptr){std::cerr << " connect MYSQL error" << std::endl;return 2;}mysql_set_character_set(my, "utf8");// std::cout<<"connect success "<<std::endl;// std::string sql="insert into user(name,age,telphone) values('jim',18,'123488899');";std::string sql="update user set name='李四' where id =2;";// std::string sql = "delete from user where id =3; ";//std::string sql = "select * from user";int n = mysql_query(my, sql.c_str());if (n == 0)std::cout << sql << " success " << std::endl;else{std::cerr << "filed :" << sql << std::endl;return 3;}mysql_close(my);return 0;
}

1. 增加删除修改均可正常执行，查询较为复杂，整体代码中有案例
2. 如果查询则需要把查询数据保存到结果集，通过mysql所提供的函数 得到行和列，遍历得到结果。

常用函数接口

1. 初始化mysql并返回句柄（默认传入nullptr即可）

MYSQL *mysql_init(MYSQL *mysql);

如： MYSQL *mfp = mysql_init(nullptr)

2. 链接数据库

MYSQL *mysql_real_connect(MYSQL *mysql, const char *host,
const char *user,
const char *passwd,
const char *db,
unsigned int port,
const char *unix_socket,
unsigned long clientflag);

所需参数分别是 mysql句柄 ，主机ip，sql用户名，sql密码，数据库，端口号，剩余两个填nullptr和0即可
如：

if(mysql_real_connect(my,host.c_str(),user.c_str(),password.c_str(), db.c_str(), port, nullptr, 0) == nullptr)
{std::cerr << " connect MYSQL error" << std::endl;return 2;
}

3. 设置字符集编码

mysql_set_character_set(myfd, "utf8");

4. 执行sql语句

int mysql_query(MYSQL *mysql, const char *q);

5. 获取查询结果并保存到结果集

MYSQL_RES *mysql_store_result(MYSQL *mysql);

如：

    //把结果存到结果集中MYSQL_RES *res = mysql_store_result(my);if (nullptr == res){std::cerr << " mysql_store_result error" << std::endl;return 4;}

• 获取结果行数mysql_num_rows

my_ulonglong mysql_num_rows(MYSQL_RES *res);

• 获取结果列数mysql_num_fields

unsigned int mysql_num_fields(MYSQL_RES *res);

• 获取列名mysql_fetch_fields

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *res);

如：

  my_ulonglong row = mysql_num_rows(res);my_ulonglong col = mysql_num_fields(res);std::cout << "行: " << row << std::endl;std::cout << "列: " << col << std::endl;std::cout<<std::endl;//属性MYSQL_FIELD* fields=mysql_fetch_fields(res);for(int i=0;i<col;++i){std::cout<< fields[i].name<<"\t";}std::cout<<std::endl;

• 获取结果内容mysql_fetch_row

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

如：

  //内容for (int i = 0; i < row; ++i){MYSQL_ROW row = mysql_fetch_row(res);for (int j = 0; j < col; ++j){std::cout << row[j] << "\t";}std::cout << "\n";}std::cout<<fields[0].db<<std::endl;std::cout<<fields[0].table<<std::endl;

6. 释放结果集

 void mysql_free_result(MYSQL_RES *result)

7. 关闭mysql连接

void mysql_close(MYSQL *sock);

整体示例：

#include <iostream>
#include <string>
#include <mysql/mysql.h>const std::string host = "127.0.0.1";
const std::string user = "connector";
const std::string password = "123456";
const std::string db = "conn";
const unsigned int port = 3306;int main()
{// std::cout<<"mysql client version: "<<mysql_get_client_info()<<std::endl;MYSQL *my = mysql_init(nullptr);if (nullptr == my){std::cerr << " init MYSQL error" << std::endl;return 1;}if (mysql_real_connect(my, host.c_str(), user.c_str(), password.c_str(), db.c_str(), port, nullptr, 0) == nullptr){std::cerr << " connect MYSQL error" << std::endl;return 2;}mysql_set_character_set(my, "utf8");// std::cout<<"connect success "<<std::endl;// std::string sql="insert into user(name,age,telphone) values('jim',18,'123488899');";// std::string sql="update user set name='李四' where id =2;";// std::string sql = "delete from user where id =3; ";std::string sql = "select * from user";int n = mysql_query(my, sql.c_str());if (n == 0)std::cout << sql << " success " << std::endl;else{std::cerr << "filed :" << sql << std::endl;return 3;}//把结果存到结果集中MYSQL_RES *res = mysql_store_result(my);if (nullptr == res){std::cerr << " mysql_store_result error" << std::endl;return 4;}my_ulonglong row = mysql_num_rows(res);my_ulonglong col = mysql_num_fields(res);std::cout << "行: " << row << std::endl;std::cout << "列: " << col << std::endl;std::cout<<std::endl;//属性MYSQL_FIELD* fields=mysql_fetch_fields(res);for(int i=0;i<col;++i){std::cout<< fields[i].name<<"\t";}std::cout<<std::endl;//内容for (int i = 0; i < row; ++i){MYSQL_ROW row = mysql_fetch_row(res);for (int j = 0; j < col; ++j){std::cout << row[j] << "\t";}std::cout << "\n";}std::cout<<fields[0].db<<std::endl;std::cout<<fields[0].table<<std::endl;mysql_free_result(res);mysql_close(my);return 0;
}