搭建Mysql，实现远程连接的增删改查

1.安装mysql及配置

       下载地址:https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-workbench-community-8.0.27-winx64.msi

  安装完成以后，如果其它支持其它电脑远程连接，需要修改如下所示

2.QT实现远程的增删改查

#ifndef _MANAGER_H_
#define _MANAGER_H_

#include <QObject>
#include <QSqlDatabase>

//1字节对齐
#pragma pack(push, 1)
 typedef struct
{
	 char did[64];
	 char key[128];
	 char mac[128];
	 char model[128];
}sys_info_t;
#pragma pack(pop)

//发送信号的时候可以用这个结构体
Q_DECLARE_METATYPE(sys_info_t)

class Manager : public QObject
{
	Q_OBJECT

public:
	
	static Manager *GetInstance();

	int ExistDatabaseName(QString sIP, int nPort, QString dataname, QString user, QString pwd);

	int ConnectDatabase();

	int CloseDatabase();

	int StartTransaction();

	int CommitTransaction();

	int RollBack();

	int InsertRowData(QString sDid, QString sKey, QString sMac, QString sModel);

	int FindDataByDid(QString sDid, QString &sKey, QString &sMac, QString &sModel);

	int FindDataByDid(QString sDid, sys_info_t *pInfo);

	int DeleteDataByDid(QString sDid);

	int UpdateDataByDid(QString sDid, sys_info_t *pInfo);
	
	QSqlDatabase* GetSqlDatabase();

	int CreateDatabaseInfo(QString dataname, QString tablename);

	void SetTableName(QString tablename);

	~Manager();
private:
	
	Manager();

	QSqlDatabase m_database;

	static Manager *m_instance;

	QString m_szIP;
	QString m_szUsr;
	QString m_szPwd;
	QString m_szDataName;
	QString m_szTableName;
};

#endif

#include "Manager.h"
#include <QMessageBox>
#include <QDebug>
#include <string.h>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDriver>
Manager::Manager()
{

}

Manager::~Manager()
{
	CloseDatabase();
}

Manager *Manager::m_instance = nullptr;
Manager *Manager::GetInstance()
{
	if (!m_instance)
	{
		m_instance = new Manager;
	}
	return m_instance;
}

int Manager::InsertRowData(QString sDid, QString sKey, QString sMac, QString sModel)
{
	QSqlQuery query(m_database);
	QString sql = QString("insert into `%1`(`did`,`key`,`mac`,`model`) values('%2','%3','%4','%5')").arg(m_szTableName)
		.arg(sDid).arg(sKey).arg(sMac).arg(sMac);

	bool bsuccess = query.exec(sql);
	return bsuccess;
}

int Manager::StartTransaction()
{
	//mysql支持事务，但qt有时候开启事务失败，可以用sql语句开启
	if (m_database.transaction())
	{
		return 0;
	}
	QSqlQuery query(m_database);
	QString sql = QString("START TRANSACTION");

	bool bsuccess = query.exec(sql);
	return bsuccess;
}

int Manager::CommitTransaction()
{
	if (m_database.commit())
	{
		return 0;
	}
	QSqlQuery query(m_database);
	QString sql = QString("COMMIT");

	bool bsuccess = query.exec(sql);
	return bsuccess;

}
//回退
int Manager::RollBack()
{
	if (m_database.rollback())
	{
		return 0;
	}
	QSqlQuery query(m_database);
	QString sql = QString("ROLLBACK");

	bool bsuccess = query.exec(sql);
	return bsuccess;
}

int Manager::CloseDatabase()
{
	m_database.close();
	return 0;
}

int Manager::CreateDatabaseInfo(QString dataname, QString tablename)
{
	m_database = QSqlDatabase::addDatabase("QMYSQL");
	m_database.setHostName(m_szIP); //数据库服务器IP
	m_database.setPort(3306);
	m_database.setUserName(m_szUsr);//用户名
	m_database.setPassword(m_szPwd);//密码
	m_szTableName = tablename;
	if (!m_database.open())
	{
		qDebug() << "CreateDataName:" << m_database.lastError().text();
		return -1;
	}
	QSqlQuery query(m_database);
	QString sql = QString("CREATE SCHEMA `%1`;").arg(dataname);
	bool isOk = query.exec(sql);
	if (!isOk)
	{
		return -1;
	}

	sql = QString("CREATE TABLE `%1`.`%2` (		\
		`did` VARCHAR(64) NOT NULL,		\
		`key` VARCHAR(128) NULL,	\
		`mac` VARCHAR(128) NULL,	\
		`model` VARCHAR(128) NULL,	\
		PRIMARY KEY(`did`)); ").arg(dataname).arg(tablename);

	isOk = query.exec(sql);
	if (!isOk)
	{
		return -1;
	}
	//事务支持(mysql默认支持)
	sql = QString("ALTER TABLE `%1`.`%2` ENGINE = INNODB;").arg(dataname).arg(tablename);
	isOk = query.exec(sql);
	if (!isOk)
	{
		qDebug() << "----------";
	}
	m_database.close();


	return 0;
}

