后台开发学习笔记（二十、mysql建表，事务和存储过程）

刚刚专门去了解了一波mysql用程序是怎么使用的，确实有点大开眼界的感觉，mysql确实提供了几种方式，我们可以官网上多了解知道。

20.1 下载相关库

我们在第一篇的时候，安装了mysql，骚操作一大堆，到现在结果发现我们那天安装的只是数据结构，如果用操作只能用命令行操作，要想用程序操作，还要再搞搞，是不是觉得有点难受，我也不知道为什么这样设计的，因为这是我了解的第一个数据库，其他数据库怎么样我目前还不清楚，所以对这种方式不是很理解，反正别人都这样提供了，那就搞搞了，本证学习嘛，就是不断的去探索。

20.1.1 官网介绍

我还是一直一如既往的相信官网，下面是官网的链接和截图：
官网下载地址

通过图片可以看到，左边的就是我们上次下载的mysql的安装包，右边的就是我们今天要了解的，

1. C API 就是直接调用mysql的API，不过好像是c语言版的，我们到时候就要以这种方式实现。
2. Connector c++/J/NET/Node.js/python 这些是官网已经把API直接封装了一层的接口，叫连接器，我们可以直接安装这些程序，然后直接调用连接器，也是可以操作mysql的，并且这些接口比较简单。

20.1.2 下载libmysqlclient

还记得我们以前下载了那个很大的包么，原来我们下载的包竟然也有这个程序的存在，竟然存在了，那就安装吧。

root@debian:~/mysql# dpkg -i libmysqlclient-dev_8.0.19-1debian9_i386.deb 
Selecting previously unselected package libmysqlclient-dev.
(Reading database ... 48495 files and directories currently installed.)
Preparing to unpack libmysqlclient-dev_8.0.19-1debian9_i386.deb ...
Unpacking libmysqlclient-dev (8.0.19-1debian9) ...
dpkg: dependency problems prevent configuration of libmysqlclient-dev:
 libmysqlclient-dev depends on libmysqlclient21 (= 8.0.19-1debian9); however:
  Package libmysqlclient21 is not installed.

dpkg: error processing package libmysqlclient-dev (--install):
 dependency problems - leaving unconfigured
Processing triggers for man-db (2.7.6.1-2) ...
Errors were encountered while processing:
 libmysqlclient-dev

我的感觉是对的，我说怎么有两个，应该是依赖关系，果然是依赖关系。
那既然是依赖关系，那我们可以把依赖的包先安装

root@debian:~/mysql# dpkg -i libmysqlclient21_8.0.19-1debian9_i386.deb 
Selecting previously unselected package libmysqlclient21:i386.
(Reading database ... 48527 files and directories currently installed.)
Preparing to unpack libmysqlclient21_8.0.19-1debian9_i386.deb ...
Unpacking libmysqlclient21:i386 (8.0.19-1debian9) ...
Setting up libmysqlclient21:i386 (8.0.19-1debian9) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...

root@debian:~/mysql# dpkg -i libmysqlclient-dev_8.0.19-1debian9_i386.deb 
(Reading database ... 48535 files and directories currently installed.)
Preparing to unpack libmysqlclient-dev_8.0.19-1debian9_i386.deb ...
Unpacking libmysqlclient-dev (8.0.19-1debian9) over (8.0.19-1debian9) ...
Setting up libmysqlclient-dev (8.0.19-1debian9) ...
Processing triggers for man-db (2.7.6.1-2) ...
root@debian:~/mysql# 

这样就安装成功了，安装成功后，我们要看一看我们安装的是啥。

我用find命令全盘搜索，找到了两个目录下我们关心的东西，
第一个就是库文件，我们等下编译程序的时候，是需要链接这个库的。
第二个就是头文件，有库文件我们也不一定会用，所以一定需要头文件，所以也找到了头文件，这样就可以给下面的编码做了一个准备。

20.1.3 下载c++连接器

