unity連接，unity3d連接數據庫

Unity3D研究院之Unity中連接本地或局域網MySQL數據庫（五十九）

時間: 2013-05-11 / 分類: 【Unity3D研究院之游戲開發】 / 瀏覽次數: 4106 瀏覽數 / 36條評論個評論 發表評論????????????????????

-

???????????????????????????????????????????????????

????????????????????????

? ? ? ? ?最近MOMO身心疲憊。。今天是周末在家無聊我還是決定來學習。不知道學什么，就學MySQL吧。本篇主要記錄從MySQL安裝到局域網內任意機器連接數據庫，也算是對自己學習的總結。今天我沒用Mac電腦，而是選擇Windows，沒有別有用心，而是想熟悉一下Windows下操作Unity。

unity連接。 官網上下載MySQL的安裝程序，這里有一篇詳細的安裝文章，http://www.jb51.net/article/23876.htm??為了讓中文完美的運行，配置MySQL的時候Character Set處設置成UTF-8，好像默認是不能顯示中文。配置完畢后就可以在本機中啟動MySQL，也可以在cmd命令行中start和stop 啟動與關閉MySQL。

view source

1	net start mysql

2	net stop mysql

?

?為了讓本機MySQL數據庫可以在局域網中任意機器都可以訪問，請看 下面這個網址。

http://dzb3688.blog.163.com/blog/static/105068522201292671444891/

文章有一點點講的不是很清楚，所以我在補充一下、

我用的是Navicat Pewmium查看數據庫，我覺得這個數據庫挺好的，因為我在Mac上也是用的這個數據庫 。（大家可以在網絡上下載它，Windows版本居然有漢化的）如下圖所示，點擊用戶，然后雙擊”root@%” 最后把主機的名字改成 “%”即可、

?

?

下面就是重點了，打開cmd 窗口cd到MySQL的路徑下，一定要cd到這個路徑下，不然mysql 會是無法識別的指令噢。

?

然后執行命令：

mysql?grant all privileges on *.* to root@”%” identified by ‘abc’ with grant option;?? flush privileges;

在執行命令：

mysql?flush privileges;

OK這樣就行了。

然后開始看看代碼怎么寫，為了方便數據庫的創建、增加、刪除、修改、查詢、我封裝了一個類。歡迎大家測試 啦啦啦啦。

SqlAccess.cs

view source

001	using UnityEngine;?

002	using System;?

003	using System.Data;?

004	using System.Collections;??

005	using MySql.Data.MySqlClient;

006	using MySql.Data;

007	using System.IO;

008	public class SqlAccess

009

{

010

?

011	????public static MySqlConnection dbConnection;

012	????//如果只是在本地的話，寫localhost就可以。

013	???// static string host = "localhost";?

014	????//如果是局域網，那么寫上本機的局域網IP

015	????static string host = "192.168.1.106";?

016	????static string id = "root";

017	????static string pwd = "1234";

018	????static string database = "xuanyusong";

019

?

020	????public SqlAccess()

021

????{

022	????????OpenSql();

023

????}

024

?

025	????public static void OpenSql()

026

????{

027

?

028	????????try

029

????????{

030	????????????string connectionString = string.Format("Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3};",host,database,id,pwd,"3306");

031	????????????dbConnection = new MySqlConnection(connectionString);

032	????????????dbConnection.Open();

033	????????}catch (Exception e)

034

????????{

035	????????????throw new Exception("服務器連接失敗，請重新檢查是否打開MySql服務。" + e.Message.ToString());?

036

?

037

????????}

038

?

039

????}

040

?

041	????public DataSet CreateTable (string name, string[] col, string[] colType)

042

????{

043	????????if (col.Length != colType.Length)

044

????????{

045

?

046	????????????throw new Exception ("columns.Length != colType.Length");

047

?

048

????????}

049

?

050	????????string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];

051

?

052	????????for (int i = 1; i < col.Length; ++i) {

053

?

054	????????????query += ", " + col[i] + " " + colType[i];

055

?

056

????????}

057

?

058	????????query += ")";

059

?

060	????????return? ExecuteQuery(query);

061

????}

