mysql的封装
MySQLTool
MySQLTool主要是封装了mysql的初始化连接、查询、执行
bool MySQLTool::connect(const string& host, const string& user, const string& passwd, const string& db, unsigned port)
connect函数就是连接,使用mysql_init来初始化数据库,使用mysql_real_connect来完成连接
QueryResultPtr MySQLTool::Query(const string& sql)
Query就是负责调用查询指令sql
mysql_real_query(m_mysql, sql.c_str(), sql.size());
MYSQL_RES* result = mysql_store_result(m_mysql); uint32_t rowcount = mysql_affected_rows(m_mysql); uint32_t cloumncount = mysql_field_count(m_mysql); cout << __FILE__ << "(" << __LINE__ << ")\r\n"; return QueryResultPtr(new QueryResult(result, rowcount, cloumncount));
mysql数据库查询后的结果是可以通过调用mysql_store_result来获得结果并存到MYSQL_RES,然后获得数据库
行和列的个数,并把结果都存放到QueryResult里
bool MySQLTool::Execute(const string& sql, uint32_t& nAffectedCount, int& nErrno)
使用mysql_query来执行sql语句
Field
Field主要包括以下几个重要的成员变量
string m_value; string m_name; DataTypes m_type; bool m_isnull;
QueryResult
用来存查询结果
成员变量
查询结果是一行一行的,所以有当前行,还有NextRow()调用下一行
所谓的Field就是查询表中一块一块的,例如10*10的表,Field就有100块
vector<Field> m_CurrentRow; //当前行 vector<string> m_vecFieldName; //每一块对应的名字 MYSQL_RES* m_result; //数据库的查询结果 uint32_t m_rowcount; //行数 uint32_t m_cloumncout; //列数
QueryResult的初始化
m_CurrentRow.resize(m_cloumncout); m_vecFieldName.resize(m_cloumncout); MYSQL_FIELD* fields = mysql_fetch_fields(m_result); MYSQL_ROW row = mysql_fetch_row(m_result); for (uint32_t i = 0; i < m_cloumncout; i++) { m_vecFieldName[i] = fields[i].name; m_CurrentRow[i].SetType(toEDYType(fields[i].type)); m_CurrentRow[i].SetName(m_vecFieldName[i]); if (row[i] == NULL) { m_CurrentRow[i].SetValue(NULL, 0); } else { //cout << __LINE__ << ")QueryResult: " << row[i] << " length:" << pFieldLength[i] << endl; m_CurrentRow[i].SetValue(row[i], pFieldLength[i]); } }
bool QueryResult::NextRow() bool QueryResult::NextRow() { if (m_result == NULL)return false; MYSQL_ROW row = mysql_fetch_row(m_result); //获取下一行 if (row == NULL) { EndQuery(); return false; } unsigned long* pFieldLength = mysql_fetch_lengths(m_result); //再把下一行的信息填入到m_CurrentRow for (uint32_t i = 0; i < m_cloumncout; i++) { if (row[i] == NULL) { m_CurrentRow[i].SetValue(NULL, 0); } else { m_CurrentRow[i].SetValue(row[i], pFieldLength[i]); } } return true; }
MySqlManager
fieldinfo是块的信息
TableInfo是表的信息
typedef struct fieldinfo{ fieldinfo() = default; fieldinfo(const string& name,const string& tp,const string& desc){ sName = name; sType = tp; sDesc = desc; } string sName; string sType; string sDesc; }sFieldInfo; typedef struct { string sName; map<string, sFieldInfo> mapField; string sKey; }sTableInfo;
map<string, sTableInfo> m_mapTable; shared_ptr<MySQLTool> m_mysql;
使用TableInfo创建表的信息,然后在表的信息里填入Field信息。然后再往mapTable里植入table的信息
//用户 sTableInfo info; info.sName = "t_user"; info.mapField["f_id"] = { "f_id","bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增ID'","bigint(20)" }; info.mapField["f_user_id"] = { "f_user_id", "bigint(20) NOT NULL COMMENT '用户ID'", "bigint(20)" }; info.mapField["f_username"] = { "f_username", "varchar(64) NOT NULL COMMENT '用户名'", "varchar(64)" }; info.mapField["f_nickname"] = { "f_nickname", "varchar(64) NOT NULL COMMENT '用户昵称'", "varchar(64)" }; m_mapTable.insert(TablePair(info.sName, info));
植入的表有:用户信息、聊天内容、用户关系
下面是创建表、检查表
bool CheckDatabase(); bool CheckTable(const sTableInfo& info); bool CreateDatabase(); bool CreateTable(const sTableInfo& info); bool UpdateTable(const sTableInfo& info);
CreateDatabse使用sql << "create database " << m_mysql->GetDBName() << ";"
m_mysql->Execute来执行
CreateTable使用sql << "create table if not exists " << info.sName << "(",
然后把表信息info里的filed信息全部填入
for (; it != info.mapField.end(); it++) { if (it != info.mapField.begin()) { sql << ","; } sFieldInfo field = it->second; sql << field.sName << " " << field.sType; } if (info.sKey.size() > 0) { sql << "," << info.sKey; } sql << ") default charset=utf-8,ENGINE=InnoDB;";
checkDatabase,就是调用m_mysql->Query("show databases;")
然后把查询出来的结果一行一行进行匹配,匹配名字和m_mysql->GetDBName()是否相同
CheckTable。检查表,如果表不存在,那么就创建。如果表存在,看一下表的类型是否相同,如果有缺少的,那么就添加上去。