最近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。
为了让本机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
004 | using System.Collections; |
005 | using MySql.Data.MySqlClient; |
008 | public class SqlAccess |
011 | public static MySqlConnection dbConnection; |
015 | static string host = "192.168.1.106" ; |
016 | static string id = "root" ; |
017 | static string pwd = "1234" ; |
018 | static string database = "xuanyusong" ; |
025 | public static void OpenSql() |
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); |
035 | throw new Exception( "服务器连接失败,请重新检查是否打开MySql服务。" + e.Message.ToString()); |
041 | public DataSet CreateTable ( string name, string [] col, string [] colType) |
043 | if (col.Length != colType.Length) |
046 | throw new Exception ( "columns.Length != colType.Length" ); |
050 | string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0]; |
052 | for ( int i = 1; i < col.Length; ++i) { |
054 | query += ", " + col[i] + " " + colType[i]; |
060 | return ExecuteQuery(query); |
063 | public DataSet CreateTableAutoID ( string name, string [] col, string [] colType) |
065 | if (col.Length != colType.Length) |
068 | throw new Exception ( "columns.Length != colType.Length" ); |
072 | string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0] + " NOT NULL AUTO_INCREMENT" ; |
074 | for ( int i = 1; i < col.Length; ++i) { |
076 | query += ", " + col[i] + " " + colType[i]; |
080 | query += ", PRIMARY KEY (" + col[0] + ")" + ")" ; |
084 | return ExecuteQuery(query); |
088 | public DataSet InsertInto ( string tableName, string [] values) |
091 | string query = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0]+ "'" ; |
093 | for ( int i = 1; i < values.Length; ++i) { |
095 | query += ", " + "'" +values[i]+ "'" ; |
102 | return ExecuteQuery (query); |
107 | public DataSet InsertInto ( string tableName, string [] col, string [] values) |
110 | if (col.Length != values.Length) |
113 | throw new Exception ( "columns.Length != colType.Length" ); |
117 | string query = "INSERT INTO " + tableName + " (" + col[0]; |
118 | for ( int i = 1; i < col.Length; ++i) |
121 | query += ", " +col[i]; |
125 | query += ") VALUES (" + "'" + values[0]+ "'" ; |
126 | for ( int i = 1; i < values.Length; ++i) |
129 | query += ", " + "'" +values[i]+ "'" ; |
136 | return ExecuteQuery (query); |
140 | public DataSet SelectWhere ( string tableName, string [] items, string [] col, string [] operation, string [] values) |
143 | if (col.Length != operation.Length || operation.Length != values.Length) { |
145 | throw new Exception ( "col.Length != operation.Length != values.Length" ); |
149 | string query = "SELECT " + items[0]; |
151 | for ( int i = 1; i < items.Length; ++i) { |
153 | query += ", " + items[i]; |
157 | query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' " ; |
159 | for ( int i = 1; i < col.Length; ++i) { |
161 | query += " AND " + col[i] + operation[i] + "'" + values[0] + "' " ; |
165 | return ExecuteQuery (query); |
169 | public DataSet UpdateInto ( string tableName, string []cols, string []colsvalues, string selectkey, string selectvalue) |
172 | string query = "UPDATE " +tableName+ " SET " +cols[0]+ " = " +colsvalues[0]; |
174 | for ( int i = 1; i < colsvalues.Length; ++i) { |
176 | query += ", " +cols[i]+ " =" + colsvalues[i]; |
179 | query += " WHERE " +selectkey+ " = " +selectvalue+ " " ; |
181 | return ExecuteQuery (query); |
184 | public DataSet Delete( string tableName, string []cols, string []colsvalues) |
186 | string query = "DELETE FROM " +tableName + " WHERE " +cols[0] + " = " + colsvalues[0]; |
188 | for ( int i = 1; i < colsvalues.Length; ++i) |
191 | query += " or " +cols[i]+ " = " + colsvalues[i]; |
194 | return ExecuteQuery (query); |
200 | if (dbConnection != null ) |
202 | dbConnection.Close(); |
203 | dbConnection.Dispose(); |
209 | public static DataSet ExecuteQuery( string sqlString) |
211 | if (dbConnection.State==ConnectionState.Open) |
213 | DataSet ds = new DataSet(); |
217 | MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection); |
223 | throw new Exception( "SQL:" + sqlString + "/n" + ee.Message.ToString()); |
然后在来看看调用,把如下脚本绑定在任意对象即可,调用包括、创建表、插入信息、查找信息、删除信息、更新信息。代码比较简单我就不一一注释了,这里我用try catch如果有错误信息将打印在屏幕中。 创建表包括是否递增ID,所以有两种创建表的方式。如果你的数据库是提前预制的话可以这样来读取数据库。
04 | using System.Collections; |
05 | using MySql.Data.MySqlClient; |
08 | public class NewBehaviourScript : MonoBehaviour { |
16 | SqlAccess sql = new SqlAccess(); |
18 | sql.CreateTableAutoID( "momo" , new string []{ "id" , "name" , "qq" , "email" , "blog" }, new string []{ "int" , "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" }); |
23 | DataSet ds = sql.SelectWhere( "momo" , new string []{ "name" , "qq" }, new string []{ "id" }, new string []{ "=" }, new string []{ "1" }); |
27 | DataTable table = ds.Tables[0]; |
29 | foreach (DataRow row in table.Rows) |
31 | foreach (DataColumn column in table.Columns) |
33 | Debug.Log(row[column]); |
38 | sql.UpdateInto( "momo" , new string []{ "name" , "qq" }, new string []{ "'ruoruo'" , "'11111111'" }, "email" , "'xuanyusong@gmail.com'" ); |
40 | sql.Delete( "momo" , new string []{ "id" , "email" }, new string []{ "1" , "'000@gmail.com'" } ); |
55 | GUILayout.Label(Error); |
然后是用到的dll 一个都不能少,不然会出现各种问题。最后的下载地址我会给出,并且包含这些文件。
为了测试局域网的连接, 我还编译到了Android手机上,在Android上访问这个数据库,也是没问题的。当然手机和电脑用的是同一个wifi网络。 目前这个项目在 Windows 和 Android上都可以很好的运行,我感觉在Mac上和iPhone上应该也木有问题,欢迎大家测试,如果发现在别的平台下有问题请告诉我,我会进一步研究的。 欢迎大家留言,一起学习啦啦啦啦 嘿嘿嘿~~。。