Unity3D连接本地或局域网MySQL数据库
准备工作：
1、打开 Unity3D 安装目录，到这个路径下 Editor > Data > Mono > lib > mono > 2.0 拷贝出下图的五个动态链接库，放到 Unity3D 工程目录下 Assets/Plugins 文件夹中。这里我想重点说一下，在使用过程中这五个动态链接库需要与 Unity3D 版本匹配，也就是说你不要在 Uniyt3D 5.3 版本拷贝出来，拿去 Uniyt3D 5.4 版本中使用，这是有可能出问题的，最好是在自己的 Unity3D 安装目录下去拷贝。I18N.CJK.dll 这个链接库，如果是 PC 端连接数据库可以不需要，如果是移动端连接数据库就必须要。
2、网上下载一个 MySql.Data.dll 动态链接库，也放到 Unity3D 工程目录下 Assets/Plugins 文件夹中。
为了方便对数据库进行创建、增加、删除、修改、查询等操作，我封装了一个工具类，代码如下：
1 using MySql.Data.MySqlClient; 2 using System; 3 using System.Data; 4 5 public class MySqlAccess 6 { 7 public static MySqlConnection mySqlConnection;//连接类对象 8 9 private static string host; //IP地址。如果只是在本地的话，写localhost就可以。 10 private static string id; //用户名。 11 private static string pwd; //密码。 12 private static string dataBase; //数据库名称。 13 14 /// <summary> 15 /// 构造方法 16 /// </summary> 17 /// <param name="_host">IP地址</param> 18 /// <param name="_id">用户名</param> 19 /// <param name="_pwd">密码</param> 20 /// <param name="_dataBase">数据库名称</param> 21 public MySqlAccess(string _host, string _id, string _pwd, string _dataBase) 22 { 23 host = _host; 24 id = _id; 25 pwd = _pwd; 26 dataBase = _dataBase; 27 OpenSql(); 28 } 29 30 /// <summary> 31 /// 打开数据库 32 /// </summary> 33 public static void OpenSql() 34 { 35 try 36 { 37 //string.Format是将指定的 String类型的数据中的每个格式项替换为相应对象的值的文本等效项。 38 string mySqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};", dataBase, host, id, pwd, "3306"); 39 mySqlConnection = new MySqlConnection(mySqlString); 40 mySqlConnection.Open(); 41 } 42 catch (Exception e) 43 { 44 throw new Exception("服务器连接失败，请重新检查是否打开MySql服务。" + e.Message.ToString()); 45 } 46 } 47 48 /// <summary> 49 /// 创建表 50 /// </summary> 51 /// <param name="name">表名</param> 52 /// <param name="colName">属性列</param> 53 /// <param name="colType">属性类型</param> 54 /// <returns></returns> 55 public DataSet CreateTable(string name, string[] colName, string[] colType) 56 { 57 if (colName.Length != colType.Length) 58 { 59 throw new Exception("输入不正确：" + "columns.Length != colType.Length"); 60 } 61 string query = "CREATE TABLE " + name + "(" + colName[0] + " " + colType[0]; 62 for (int i = 1; i < colName.Length; i++) 63 { 64 query += "," + colName[i] + " " + colType[i]; 65 } 66 query += ")"; 67 return QuerySet(query); 68 } 69 70 /// <summary> 71 /// 创建具有id自增的表 72 /// </summary> 73 /// <param name="name">表名</param> 74 /// <param name="col">属性列</param> 75 /// <param name="colType">属性列类型</param> 76 /// <returns></returns> 77 public DataSet CreateTableAutoID(string name, string[] col, string[] colType) 78 { 79 if (col.Length != colType.Length) 80 { 81 throw new Exception("columns.Length != colType.Length"); 82 } 83 string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0] + " NOT NULL AUTO_INCREMENT"; 84 for (int i = 1; i < col.Length; ++i) 85 { 86 query += ", " + col[i] + " " + colType[i]; 87 } 88 query += ", PRIMARY KEY (" + col[0] + ")" + ")"; 89 return QuerySet(query); 90 } 91 92 /// <summary> 93 /// 插入一条数据，包括所有，不适用自动累加ID。 94 /// </summary> 95 /// <param name="tableName">表名</param> 96 /// <param name="values">插入值</param> 97 /// <returns></returns> 98 public DataSet InsertInto(string tableName, string[] values) 99 { 100 string query = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'"; 101 for (int i = 1; i < values.Length; ++i) 102 { 103 query += ", " + "'" + values[i] + "'"; 104 } 105 query += ")"; 106 return QuerySet(query); 107 } 108 109 /// <summary> 110 /// 插入部分ID 111 /// </summary> 112 /// <param name="tableName">表名</param> 113 /// <param name="col">属性列</param> 114 /// <param name="values">属性值</param> 115 /// <returns></returns> 116 public DataSet InsertInto(string tableName, string[] col, string[] values) 117 { 118 if (col.Length != values.Length) 119 { 120 throw new Exception("columns.Length != colType.Length"); 121 } 122 string query = "INSERT INTO " + tableName + " (" + col[0]; 123 for (int i = 1; i < col.Length; ++i) 124 { 125 query += ", " + col[i]; 126 } 127 query += ") VALUES (" + "'" + values[0] + "'"; 128 for (int i = 1; i < values.Length; ++i) 129 { 130 query += ", " + "'" + values[i] + "'"; 131 } 132 query += ")"; 133 return QuerySet(query); 134 } 135 136 /// <summary> 137 /// 查询表数据 138 /// </summary> 139 /// <param name="tableName">表名</param> 140 /// <param name="items">需要查询的列</param> 141 /// <param name="whereColName">查询的条件列</param> 142 /// <param name="operation">条件操作符</param> 143 /// <param name="value">条件的值</param> 144 /// <returns></returns> 145 public DataSet Select(string tableName, string[] items, string[] whereColName, string[] operation, string[] value) 146 { 147 if (whereColName.Length != operation.Length || operation.Length != value.Length) 148 { 149 throw new Exception("输入不正确：" + "col.Length != operation.Length != values.Length"); 150 } 151 string query = "SELECT " + items[0]; 152 for (int i = 1; i < items.Length; i++) 153 { 154 query += "," + items[i]; 155 } 156 query += " FROM " + tableName + " WHERE " + " " + whereColName[0] + operation[0] + " '" + value[0] + "'"; 157 for (int i = 1; i < whereColName.Length; i++) 158 { 159 query += " AND " + whereColName[i] + operation[i] + "' " + value[i] + "'"; 160 } 161 return QuerySet(query); 162 } 163 164 /// <summary> 165 /// 更新表数据 166 /// </summary> 167 /// <param name="tableName">表名</param> 168 /// <param name="cols">更新列</param> 169 /// <param name="colsvalues">更新的值</param> 170 /// <param name="selectkey">条件：列</param> 171 /// <param name="selectvalue">条件：值</param> 172 /// <returns></returns> 173 public DataSet UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue) 174 { 175 string query = "UPDATE " + tableName + " SET " + cols[0] + " = " + colsvalues[0]; 176 for (int i = 1; i < colsvalues.Length; ++i) 177 { 178 query += ", " + cols[i] + " =" + colsvalues[i]; 179 } 180 query += " WHERE " + selectkey + " = " + selectvalue + " "; 181 return QuerySet(query); 182 } 183 184 /// <summary> 185 /// 删除表数据 186 /// </summary> 187 /// <param name="tableName">表名</param> 188 /// <param name="cols">条件：删除列</param> 189 /// <param name="colsvalues">删除该列属性值所在得行</param> 190 /// <returns></returns> 191 public DataSet Delete(string tableName, string[] cols, string[] colsvalues) 192 { 193 string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsvalues[0]; 194 for (int i = 1; i < colsvalues.Length; ++i) 195 { 196 query += " or " + cols[i] + " = " + colsvalues[i]; 197 } 198 return QuerySet(query); 199 } 200 201 /// <summary> 202 /// 释放 203 /// </summary> 204 public void Close() 205 { 206 if (mySqlConnection != null) 207 { 208 mySqlConnection.Close(); 209 mySqlConnection.Dispose(); 210 mySqlConnection = null; 211 } 212 } 213 214 /// <summary> 215 /// 执行Sql语句 216 /// </summary> 217 /// <param name="sqlString">sql语句</param> 218 /// <returns></returns> 219 public static DataSet QuerySet(string sqlString) 220 { 221 if (mySqlConnection.State == ConnectionState.Open) 222 { 223 DataSet ds = new DataSet(); 224 try 225 { 226 MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sqlString, mySqlConnection); 227 mySqlDataAdapter.Fill(ds); 228 } 229 catch (Exception e) 230 { 231 throw new Exception("SQL:" + sqlString + "/n" + e.Message.ToString()); 232 } 233 finally 234 { 235 } 236 return ds; 237 } 238 return null; 239 } 240 }
接下来我们就可以访问数据库了，代码如下：
1 using UnityEngine; 2 using System.Data; 3 4 public class Test : MonoBehaviour 5 { 6 private void Start() 7 { 8 MySqlAccess mySql = new MySqlAccess("localhost", "root", "root", "Test"); 9 mySql.CreateTableAutoID("tableTest", new string[] { "id", "name", "age" }, new string[] { "int", "text", "text" }); 10 mySql.InsertInto("tableTest", new string[] { "name", "age" }, new string[] { "张三", "28" }); 11 mySql.InsertInto("tableTest", new string[] { "name", "age" }, new string[] { "李四", "20" }); 12 for (int i = 1; i < 3; i++) 13 { 14 DataSet ds = mySql.Select("tableTest", new string[] { "name", "age" }, new string[] { "id" }, new string[] { "=" }, new string[] { i.ToString() }); 15 if (ds != null) 16 { 17 DataTable table = ds.Tables[0]; 18 foreach (DataRow row in table.Rows) 19 { 20 foreach (DataColumn column in table.Columns) 21 { 22 Debug.Log(row[column]); 23 } 24 } 25 } 26 } 27 mySql.Close(); 28 } 29 }