062

?

063	????public DataSet CreateTableAutoID (string name, string[] col, string[] colType)

064

????{

065	????????if (col.Length != colType.Length)

066

????????{

067

?

068	????????????throw new Exception ("columns.Length != colType.Length");

069

?

070

????????}

071

?

072	????????string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0] +? " NOT NULL AUTO_INCREMENT";

073

?

074	????????for (int i = 1; i < col.Length; ++i) {

075

?

076	????????????query += ", " + col[i] + " " + colType[i];

077

?

078

????????}

079

?

080	????????query += ", PRIMARY KEY ("+ col[0] +")" + ")";

081

?

082	????????Debug.Log(query);

083

?

084	????????return? ExecuteQuery(query);

085

????}

086

?

087	????//插入一條數據，包括所有，不適用自動累加ID。

088	????public DataSet InsertInto (string tableName, string[] values)

089

????{

090

?

091	????????string query = "INSERT INTO " + tableName + " VALUES (" + "'"+ values[0]+ "'";

092

?

093	????????for (int i = 1; i < values.Length; ++i) {

094

?

095	????????????query += ", " + "'"+values[i]+ "'";

096

?

097

????????}

098

?

099	????????query += ")";

100

?

101	????????Debug.Log(query);

102	????????return ExecuteQuery (query);

103

?

104

????}

105

?

106	????//插入部分ID

107	????public DataSet InsertInto (string tableName, string[] col,string[] values)

108

????{

109

?

110	????????if (col.Length != values.Length)

111

????????{

112

?

113	????????????throw new Exception ("columns.Length != colType.Length");

114

?

115

????????}

116

?

117	????????string query = "INSERT INTO " + tableName + " (" + col[0];

118	????????for (int i = 1; i < col.Length; ++i)

119

????????{

120

?

121	????????????query += ", "+col[i];

122

?

123

????????}

124

?

125	????????query += ") VALUES (" + "'"+ values[0]+ "'";

126	????????for (int i = 1; i < values.Length; ++i)

127

????????{

128

?

129	????????????query += ", " + "'"+values[i]+ "'";

130

?

131

????????}

132

?

133	????????query += ")";

134

?

135	????????Debug.Log(query);

136	????????return ExecuteQuery (query);

137

?

138

????}

139

?

140	????public DataSet SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)

141

????{

142

?

143	????????if (col.Length != operation.Length || operation.Length != values.Length) {

144

?

145	????????????throw new Exception ("col.Length != operation.Length != values.Length");

146

?

147

????????}

148

?

149	????????string query = "SELECT " + items[0];

150

?

151	????????for (int i = 1; i < items.Length; ++i) {

152

?

153	????????????query += ", " + items[i];

154

?

155

????????}

156

?

157	????????query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";

158

?

159	????????for (int i = 1; i < col.Length; ++i) {

160

?

161	????????????query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";

162

?

163

????????}

164

?

165	????????return ExecuteQuery (query);

166

?

167

????}

168

?

169	????public DataSet UpdateInto (string tableName, string []cols,string []colsvalues,string selectkey,string selectvalue)

170

????{

171

?

172	????????string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0];

173

?

174	????????for (int i = 1; i < colsvalues.Length; ++i) {

175

?

176	?????????????query += ", " +cols[i]+" ="+ colsvalues[i];

177

????????}

178

?

179	?????????query += " WHERE "+selectkey+" = "+selectvalue+" ";

180

?

181	????????return ExecuteQuery (query);

182

????}

183

?

184	????public DataSet Delete(string tableName,string []cols,string []colsvalues)

185

????{

186	????????string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0];

187

?

188	????????for (int i = 1; i < colsvalues.Length; ++i)

189

????????{

190

?

191	????????????????query += " or " +cols[i]+" = "+ colsvalues[i];

192

????????}

193	????????Debug.Log(query);

194	????????return ExecuteQuery (query);

195

????}

196

?

197	????public? void Close()

198

????{

199

?

200	????????if(dbConnection != null)

201

????????{

202	????????????dbConnection.Close();

203	????????????dbConnection.Dispose();

204	????????????dbConnection = null;

205

????????}

206

?

207

????}

