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实现对数据的增删改查。
浙公网安备 33010602011771号