Unity连接MySql
软件版本
- Untiy:2020.3.15f1c1
- Visual Studio:2019
- MySQL Server:5.7.21
- Connector/ODBC:5.3.9
- Connector/NET:6.10.5
- MySQL for Visual Studio:1.2.7
安装软件
1.首先,已经具有了Unity和MySQL Server。在MySQL Installer中安装Connector/ODBC和Connector/NET。
(1).添加Add
(2).选择需要安装的组件(这里我已经安装了Connector/NET)。
(3).安装完成
2.安装MySQL for Visual Studio。如果你的vs studio是2017版,可以在MySQL Installer中进行安装mysql for visual studio。而我的vs studio是2019版,不能在MySQL Installer中直接安装mysql for visual studio,需要去mysql官网进行下载安装。下载链接。
(1).推荐选择1.2.7版本,具体原因会在后面给出。这两个可以都下载,安装msi格式的文件,保存zip文件,后面会用到。
(2).安装完成
到此为止,一切准备工作结束。
Unity导入dll文件
mysql.data.dll文件位置:解压上一步下载的zip文件,在v45文件夹下。
在Unity软件中,assets目录下创建Plugins文件夹,将mysql.data.dll放在Plugins目录下。使用vs studio打开任意脚本,在引用下,进行查看mysql.data是否导出成功。如下,则导出成功。
编写脚本测试
MySqlAccess.cs
using MySql.Data.MySqlClient;
using System;
using System.Data;
public class MySqlAccess
{
public static MySqlConnection mySqlConnection;//连接类对象
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 MySqlAccess(string _host, string _id, string _pwd, string _dataBase)
{
host = _host;
id = _id;
pwd = _pwd;
dataBase = _dataBase;
OpenSql();
}
/// <summary>
/// 打开数据库
/// </summary>
public static void OpenSql()
{
try
{
//string.Format是将指定的 String类型的数据中的每个格式项替换为相应对象的值的文本等效项。
string mySqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};", dataBase, host, id, pwd, "3306");
mySqlConnection = new MySqlConnection(mySqlString);
mySqlConnection.Open();
}
catch (Exception e)
{
throw new Exception("服务器连接失败,请重新检查是否打开MySql服务。" + e.Message.ToString());
}
}
/// <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)
{
throw new Exception("输入不正确:" + "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 DataSet CreateTableAutoID(string name, string[] col, string[] colType)
{
if (col.Length != colType.Length)
{
throw new Exception("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] + ")" + ")";
return QuerySet(query);
}
/// <summary>
/// 插入一条数据,包括所有,不适用自动累加ID。
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="values">插入值</param>
/// <returns></returns>
public DataSet InsertInto(string tableName, string[] values)
{
string query = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
for (int i = 1; i < values.Length; ++i)
{
query += ", " + "'" + values[i] + "'";
}
query += ")";
return QuerySet(query);
}
/// <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");
}
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");
}
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="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>
public void Close()
{
if (mySqlConnection != null)
{
mySqlConnection.Close();
mySqlConnection.Dispose();
mySqlConnection = null;
}
}
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sqlString">sql语句</param>
/// <returns></returns>
public static DataSet QuerySet(string sqlString)
{
if (mySqlConnection.State == ConnectionState.Open)
{
DataSet ds = new DataSet();
try
{
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sqlString, mySqlConnection);
mySqlDataAdapter.Fill(ds);
}
catch (Exception e)
{
throw new Exception("SQL:" + sqlString + "/n" + e.Message.ToString());
}
finally
{
}
return ds;
}
return null;
}
}
Test.cs
using UnityEngine;
using System.Data;
public class Test : MonoBehaviour
{
private void Start()
{
MySqlAccess mySql = new MySqlAccess("localhost", "root", "123456", "haha");
mySql.CreateTableAutoID("tableTest", new string[] { "id", "name", "age" }, new string[] { "int", "text", "text" });
mySql.InsertInto("tableTest", new string[] { "name", "age" }, new string[] { "张三", "28" });
mySql.InsertInto("tableTest", new string[] { "name", "age" }, new string[] { "李四", "20" });
for (int i = 1; i < 3; i++)
{
DataSet ds = mySql.Select("tableTest", new string[] { "name", "age" }, new string[] { "id" }, new string[] { "=" }, new string[] { i.ToString() });
if (ds != null)
{
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
Debug.Log(row[column]);
}
}
}
}
mySql.Close();
}
}
到此结束,测试成功。
问题
在整个过程中,遇到了很多问题,在他人的博客中,需要导入以下dll文件:
这些我都试过,可惜都出现了很多问题。其实vs studio中已经具有了System.Data.dll和System.Drawing.dll不必导入了。在引用中可以查看:
对于,mysql.data.dll文件,可能会出现版本不兼容问题。之所以下载1.2.7版本的zip格式mysql for visual studio,也正是这个原因。从1.2.8版本的mysql for visual studio开始,其mysql.data.dll文件导入unity后,就会出现版本不兼容问题。