Back
Featured image of post Mysql基本知识
数据库

Mysql基本知识

基础知识

MYSQL

mysql安装与配置

在虚拟机上安装mysql,使用apt-get install就可以 这里我只检索到了mysql-server-5.7就安装了5.7

在本地win10上安装mysqlbench用于连接虚拟机的mysql服务器 这里使用网络连接，可能是因为mysql版本的原因，本来应该在/etc/mysql中的my.cnf文件中显式的配置有基本信息，我只需要修改部分，但5.7在/etc/mysql/mysql.conf.d/mysqld.cnf,在它的基础上修改对应的bind-address为0.0.0.0保证回环地址可访问：


#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

这样保证win10的mysqlbench可以连接到虚拟机的mysql服务器

但是还需要在mysql中设置对应用户并使之具有外部访问的权限和操作数据库的权限，我这里直接新建gao用户并赋予外部访问权限和操作权限：

CREATE USER 'gao'@'%' IDENTIFIED BY 'password';

%号表示可以被任意位置访问也就允许了远程ip访问 然后给gao用户授权，使其能随意操作数据库：

GRANT all privileges ON * TO 'gao'@'%';

可以在mysql这个数据库内的user表内找到自己添加的用户信息

mysql 建表添加数据等操作

建立使用的数据库USR_DB

create databases USR_DB;

使用USR_DB

use USR_DB;

建表TBL_USR

create table TBL_USR(
    U_ID int primary key auto_increment,
    U_NAME char(10), 
    U_GENGDER char(10)
);

插入表项

insert TBL_USR(U_NAME, U_GENGDER) values('gao', 'man');

选取表中所有数据显示

select * from TBL_USR;

删除与修改表项就涉及到安全模式，mysql默认运行在安全模式所以不能进行修改和删除表项的操作，所以需要取消安全模式然后操作，操作结束后需要再设置回安全模式

set SQL_SAFE_UPDATES = 0;
delete from TBL_USR where U_NAME = UNAME;
set SQL_SAFE_UPDATES = 1;

但这样操作是有问题的，如果这三部操作是原子的，是没有问题的，但不是原子的就引入不安全的因素，其他进程可能趁这个时候错误的篡改数据，所以将这三条合成一个过程来确保操作的安全性

DELIMITER ##
create procedure proc_delete_usr(in UNAME char(10))
begin
set SQL_SAFE_UPDATES = 0;
delete from TBL_USR where U_NAME = UNAME;
set SQL_SAFE_UPDATES = 1;
end##

上面这个DELIMITER ##的意思就是这一段使用##作为限制符号，也就是##框住的区域视为一个整体区域，用于指示过程的区域

定义了过程之后就可以使用call调用过程达到安全的操作：

call proc_delete_usr('gao');

同样的，修改也可以这样：

DELIMITER ##
create procedure set_img(in UNAME char(10), UIMG BLOB)
begin
set SQL_SAFE_UPDATES = 0;
update TBL_USR set U_IMG = UIMG where U_NAME = UNAME;
set SQL_SAFE_UPDATES = 1;
end##
call set_img('gao',IMG);

这里的call set_img里的IMG其实在后面用于c的API调用,绑定statement之后传入的是一个char*的buffer接收的图像数据,然后设置到数据库里

上面用到了U_IMG的column,这个列在建表时没有建立，需要使用添加column操作：

ALTER TABLE TBL_USR create column U_IMG;

当然也可以使用以下操作删除：

ALTER TABLE TBL_USR drop column U_IMG;

C api远程调用mysql

编写C程序来做到控制mysql数据库

安装库：

先安装相关依赖和库才可以调用c api： 直接在虚拟机上

sudo apt-get install libmysqlclient-dev;

就安装成功了相关的c开发套件

使用时需要在程序中包含头文件：

#include<mysql.h>

在编译相关程序时:

gcc -o xxx xxx.c -I /usr/include/mysql -lmysqlclient

这里基本准备就完成

基本操作:

首先需要连接mysql数据库，可以想到的就是建立一个mysql的handler，所以很自然的这里就需要一个特殊的struct，库为我们提供了MYSQL的数据类型：

MYSQL mysql;

这样就建立了mysql这样一个handle，之后的所有操作都基于这个handle进行

连接操作：

if(NULL == mysql_init(&mysql))
{
    printf("mysql init %s\n", mysql_error(&mysql));
    return -1;
}

