C++项目 | 集群聊天服务器 | Mysql

1.MySQL环境安装设置

ubuntu环境安装mysql-server和mysql开发包，包括mysql头文件和动态库文件，命令如下：

sudo apt-get install mysql-server  =》 安装最新版MySQL服务器
sudo apt-get install libmysqlclient-dev =》 安装开发包

ubuntu默认安装最新的mysql，但是初始的用户名和密码是自动生成的，按下面步骤修改mysql的root用

户密码为123456

【step 1 】tony@tony-virtual-machine:~$ sudo cat /etc/mysql/debian.cnf

[client]
host = localhost
user = debian-sys-maint 《============== 初始的用户名
password = Kk3TbShbFNvjvhpM 《=============== 初始的密码
socket = /var/run/mysqld/mysqld.sock

【step 2 】用上面初始的用户名和密码，登录mysql server，修改root用户的密码，命令如下：

tony@tony-virtual-machine:~$ mysql -u debian-sys-maint -pKk3TbShbFNvjvhpM

命令解释： -u后面是上面查看的用户名 -p后面紧跟上面查看的密码

mysql> update mysql.user set authentication_string=password('123456') where
user='root' and host='localhost';

mysql> update mysql.user set plugin="mysql_native_password";

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

【step 3】重新用root和123456登录mysql-server

tony@tony-virtual-machine:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5 .7.26-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000 , 2019 , Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


【step 4】设置MySQL字符编码utf-8，可以支持中文操作
mysql> show variables like "char%";  # 先查看MySQL默认的字符编码
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 | 《============不支持中
文！！！
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.06 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)

修改表的字符编码：alter table user default character set utf8;

修改属性的字符编码：alter table user modify column name varchar(50) character set utf8;

安装好以后的结果图

2.数据库设计

create database chat;
show databases;

数据库创建完毕

表设计

User表

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(50) NOT NULL,
    state ENUM('online', 'offline') DEFAULT 'offline'
);

Friend表

CREATE TABLE friend (
    userid INT NOT NULL,
    friendid INT NOT NULL,
    PRIMARY KEY (userid, friendid)
);

AllGroup表

CREATE TABLE allGroup (
    id INT PRIMARY KEY AUTO_INCREMENT,
    groupname VARCHAR(50) NOT NULL UNIQUE,
    groupdesc VARCHAR(200) DEFAULT ''
);

GroupUser表

CREATE TABLE groupUser (
    groupid INT NOT NULL,
    userid INT NOT NULL,
    grouprole ENUM('creator', 'normal') DEFAULT 'normal',
    PRIMARY KEY (groupid, userid)
);

OfflineMessage表

-- 创建OfflineMessage表
CREATE TABLE offlineMessage (
    userid INT NOT NULL,
    message VARCHAR(500) NOT NULL
);

3.MySQL数据库编程

// 数据库配置信息
static string server = "127.0.0.1";
static string user = "root";
static string password = "123456";
static string dbname = "chat";

// 数据库操作类
class MySQL
{
public:
    // 初始化数据库连接
    MySQL()
    {
        _conn = mysql_init(nullptr);
    }
    // 释放数据库连接资源
    ~MySQL()
    {
        if (_conn != nullptr)
            mysql_close(_conn);
    }
    // 连接数据库
    bool connect()
    {
        MYSQL *p = mysql_real_connect(_conn, server.c_str(), user.c_str(),
                                      password.c_str(), dbname.c_str(), 3306, nullptr, 0);
        if (p != nullptr)
        {
            //支持中文
            mysql_query(_conn, "set names gbk");
        }
        return p;
    }
    // 更新操作
    bool update(string sql)
    {
        if (mysql_query(_conn, sql.c_str()))
        {
            LOG_INFO << __FILE__ << ":" << __LINE__ << ":"
                     << sql << "更新失败!";
            return false;
        }
        return true;
    }
    // 查询操作
    MYSQL_RES *query(string sql)
    {
        if (mysql_query(_conn, sql.c_str()))
        {
            LOG_INFO << __FILE__ << ":" << __LINE__ << ":"
                     << sql << "查询失败!";
            return nullptr;
        }
        return mysql_use_result(_conn);
    }

private:
    MYSQL *_conn;
};

这里用UserModel示例，通过UserModel如何对业务层封装底层数据库的操作。代码示例如下：

class UserModel
{
public:
    // 重写add接口方法，实现增加用户操作
    bool add(UserDO &user)
    {
        // 组织sql语句
        char sql[1024] = {0};
		sprintf(sql, "insert into user(name,password,state) values('%s', '%s',
'%s')",
            user.getName().c_str(),
            user.getPwd().c_str(),
            user.getState().c_str());
		MySQL mysql;
        if (mysql.connect())
        {
            if (mysql.update(sql))
            {
                LOG_INFO << "add User success => sql:" << sql;
                return true;
            }
        }
        LOG_INFO << "add User error => sql:" << sql;
        return false;
    }
};