unity3d连接数据库

Unity3D研究院之Unity中连接本地或局域网MySQL数据库（五十九）

时间: 2013-05-11 / 分类: 【Unity3D研究院之游戏开发】 / 浏览次数: 4106 浏览数 / 36条评论个评论发表评论

最近MOMO身心疲惫。。今天是周末在家无聊我还是决定来学习。不知道学什么，就学MySQL吧。本篇主要记录从MySQL安装到局域网内任意机器连接数据库，也算是对自己学习的总结。今天我没用Mac电脑，而是选择Windows，没有别有用心，而是想熟悉一下Windows下操作Unity。

官网上下载MySQL的安装程序，这里有一篇详细的安装文章，http://www.jb51.net/article/23876.htm 为了让中文完美的运行，配置MySQL的时候Character Set处设置成UTF-8，好像默认是不能显示中文。配置完毕后就可以在本机中启动MySQL，也可以在cmd命令行中start和stop 启动与关闭MySQL。

`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

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，所以有两种创建表的方式。如果你的数据库是提前预制的话可以这样来读取数据库。

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上应该也木有问题，欢迎大家测试，如果发现在别的平台下有问题请告诉我，我会进一步研究的。欢迎大家留言，一起学习啦啦啦啦嘿嘿嘿～～。。