目前还没想用这个，先留空把，以后又用到，再写。

20.2 使用libmysql

我们现在先用libmysql这种方式实现，c++的方式把，应该不用c了，c写面向对象有点累。

20.2.1 libmysql开发环境

由上面介绍，我们的lib头文件存在/usr/include/mysql,然后我们看一下文件：

好少文件，我当时还以为我下载错误了，然后再复查一遍，发现没错啊，可能是mysql升级的过程中，已经减少了一些头文件。这里面的文件，核心的是mysql.h,这个是主要的API接口文件。

20.2.2 重点数据结构解析

MYSQL ： mysql数据库链接句柄。在执行任何数据库操作之前首先就需要创建一个MYSQL结构。

MYSQL_RES ： 执行查询语句（SELECT，SHOW，DESCRIBE,EXPLAN）返回结果。

MYSQL_ROW ： 用来表示返回结果中的一行数据。由于每行数据格式不一致，因此使用此结构来统一表示。调用mysql_fetch_row()可从MYSQL_RES中返回一个MYSQL_ROW结果。

MYSQL_FIELD： 用来表示一个field信息的元数据（元数据，即描述数据的数据），包括field name，field type以及field size等。MYSQL_FIELD不包含field的值（MYSQL_ROW真正保存各field的值。）

MYSQL_FIELD_OFFSET： field在row中的索引值，从0开始。

20.2.3 重点函数解析

/* 创建一个mysql对象，后面所有的接口都依赖mysql */
MYSQL *mysql_init(MYSQL *mysql)

/* 连接mysql数据库 */
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd,
const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag);

/* 执行mysql语句 */
int mysql_real_query(MYSQL *mysql, const char *stmt_str, unsigned long length);

/* 执行完查询语句后，获取结果集 */
MYSQL_RES *mysql_store_result(MYSQL *mysql);

/* 对结果集的操作*/
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
my_ulonglong mysql_num_rows(MYSQL_RES *res);
unsigned int mysql_num_fields(MYSQL_RES *res);

/* 一行数据的结果，通过row[i]去获取值，如果是整形，还得去转换 */
typedef char **MYSQL_ROW;

/* 释放结果集的空间 */
void mysql_free_result(MYSQL_RES *result);

/* 关闭mysql连接，释放mysql句柄 */
void STDCALL mysql_close(MYSQL *sock);

20.2.4 存储引擎的区别

show engines;

输入这个命令即可查看存储引擎：

我也是第一次学习mysql，所以就对着上面的引擎都看一遍。

FEDERATED：联合MySQL存储引擎。
在实际工作中，我们可能会遇到需要操作其他数据库实例的部分表，但又不想系统连接多库。此时我们就需要用到数据表映射。这个引擎模式是关闭的，需要手动打开。相关连接：MySQL下FEDERATED引擎的开启和使用

MRG_MYISAM：相同的MyISAM表的集合。不是很懂，不过这里分享一下连接mysql使用MRG_MyISAM(MERGE)实现水平分表。

InnoDB：支持事务，行级锁定和外键。这个是mysql默认创建的引擎，这个引擎支持事务，支持行级锁，支持外键（我也不知道外键是啥）innodb中外键的简单讲解。

CSV：csv存储引擎。MySQL数据库CSV存储引擎的使用（转）

MEMORY：哈希表怎么底层，所以查询速度很快，但是存储在内存中，所以可以作为临时表存储，比较少用。

MyISAM：MyISAM存储引擎。可以看看这篇文章：MySQL常见的三种存储引擎（InnoDB、MyISAM、MEMORY）。

PERFORMANCE_SCHEMA：MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。推荐链接：MySQL默认数据库之performance_schema库。

ARCHIVE：这个数据只能读，不能写。推荐链接：mysql存储引擎ARCHIVE。

BLACKHOLE：任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select语句的内容永远是空。 和Linux中的 /dev/null 文件完成的作用完全一致。MySQL的BlackHole引擎在主从架构中的作用。

