参考flamingo即时通信软件。
在上一篇文章中,我们定义了与Mysql服务器进行交互,执行sql语句的DatabaseMysql类。
这个类可以对已经存在的数据库进行查询、更新操作,提供了方便调用的接口。
但是我们到现在还没有创建数据库和表。本篇文章需要在DatabaseMysql的基础上,定义数据库以及表的结构。
数据库表定义
目前我们开发的是聊天服务器,不考虑文件和图片。经过分析,可以设计出下面三张表:
t_user表
这个表记录的是用户的个人信息。
列名 | 类型 | 约束条件 | 说明 |
---|---|---|---|
f_id | bigint(20) | NOT NULL AUTO_INCREMENT | 自增id |
f_user_id | bigint(20) | NOT NULL | 用户id |
f_username | varchar(64) | NOT NULL | 用户名 |
f_password | varchar(64) | NOT NULL | 登陆密码 |
f_facetype | int(10) | DEFAULT 0 | 头像类型 |
f_customface | varchar(64) | DEFAULT NULL | 自定义头像名 |
f_gender | int(2) | DEFAULT 0 | 性别 |
f_mail | varchar(256) | DEFAULT NULL | 邮件 |
f_register_time | datetime | NOT NULL | 注册时间 |
f_update_time | timestamp | NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 表项更新时间 |
然后设置这张表的主键、索引:
PRIMARY KEY (f_user_id), INDEX f_user_id (f_user_id), KEY f_id ( f_id )
t_user_relationship表
这张表记录的是好友的信息
列名 | 类型 | 约束条件 | 说明 |
---|---|---|---|
f_id | bigint(20) | NOT NULL AUTO_INCREMENT | 自增id |
f_user_id1 | bigint(20) | NOT NULL | 用户1id |
f_user_id2 | bigint(20) | NOT NULL | 用户2id |
f_user1_markname | varchar(32) | DEFAULT NULL | 用户2对用户1的备注名字 |
f_user2_markname | varchar(32) | DEFAULT NULL | 用户1对用户2的备注名字 |
f_update_time | timestamp | NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 表项更新时间 |
然后设置这张表的主键、索引:
PRIMARY KEY (f_id), INDEX f_id (f_id)
t_chatmsg表
这张表记录的是聊天信息
列名 | 类型 | 约束条件 | 说明 |
---|---|---|---|
f_id | bigint(20) | NOT NULL AUTO_INCREMENT | 自增id |
f_senderid | bigint(20) | NOT NULL | 发送者id |
f_receiverid | bigint(20) | NOT NULL | 接收者id |
f_msgcontent | BLOB | NOT NULL | 发送的信息 |
f_create_time | timestamp | NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 消息发送时间 |
然后设置这张表的主键、索引
PRIMARY KEY (f_id), INDEX f_id (f_id)
代码
确定了数据库表的结构,下面使用代码创建数据库和表。
MysqlManager
定义了两个结构体用来存储表的定义。并且定义了函数用来创建数据库、创建表。它的工作流程是:
- 首先连接一个默认数据库,查询当前mysql服务器中有哪些数据库
isDBExist()
- 如果我们要连接的数据库不存在,则创建数据库
createDB()
- 接下来连接我们需要使用的数据库,检查数据库中每个表的定义是否正确;如果表不存在,则创建表
createTable()
;如果表定义不正确,则修改表的定义
// MysqlManager.h
#pragma once
#include <memory>
#include <map>
#include <vector>
#include <string>
#include <iostream>
#include "DatabaseMysql.h"
#define MAXCMDLEN 8192
struct TableField
{
TableField() {}
TableField(std::string name, std::string type, std::string des) : m_name(name), m_type(type), m_des(des)
{
}
// 当前列的名称
std::string m_name;
// 当前列的约束条件
std::string m_type;
// 当前列的数据类型
std::string m_des;
};
struct TableInfo
{
TableInfo() {}
TableInfo(std::string name) : m_name(name) {}
// 表的名称
std::string m_name;
// 表的每一列的定义
std::map<std::string, TableField> m_mapField;
// 这张表的主键、索引
std::string m_keyStr;
};
class MysqlManager
{
public:
MysqlManager();
virtual ~MysqlManager() {}
public:
bool init(const char* host, const char* user, const char* pwd, const char* dbname, const int port);
std::string getHost() { return m_strHost; }
std::string getUser() { return m_strUser; }
std::string getPwd() { return m_strPwd; }
std::string getDbname() { return m_dbname; }
private:
bool isDBExist();
bool createDB();
bool checkTable(const TableInfo& table);
bool createTable(const TableInfo& table);
bool updateTable(const TableInfo& table);
protected:
std::shared_ptr<DatabaseMysql> m_conn;
std::string m_strHost;
std::string m_strUser;
std::string m_strPwd;
std::string m_dbname;
int m_port;
std::string m_charset;
std::vector<TableInfo> m_vecTableInfo;
};
#include "MysqlManager.h"
#include <sstream>
MysqlManager::MysqlManager()
{
m_charset = "utf8mb4";
// 初始化表 t_user
{
TableInfo info;
info.m_name = "t_user";
info.m_mapField["f_id"] = { "f_id", "bigint(20) NOT NULL AUTO_INCREMENT", "bigint(20)" };
info.m_mapField["f_user_id"] = { "f_user_id", "bigint(20) NOT NULL", "bigint(20)" };
info.m_mapField["f_username"] = { "f_username", "varchar(64) NOT NULL", "varchar(64)" };
info.m_mapField["f_password"] = { "f_password", "varchar(64) NOT NULL", "varchar(64)" };
info.m_mapField["f_facetype"] = { "f_facetype", "int(10) DEFAULT 0", "int(10)" };
info.m_mapField["f_customface"] = { "f_customface", "varchar(32) DEFAULT NULL", "varchar(32)" };
info.m_mapField["f_gender"] = { "f_gender", "int(2) DEFAULT 0", "int(2)" };
info.m_mapField["f_mail"] = { "f_mail", "varchar(256) DEFAULT NULL", "varchar(256)" };
info.m_mapField["f_register_time"] = { "f_register_time", "datetime NOT NULL", "datetime" };
info.m_mapField["f_update_time"] = { "f_update_time", "timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", "timestamp" };
// 设置索引,这样查找效率高
info.m_keyStr = "PRIMARY KEY (f_user_id), INDEX f_user_id (f_user_id), KEY f_id ( f_id )";
m_vecTableInfo.push_back(info);
}
// t_user_relationship
{
TableInfo info;
info.m_name = "t_user_relationship";
info.m_mapField["f_id"] = { "f_id", "bigint(20) NOT NULL AUTO_INCREMENT", "bigint(20)" };
info.m_mapField["f_user_id1"] = { "f_user_id1", "bigint(20) NOT NULL", "bigint(20)" };
info.m_mapField["f_user_id2"] = { "f_user_id2", "bigint(20) NOT NULL", "bigint(20)" };
info.m_mapField["f_user1_markname"] = { "f_user1_markname", "VARCHAR(32)", "VARCHAR(32)" },
info.m_mapField["f_user2_markname"] = { "f_user2_markname", "VARCHAR(32)", "VARCHAR(32)" },
info.m_mapField["f_update_time"] = { "f_update_time", "timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", "timestamp" };
info.m_keyStr = "PRIMARY KEY (f_id), INDEX f_id (f_id)";
m_vecTableInfo.push_back(info);
}
// t_chatmsg
{
TableInfo info;
info.m_name = "t_chatmsg";
info.m_mapField["f_id"] = { "f_id", "bigint(20) NOT NULL AUTO_INCREMENT", "bigint(20)" };
info.m_mapField["f_senderid"] = { "f_senderid", "bigint(20) NOT NULL", "bigint(20)" };
info.m_mapField["f_receiverid"] = { "f_receiverid", "bigint(20) NOT NULL", "bigint(20)" };
info.m_mapField["f_msgcontent"] = { "f_msgcontent", "BLOB NOT NULL", "BLOB" };
info.m_mapField["f_create_time"] = { "f_create_time", "timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", "timestamp" };
info.m_keyStr = "PRIMARY KEY (f_id), INDEX f_id (f_id)";
m_vecTableInfo.push_back(info);
}
}
bool MysqlManager::init(const char* host, const char* user, const char* pwd, const char* dbname, const int port)
{
m_strHost = host;
m_strUser = user;
// 数据库密码可能为空
m_strPwd = pwd;
m_dbname = dbname;
m_port = port;
m_conn.reset(new DatabaseMysql());
// 测试了一下能不能连接上数据库,要把数据库名字设置为空,因为此时我们需要的数据库可能还不存在
// 如果将 dbname 参数设置为空字符串或者 NULL,则 mysql_real_connect 函数将连接到 MySQL 服务器的默认数据库,通常是 mysql 数据库,而不是真正需要连接的数据库
if (!m_conn->initialize(host, user, pwd, "", port))
{
return false;
}
// 检查数据库是否存在
if (!isDBExist())
{
// 不存在的话,创建数据库
if (!createDB())
{
return false;
}
}
// 测试能不能连上我们真正需要的数据库
m_conn.reset(new DatabaseMysql());
if (!m_conn->initialize(m_strHost, m_strUser, m_strPwd, m_dbname, m_port))
{
return false;
}
// 检查数据库中表是否正确
for (size_t i = 0; i < m_vecTableInfo.size(); i++)
{
TableInfo table = m_vecTableInfo[i];
if (!checkTable(table))
{
return false;
}
}
return true;
}
bool MysqlManager::isDBExist()
{
if (nullptr == m_conn)
{
return false;
}
QueryResult* pResult = m_conn->query("show databases");
if (pResult == nullptr)
{
return false;
}
// 循环读取数据库
std::cout << "existing databases: " << std::endl;
while (pResult->nextRow())
{
Field* pRow = pResult->fetch();
std::cout << pRow[0].getFieldValue() << std::endl;
if (m_dbname == pRow[0].getFieldValue())
{
// 当前数据库存在,任务结束,释放内存
delete pResult;
return true;
}
}
delete pResult;
return false;
}
bool MysqlManager::createDB()
{
if (m_conn == nullptr)
{
return false;
}
uint32_t uAffectedCount = 0;
int nError = 0;
std::stringstream ss;
ss << "create database " << m_dbname;
std::cout << ss.str() << std::endl;
if (m_conn->execute(ss.str().c_str(), uAffectedCount, nError))
{
if (uAffectedCount == 1)
{
return true;
}
}
else
{
return false;
}
return false;
}
bool MysqlManager::checkTable(const TableInfo& table)
{
if (m_conn == nullptr)
{
return false;
}
// 如果 table 对象的 m_name 成员变量中包含的所有字符都是制表符、回车符或换行符,则该代码将返回 true,否则返回 false
// 这个意思就是如果当前表的名字为空,那就返回true,不需要检查
if (table.m_name.find_first_not_of("\t\r\n") == std::string::npos)
{
return true;
}
std::stringstream ss;
ss << "desc " << table.m_name;
QueryResult* pResult = m_conn->query(ss.str());
std::cout << "check table " << table.m_name << std::endl;
// 查找不到
if (pResult == nullptr)
{
if (createTable(table))
{
return true;
}
return false;
}
// 查找到了,检查表的字段是否匹配
else
{
// 记录表中每一列的名字以及约束条件
std::map<std::string, std::string> mapOldTable;
while (pResult->nextRow())
{
Field* pRow = pResult->fetch();
std::string name = pRow[0].getFieldValue();
std::string type = pRow[1].getFieldValue();
mapOldTable[name] = type;
}
delete pResult;
for (std::map<std::string, TableField>::const_iterator it = table.m_mapField.begin(); it != table.m_mapField.end(); it++)
{
TableField field = it->second;
// 没有当前列的话,添加这一列
if (mapOldTable.find(field.m_name) == mapOldTable.end())
{
std::stringstream ss;
ss << "alter table " << table.m_name << " add column " << field.m_name << " " << field.m_type;
if (m_conn->execute(ss.str().c_str()))
{
continue;
}
else
{
return false;
}
}
}
}
return true;
}
bool MysqlManager::createTable(const TableInfo& table)
{
if (table.m_mapField.size() == 0)
{
return false;
}
std::cout << "create table " << table.m_name << std::endl;
std::stringstream ss;
ss << "create table if not exists " << table.m_name << " (";
for (std::map<std::string, TableField>::const_iterator it = table.m_mapField.begin(); it != table.m_mapField.end(); it++)
{
if (it != table.m_mapField.begin())
{
ss << ", ";
}
TableField field = it->second;
ss << field.m_name << " " << field.m_type;
}
// 这个数据库表还设置了主键和索引
if (table.m_keyStr != "")
{
ss << ", " << table.m_keyStr;
}
ss << ") default charset=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_unicode_ci;";
if (m_conn->execute(ss.str().c_str()))
{
return true;
}
LOGE("Create table error, sql: %s", ss.str().c_str());
return false;
}
结果
创建MysqlManager
实例后,运行init方法,在服务器中创建数据库,可以使用desc [表名字]
的方式查询一张表的定义。
mysql> desc t_user;
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| f_customface | varchar(32) | YES | | NULL | |
| f_facetype | int | YES | | 0 | |
| f_gender | int | YES | | 0 | |
| f_id | bigint | NO | MUL | NULL | auto_increment |
| f_mail | varchar(256) | YES | | NULL | |
| f_password | varchar(64) | NO | | NULL | |
| f_register_time | datetime | NO | | NULL | |
| f_update_time | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| f_user_id | bigint | NO | PRI | NULL | |
| f_username | varchar(64) | NO | | NULL | |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
10 rows in set (0.00 sec)
mysql> desc t_user_relationship;
+------------------+-------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+-------------------+-----------------------------------------------+
| f_id | bigint | NO | PRI | NULL | auto_increment |
| f_update_time | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| f_user1_markname | varchar(32) | YES | | NULL | |
| f_user2_markname | varchar(32) | YES | | NULL | |
| f_user_id1 | bigint | NO | | NULL | |
| f_user_id2 | bigint | NO | | NULL | |
+------------------+-------------+------+-----+-------------------+-----------------------------------------------+
6 rows in set (0.00 sec)
mysql> desc t_chatmsg;
+---------------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------+------+-----+-------------------+-----------------------------------------------+
| f_create_time | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| f_id | bigint | NO | PRI | NULL | auto_increment |
| f_msgcontent | blob | NO | | NULL | |
| f_receiverid | bigint | NO | | NULL | |
| f_senderid | bigint | NO | | NULL | |
+---------------+-----------+------+-----+-------------------+-----------------------------------------------+
5 rows in set (0.00 sec)
总结
下面主要总结一下数据据库表的设计。
数据类型选择
表的字段类型主要是int、bigint、varchar类型,在mysql中这几种类型的定义如下:
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
VARCHAR | 0-65535 bytes | 变长字符串 |
表中使用int类型的字段主要表示数量有限的种类、类型(如性别、头像类型)
表中使用bigint类型的字段主要用来记录数量比较巨大的id(如表中的自增id、用户id)
表中使用varchar类型的字段主要用来保存长度在一定范围内的字符串(用户名、密码、邮箱地址)
除了这三个基本的数据类型,表中还使用了datetime、timestamp、blob类型,在mysql中这几种类型定义如下:
类型 | 大小( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATETIME | 8 | ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
TIMESTAMP | 4 | ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC | YYYY-MM-DD hh:mm:ss | 混合日期和时间值,时间戳 |
其中TIMESTAMP类型数据有自动更新特性,我们在定义表的时候都设置好了。
下面是对自动更新特性的解释:
在MySQL中,TIMESTAMP类型具有自动更新特性,即当一行中的任意字段被修改时, TIMESTAMP类型字段将自动更新为当前时间戳。这个特性有以下作用:
(1)记录数据修改时间:TIMESTAMP字段的自动更新特性可以帮助我们记录数据的修改时间。例如,在一个文章表中,可以添加一个名为update_time的TIMESTAMP类型字段,用于记录文章最后一次修改的时间。每次修改文章时,update_time字段都会自动更新为当前时间戳,方便我们追踪文章的修改历史。
(2)实现数据版本控制:TIMESTAMP字段的自动更新特性可以用于实现数据版本控制。例如,在一个订单表中,可以添加一个名为version的TIMESTAMP类型字段,用于记录订单的版本。每次修改订单时,version字段都会自动更新为当前时间戳,这样就可以方便地判断哪个版本的订单是最新的。
(3)提高数据查询效率:TIMESTAMP字段的自动更新特性可以提高数据查询效率。例如,在一个用户表中,可以添加一个名为last_login_time的TIMESTAMP类型字段,用于记录用户最后一次登录的时间。当查询用户最后一次登录时间时,不需要进行额外的查询操作,只需要直接读取last_login_time字段即可。
表中使用DATETIME类型的字段主要用来记录当前条目第一次插入的时间(比如用户注册时间)
表中使用TIMESTAMP类型的字段主要用来更新当前条目最后一次修改时间(对于不需要修改的表,这个字段会自动记录条目的插入时间,使用很方便,比如用来记录一条聊天消息创建的时间)
BLOB类型数据主要用来存储二进制形式的长文本数据,在表中用来存放发送的聊天消息。需要注意的是,BLOB类型和TEXT类型字段占用的空间大小取决于实际数据长度。
类型 | 大小 | 用途 |
---|---|---|
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
BLOB一般用来存储图片、视频、音频的二进制数据,存储聊天消息直接用text类型就行了。而源码中使用的是BLOB,不知道有什么特别的考虑,以下是chatgpt的回答。
使用BLOB类型存储文本类型的聊天消息,可能是出于以下考虑:
(1)数据库设计的历史原因:可能是在早期的数据库设计中,当时使用BLOB类型存储文本类型的数据。随着时间推移,虽然数据库的结构和功能发生了变化,但是这种存储方式并没有改变。
(2)跨平台兼容性:BLOB类型是一种标准的二进制数据类型,在不同的数据库管理系统之间具有很好的兼容性。如果使用TEXT类型存储文本数据,可能会因为编码格式或其他原因,在不同的数据库管理系统之间出现兼容性问题。
(3)存储需求的变化:聊天消息在不同的应用场景下,可能需要存储不同的数据类型,例如文本、图片、音频、视频等。如果使用BLOB类型存储所有类型的数据,可以更加灵活地适应不同的存储需求。
需要注意的是,在使用BLOB类型存储文本类型的数据时,可能会导致查询和存储的效率较低。因此,在实际应用中,需要根据具体情况来选择合适的数据类型,并考虑到查询和存储的性能问题。
主键与索引
在创建表的时候,我们都指定了primary key
、key
、index
。这有什么用呢?
在MySQL中,key和index几乎是等价的概念,都可以称为“索引”。索引是帮助MySQL高效获取数据的数据结构。对于一张表来说,如果不加索引的话就要从表的第一行开始查找,如果一个表有百万行的话效率会非常低。如果有了索引,利用数据结构就可以快速查找。
索引的原理是,使用数据结构(B+树索引、Hash索引、Full-text索引)创建一张额外的索引表,提高查找效率,但是维护索引表也有开销。
索引主要可以分为三类:
- 普通索引:key或者index。作用是提高查询效率,允许空值和重复值。一张表可以有多个。
- 主键索引:primary key。作用是提高查找效率,不允许空值和重复值。一张表只能有一个。
- 唯一索引:unique key。作用是提高查找效率,允许空值,不允许重复值。一张表可以有多个。
源代码中,我们看到每个表都创建了索引。但是里面有个问题,就比如对t_user表创建的索引,为什么对f_user_id创建了主键索引的同时,还要对它创建一个普通索引呢?
PRIMARY KEY (f_user_id), INDEX f_user_id (f_user_id), KEY f_id ( f_id )
查资料没找到答案,先留一个坑吧。