Unity与MySQL数据库连接和交互常用方法

导入必需的动态链接库

Unity连接MySql数据库时，需要导入必需的动态链接库（dll）
1、在Unity的安装目录D:\unity2017.2.0f3\Editor\Data\Mono\lib\mono\2.0引用dll文件：
I18N.CJK.dll、I18N.dll、I18N.West.dll、I18N.MidEast、I18N.Rare、I18N.Other、System.data.dll
MySql.Data.dll外部导入：
链接：https://pan.baidu.com/s/12329Gi9aeGQKp6iAT3Dp8g
提取码：5soo

2、在Unity目录下添加Plugins文件夹，将这些dll放进Plugins文件夹中，unity将会自动引用

3、在PlayerSetting设置Api Compatibility Level为.net 2.0，避免发布时候出错。

Unity与MySQL交互常用的方法

在脚本中添加引用：
using MySql.Data.MySqlClient;

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

public class MySQLManager
{
    public static MySqlConnection SqlConnection;//连接类对象

    private static string host;     //IP地址。如果只是在本地的话，写localhost就可以。
    private static string id;       //用户名。
    private static string pwd;      //密码。
    private static string dataBase; //数据库名称。

    /// <summary>
    /// 构造方法
    /// </summary>
    /// <param name="_host">IP地址</param>
    /// <param name="_id">用户名</param>
    /// <param name="_pwd">密码</param>
    /// <param name="_dataBase">数据库名称</param>
    public  MySQLManager(string _host,string _id,string _pwd,string _dataBase)
    {
        host = _host;
        id = _id;
        pwd = _pwd;
        dataBase = _dataBase;
        OpenSql();
    }

