unity3d-连接mysql数据库

前段时间由于要做项目，所以研究了下unity连接mysql数据库的一些相关知识，现在讲自己的一些理解写下来，如果有更好的做法，可以连接我哦。
首先我们需要安装mysql数据库和Navicat for MySQL，稍后我会把安装包的连接传上来。
mysql和Navicat for MySQL的安装网上有很多，在这里我就不多说了。安装完成后，创建一个名为：testmysql的数据库，字符集选择utf8，如图所示

然后自己创建表并设计表，我设计的表如图：

自己随便插入一些数据，接下来我们就可以回到unity中了。
unity连接数据库需要通过c#来连接，然后在unity中在调用c#脚本中的方法获取数据库的数据。
首先，我们要创建一个c#代码，命名为SqlAccess，代码如下：

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using UnityEngine;


public class SqlAccess
{
    //static void Main(string[] args) { }
        public static MySqlConnection dbConnection;
        //如果只是在本地的话，写localhost就可以。
        // static string host = "localhost";  
        //如果是局域网，那么写上本机的局域网IP
        static string host = "localhost";//ip地址，如果是本机则写localhost，如果不是本机，则写安装数据库电脑的ip
        static string id = "root";//数据库的用户名
        static string pwd = "1234";//数据库的密码
        static string database = "testmysql";//数据库的名称  这里我的数据库名称为：examsystem

     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);

    }
    //选择数据库中的id
    public DataSet SelectWhere1(string tableName,string[] items) {
        string query = "SELECT " + items[0];

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

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

        }

        query += " FROM " + tableName;
        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 GetAllUser(string tableName,string[] items) {
        string query = "SELECT " + items[0];
        for (int i = 1; i < items.Length; ++i)
        {

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

        }
        query += " From " + tableName;

        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);
    }
    //更新一条数据，例如  分数为90，现在想更新为99
    public DataSet updatewhere(string tableName,string score1,string userName) { 
        string query="update "+tableName+" set score = '"+score1+"' where user"+" = '"+userName+"'";


        return ExecuteQuery(query);
    }

//求数据库中有多少行数据
public DataSet Count(string tableName){
string query="select count(*) from "+tableName;

return ExecuteQuery(query);
// return query;
}
//检查密码是否正确
    public DataSet CheckPassword111(string tableName,string[] user) {
        string query = "select password from " + tableName + " where user = '" + user[0]+"'";

        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 DataSet DeleteUser(string tableName,string col,string colvalue) {
        string query = "delete from " + tableName + " where " + col + " = '" + colvalue + "' ";

        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;
    }

}

以上代码是连接数据库和一些获取数据库数据的一些方法！对于一些学过数据库语言的同学，会比较好理解，如果没有学过数据库语言，看本文章会有些吃力，建议先去了解一下数据库语言在来看本文章！
接下来我们再创建一个ConnectMysql.cs脚本，代码如下：

using UnityEngine;
using System.Collections;
using System.Data;


public class ConnectMysql : MonoBehaviour {

    SqlAccess sql = new SqlAccess();//调用SqlAccess脚本

// Use this for initialization
void Start () {
        GetMessageFromSQL();
}
// Update is called once per frame
void Update () {
}
    //从数据库中获取数据
    void GetMessageFromSQL() { 
        //调用SqlAccess里面的函数
        DataSet ds=sql.SelectWhere("login",new string[]{"id","userName","password"},new string []{"id"},new string[]{"="},new string[]{"1"});//这句代码的意思是：从表login中，选择出id，userName，password，当id=1的时候
        if (ds != null) {
            DataTable table = ds.Tables[0];
            print("id:"+table.Rows[0][0]+"    userName:"+table.Rows[0][1]+"   password:"+table.Rows[0][2]);//输出得到的结果，并打印出来
        }
    }
}

然后我们运行unity，可以看到输出了结果：

我们可以与我们数据库中的数据对应一下：

unity连接mysql数据库就讲到这里，有兴趣的同学可以多研究一下其他功能！并且数据库语言基础的同学可以在SqlAccess.cs代码里面按照我给的形式自己添加一些函数。