Unity 连接MySql 数据库

亦凉 2022-09-03 00:15 59阅读 0赞

Unity版本最好是2018.4.22以上，2017.4.8是不行的（亲测)

工程设置如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70][]

导入如下dll：

![20210804165653307.png][]

[https://www.xuanyusong.com/archives/2326][https_www.xuanyusong.com_archives_2326]

[https://blog.csdn.net/qq\_42462109/article/details/83385658][https_blog.csdn.net_qq_42462109_article_details_83385658]

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

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

C\#

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p></p> <p>net start mysql</p> <p>net stop mysql</p> <p></p> </td> 
  </tr> 
 </tbody> 
</table>

**为了让本机MySQL数据库可以在局域网中任意机器都可以访问，请看 下面这个网址。**

**[http://dzb3688.blog.163.com/blog/static/105068522201292671444891/][http_dzb3688.blog.163.com_blog_static_105068522201292671444891]**

**文章有一点点讲的不是很清楚，所以我在补充一下、**

**我用的是Navicat Pewmium查看数据库，我觉得这个数据库挺好的，因为我在Mac上也是用的这个数据库 。（大家可以在网络上下载它，Windows版本居然有汉化的）如下图所示，点击用户，然后双击”root@%” 最后把主机的名字改成 “%”即可、**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70 1][]

**下面就是重点了，打开cmd 窗口cd到MySQL的路径下，一定要cd到这个路径下，不然mysql 会是无法识别的指令噢。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70 2][]

**然后执行命令：**

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

**在执行命令：**

**mysql flush privileges;**

**OK这样就行了。**

**然后开始看看代码怎么写，为了方便数据库的创建、增加、删除、修改、查询、我封装了一个类。欢迎大家测试 啦啦啦啦。**

**SqlAccess.cs**

using UnityEngine;  
    using System;  
    using System.Data;  
    using System.Collections;   
    using MySql.Data.MySqlClient;
    using MySql.Data;
    using System.IO;
    public class SqlAccess 
    {
     
        public static MySqlConnection dbConnection;
    	//如果只是在本地的话，写localhost就可以。
       // static string host = "localhost";  
    	//如果是局域网，那么写上本机的局域网IP
    	static string host = "192.168.1.106";  
        static string id = "root";
        static string pwd = "1234";
        static string database = "xuanyusong"; 
     
    	public SqlAccess()
    	{
    		OpenSql();
    	}
     
    	public static void OpenSql()
    	{
     
    		try
    		{
    			string connectionString = string.Format("Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3};",host,database,id,pwd,"3306");
    			dbConnection = new MySqlConnection(connectionString);
    			dbConnection.Open(); 
    		}catch (Exception e)
    		{
    	        throw new Exception("服务器连接失败，请重新检查是否打开MySql服务。" + e.Message.ToString());  
     
    		}
     
    	}
     
    	public DataSet CreateTable (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];
     
            for (int i = 1; i < col.Length; ++i) {
     
                query += ", " + col[i] + " " + colType[i];
     
            }
     
            query += ")";
     
            return  ExecuteQuery(query);
        }
     
    	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] +")" + ")";
     
    		Debug.Log(query);
     
            return  ExecuteQuery(query);
        }
     
    	//插入一条数据，包括所有，不适用自动累加ID。
    	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 += ")";
     
    		Debug.Log(query);
            return ExecuteQuery (query);
     
        }
     
    	//插入部分ID
    	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 += ")";
     
    		Debug.Log(query);
            return ExecuteQuery (query);
     
        }
     
       	public DataSet SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)
        {
     
            if (col.Length != operation.Length || operation.Length != values.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 " + col[0] + operation[0] + "'" + values[0] + "' ";
     
            for (int i = 1; i < col.Length; ++i) {
     
                query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
     
            }
     
            return ExecuteQuery (query);
     
        } 
     
    	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 ExecuteQuery (query);
    	}
     
    	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];
    		}
    		Debug.Log(query);
    		return ExecuteQuery (query);
    	}
     
    	public  void Close()
    	{
     
    		if(dbConnection != null)
    		{
    			dbConnection.Close();
    			dbConnection.Dispose();
    			dbConnection = null;
    		}
     
    	}
     
        public static DataSet ExecuteQuery(string sqlString)  
        {  
    		if(dbConnection.State==ConnectionState.Open)
    		{
         		DataSet ds = new DataSet();  
          		try  
    	    	{  
     
    				MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection); 
    				da.Fill(ds);
     
    	    	}  
    		    catch (Exception ee)  
    		    {
    		        throw new Exception("SQL:" + sqlString + "/n" + ee.Message.ToString());  
    		    }
    			finally
    			{
    			}
    			return ds;
    		}
    	    return null;
    	}
     
    }

**然后是用到的dll 一个都不能少，不然会出现各种问题。最后的下载地址我会给出，并且包含这些文件。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70 3][]

**为了测试局域网的连接， 我还编译到了Android手机上，在Android上访问这个数据库，也是没问题的。当然手机和电脑用的是同一个wifi网络。 目前这个项目在 Windows 和 Android上都可以很好的运行，我感觉在Mac上和iPhone上应该也木有问题，欢迎大家测试，如果发现在别的平台下有问题请告诉我，我会进一步研究的。 欢迎大家留言，一起学习啦啦啦啦 嘿嘿嘿～～。。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70]: /images/20220829/937baf600d8a46e49841d25b450fb28e.png
[20210804165653307.png]: /images/20220829/47093f97a2db413e9f7964586839868b.png
[https_www.xuanyusong.com_archives_2326]: https://www.xuanyusong.com/archives/2326
[https_blog.csdn.net_qq_42462109_article_details_83385658]: https://blog.csdn.net/qq_42462109/article/details/83385658
[http_www.jb51.net_article_23876.htm]: http://www.jb51.net/article/23876.htm
[http_dzb3688.blog.163.com_blog_static_105068522201292671444891]: http://dzb3688.blog.163.com/blog/static/105068522201292671444891/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70 1]: /images/20220829/170b788d92bd48c980530bb4796db3c0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70 2]: /images/20220829/f302d4410e544444a337e3c7bbbeb768.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70 3]: /images/20220829/d22981e7256245d99f5d708747e36337.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpemhlbnhpcW5tbGdi_size_16_color_FFFFFF_t_70 4]: /images/20220829/241d9c84e33e477bab7241e39bb24a95.png