    /// <summary>  
    /// 打开数据库  
    /// </summary>  
    public   void OpenSql()
    {
        try
        {           
            string SqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};", dataBase, host, id, pwd, "3306");
            SqlConnection = new MySqlConnection(SqlString);
            SqlConnection.Open();
            Debug.Log("打开数据库");
            
        }
        catch ( Exception e )
        {           
            Debug.Log("服务器连接失败，请重新检查是否打开MySql服务。" + e.Message.ToString());
            InputIP.ReadIP();
        }
    }


    /// <summary>
    /// 通过SQL语句查询是否数据库存在某表
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public bool ExitTable(string sql) {
     
        OpenSql();
        MySqlCommand cmd = new MySqlCommand(sql, SqlConnection);
        MySqlDataReader reader = cmd.ExecuteReader();
        if ( reader.HasRows == false )
        {
            Debug.Log("该表不存在");
            Close();
            return false;
        }
        else {
            Debug.Log("表存在");
            Close();
            return true;
        }
       
    }
    /// <summary>  
    /// 创建表  
    /// </summary>  
    /// <param name="name">表名</param>  
    /// <param name="colName">属性列</param>  
    /// <param name="colType">属性类型</param>  
    /// <returns></returns>  
    public  DataSet CreateTable(string name, string[] colName, string[] colType)
    {
        if ( colName.Length != colType.Length )
        {         
            Debug.Log("输入不正确：" + "columns.Length != colType.Length");
        }
      
        string query = "CREATE TABLE  " + name + "(" + colName[0] + " " + colType[0];
        for ( int i = 1; i < colName.Length; i++ )
        {
            query += "," + colName[i] + " " + colType[i];
        }
        query += ")";
        return QuerySet(query);
    }

    /// <summary>  
    /// 创建具有id自增的表  
    /// </summary>  
    /// <param name="name">表名</param>  
    /// <param name="col">属性列</param>  
    /// <param name="colType">属性列类型</param>  
    /// <returns></returns>  
    public bool CreateTableAutoID(string name, string[] col, string[] colType)
    {
        if ( col.Length != colType.Length )
        {
            //  throw new Exception("columns.Length != colType.Length");
            Debug.Log("输入不正确：" + "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] + ")" + ")";

        if ( ExecuteSqlCmd(query) )
        {
            return true;
        }
        else {
            return false;
        }
       
       
    }


    /// <summary>  
    /// 插入部分ID  
    /// </summary>  
    /// <param name="tableName">表名</param>  
    /// <param name="col">属性列</param>  
    /// <param name="values">属性值</param>  
    /// <returns></returns>  
    public   DataSet InsertInto(string tableName, string[] col, string[] values)
    {
        if ( col.Length != values.Length )
        {
            // throw new Exception("columns.Length != colType.Length");
            Debug.Log("输入不正确：" + "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 += ")";
        return QuerySet(query);
    }

    /// <summary>  
    /// 查询表数据 
    /// </summary>  
    /// <param name="tableName">表名</param>  
    /// <param name="items">需要查询的列</param>  
    /// <param name="whereColName">查询的条件列</param>  
    /// <param name="operation">条件操作符</param>  
    /// <param name="value">条件的值</param>  
    /// <returns></returns>  
    public   DataSet Select(string tableName, string[] items, string[] whereColName, string[] operation, string[] value)
    {
        if ( whereColName.Length != operation.Length || operation.Length != value.Length )
        {
            // throw new Exception("输入不正确：" + "col.Length != operation.Length != values.Length");
            Debug.Log("输入不正确：" + "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 " + " " + whereColName[0] + operation[0] + " '" + value[0] + "'";
        for ( int i = 1; i < whereColName.Length; i++ )
        {
            query += " AND " + whereColName[i] + operation[i] + "' " + value[i] + "'";
        }
        return QuerySet(query);
    }
    /// <summary>
    /// 读取数据  读取数据库指定表中的指定数据
    /// </summary>
    /// <param name="column_name">所要查询数据的列名称或以*代替</param>
    /// <param name="index">列索引号</param>
    /// <param name="table_name">表名称</param>
    /// <param name="ConditonVar">依据条件列名称</param>>
    /// <param name="Condtionvalue">依据条件列的值 </param>>
    /// <returns></returns>
    public string GetValueByRead(string column_name, int index, string table_name, string ConditonVar, string Condtionvalue)
    {
       // OpenSql();
        string _value = "";
        MySqlCommand cmd =new MySqlCommand();
        cmd.CommandText = "select " + column_name + " from " + table_name + " where " + ConditonVar + " = '" + Condtionvalue + "'";
        cmd.Connection = SqlConnection;
        MySqlDataReader reader = cmd.ExecuteReader();
        while ( reader.Read() )
        {
            _value = reader[index].ToString();
        }
        reader.Close();
      //  Close();
        return _value;
    }

    /// <summary>  
    /// 更新表数据 
    /// </summary>  
    /// <param name="tableName">表名</param>  
    /// <param name="cols">更新列</param>  
    /// <param name="colsvalues">更新的值</param>  
    /// <param name="selectkey">条件：列</param>  
    /// <param name="selectvalue">条件：值</param>  
    /// <returns></returns>  
    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 QuerySet(query);
    }

    /// <summary>  
    /// 删除表数据  
    /// </summary>  
    /// <param name="tableName">表名</param>  
    /// <param name="cols">条件：删除列</param>  
    /// <param name="colsvalues">删除该列属性值所在得行</param>  
    /// <returns></returns>  
    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];
        }
        return QuerySet(query);
    }
    /// <summary>
    /// 按照表名删除数据库中的表
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public bool DeleteTable(string tableName) {
        string query = "DROP TABLE " + tableName;
        return ExecuteSqlCmd(query);
    }

    /// <summary>
    /// 释放
    /// </summary>
    public  void Close()
    {
        if ( SqlConnection != null )
        {
            SqlConnection.Close();
            SqlConnection.Dispose();
            SqlConnection = null;
        }
        Debug.Log("关闭数据库");
    }

    /// <summary>    
    /// 执行Sql语句  
    /// </summary>  
    /// <param name="sqlString">sql语句</param>  
    /// <returns></returns>  
    public  DataSet QuerySet(string sqlString)
    {
        OpenSql();
        if ( SqlConnection.State == ConnectionState.Open )
        {
            DataSet ds = new DataSet();
            try
            {
                MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sqlString, SqlConnection);
                mySqlDataAdapter.Fill(ds);
            }
            catch ( Exception e )
            {
                // throw new Exception("SQL:" + sqlString + "/n" + e.Message.ToString());
                Debug.Log("SQL:" + sqlString + "/n" + e.Message.ToString());
            }
            finally
            {
                Close();
            }
            return ds;
        }
        return null;
    }
    /// <summary>
    /// 通过sql语句返回查询行数
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public  int SqlRecordCount(string sql)
    {
        OpenSql();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = sql;
        cmd.Connection = SqlConnection;
        MySqlDataReader dr;
        dr = cmd.ExecuteReader();
        int RecordCount = 0;
        while ( dr.Read() )
        {
            RecordCount = RecordCount + 1;
        }
        Close();
        return RecordCount;
    }
    /// <summary>
    /// 获取表中指定条件下，某字段的行数
    /// </summary>
    /// <param name="column_name"></param>
    /// <param name="table_name"></param>
    /// <param name="condition"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public int GetDataLineCount(string column_name, string table_name, string condition,string value)
    {
        int _Num = 0;
        OpenSql();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "select " + column_name + " from "+ table_name+ " where find_in_set(" + value +"," + condition + ")"; //find_in_set(1,position)
        Debug.Log(cmd.CommandText);
        cmd.Connection = SqlConnection;
        MySqlDataReader reader = cmd.ExecuteReader();
        while ( reader.Read() )
        {
            _Num = _Num + 1;
        }
        reader.Close();
        Close();
        return _Num;
    }
    /// <summary>
    /// 执行sql语句并返回是否执行完成
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public bool ExecuteSqlCmd(string sql)
    {
        Debug.Log(sql);
        OpenSql();
        if ( SqlConnection.State == ConnectionState.Open )
        {
            MySqlCommand cmd = new MySqlCommand(sql,SqlConnection);         
                return cmd.ExecuteNonQuery() > 0;                    
        }
        Close();
        return false;
    }


  
}