Unity3D笔记——MySQL数据库 简单使用
**
Unity3D笔记——MySQL数据库 简单使用
前言：Unity3D笔记是我平时做一些好玩的测试和研究，记录的笔记。会比较详细也可能随口一提就过了。 所以大家见谅了，内容一般都会是原创的（非原创我会注明转载）。由于很多内容其他的朋友也肯定研究发表过，大家请指出错误。
公司需要做一个仿真监测软件，同时需要显示特别大的数据统计。表格，统计图 把我搞得头昏脑涨的。然后发现单纯的json或者xml的简单数据已经不够我用了，因为数据量真的太大了。（每分钟都会记录几个监测点的温度，湿度，粒子浓度等等）几个月下来就是海量的数据了。于是，就想到了数据库~
想了两种数据库：mysql还有sqlite。都研究了下 先捣鼓Mysql吧
一.准备工作
当然显示搭建数据库啦；
这是Mysql的安装地址：https://dev.mysql.com/downloads/mysql/
然后安装一个 Navicat，方便处理数据库，也可以用来验证SQL语句。
二.unity需要的dll
unity的安装目录找一下这些DLL 放入Plugins文件夹中（找不到网上搜下 估计也能下载）
三.MySql代码
创建一个用于链接Mysql的单位脚本，所有的链接 查询都是基于该单位脚本。可以实例化多个，分别链接不同的db；
using MySql.Data.MySqlClient;
using System;
using System.Data;
public class MySqlUnit
{
protected MySqlConnection mySqlConnection;
//IP地址
protected string _host;
//端口号
protected string _port;
//用户名
protected string _userName;
//密码
protected string _password;
//数据库名称
protected string _databaseName;
/// <summary>
/// 构造方法
/// </summary>
/// <param name="_host">ip地址</param>
/// <param name="_userName">用户名</param>
/// <param name="_password">密码</param>
/// <param name="_databaseName">数据库名称</param>
public MySqlUnit(string _host, string _port, string _userName, string _password, string _databaseName)
{
this._host = _host;
this._port = _port;
this._userName = _userName;
this._password = _password;
this._databaseName = _databaseName;
OpenSQL();
}
public void OpenSQL()
{
try
{
string mySqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};port={4};CharSet=utf8", _databaseName, _host, _userName, _password, _port);
mySqlConnection = new MySqlConnection(mySqlString);
mySqlConnection.Open();
}
catch (Exception e)
{
throw new Exception( e.Message.ToString());
}
}
/// <summary>
/// 关闭数据库
/// </summary>
public void CloseSQL()
{
if (mySqlConnection != null)
{
mySqlConnection.Close();
mySqlConnection.Dispose();
mySqlConnection = null;
}
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sqlString">sql语句</param>
/// <returns></returns>
public DataSet ExecuteString(string sqlString)
{
if (mySqlConnection.State == ConnectionState.Open)
{
DataSet ds = new DataSet();
try
{
MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter(sqlString, mySqlConnection);
mySqlAdapter.Fill(ds);
}
catch (Exception e)
{
throw new Exception("SQL:" + sqlString + "/n" + e.Message.ToString());
}
return ds;
}
return null;
}
}
四 SQLmanger——具体怎么增删改查
先创建连接，IP地址和端口 我是放在ini配置表里了，这里看情况自己定。结束的时候记得关闭连接
private string iniPath = "";
private MySqlUnit mySqlUnit;
private void Awake()
{
_instance = this;
iniPath = System.IO.Directory.GetCurrentDirectory()+ "/MySQL.ini";
IniFiles iniFiles = new IniFiles(iniPath);
string HostIP = iniFiles.IniReadValue("Server", "HostIP");
string Port = iniFiles.IniReadValue("Server", "Port");
string DBName = iniFiles.IniReadValue("Server", "DBName");
string UserName = iniFiles.IniReadValue("Server", "UserName");
string Password = iniFiles.IniReadValue("Server", "Password");
mySqlUnit = new MySqlUnit(HostIP, Port, UserName, Password, DBName);
}
private void OnDestroy()
{
mySqlUnit.CloseSQL();
}
查询一个表格：比如想要查24小时的温度统计：
Dictionary<string, float> QueryWetHour(DateTime startTime, DateTime endTime, string cleanName)
{
int startTime_Stamp = DateTimeToStamp(startTime);
int endTime_Stamp = DateTimeToStamp(endTime);
string SQLstring = "";
//如果在同一个月
if (startTime.Year == endTime.Year && startTime.Month == endTime.Month)
{
string cTableName = TABLE_HEADER_WET + "tbl" + startTime.Year + startTime.Month; //表名字
SQLstring = string.Format("select b.nHour, b.hours, b.nAvg, b.nMax, b.nMin from (SELECT TIMESTAMPDIFF(hour,FROM_UNIXTIME(wet_time),FROM_UNIXTIME(unix_timestamp(NOW()))) as nHour, HOUR(FROM_UNIXTIME(wet_time)) hours, avg(wet_value) as nAvg, max(wet_value) as nMax, min(wet_value) as nMin FROM {0} where wet_time BETWEEN {1} AND {2} AND clean_area='{3}' group by nHour, hours order by nHour desc) b ;", cTableName, startTime_Stamp, endTime_Stamp, cleanName);
}
else //如果不在同一个月
{
string cTableName1 = TABLE_HEADER_WET + "tbl" + startTime.Year + startTime.Month; //表名字1
string cTableName2 = TABLE_HEADER_WET + "tbl" + endTime.Year + endTime.Month; //表名字2
string sqlstring1 = string.Format("select a.nHour, a.hours, a.nAvg, a.nMax, a.nMin from (SELECT TIMESTAMPDIFF(hour,FROM_UNIXTIME(wet_time),FROM_UNIXTIME(unix_timestamp(NOW()))) as nHour, HOUR(FROM_UNIXTIME(wet_time)) hours, avg(wet_value) as nAvg, max(wet_value) as nMax, min(wet_value) as nMin FROM {0} where wet_time BETWEEN {1} AND {2} AND clean_area='{3}' group by nHour, hours order by nHour desc) a union ", cTableName1, startTime_Stamp, endTime_Stamp, cleanName);
string sqlstring2 = string.Format("select b.nHour, b.hours, b.nAvg, b.nMax, b.nMin from (SELECT TIMESTAMPDIFF(hour,FROM_UNIXTIME(wet_time),FROM_UNIXTIME(unix_timestamp(NOW()))) as nHour, HOUR(FROM_UNIXTIME(wet_time)) hours, avg(wet_value) as nAvg, max(wet_value) as nMax, min(wet_value) as nMin FROM {0} where wet_time BETWEEN {1} AND {2} AND clean_area='{3}' group by nHour, hours order by nHour desc) b ;", cTableName2, startTime_Stamp, endTime_Stamp, cleanName);
SQLstring = sqlstring1 + sqlstring2;
}
DataSet dataSet2 = mySqlUnit.ExecuteString(SQLstring);
DataTable table_wetDays = dataSet2.Tables[0];
if (table_wetDays.Rows.Count > 0)
{
Dictionary<string, float> dicWetHour = new Dictionary<string, float>();
for (int i = 0; i < table_wetDays.Rows.Count; i++)
{
int hours = int.Parse(table_wetDays.Rows[i][0].ToString());
float avg = float.Parse(table_wetDays.Rows[i][2].ToString());
DateTime dateTime = DateTime.Now.AddHours(-hours);
string key = string.Format("{0}/{1} {2}:00", dateTime.Month, dateTime.Day, dateTime.Hour);
dicWetHour.Add(key, avg);
}
return dicWetHour;
}
return null;
}
这里代码其实不重要，主要还是SQL语句。 DataSet dataSet2 = mySqlUnit.ExecuteString(SQLstring); 自定义SQL语句，执行后 返回 DataSet 类型实例。表格结果就在里面，然后操作就可以啦。
SQL语句 可以先在Navicat里先试验下，看看数据格式。
我这里是查询固定 时间戳里温度，然后找出最高温度，最低温度，平均温度。并且根据距离当前时间的长度 排序~~
五.结果和小结
查到的结果，我是返回了 字典。然后用于显示：
小结：unity对于mysql支持是很好的。相应的dll都有，网上的教程也很多。具体的执行却要自己写SQL语句了，增删改查。我这边只做了查的功能，其他的功能也是用SQL语句自己自定义的。当然，也可以自己写下 通用的执行语句，用来嵌套。
这次的源码，就不放网盘了。因为是公司的项目，我自己其实没有打草稿 直接写的。