首页 > 代码库 > Unity 3D 连接Mysql数据库

Unity 3D 连接Mysql数据库

要想使用Unity直接连接数据库需要以下几个动态库

连接数据库前

将关于数据库的方法进行封装:MySqlConnection .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 = "127.0.0.1";  
    static string id = "root";
    static string pwd = "123456";
    static string database = "test"; 
    
    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 SelectAll(string Name)
    {
        string query ="select * from "+" "+Name;
        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; } }
  //执行sql语句
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; } }

连接数据库的代码如下:NewBehaviourScript.cs

using UnityEngine;  
using System;  
using System.Data;  
using System.Collections;   
using MySql.Data.MySqlClient;
using MySql.Data;
using System.IO;

public class NewBehaviourScript : MonoBehaviour
{
        string Error = null;

        void Start ()
        {
                try {
                        SqlAccess sql = new  SqlAccess ();        //创建SqlAccess 对象
                        sql.CreateTableAutoID("stu",new string[]{"id","name","qq"}, new string[]{"int","text","text"}); //主键自增长       
                        //sql.CreateTable("stu",new string[]{"name","qq"}, new string[]{"text","text"});  //建普通表
                        sql.InsertInto("stu",new string[]{"name","qq"},new string[]{"aa","12345678"});//插入数据
                        sql.InsertInto("stu",new string[]{"name","qq"},new string[]{"bb","98765432"});
                        DataSet ds = sql.SelectAll ("stu");
                        if (ds != null) {
                                DataTable table = ds.Tables [0];
                                foreach (DataRow row in table.Rows) 
                   {
foreach (DataColumn column in table.Columns) { Debug.Log (row [column]); } } } sql.Close (); } catch (Exception e) { Error = e.Message; } } // Update is called once per frame void OnGUI () { if (Error != null) { GUILayout.Label (Error); } } }

将脚本NewBehaviourScript.cs放到MainCamera上并运行:

在Unity中已执行,那就去数据库中查看是否产生:

对比发现表已经产生,查看数据:

没问题了,其它操作也可测试了。