if(!mysql_real_connect(&mysql, 
    king_db_server_ip, king_db_username,
    king_db_password, king_db_default_db, 
    king_db_server_port, NULL, 0))
{
    printf("mysql_real_connect: %s\n", mysql_error(&mysql));
}

可读性很高，这里不解释

然后发送自己预定义好的sql语句：

#define sql_insert "insert TBL_USR(U_NAME, U_GENGDER) values('qiuxiang', 'woman');"

#if 1
if(mysql_real_query(&mysql, sql_insert, strlen(sql_insert)))
{
    printf("mysql_real_query: %s\n", mysql_error(&mysql));
}
#endif

一般情况下不进行其他操作了的话需要关闭mysql连接：

mysql_close(&mysql);

以上就是简单的基于c api的mysql操作了

其他操作

select基础

如果需要从mysql服务器接收数据，比如select一些数据， 那么就需要一个容器来接受数据，这里使用MYSQL_RES来保存mysql的返回的结果:

同样需要先query：

if(mysql_real_query(mysql, sql_select, strlen(sql_select)))
{
    printf("mysql_real_query: %s\n", mysql_error(mysql));
    return -1;
}

然后接收数据

MYSQL_RES *res = mysql_store_result(mysql);
if(res == NULL)
{
    printf("mysql_real_query: %s\n", mysql_error(mysql));
    return -2;
}

然后处理数据（打印出来） 想打印的话首先需要知道行列数然后再选取需要的数据来打印：

int rows = mysql_num_rows(res);
printf("rows: %d\n", rows);
int fields = mysql_num_fields(res);
printf("fields: %d\n", fields);

再根据获取的行列数循环fetch数据行然后打印特定行列的数据

MYSQL_ROW row;
    
while(row = mysql_fetch_row(res))
{
    int i=0;
    for(i=0; i<fields;++i)
    {
        printf("%s\t", row[i]);
    }
    printf("\n");
}

这里就可以看到，数据转存到了MYSQL_ROW这个结构中

最后释放接收的结果

mysql_free_result(res);

statement

使用statement来存储或发送数据到mysql服务器

整个流程： 1、初始化stmt，使用MYSQL* handle 2、 准备statement类似于query但是不执行 3、初始化绑定参数MYSQL_BIND param，因为要insert所以要初始化buffer用于指示insert数据 4、将参数绑定到stmt上 5、将buffer中的数据通过statement发送到mysql服务器？（不太清楚是否真的发送了） 6、执行statement 7、执行完毕关闭statement

MYSQL_STMT *stmt = mysql_stmt_init(handle);

int ret = mysql_stmt_prepare(stmt, sql_insert_img, strlen(sql_insert_img));
if(ret)
{
    printf("mysql_stmt_prepare error: %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 error: %s\n", mysql_error(handle));
    return -3;
}  

ret = mysql_stmt_send_long_data(stmt, 0, buffer, length);

if(ret)
{
    printf("mysql_stmt_send_long_data error: %s\n", mysql_error(handle));
    return -4;
}

ret = mysql_stmt_execute(stmt);

if(ret)
{
    printf("mysql_stmt_execute error: %s\n", mysql_error(handle));
    return -5;
}

ret = mysql_stmt_close(stmt);

if(ret)
{
    printf("mysql_stmt_close error: %s\n", mysql_error(handle));
    return -6;
}

以下是一个read的statement：

MYSQL_STMT *stmt = mysql_stmt_init(handle);

int ret = mysql_stmt_prepare(stmt, sql_select_img, strlen(sql_select_img));
if(ret)
{
    printf("mysql_stmt_prepare error: %s\n", mysql_error(handle));
    return -2;
}

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);

if(ret)
{
    printf("mysql_stmt_bind_result error: %s\n", mysql_error(handle));
    return -3;
}

ret = mysql_stmt_execute(stmt);

if(ret)
{
    printf("mysql_stmt_execute error: %s\n", mysql_error(handle));
    return -4;
}

ret = mysql_stmt_store_result(stmt);

if(ret)
{
    printf("mysql_stmt_store_result error: %s\n", mysql_error(handle));
    return -5;
}  

while(1)
{
    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;
    }
}
mysql_stmt_close(stmt);

可以看到多了fetch操作将result的buffer成员指向外部接受用的buffer的最新的接受位置，mysql_stmt_fetch_column进行了接收工作，MYSQL_BIND result也有了新的定义方式

comments powered by Disqus
© 2020 - 2022 Gao's Happy Day
Built with Hugo
Theme Stack designed by Jimmy