void Manager::SetTableName(QString tablename)
{
	m_szTableName = tablename;
}


QSqlDatabase* Manager::GetSqlDatabase()
{
	return &m_database;
}

int Manager::ExistDatabaseName(QString sIP, int nPort, QString dataname, QString user, QString pwd)
{
	m_szIP = sIP;
	m_szUsr = user;
	m_szDataName = dataname;
	m_szPwd = pwd;

	m_database = QSqlDatabase::addDatabase("QMYSQL");
	m_database.setHostName(sIP); //数据库服务器IP
	m_database.setPort(nPort);
	m_database.setUserName(user);//用户名
	m_database.setPassword(pwd);//密码
	m_database.setDatabaseName(dataname);//使用的数据库

	if (!m_database.open())
	{
		qDebug() << m_database.lastError().text();
		return -1;
	}

	m_database.close();
	return 0;
}

int Manager::ConnectDatabase()
{
	m_database = QSqlDatabase::addDatabase("QMYSQL");
	m_database.setHostName(m_szIP); //数据库服务器IP
	m_database.setPort(3306);
	m_database.setUserName(m_szUsr);//用户名
	m_database.setPassword(m_szPwd);//密码
	m_database.setDatabaseName(m_szDataName);//使用的数据库

	if (!m_database.open())
	{
		qDebug() << m_database.lastError().text();
		return -1;
	}
	return 0;
}


int Manager::FindDataByDid(QString sDid, QString &sKey, QString &sMac, QString &sModel)
{
	if (sKey.length() == 0)
	{
		return -1;
	}
	QSqlQuery query(m_database);
	QString sql = QString("SELECT * FROM `%1` where `did` = \"%2\"").arg(m_szTableName).arg(sDid);
	if (!query.exec(sql))
	{
		return -1;
	}
	while (query.next())
	{
		sDid = query.value(0).toString();
		sKey = query.value(1).toString();
		sMac = query.value(2).toString();
		sModel = query.value(3).toString();
		return 0;
	}
	return -1;
}

int Manager::FindDataByDid(QString sDid, sys_info_t *pInfo)
{
	if (sDid.length() == 0 || nullptr == pInfo)
	{
		return -1;
	}
	QSqlQuery query(m_database);
	QString sql = QString("SELECT * FROM `%1` where `did` = \"%2\"").arg(m_szTableName).arg(sDid);
	if (!query.exec(sql))
	{
		return -1;
	}
	while (query.next())
	{
		QString sDid = query.value(0).toString();
		QString sKey = query.value(1).toString();
		QString sMac = query.value(2).toString();
		QString sModel = query.value(3).toString();
		memcpy(pInfo->did, sDid.toStdString().c_str(), sDid.length());
		memcpy(pInfo->key, sKey.toStdString().c_str(), sKey.length());
		memcpy(pInfo->mac, sMac.toStdString().c_str(), sMac.length());
		memcpy(pInfo->model, sModel.toStdString().c_str(), sModel.length());

		return 0;
	}
	return -1;
}


int Manager::DeleteDataByDid(QString sDid)
{
	QSqlQuery query(m_database);
	QString sql = QString("DELETE FROM `%1` where `did` = \"%2\"").arg(m_szTableName).arg(sDid);
	if (!query.exec(sql))
	{
		return -1;
	}
	return 0;
}

int Manager::UpdateDataByDid(QString sDid, sys_info_t *pInfo)
{
	if (sDid.length() == 0 || !pInfo)
	{
		return -1;
	}
	//update mytest.deviceinfo set model  = '11'  where did = '320200848';
	QString sql(QString("UPDATE `%1` set ").arg(m_szTableName));
	if (strlen(pInfo->key) > 0)
	{
		sql.append(QString("`key`='%1',").arg(pInfo->key));
	}
	if (strlen(pInfo->mac) > 0)
	{
		sql.append(QString("`mac`='%1',").arg(pInfo->mac));
	}
	if (strlen(pInfo->model) > 0)
	{
		sql.append(QString("`model`='%1',").arg(pInfo->model));
	}
	sql = sql.left(sql.length() - 1);
	sql.append(QString(" where `did` = \"%1\"").arg(sDid));

	QSqlQuery query(m_database);
	if (!query.exec(sql))
	{
		return -1;
	}
	return 0;
}

 调用流程部分代码如下：

int nRet = Manager::GetInstance()->ExistDatabaseName(sIP, 3306, sDataName, sUsr, sPwd);
	if (nRet == -1)
	{
		qDebug() << "ExistDatabaseName error";
		nRet = Manager::GetInstance()->CreateDatabaseInfo(sDataName, sTableName);	
	}
	if (nRet == -1)
	{
		return;
	}
	 Manager::GetInstance()->SetTableName(sTableName);

	nRet = Manager::GetInstance()->ConnectDatabase();

       连接的过程可能打开数据库失败，可能缺少如下库