Mysql的安装远程连接及基本curd操作
Mysql的安装远程连接及基本curd操作
简介:在Ubuntu14.04.6-server
操作系统上安装MySQL
数据库,远程连接,通过C/C++
实现基础crud。
引言
数据作为程序的灵魂,通过数据库统一管理。数据库往往有多种类型,根据其对数据的组织方式可以分为关系型数据库和非关系型数据库。本文所使用的MySQL
即是关系型数据库,基于关系使用表存储数据,通过SQL(Structured Query Language)
进行管理和查询。本文基于Ubuntu14.04.6-server
操作系统,采用mysql-5.6.33
,使用MySQLWorkbench
远程连接管理。为熟悉基本操作,通过node server
实现业务逻辑与MySQL
进行交互。
MySQL的安装及配置
Ubuntu
操作系统通过包管理器安装MySLQ-5.6.33
,因为在MySQL5.5
之后的版本,采用了更优的搜索引擎。
使用包管理器安装sudo apt-install mysql-server-5.6
,安装过程中,输入两次密码作为管理员root
的登录密码。安装完成登录MySQL
。
登录成功界面如上图所示,进入MySQL
操作命令行。目前作为root
用户对所有数据进行操作。
输入命令show datases;
查看所有的数据库,同时命令以;
结尾。
use mysql
选择对mysql
数据库进行操作。show tables
查看当前数据库的所有表。
在这其中的表分别包含用户权限、时区字符集、事件日志等的相关表格。
数据库本质上就是一个服务器,接受处理返回数据,而命令行形式并不便于后续的开发和查找,使用图形化界面MySQLWorkbench
远程连接数据库服务器。
安装MySQLWorkbench
,新建连接输入ip地址及端口号,连接时会出现问题:错误代码10061。原因在于,数据库默认绑定在127.0.0.1
,该地址为本地回环地址,表明对于该数据库仅能接受本地机器对其进行连接操作。修改配置文件,将绑定地址修改为0.0.0.0
使得所有机器都可以连接该数据库。打开数据库配置文件:
sudo vim /etc/mysql/my.cnf
该文件配置了数据库的基本信息,修改bind-address
为0.0.0.0重启数据库即可。
MySQL
不允许以root
身份远程连接数据库,在mysql
数据库中的user
表新建用户,在远程使用该用户身份进行登录。
新建立的用户,并没有对数据库的crud操作权限。通过root用户赋予新建用户在所有的数据库中的所有表格赋予所有权限:grant all privileges on \*.\* to 'admin'@'%';
。
至此,MySQL
数据库安装配置完成,实现了远程连接数据库,通过可视化图形界面登录赋予权限的用户对数据库进行操作。
数据库操作及node server编程
- 数据库建模操作
新建数据库:
CREATE DATABASES ZHENG_DB;
USE ZHENG_DB;
在新建数据库中新建用户表格,分别表示用户id、用户名、性别和图片数据。
CREATE TABLE TBL_USER (
U_ID INT primary KEY AUTO_INCREMENT,
U_NAME VARCHAR(32),
U_GENDER VARCHAR(8),
U_IMG BLOB
);
查询所有用户:
SELECT * FROM TBL_USER;
插入用户,指明插入数据的值:
INSERT TBL_USER(UNAME, U_GENDER, U_IMG) VALUES('C', 'man', NULL);
删除用户时,不以主键为索引删除数据,sql
无法执行。使用SET SQL_SAFE_UPDATES=0、SET SQL_SAFE_UPDATE=1
分别在删除数据前关闭安全模式,删除结束后打开安全模式,使用存储过程实现:
DELIMITER $$
CREATE PROCEDURE PROC_DELETE_USER(IN UNAME VARCHAR(32))
BEGIN
SET SQL_SAFE_UPDATES=0;
DELETE FROM TBL_USER WHERE U_NAME=UNAME;
SET SQL_SAFE_UPDATES=1;
END$$
通过CALL PROC_DELETE_USER('Zhang');
调用上述存储过程,即可实现按照U_NAME
删除数据。
- C语言与数据库交互
MySQL
作为服务器开放端口号3306接收请求,此时需要中间件来处理客户端的请求,与数据库进行交互;拿到服务器的数据之后将数据处理发送到客户端。工作流程图如下:
- 连接数据库
如工作流程图所示,整个crud操作都是围绕mysql.h
提供的API
进行。
取到MYSQL
对象并对其初始化。
MYSQL mysql;
//初始化管道
if(NULL == mysql_init(&mysql)){
printf("mysql_init : %s\n",mysql_error(&mysql));
return -1;
}
通过MYSQL
对象连接数据库,要求数据库的ip
、端口、用户名、密码、数据库名称。
#define ZHENG_DB_SERVER_IP "192.168.36.129"
#define ZHENG_DB_SERVER_PORT 3306
#define ZHENG_DB_USERNAME "admin"
#define ZHENG_DB_PASSWORD "123456"
#define ZHENG_DB_DEFAUlTDB "ZHENG_DB"
//连接数据库 若返回非零值表示连接成功
if(!mysql_real_connect(&mysql,ZHENG_DB_SERVER_IP,
ZHENG_DB_USERNAME,ZHENG_DB_PASSWORD,ZHENG_DB_DEFAUlTDB,
ZHENG_DB_SERVER_PORT,NULL,0)){
printf("mysql_real_connect : %s\n",mysql_error(&mysql));
return -2;
}
- 查找数据
查找数据,接收从数据库传来的数据,按照行列处理。其中常用到mysql_real_query
、mysql_store_result()
方法,其关系如下:
当使用``方法,根据SQL
得到查询类型。只有查找类SQL
才会从数据库中返回数据,此时的数据存放在内存中,通过mysql_store_result()
获得存放位置的指针进行下一步操作。
#define SQL_SELECT_TBL_USER "SELECT * FROM TBL_USER;"
//先查询数据
if(mysql_real_query(handle,SQL_SELECT_TBL_USER,strlen(SQL_SELECT_TBL_USER))){
printf("mysql_real_query : %s\n",mysql_error(handle));
return -1;
}
//处理查询到的数据
MYSQL_RES* res = mysql_store_result(handle);
if(res == NULL){
printf("mysql_store_result : %s\n",mysql_error(handle));
return -2;
}
mysql_num_rows
()、mysql_num_fields()
都需要上一步获得的结果集指针,分别取到结果集的行列数。根据行列数逐行处理数据,mysql_fetch_row()
一次返回一行数据。
int rows = mysql_num_rows(res);
printf("rows : %d\n",rows);
int fields = mysql_num_fields(res);
printf("fields : %d\n",fields);
MYSQL_ROW row;//表示查询结果中一行的数据
//mysql_fetch_row 返回当前行的数据
while((row = mysql_fetch_row(res))){//逐行获取数据 直到末尾
int i = 0;
for(i = 0;i < fields;i++)
printf("%s\t",row[i]);
printf("\n");
}
- 插入与删除数据
插入与删除数据同样是基于mysql_real_qury()
方法。需要注意的是,在数据库建模部分提到的删除数据的安全模式,在此处直接调用已经编写好的存储过程即可。
#define SQL_INSERT_TBL_USER "INSERT TBL_USER(U_NAME, U_GENDER) VALUES('Zhang','man');"
#define SQL_DELETE_TBL_USER "CALL PROC_DELETE_USER('Zhang');"
//执行插入功能
if(mysql_real_query(&mysql,SQL_INSERT_TBL_USER,strlen(SQL_INSERT_TBL_USER))){
printf("mysql_real_query : %s\n",mysql_error(&mysql));
return -3;
}
//执行插入功能
if(mysql_real_query(&mysql,SQL_DELETE_TBL_USER,strlen(SQL_DELETE_TBL_USER))){
printf("mysql_real_query : %s\n",mysql_error(&mysql));
return -3;
}
- 处理二进制类型数据
在数据库中,可存放字符串整数类型值之外的类型值。本文仅以图片类型BLOB
作为案例处理。将本地的.jpg
文件存放到数据库中,再从数据库中取出并保存在本地。
实现本地对文件的读写操作,通过文件指针实现操作。
int read_image(char* filename,char* buffer){
if(filename == NULL || buffer == NULL)
return -1;
FILE* fp = fopen(filename,"rb");
if(fp == NULL){
printf("fopen failed\n");
return -2;
}
//查找到文件的长度
fseek(fp,0,SEEK_END);//将文件指针置为末尾
int length = ftell(fp);//求得文件长度
fseek(fp,0,SEEK_SET);//将文件置为开头
//读取文件
int size = fread(buffer,1,size,fp);
if(size != length){
printf("fread failed: %d\n",size);
return -3;
}
fclose(fp);
return 0;
}
int write_image(char* filename,char* buffer,int length){
if(filename == NULL || buffer == NULL || length < 0)
return -1;
FILE* fp = fopen(filename,"wb+");
if(fp == NULL){
printf("fopen failed\n");
return -1;
}
int size = fwrite(buffer,1,length,fp);
if(size != length){
printf("fwrite failed: %d\n",size);
return -2;
}
fclose(fp);
return 0;
}
值得注意的是,写入数使用wb+
模式,表示以二进制写入到filename
文件中,若该文件不存在就创建。
处理二进制文件需要使用预处理方式。初始化预处理语句之后,通过对参数的绑定,对占位符的数据进行填充,后续执行预处理语句。
预处理对图片向数据库写入图片。
#define SQL_INSERT_IMG_USER "INSERT TBL_USER(U_NAME, U_GENDER, U_IMG) VALUES('Zhang','man',?);"
//初始化stmt
MYSQL_STMT* stmt = mysql_stmt_init(handle);
int ret = mysql_stmt_prepare(stmt,SQL_INSERT_IMG_USER,strlen(SQL_INSERT_IMG_USER));
if(ret){
printf("mysql_stmt_prepare : %s\n",mysql_error(handle));
return -2;
}
对占位符进行参数绑定。
MYSQL_BIND param = {0};
param.buffer_type = MYSQL_TYPE_LONG_BLOB;
param.buffer = NULL;
param.is_null = 0;
param.length = NULL;
ret = mysql_stmt_bind_param(stmt,¶m);
if(ret){
printf("mysql_stmt_bind_param : %s\n",mysql_error(handle));
return -3;
}
使用方法mysql_stmt_send_long_data()
将数据装载,mysql_stmt_execute()
发送数据。
ret = mysql_stmt_send_long_data(stmt,0,buffer,length);
if(ret){
printf("mysql_stmt_send_long_data : %s\n",mysql_error(handle));
return -4;
}
ret = mysql_stmt_execute(stmt);
if(ret){
printf("mysql_stmt_execute : %s\n",mysql_error(handle));
return -5;
}
ret = mysql_stmt_close(stmt);
if(ret){
printf("mysql_stmt_close : %s\n",mysql_error(handle));
return -6;
}
预处理从数据库中查找数据同理。通过预处理方绑定参数执行。
#define SQL_SELECT_IMG_USR "SELECT U_IMG FROM TBL_USER WHERE U_NAME='A';"
MYSQL_STMT* stmt = mysql_stmt_init(handle);
int ret = mysql_stmt_prepare(stmt,SQL_SELECT_IMG_USR,strlen(SQL_SELECT_IMG_USR));
MYSQL_BIND result = {0};
result.buffer_type = MYSQL_TYPE_LONG_BLOB;
unsigned long total_length = 0;
result.length = &total_length;
ret = mysql_stmt_bind_result(stmt,&result);
ret = mysql_stmt_execute(stmt);
ret = mysql_stmt_store_result(stmt);
与查找数据类似,被查找的数据存放在内存中,mysql_stmt_store_result()
返回存放数据的指针。与查找数据不同的是,二进制是被按照一定长度截断逐行存放。
执行预处理语句之后,使用mysql_stmt_fetch()
获取结果行,自动填充total_length
。从结果中逐行取出数据处理,每次往缓冲区中写入一个字节。
ret = mysql_stmt_fetch(stmt);
if(ret != 0 && ret != MYSQL_DATA_TRUNCATED) break;
int start = 0;
while(start < (int)total_length){
result.buffer = buffer + start;
result.buffer_length = 1;
mysql_stmt_fetch_column(stmt,&result,0,start);
start += result.buffer_length;
}
在主函数中依次调用:
printf("case : mysql --> read image and write mysql\n");
char buffer[FILE_IMAGE_LENGTH] = {0};
int length = read_image("0voice.jpg",buffer);
if(length < 0) goto Exit;
mysql_write(&mysql,buffer,length);
printf("case : mysql --> read mysql and write image\n");
memset(buffer,0,FILE_IMAGE_LENGTH);
length = mysql_read(&mysql,buffer,FILE_IMAGE_LENGTH);
write_image("a.jpg",buffer,length);
问题及解决方案
问题一:如何理解MySQL
的用户管理?
MySQL
基于权限有严格的访问控制,每个用户由用户名和主机名组成('username'@'host'
)。每个不同级别的权限仅对限定的内容有访问权限,例如:全局权限、数据库级权限等。
问题二:如何解决SQL
注入的问题?
如果在进行SQL
语句的执行时,直接使用mysql_real_query()
输入原始SQL
语句则会出现SQL
注入问题。在任何情况下优先使用预处理语句可以有效避免该问题。
问题三:实现一个数据库连接池
声明结构体DBConnection
用于统一管理所有的连接,ConnectionPool
结构体作为每一个数据库的连接。代码如下:https://github.com/208822032/ConnectionPoll.git
总结
数据库作为程序的基石用于存放数据,本文介绍了在Linux操作系统中使用MySQL数据库,并通过远程连接node server
实现对数据的增删改查。