Unity连接Mysql数据库

下载对应版本的dll文件,包括Unity版本和MysqlServer版本
本项目使用的是
MysqlServer8.0
Unity2020.3.21
所以下载的dll文件需要满足这两个版本
1.mysql8.0以上
2.4.5以及4.5以下(只是猜测,4.6以上unity也确实不能识别)

综上,选择了8.0.11版本,网址如下
https://downloads.mysql.com/archives/c-net/

下面是具体脚本:

using MySql.Data.MySqlClient;
using System.Data;
using System.Collections.Generic;
using Debug = UnityEngine.Debug;
namespace CSharpMysql
{
    
    public static class MysqlHelper
    {
        private static MySqlConnection connection;
        static MysqlHelper()
        {
            connection = new MySqlConnection(MySqlConnectString.New().ToString());
        }


        public static DataTable SelectFrom(string table)
        {
            MySqlCommand command = new MySqlCommand();
            command.CommandText = $"select * from {table};";
            return ExecuteReader(command);
        }
        // MySQL 的 WHERe 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERe 子句的字符串比较是区分大小写的。
        public static DataTable SelectFrom(string table, params MysqlData[] datas)
        {
            MySqlCommand command = new MySqlCommand();
            var builder = AppendJoin(" and ", datas);
            command.CommandText = $"select * from {table} where BINARY {builder};";
            Debug.Log(command.CommandText);
            foreach (var item in datas)
            {
                command.Parameters.AddWithValue($"@{item.key}", item.value);
            }
            return ExecuteReader(command);
        }
        public static int InsertInto(string table, params MysqlData[] datas)
        {
            MySqlCommand command = new MySqlCommand();

            var builder = AppendJoin(",", datas);
            command.CommandText = $"insert into {table} set {builder};";
            Debug.Log(command.CommandText);
            foreach (var item in datas)
            {
                command.Parameters.AddWithValue($"@{item.key}", item.value);
            }
            return ExecuteNonQuery(command);
        }
        public static int Update(string table, MysqlData[] updateDatas, params MysqlData[] whereDatas)
        {
            List updateStrList = new List();
            foreach (var item in updateDatas)
            {
                updateStrList.Add($"{item.key}=@1{item.key}");
            }
            var updateStr = AppendJoin(",", updateStrList);

            List whereStrList = new List();
            foreach (var item in whereDatas)
            {
                whereStrList.Add($"{item.key}=@2{item.key}");
            }
            var whereStr = AppendJoin(" and ", whereStrList);


            MySqlCommand command = new MySqlCommand();
            command.CommandText = $"update {table} set {updateStr} where {whereStr};";
            Debug.Log(command.CommandText);
            foreach (var item in updateDatas)
            {
                command.Parameters.AddWithValue($"@1{item.key}", item.value);
            }
            foreach (var item in whereDatas)
            {
                command.Parameters.AddWithValue($"@2{item.key}", item.value);
            }
            return ExecuteNonQuery(command);
        }
        public static int Delete(string table, params MysqlData[] datas)
        {
            MySqlCommand command = new MySqlCommand();
            var builder = AppendJoin(" and ", datas);
            command.CommandText = $"delete from {table} where {builder};";
            Debug.Log(command.CommandText);
            foreach (var item in datas)
            {
                command.Parameters.AddWithValue($"@{item.key}", item.value);
            }
            return ExecuteNonQuery(command);
        }


        private static DataTable ExecuteReader(MySqlCommand command)
        {
            connection.Open();
            command.Connection = connection;
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
            DataTable table = new DataTable();
            dataAdapter.Fill(table);
            connection.Close();
            return table;
        }
        private static int ExecuteNonQuery(MySqlCommand command)
        {
            connection.Open();
            command.Connection = connection;
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
            var rowNum = command.ExecuteNonQuery();
            connection.Close();
            return rowNum;
        }



        private static string AppendJoin(string separator, List values)
        {
            string str = "";
            for (int i = 0; i < values.Count; i++)
            {
                if (i == values.Count - 1)
                {
                    str += values[i];
                }
                else
                {
                    str += values[i] + separator;
                }
            }
            return str;
        }
        private static string AppendJoin(string separator, params object[] values)
        {
            string str = "";
            for (int i = 0; i < values.Length; i++)
            {
                if (i == values.Length - 1)
                {
                    str += values[i];
                }
                else
                {
                    str += values[i] + separator;
                }
            }
            return str;
        }
    }
}


namespace CSharpMysql
{
    public class MysqlData
    {
        public string key { get; private set; }
        public object value { get; private set; }
        public MysqlData(string key, object value)
        {
            this.key = key;
            this.value = value;
        }
        

        public override string ToString()
        {
            return $"{key}=@{key}";
        }
    }
}
namespace CSharpMysql
{
    public class MySqlConnectString
    {
        public string server = "127.0.0.1";
        public int port = 3306;
        public string user = "root";
        public string password = "root";
        public string database = "test";

        public override string ToString()
        {
            return $"server={server};user={user};database={database};port={port};password={password}";
        }

        public static MySqlConnectString New()
        {
            return new MySqlConnectString();
        }
    }
}