208

?

209	????public static DataSet ExecuteQuery(string sqlString)?

210

????{?

211	????????if(dbConnection.State==ConnectionState.Open)

212

????????{

213	????????????DataSet ds = new DataSet();?

214	????????????try?

215	????????????{?

216

?

217	????????????????MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);

218	????????????????da.Fill(ds);

219

?

220	????????????}?

221	????????????catch (Exception ee)?

222	????????????{

223	????????????????throw new Exception("SQL:" + sqlString + "/n" + ee.Message.ToString());?

224	????????????}

225	????????????finally

226	????????????{

227	????????????}

228	????????????return ds;

229

????????}

230	????????return null;

231

????}

232

?

233

}

?

然后在來看看調用，把如下腳本綁定在任意對象即可，調用包括、創建表、插入信息、查找信息、刪除信息、更新信息。代碼比較簡單我就不一一注釋了，這里我用try catch如果有錯誤信息將打印在屏幕中。 創建表包括是否遞增ID，所以有兩種創建表的方式。如果你的數據庫是提前預制的話可以這樣來讀取數據庫。

view source

01	using UnityEngine;?

02	using System;?

03	using System.Data;?

04	using System.Collections;??

05	using MySql.Data.MySqlClient;

06	using MySql.Data;

07	using System.IO;

08	public class NewBehaviourScript : MonoBehaviour {

09

?

10	????string Error = null;

11	????void Start ()

12

????{

13	????????try

14

????????{

15

?

16	????????SqlAccess sql = new? SqlAccess();

17

?

18	?????????sql.CreateTableAutoID("momo",new string[]{"id","name","qq","email","blog"}, new string[]{"int","text","text","text","text"});

19	????????//sql.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"});

20	????????sql.InsertInto("momo",new string[]{"name","qq","email","blog"},new string[]{"xuanyusong","289187120","xuanyusong@gmail.com","xuanyusong.com"});

21	????????sql.InsertInto("momo",new string[]{"name","qq","email","blog"},new string[]{"ruoruo","34546546","ruoruo@gmail.com","xuanyusong.com"});

22

?

23	????????DataSet ds? = sql.SelectWhere("momo",new string[]{"name","qq"},new string []{"id"},new string []{"="},new string []{"1"});

24	????????if(ds != null)

25

????????{

26

?

27	????????????DataTable table = ds.Tables[0];

28

?

29	????????????foreach (DataRow row in table.Rows)

30	????????????{

31	???????????????foreach (DataColumn column in table.Columns)

32	???????????????{

33	????????????????????Debug.Log(row[column]);

34	???????????????}

35	?????????????}

36	????????}??

37

?

38	?????????sql.UpdateInto("momo",new string[]{"name","qq"},new string[]{"'ruoruo'","'11111111'"}, "email", "'xuanyusong@gmail.com'"? );

39

?

40	?????????sql.Delete("momo",new string[]{"id","email"}, new string[]{"1","'000@gmail.com'"}? );

41	?????????sql.Close();

42	????????}catch(Exception e)

43

????????{

44	????????????Error = e.Message;

45

????????}

46

?

47

????}

48

?

49	????// Update is called once per frame

50	????void OnGUI ()

51

????{

52

?

53	????????if(Error != null)

54

????????{

55	????????????GUILayout.Label(Error);

56

????????}

57

?

58

????}

59

}

然后是用到的dll 一個都不能少，不然會出現各種問題。最后的下載地址我會給出，并且包含這些文件。

?

?

?

為了測試局域網的連接， 我還編譯到了Android手機上，在Android上訪問這個數據庫，也是沒問題的。當然手機和電腦用的是同一個wifi網絡。 目前這個項目在 Windows 和 ?Android上都可以很好的運行，我感覺在Mac上和iPhone上應該也木有問題，歡迎大家測試，如果發現在別的平臺下有問題請告訴我，我會進一步研究的。 歡迎大家留言，一起學習啦啦啦啦 嘿嘿嘿～～。。

?

?

?