一、准备
1.下载mysql库
apt/yum install -y mysql-***munity-server
2.创建用户
create user '用户名'@'%' identified by '密码'
//%代表在所有设备上都能登录,只能本地登录使用'lo***alhost'
3.设置权限
grant 权限 on 范围 to 用户名
//例: grant all on conn.* to 'connector'@'%'
4.官方文档
mysql.*** --> Documentation ->C API -->C API Function Reference
5.包含头文件
#include<mysql/mysql.h>
6.编译
g++ -o mytest test.*** -std=c++11 -L/lib64/mysql -lmysqlclient
二、常用接口
1.mysql_init
功能：初始化
MYSQL *my = mysql_init(nullptr);//失败返回nullptr
2.mysql_close
功能：关闭
mysql_close(my);//不用了就要关闭
3.mysql_real_connect
功能：连接mysql
参数解释：host参数用于指定MySQL服务器的主机地址
user用户用passwd密码连接db数据块
port连接端口号，默认3306
unix_socket 设置为nullptr
client_flag设置为0
返回值：失败返回null
参数准备
const std::string host = "127.0.0.1";//localhost也可以
const std::string user = "connector";
const std::string database = "conn";
const unsigned int port = 3306;
使用
if(mysql_real_connect(my,host.c_str(),user.c_str(),passwd.c_str(),
database.c_str(),port,nullptr,0) == nullptr)
{
std::cout << "connect error" << std::endl;
return 2;
}
std::cout << "connect su***ess" << std::endl;
4.mysql_query
功能：下达指令
我有一个表
使用
std::string sql;
while(true)
{
std::cout << "MySQL>>>";
if(std::getline(std::cin,sql) || sql == "quit")
{
std::cout << "bye" << std::endl;
break;
}
int n = mysql_query(my,sql.c_str());
if(n == 0)//成功
std::cout << sql << "su***ess: " << n << std::endl;
else
std::cerr << sql << "failed: " << n << std::endl;;
}
插入成功
//常用示例
std::string sql = "update user set name='jimmy' where name=Tom";
int n = mysql_query(my, sql.c_str());
if(n == 0) std::cout << sql << " su***ess" << std::endl;
else std::cout << sql << "failed" << std::endl;
5.mysql_set_character
功能：插入中文后再select会出现乱码，是因为建立链接的默认字符集不同，该函数能建立字符集
使用，在初始化后
mysql_set_character_set(my, "utf8");
6.mysql_store_result
MYSQL_RES:类型名，将select查出来的数据组织好
MYSQL_RES* res = mysql_store_result(my);
if(nullptr == res)
{
std::cerr << "mysql_store_result error" << std::endl;
return 4;
}
7.mysql_num_rows
功能: 获取行数
mysql_num_fields
功能：获取列数
使用案例
my_ulonglong rows = mysql_num_rows(res);
my_ulonglong fields = mysql_num_rows(res);
std::cout << "行：" << rows << std::endl;
std::cout << "列：" << fields << std::endl;
8.mysql_fetch_row
功能：获取一行数据
用法：row保存一行数据，先读取一行，然后再按列打印
for(int i = 0; i < rows; i++)
{
MYSQL_ROW row = mysql_fetch_row(res);
for(int j = 0; j < fields; j++)
{
std::cout << row[j] << '\t';
}
std::cout << std::endl;
}
9.mysql_fetch_fields
功能：提取属性
用法
MYSQL_FIELD *fields_array = mysql_fetch_fields(res);
for(int i = 0; i < fields; i++)
{
std::cout << fields_array[i].name << '\t';
}
std::cout << std::endl;
10.mysql_free_result
功能：查询结果不会自动释放，必须使用该函数主动释放
使用
mysql_free_result(res);
三、补充内容
1.一个问题：mysql_store_result的返回值MYSQL_RES到底是什么？
2. mysql_fetch_fields能提取的属性
3.MYSQL_FIELD类型
typedef struct MYSQL_FIELD {
char *name; /* Name of column */
char *org_name; /* Original column name, if an alias */
char *table; /* Table of column if column was a field */
char *org_table; /* Org table name, if table was an alias */
char *db; /* Database for table */
char *catalog; /* Catalog for table */
char *def; /* Default value (set by mysql_list_fields) */
unsigned long length; /* Width of column (create length) */
unsigned long max_length; /* Max width for selected set */
unsigned int name_length;
unsigned int org_name_length;
unsigned int table_length;
unsigned int org_table_length;
unsigned int db_length;
unsigned int catalog_length;
unsigned int def_length;
unsigned int flags; /* Div flags */
unsigned int decimals; /* Number of decimals in field */
unsigned int charsetnr; /* Character set */
enum enum_field_types type; /* Type of field. See mysql_***.h for types */
void *extension;
} MYSQL_FIELD;
四、代码展示
#include<iostream>
#include<string>
#include<mysql/mysql.h>
const std::string host = "127.0.0.1";
const std::string user = "connector";
const std::string passwd = "123456";
const std::string database = "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(),passwd.c_str(),database.c_str(),port,nullptr,0) == nullptr)
{
std::cout << "connect error" << std::endl;
return 2;
}
std::cout << "connect su***ess" << std::endl;
mysql_set_character_set(my, "utf8");
// std::string sql = "update user set name='jimmy' where name=Tom";
std::string sql = "select * from user";
// std::string sql = "insert into user(name,age,telphone) values('张三',33,'3333')";
int n = mysql_query(my, sql.c_str());
if(n == 0) std::cout << sql << " su***ess" << std::endl;
else std::cout << sql << "failed" << std::endl;
MYSQL_RES* res = mysql_store_result(my);
if(nullptr == res)
{
std::cerr << "mysql_store_result error" << std::endl;
return 4;
}
my_ulonglong rows = mysql_num_rows(res);
my_ulonglong fields = mysql_num_rows(res);
std::cout << "行：" << rows << std::endl;
std::cout << "列：" << fields << std::endl;
//获取列名
MYSQL_FIELD *fields_array = mysql_fetch_fields(res);
for(int i = 0; i < fields; i++)
{
std::cout << fields_array[i].name << '\t';
}
std::cout << std::endl;
//获取表内容
for(int i = 0; i < rows; i++)
{
MYSQL_ROW row = mysql_fetch_row(res);
for(int j = 0; j < fields; j++)
{
std::cout << row[j] << '\t';
}
std::cout << std::endl;
}
mysql_free_result(res);
mysql_close(my);
return 0;
}