看着这么多存储引擎，突然发现mysql还真不容易，在选择的时候，还需要选择不同的引擎，不过我们这里常用的是InnoDB和MYISAM，所以下面我们来看看两个的区别：

InnoDB与MyISAM的区别：

1. InnoDB支持事务，MyISAM不支持事务
2. InnoDB支持行级锁，MyISAM支持表级锁
3. InnoDB支持MVCC,MyISAM不支持
4. InnoDB支持外键，MYISAM不支持
5. InnoDB不支持全文索引，MYISAM支持

MYISAM：如果数据表只用用来插入和查询记录，则MYISAM（但不支持事务）引擎能提供较高的处理效率。

20.2.5 简单的实现

其实通过上面的函数我们也知道了一些简单的接口，下面就来封装一些这些简单的接口。

先来看看类的定义：

class Sqlconnection
{
    public:
        Sqlconnection();
        ~Sqlconnection();

        bool connect(const char *host, const char *user, const char *passwd, const char *db, unsigned int port);
        const char* GetErrInfo();
        bool Execyte(const char* Sql);
        bool Execyte(const char* sql, SqlResult& res);
        void Reconnect();
        void Close();

        bool CreatDataBase(const char* name);
        bool UseDataBase(const char* name);
        MYSQL_ROW GetData(const char* tablename, const char* key, const char* value);

    private:
        MYSQL *_mysql;

    protected:

};

这是我自己封装的，水平有限，不要吐槽了，就是当做学习的一个笔记而已。
我在这个类里封装了一个MYSQL的指针，这个指针就是MYSQL的句柄，以后挺有用的，所以就封装了一个。

20.2.6 构造析构

/**
    * @brief  Sqlconnection构造函数
    * @param  
    * @retval 
    */ 
Sqlconnection::Sqlconnection()
{
    _mysql = new MYSQL;
}    

/**
    * @brief  Sqlconnection析构函数
    * @param  
    * @retval 
    */ 
Sqlconnection::~Sqlconnection()
{
    if(_mysql) {
        mysql_close(_mysql);
        delete _mysql;
        _mysql = NULL;
    }
}

构造析构很简单的，就是申请了句柄指针的内存，析构就是释放。

20.2.7 连接函数

/**
    * @brief  Sqlconnection连接函数
    * @param  
    * @retval 
    */ 
bool Sqlconnection::connect(const char *host, const char *user, const char *passwd, const char *db, unsigned int port)
{
    _mysql = mysql_init(_mysql);
    if(!_mysql)  {
        printf("init %s\n", GetErrInfo());
        return false;
    }

    //尝试连接
    if(mysql_real_connect(_mysql, host, user, passwd, db, port, NULL, 0) == NULL ) {
        printf("connect %s\n", GetErrInfo());
        return false;
    }

    char cAuto = 1;
    if(!mysql_options(_mysql, MYSQL_OPT_RECONNECT, &cAuto)) {
        printf("option MYSQL_OPT_RECONNECT %s\n", GetErrInfo());
    }

    return true;
}

连接函数做的事情比较多，首先是初始化mysql的句柄，初始化成功以后，就调用真正的连接函数，这个连接函数参数还不少，有主机名，有用户，有密码，就是我们用命令行登录mysql差不多。连接成功之后，我们可以设置一下重连的参数，不过我这里一直设置都是失败，不知道为什么。

20.2.8 关闭函数

void Sqlconnection::Close()
{
    mysql_close(_mysql);
    _mysql = NULL;
    printf("disconnect with mysql.");
}

有连接就有关闭，关闭比较简单也是直接调用API即可。

20.2.9 执行mysql命令

bool Sqlconnection::Execyte(const char* sql)
{
    if(mysql_real_query(_mysql, sql, strlen(sql)) != 0 ) {
        if (mysql_errno(_mysql) == CR_SERVER_GONE_ERROR) {
            Reconnect();
	    }
        printf("execty %s\n", GetErrInfo());
        return false;
    }
    return true;
}

