前置工作
- 創(chuàng)建數(shù)據(jù)庫并選中
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
- 創(chuàng)建表并描述表結(jié)構(gòu)
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;
}
- 增加刪除修改均可正常執(zhí)行，查詢較為復(fù)雜，整體代碼中有案例
- 如果查詢則需要把查詢數(shù)據(jù)保存到結(jié)果集，通過mysql所提供的函數(shù) 得到行和列，遍歷得到結(jié)果。
常用函數(shù)接口
- 初始化mysql并返回句柄（默認(rèn)傳入nullptr即可）
MYSQL *mysql_init(MYSQL *mysql);
如： MYSQL *mfp = mysql_init(nullptr)
- 鏈接數(shù)據(jù)庫
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);
所需參數(shù)分別是 mysql句柄 ，主機(jī)ip，sql用戶名，sql密碼，數(shù)據(jù)庫，端口號，剩余兩個填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;
}
- 設(shè)置字符集編碼
mysql_set_character_set(myfd, "utf8");
- 執(zhí)行sql語句
int mysql_query(MYSQL *mysql, const char *q);
- 獲取查詢結(jié)果并保存到結(jié)果集
MYSQL_RES *mysql_store_result(MYSQL *mysql);
如：
//把結(jié)果存到結(jié)果集中
MYSQL_RES *res = mysql_store_result(my);
if (nullptr == res)
{
std::cerr << " mysql_store_result error" << std::endl;
return 4;
}
- 獲取結(jié)果行數(shù)mysql_num_rows
my_ulonglong mysql_num_rows(MYSQL_RES *res);
- 獲取結(jié)果列數(shù)mysql_num_fields
unsigned int mysql_num_fields(MYSQL_RES *res);
- 獲取列名mysql_fetch_fields
MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *res);
如：文章來源：http://www.zghlxwxcb.cn/news/detail-641967.html
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;
- 獲取結(jié)果內(nèi)容mysql_fetch_row
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
如：文章來源地址http://www.zghlxwxcb.cn/news/detail-641967.html
//內(nèi)容
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;
- 釋放結(jié)果集
void mysql_free_result(MYSQL_RES *result)
- 關(guān)閉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;
}
//把結(jié)果存到結(jié)果集中
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;
//內(nèi)容
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;
}
到了這里，關(guān)于【MySQL】使用C++連接數(shù)據(jù)庫的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
