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接收请求,此时需要中间件来处理客户端的请求,与数据库进行交互;拿到服务器的数据之后将数据处理发送到客户端。工作流程图如下:

  1. 连接数据库

​ 如工作流程图所示,整个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;
        }
  1. 查找数据

​ 查找数据,接收从数据库传来的数据,按照行列处理。其中常用到mysql_real_querymysql_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");
    }
  1. 插入与删除数据

​ 插入与删除数据同样是基于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;
    }
  1. 处理二进制类型数据

​ 在数据库中,可存放字符串整数类型值之外的类型值。本文仅以图片类型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,&param);
    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实现对数据的增删改查。

posted @ 2025-05-01 20:41  +_+0526  阅读(18)  评论(0)    收藏  举报