bool Sqlconnection::Execyte(const char* sql, SqlResult& res)
{
    if(mysql_real_query(_mysql, sql, strlen(sql)) != 0 ) {
        if (mysql_errno(_mysql) == CR_SERVER_GONE_ERROR) {
            Reconnect();
	    }
        printf("execty %s\n", GetErrInfo());
        return false;
    }

    MYSQL_RES* pRes = mysql_store_result(_mysql);
    if(!pRes)  {
        return NULL;
    }
    res.SerResult(pRes);

    return true;
}

这个写了一个重载函数，mysql没有提供直接的创建，删除，插入等操作，只是提供了一个可以执行mysql命令的函数，mysql_real_query就是执行mysql命令的语句，我们把命令写好作为一个参数传入。
第二个函数是重载的，mysql的查询也是通过命令执行的，所以还需要一个查询命令，然后把返回值保存，mysql_store_result可以通过这个函数获取结果集。这个结果集是一个结构体，类型是MYSQL_RES，然后我又把这个结构体又给封装了一层：

class SqlResult
{
    public:
        SqlResult() : _sqlres(NULL)
        {}

        ~SqlResult()
        {
            mysql_free_result(_sqlres);
        }

        inline void SerResult(MYSQL_RES* pRes)
        {
            assert(_sqlres == NULL);
            if(_sqlres) {
                printf("MYSQL_RES已经存储了结果，可能会导致内存泄漏");
            }

            _sqlres = pRes;
        }

        inline MYSQL_RES* GetResult()
        {
            return _sqlres;
        }

        void FetchRow(MYSQL_ROW& row)
        {
            row = mysql_fetch_row(_sqlres);
        }

        int RowCount()
        {
            return  _sqlres->field_count; 
        }

        inline int GetRowCount()  //这个表有多少条数据
        {
            return _sqlres->row_count;
        }

    private:
        MYSQL_RES* _sqlres;
};

这个封装比较简单，就是保存结果，并且提供一些提取结果的函数，

 void FetchRow(MYSQL_ROW& row)
{
     row = mysql_fetch_row(_sqlres);
}

这个函数就是提取查询结果的，如果能打开mysql.h的话，就知道MYSQL_ROW是一个二维指针，存储的是一个二维数组，也是一个字符串数据，需要获取数据是直接下标操作方式如：row[i]，如果不知道获取回来的数据的长度，可以使用这个函数RowCount(),这个函数就是获取这个字符串数组的长度的。

20.2.10 创建数据库

bool Sqlconnection::CreatDataBase(const char* name)
{
    char cmd[100] = {0};

    snprintf(cmd, sizeof(cmd), "CREATE DATABASE %s;", name);
    return Execyte(cmd);
}

bool Sqlconnection::UseDataBase(const char* name)
{
    char cmd[100] = {0};

    snprintf(cmd, sizeof(cmd), "USE %s;", name);
    return Execyte(cmd);
}

这个很简单，就是把mysql的语句拼装成一个字符串，然后调用执行mysql语句的函数即可。本来还想写创建表的，结果发现很难，因为创建表的时候需要带一些数据，这些数据好像不能通过参数决定的，所以就不写了，以后看看别人是怎么写的，再回来封装，这个封装其实就是熟悉mysql的过程，代码感觉还是比较简单。

20.2.11 获取数据

MYSQL_ROW Sqlconnection::GetData(const char* tablename, const char* key, const char* value)
{
    char cmd[100] = {0};
    //SqlResult *res = new SqlResult();
    SqlResult res;

    snprintf(cmd, sizeof(cmd), "select * from %s where %s='啊甘';", tablename, key);
    Execyte(cmd, res);

    int count=res.GetRowCount();
    printf("count %d\n", count);
    MYSQL_ROW row = NULL;
    res.FetchRow(row);

    for(int i=0; i <res.RowCount(); i++ ) {
        printf("%s ", row[i]);
    }

    return NULL;
}

