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

最新推荐文章于 2024-08-05 21:34:56 发布

翻转的乌龟

最新推荐文章于 2024-08-05 21:34:56 发布

阅读量1.2k

点赞数

分类专栏： MySQL 文章标签：数据库 mysql c++

本文链接：https://blog.csdn.net/m0_56177713/article/details/132167533

版权

MySQL 专栏收录该内容

15 篇文章 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

创建表并描述表结构

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

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

常用函数接口

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

MYSQL *mysql_init(MYSQL *mysql);

如： MYSQL *mfp = mysql_init(nullptr)

链接数据库

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

设置字符集编码

mysql_set_character_set(myfd, "utf8");

执行sql语句

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

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

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;

释放结果集

 void mysql_free_result(MYSQL_RES *result)

关闭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;
}