一、获取两个动态连接库:MySql.Data.dll和System.Data.dll;
(一)MySql.Data.dll
一般从MySql的安装位置获取(C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2.MySql.Data.dll),然而由于本人安装的MySQL8.0版本,所以一导入Unity就报错了,这里提供一个旧版MySql.Data.dll供大家使用:
链接:https://pan.baidu.com/s/1oXanUONd2vFigWlUcPlMJw
提取码:7lm8
另:为避免后续代码、打包发生错误,请务必确认Unity的PlayerSettings参数选择如下:
(二)System.Data.dll
从安装Unity的地方(D:\Program Files\Unity2017.3.0f3\Unity2017.3.0f3\Editor\Data\Mono\lib\mono\2.0.System.Data.dll)获取;
二、在Unity的Assets文件夹下,创建Plugins文件夹,将上面获取到的两个dll拖拽到Plugins文件夹里面
三、MySQL管理类:MySqlManager.cs
using MySql.Data.MySqlClient;
using System;
using System.Data;
using UnityEngine;
public class MySQLManager
{
public static MySqlConnection SqlConnection;//连接类对象
private static string host; //IP地址。如果只是在本地的话,写localhost就可以。
private static string id; //用户名。
private static string pwd; //密码。
private static string dataBase; //数据库名称。
/// <summary>
/// 构造方法
/// </summary>
/// <param name="_host">IP地址</param>
/// <param name="_id">用户名</param>
/// <param name="_pwd">密码</param>
/// <param name="_dataBase">数据库名称</param>
public MySQLManager(string _host, string _id, string _pwd, string _dataBase)
{
host = _host;
id = _id;
pwd = _pwd;
dataBase = _dataBase;
OpenSql();
}
#region mysql管理类(使用部分)
//查询某表中0、1、2列所有数据
public void Read(string command)
{
//跟MySQL建立连接
string SqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(SqlString);
try
{
conn.Open();
string sql = command;
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
//cmd.ExecuteReader();//执行一些查询
//cmd.ExecuteScalar();//执行一些查询,返回一个单个的值
MySqlDataReader reader = cmd.ExecuteReader();//读出流
//reader.Read();//打开第一个数据(如果读取成功,返回true; 如果没有下一页数据,则读取失败,返回false)
//Debug.Log(reader[0].ToString() + reader[1].ToString() + reader[2].ToString());
//reader.Read();//打开第二个数据
//Debug.Log(reader[0].ToString() + reader[1].ToString() + reader[2].ToString());
while (reader.Read())//打开所有的数据
{
//Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString());
Debug.Log(reader.GetInt32(0) + " " + reader.GetString(1) + " " + reader.GetString(2) + " " + reader.GetString(3));
//Debug.Log(reader.GetInt32("id") + " " + reader.GetString("username") + " " + reader.GetString("password"));
}
Debug.Log("已经成功读取到数据!");
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
}
//插入
public void Insert(string command)
{
//跟MySQL建立连接
string connectStr = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
//string sql = "insert into users(username,password) values('caixukun','234')";
//string sql = "insert into users(username,password,registerdate) values('小猪猪','23','2020-09-14')";//插入指定日期
string sql = command;
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数
Debug.Log("已经成功插入一条数据!");
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
}
//按时间升序
public void AscSort(string command)
{
//跟MySQL建立连接
string connectStr = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
string sql = command;
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数
Debug.Log("数据升序完成!");
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
}
//更新一条指定数据
public void Update(string command)
{
//跟MySQL建立连接
string connectStr = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
string sql = command;
Debug.Log(sql);
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数
Debug.Log("已经成功更新一条数据!");
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
}
//更新id,让ID变得从1开始连续
public void UpdateID(string command)
{
//跟MySQL建立连接
string connectStr = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8;Allow User Variables=True;", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
string sql = command;
//Debug.Log(sql);
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数
Debug.Log("ID已经更新完毕!");
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
}
/// <summary>
/// 删除表中的一条指定id数据
/// </summary>
/// <param name="command">写入mysql命令行</param>
public void DeleteOne(string command)
{
//跟MySQL建立连接
string connectStr = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
string sql = command;
Debug.Log(sql);
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数
Debug.Log("已经成功删除一条数据!");
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
}
//删除表中所有数据
public void DeleteTable(string command)
{
string connectStr = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
string sql = command;
Debug.Log(sql);
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
int result = cmd.ExecuteNonQuery();//会返回受影响的行数
Debug.Log("已经删除指定表中的所有数据!");
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
}
//查询表中数据的总行数
public void ExcuteScalar(string command)
{
//跟MySQL建立连接
string connectStr = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
string sql = command;
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
object o = cmd.ExecuteScalar();
int count = Convert.ToInt32(o.ToString());
Debug.Log(count);
Debug.Log("已经查询到表中数据总行数!");
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
}
//验证用户名
public bool VerifyUser(string username,string command)
{
//跟MySQL建立连接
string connectStr = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
string sql = command;
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
cmd.Parameters.AddWithValue("username", username);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
return true;
}
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
return false;
}
//验证用户名和密码
public bool Verify(string username, string password,string command)
{
//跟MySQL建立连接
string connectStr = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
string sql = command;
MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
cmd.Parameters.AddWithValue("username", username);
cmd.Parameters.AddWithValue("password", password);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
return true;
}
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn.Close();
}
return false;
}
#endregion
#region mysql管理类(未使用部分)
/// <summary>
/// 打开数据库
/// </summary>
public void OpenSql()
{
try
{
string SqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8", dataBase, host, id, pwd, "3306");
SqlConnection = new MySqlConnection(SqlString);
SqlConnection.Open();
Debug.Log("打开数据库");
}
catch (Exception e)
{
Debug.Log("服务器连接失败,请重新检查是否打开MySql服务。" + e.Message.ToString());
//InputIP.ReadIP();
}
}
/// <summary>
/// 通过SQL语句查询是否数据库存在某表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool ExitTable(string sql)
{
OpenSql();
MySqlCommand cmd = new MySqlCommand(sql, SqlConnection);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows == false)
{
Debug.Log("该表不存在");
Close();
return false;
}
else
{
Debug.Log("表存在");
Close();
return true;
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="name">表名</param>
/// <param name="colName">属性列</param>
/// <param name="colType">属性类型</param>
/// <returns></returns>
public DataSet CreateTable(string name, string[] colName, string[] colType)
{
if (colName.Length != colType.Length)
{
Debug.Log("输入不正确:" + "columns.Length != colType.Length");
}
string query = "CREATE TABLE " + name + "(" + colName[0] + " " + colType[0];
for (int i = 1; i < colName.Length; i++)
{
query += "," + colName[i] + " " + colType[i];
}
query += ")";
return QuerySet(query);
}
/// <summary>
/// 创建具有id自增的表
/// </summary>
/// <param name="name">表名</param>
/// <param name="col">属性列</param>
/// <param name="colType">属性列类型</param>
/// <returns></returns>
public bool CreateTableAutoID(string name, string[] col, string[] colType)
{
if (col.Length != colType.Length)
{
//throw new Exception("columns.Length != colType.Length");
Debug.Log("输入不正确:" + "columns.Length != colType.Length");
}
string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0] + " NOT NULL AUTO_INCREMENT";
for (int i = 1; i < col.Length; ++i)
{
query += ", " + col[i] + " " + colType[i];
}
query += ", PRIMARY KEY (" + col[0] + ")" + ")";
if (ExecuteSqlCmd(query))
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 插入部分ID
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="col">属性列</param>
/// <param name="values">属性值</param>
/// <returns></returns>
public DataSet InsertInto(string tableName, string[] col, string[] values)
{
if (col.Length != values.Length)
{
// throw new Exception("columns.Length != colType.Length");
Debug.Log("输入不正确:" + "columns.Length != colType.Length");
}
string query = "INSERT INTO " + tableName + " (" + col[0];
for (int i = 1; i < col.Length; ++i)
{
query += ", " + col[i];
}
query += ") VALUES (" + "'" + values[0] + "'";
for (int i = 1; i < values.Length; ++i)
{
query += ", " + "'" + values[i] + "'";
}
query += ")";
return QuerySet(query);
}
/// <summary>
/// 查询表数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="items">需要查询的列</param>
/// <param name="whereColName">查询的条件列</param>
/// <param name="operation">条件操作符</param>
/// <param name="value">条件的值</param>
/// <returns></returns>
public DataSet Select(string tableName, string[] items, string[] whereColName, string[] operation, string[] value)
{
if (whereColName.Length != operation.Length || operation.Length != value.Length)
{
// throw new Exception("输入不正确:" + "col.Length != operation.Length != values.Length");
Debug.Log("输入不正确:" + "col.Length != operation.Length != values.Length");
}
string query = "SELECT " + items[0];
for (int i = 1; i < items.Length; i++)
{
query += "," + items[i];
}
query += " FROM " + tableName + " WHERE " + " " + whereColName[0] + operation[0] + " '" + value[0] + "'";
for (int i = 1; i < whereColName.Length; i++)
{
query += " AND " + whereColName[i] + operation[i] + "' " + value[i] + "'";
}
return QuerySet(query);
}
/// <summary>
/// 读取数据 读取数据库指定表中的指定数据
/// </summary>
/// <param name="column_name">所要查询数据的列名称或以*代替</param>
/// <param name="index">列索引号</param>
/// <param name="table_name">表名称</param>
/// <param name="ConditonVar">依据条件列名称</param>>
/// <param name="Condtionvalue">依据条件列的值 </param>>
/// <returns></returns>
public string GetValueByRead(string column_name, int index, string table_name, string ConditonVar, string Condtionvalue)
{
// OpenSql();
string _value = "";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "select " + column_name + " from " + table_name + " where " + ConditonVar + " = '" + Condtionvalue + "'";
cmd.Connection = SqlConnection;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
_value = reader[index].ToString();
}
reader.Close();
// Close();
return _value;
}
/// <summary>
/// 更新表数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="cols">更新列</param>
/// <param name="colsvalues">更新的值</param>
/// <param name="selectkey">条件:列</param>
/// <param name="selectvalue">条件:值</param>
/// <returns></returns>
public DataSet UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue)
{
string query = "UPDATE " + tableName + " SET " + cols[0] + " = " + "'" + colsvalues[0] + "'";
for (int i = 1; i < colsvalues.Length; ++i)
{
query += ", " + cols[i] + " =" + "'" + colsvalues[i] + "'";
}
query += " WHERE " + selectkey + " = " + "'" + selectvalue + "'" + " ";
return QuerySet(query);
}
/// <summary>
/// 删除表数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="cols">条件:删除列</param>
/// <param name="colsvalues">删除该列属性值所在得行</param>
/// <returns></returns>
public DataSet Delete(string tableName, string[] cols, string[] colsvalues)
{
string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsvalues[0];
for (int i = 1; i < colsvalues.Length; ++i)
{
query += " or " + cols[i] + " = " + colsvalues[i];
}
return QuerySet(query);
}
/// <summary>
/// 按照表名删除数据库中的表
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public bool DeleteTable1(string tableName)
{
string query = "DROP TABLE " + tableName;
return ExecuteSqlCmd(query);
}
/// <summary>
/// 释放
/// </summary>
public void Close()
{
if (SqlConnection != null)
{
SqlConnection.Close();
SqlConnection.Dispose();
SqlConnection = null;
}
Debug.Log("关闭数据库");
}
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sqlString">sql语句</param>
/// <returns></returns>
public DataSet QuerySet(string sqlString)
{
OpenSql();
if (SqlConnection.State == ConnectionState.Open)
{
DataSet ds = new DataSet();
try
{
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sqlString, SqlConnection);
mySqlDataAdapter.Fill(ds);
}
catch (Exception e)
{
// throw new Exception("SQL:" + sqlString + "/n" + e.Message.ToString());
Debug.Log("SQL:" + sqlString + "/n" + e.Message.ToString());
}
finally
{
Close();
}
return ds;
}
return null;
}
/// <summary>
/// 通过sql语句返回查询行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int SqlRecordCount(string sql)
{
OpenSql();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = sql;
cmd.Connection = SqlConnection;
MySqlDataReader dr;
dr = cmd.ExecuteReader();
int RecordCount = 0;
while (dr.Read())
{
RecordCount = RecordCount + 1;
}
Close();
return RecordCount;
}
/// <summary>
/// 获取表中指定条件下,某字段的行数
/// </summary>
/// <param name="column_name"></param>
/// <param name="table_name"></param>
/// <param name="condition"></param>
/// <param name="value"></param>
/// <returns></returns>
public int GetDataLineCount(string column_name, string table_name, string condition, string value)
{
int _Num = 0;
OpenSql();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "select " + column_name + " from " + table_name + " where find_in_set(" + value + "," + condition + ")"; //find_in_set(1,position)
Debug.Log(cmd.CommandText);
cmd.Connection = SqlConnection;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
_Num = _Num + 1;
}
reader.Close();
Close();
return _Num;
}
/// <summary>
/// 执行sql语句并返回是否执行完成
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool ExecuteSqlCmd(string sql)
{
Debug.Log(sql);
OpenSql();
if (SqlConnection.State == ConnectionState.Open)
{
MySqlCommand cmd = new MySqlCommand(sql, SqlConnection);
return cmd.ExecuteNonQuery() > 0;
}
Close();
return false;
}
#endregion
}
using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
public class MySqlTest : MonoBehaviour {
[Header("IP地址:")]
public string host; //IP地址。如果只是在本地的话,写localhost就可以。
[Header("用户名:")]
public string id; //用户名。
[Header("密码:")]
public string pwd; //密码。
[Header("数据库名称:")]
public string dataBase; //数据库名称。
void Start()
{
MySQLManager mySQLManager = new MySQLManager(host, id, pwd, dataBase);
//mySQLManager.Read("select * from users"); //读取users表中的数据
//mySQLManager.Insert("insert into users(username,password,registerdate) values('萧然','1314',now())"); //插入users表中一条数据的三个属性值
//mySQLManager.Update("update users set username='蔡徐坤',password='123' where id=10");//更新users表中id=10的数据的两个属性值
//mySQLManager.UpdateID("alter table users drop id; alter table users add id int not null primary key auto_increment first; ");//更新表中所有的id,让ID变得从1开始连续
//mySQLManager.DeleteOne("delete from users where id=2");//删除users表中id=2的所有属性值
//mySQLManager.DeleteTable("delete from users");//删除users表中所有数据
//mySQLManager.ExcuteScalar("select count(*) from users");//查询users表中数据的总行数
//Debug.Log(mySQLManager.Verify("冉世林", "131", "select * from users where username = @username and password = @password"));//验证users表中的两个属性:用户名和密码
}
}
注意:MySql中:插入实时时间的语句:(注意now()的用法 年月日时分秒)
string sql = "insert into users(username,password,registerdate) values('萧然','1314',now())";//插入实时日期
注意:解决auto_increment 编号不连续的问题;
往mysql命令行中输入:
alter table users drop id;
alter table users add id int not null primary key auto_increment first;
注意:让Mysql数据库中的表按照日期时间进行排序:
注意:让Mysql数据库中的表既按照日期时间进行排序的同时,解决主键id不连续的解决办法:
注意:MySqlDataReader的嵌套使用:
DataReader对数据库的读取是通过connection完成,一个connection对象同时只可以对应一个DataReader.所以你需要创建两个或多个connection对象,还有值得一提的是一个数据库连接一次只能打开一个SqlDataReader,如果要打开另一个reader,必须先关闭第一个,否则将会报错:
There is already an open DataReader associated with this Connection which must be closed first.
可供参考的代码如下(当然肯定有更简便的写法,在这里我先偷个懒):
/// <summary>
/// 查看历史记录
/// </summary>
private void OnHistoryClick()
{
GameObject go = Instantiate(prefab_panelHistoryScore, GameObject.Find("BG").transform);
string SqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};charset=utf8;Allow User Variables=True;", dataBase, host, id, pwd, "3306");
MySqlConnection conn01 = new MySqlConnection(SqlString);
string str_username = "";
string str_sceneCreateTime = "";
//第一条sql命令
try
{
conn01.Open();
string sql_users = "select * from users where id=" + txt_ID.text;
MySqlCommand cmd_users = new MySqlCommand(sql_users, conn01);//创建命令
MySqlDataReader reader_users = cmd_users.ExecuteReader();//读出流
reader_users.Read();//打开对应ID的数据
str_username = reader_users.GetString(1);
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn01.Close();
}
//第二条sql命令
try
{
conn01.Open();
string sql_scores01 = "select * from scores where username='" + str_username + "'";
MySqlCommand cmd_scores01 = new MySqlCommand(sql_scores01, conn01);//创建命令
MySqlDataReader reader_scores01 = cmd_scores01.ExecuteReader();//读出流
while (reader_scores01.Read())//打开对应username的数据
{
str_sceneCreateTime = reader_scores01.GetString(1);
txt_SceneCreateTime.text = reader_scores01.GetString(1);
txt_Scene.text = reader_scores01.GetString(2);
txt_peopleSum.text = reader_scores01.GetString(3);
GameObject historyList = Instantiate(prefab_panelHistoryScoreList);
historyList.transform.SetParent(GameObject.Find("Content_History").transform);
//第三条sql命令(嵌套)
MySqlConnection conn02 = new MySqlConnection(SqlString);
try
{
conn02.Open();
string sql_scores02 = "select * from scores where createtime='" + str_sceneCreateTime + "'";
MySqlCommand cmd_scores02 = new MySqlCommand(sql_scores02, conn02);//创建命令
MySqlDataReader reader_scores02 = cmd_scores02.ExecuteReader();//读出流
while (reader_scores02.Read())//打开对应createtime的数据
{
txt_AccountSingle.text = reader_scores02.GetString(4);
txt_ScoreSingle.text = reader_scores02.GetString(5);
GameObject scoreList = Instantiate(prefab_panelHistoryScoreSingle);
scoreList.transform.SetParent(GameObject.Find("Content_Score").transform);
}
GameObject.Find("Content_Score").name = "Content";//改名字是为了:防止所有的账号成绩都添加到最上层的Content_Score列表里
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn02.Close();
}
}
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
finally
{
conn01.Close();
}
}