写到这里了，总要看看怎么查询数据把，查询数据也简单，还是封装查询的mysql命令，然后调用执行mysql命令的函数，但是这个是有返回值，上面也讲过了，这个返回其实就是一个字符串数组，只要要小标遍历即可，所以我这里就直接遍历了，说实话，还是不知道别人是怎么封装数据库，所以我只是调试一下就直接用打印了。

20.3 使用链接器

暂时先不用写。

20.4 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性
  一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性
  在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性
  数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性
  事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

20.3.1 事务隔离级别

事务的隔离性也有4个等级。

• READ UNCOMMITTED：事务中的修改，即使没有提交，对其他会话也是可见的。
  可以读取为提交的数据——脏读。脏读会导致很多问题，一般不适用这个隔离级别。

• READ COMMITTED：一般数据库都默认使用这个隔离级别（mysql不是），这个隔离级别保证了一个事务如果没有完全成功（commit执行完），事务中的操作对其他会话是不可见的。但是，commit之后是可见的，这样会产生不可重复读，比如事务1有两次查询得到的结果不一样。

• REPEATABLE READ：一个事务中多次执行统一读SQL,返回结果一样。 这个隔离级别解决了脏读的问题，和不可重复读的问题。但是可能会引起幻读的
  问题，比如下图

• SERIALIZABLE：innodb中使用next-key锁对”当前读”进行加锁，锁住行以及可能产生幻读的插入位置，阻止新的数据插入产生幻行，最强的隔离
  级别，通过给事务中每次读取的行加锁，写加写锁，保证不产生幻读问题，但是会导致大量超时以及锁争用问题。
  
  这个太难了，目前不做考虑。

20.3.2 设置事务隔离级别

例：
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

20.3.3 代码使用一个事务

bool Sqlconnection::Transaction(char** sqls, int size)
{
    int ret = 0;
    ret = mysql_real_query(_mysql, "SET AUTOCOMMIT=0;", (unsigned int)strlen("SET AUTOCOMMIT=0;"));
    if(ret != 0)
    {
        printf("SET AUTOCOMMIT=0; flase\n");
        return false;
    }

    ret = mysql_real_query(_mysql, "begin;", (unsigned int)strlen("begin;"));
    if(ret != 0)
    {
        printf("begin\n");
        return false;
    }

    for(int i=0; i < size; i++)  {
        ret = mysql_real_query(_mysql, sqls[i], (unsigned int)strlen(sqls[i]));
        if(ret != 0)
        {
            printf("execute sql=%s failed\n", sqls[i]);
            mysql_real_query(_mysql, "rollback;", (unsigned int)strlen("rollback;"));
            return false;
        }
    }

    ret = mysql_real_query(_mysql, "commit;", (unsigned int)strlen("commit;"));
    if(ret != 0)
    {
        printf("commit\n");
        mysql_real_query(_mysql, "rollback;", (unsigned int)strlen("rollback;"));
        return false;
    }

    return true;
}

这就是mysql的一个简单的事务封装，

1. 先把自动提供关闭
2. 开启事务，begin语句
3. 执行一系列的sql语句，这些语句就是要求有原子性，如果有一哥执行错误，立即回滚
4. 执行commit语句，事务执行完成之后，都需要提交的，如果提供出错，也立即回滚。

20.5 存储过程

mysql是可以保存存储过程的，保存下来之后，可以在代码中直接调用这个过程。

语法：
create procedure 存储过程名字 ( [in|out|inout] 参数 datatype )
begin
MySQL 语句;
end;

调用：
call procedure_name(参数列表)

这个就不试了，偷下懒，以后有用到再试。

这一篇都是比较简单的mysql的使用，等以后学多几个数据库之后，再另行封装。