Unity 幻径-多人排行

---

前言

学习完SIKI学院教程(幻径 - 超级上瘾的小游戏)后对排行榜进行修改，使用MySql数据库实现多人排行功能。

一、数据库

I.不熟悉数据库可以先学习SIKI学院教程(MySQL数据库从零到精通)了解数据库
II.建一个Plugins文件导入MySql插件(需要可以直接在我的主页下载)

III.根据需求设置表

IV.写一个脚本连接数据库和使用sql语句

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

public class MySqlAccess : MonoBehaviour
{
    //连接类对象
    private static MySqlConnection mySqlConnection;
    //IP地址
    private static string host;
    //端口号
    private static string port;
    //用户名
    private static string userName;
    //密码
    private static string password;
    //数据库名称
    private static string databaseName;
    /// <summary>
    /// 初始化
    /// </summary>
    public MySqlAccess(string _host, string _port, string _userName, string _password, string _databaseName)
    {
        host = _host;
        port = _port;
        userName = _userName;
        password = _password;
        databaseName = _databaseName;
        OpenSql();
    }
    /// <summary>
    /// 打开数据库
    /// </summary>
    public void OpenSql()
    {
        try
        {
            string mySqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};port={4}"
                , databaseName, host, userName, password, port);
            mySqlConnection = new MySqlConnection(mySqlString);
            mySqlConnection.Open();
        }
        catch (Exception e)
        {
            throw new Exception("服务器连接失败，请重新检查MySql服务是否打开。" + e.Message.ToString());
        }
    }

    /// <summary>
    /// 关闭数据库
    /// </summary>
    public void CloseSql()
    {
        if (mySqlConnection != null)
        {
            mySqlConnection.Close();
            mySqlConnection.Dispose();
            mySqlConnection = null;
        }
    }
    /// <summary>
    /// 执行SQL语句
    /// </summary>
    public DataSet QuerySet(string sqlString)
    {
        if (mySqlConnection.State == ConnectionState.Open)
        {
            DataSet ds = new DataSet();
            try
            {
                MySqlDataAdapter mySqlAsapter = new MySqlDataAdapter(sqlString, mySqlConnection);
                mySqlAsapter.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception("SQL:" + sqlString + "/n" + e.Message.ToString());
            }
            return ds;
        }
        return null;
    }
}

二、GameManager

思路：
a.将名字与成绩用类存起来放入列表(方便调用与排序
b.游戏结束后把数据库中的排行取出来，将当前成绩直接与排行最后一名进行比较
-大于，出现输入名字面板
-小于等于，不出现
c.确认名字按钮点击
-拿到数据(名字、成绩)
-将数据插入队列进行排序
-将数据库中的数据进行替换

添加代码如下

using System.Collections;
using System.Collections.Generic;
using System.Runtime.Serialization.Formatters.Binary;
using UnityEngine;
using System.IO;
using System.Linq;
using System;
using System.Data;
using MySql;
using MySql.Data.MySqlClient;
using UnityEngine.UI;

/// <summary>
/// 定义排行类
/// </summary>
[System.Serializable]
public class Ranking
{
    public string name;
    public int score;

    public Ranking(string name,int score)
    {
        this.name = name;
        this.score = score;
    }
}

/// <summary>
/// 排序方法类
/// </summary>
public class My_Sort : IComparer<Ranking>
{
    public int Compare(Ranking x, Ranking y)
    {
        if (x == null && y == null) return 0;
        if (x == null) return -1;
        if (y == null) return 1;

        if (x.score < y.score) return 1;
        if (x.score > y.score) return -1;
        
        return 0;
    }
}


public class GameManager : MonoBehaviour
{
    public static GameManager _instance;
    public List<Ranking> ranking = new List<Ranking>();

    //IP地址
    private string host = "***";
    //端口号
    private string port = "***";
    //用户名
    private string userName = "***";
    //密码
    private string password = "***";
    //数据库名称
    private string databaseName = "***";
    //封装好的数据库类
    MySqlAccess mysql;

    private void Awake()
    {
        mysql = new MySqlAccess(host, port, userName, password, databaseName);
    }
    /// <summary>
    /// 获取数据库中的数据
    /// </summary>
    public void UpdateRankFromSql()
    {
        mysql.OpenSql();//打开数据库
        string updateScore = "Select * From pathtogodrank";
        DataSet ds = mysql.QuerySet(updateScore);
        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            //ranking = new List<Ranking>();//新的表
            if (ranking != null)
            {
                ranking.Clear();
            }
            for (int i = 0; i < 11; ++i)
            {
                ranking.Add(new Ranking((string)table.Rows[i][1], (int)table.Rows[i][2]));
            }
        }
    }

    /// <summary>
    /// 添加数据到数据库
    /// </summary>
    /// <param name="name"></param>
    /// <param name="score"></param>
    public void AddSqlData()
    {
        mysql.OpenSql();//打开数据库
        //将原本数据清空
        string sql = "TRUNCATE TABLE pathtogodrank";
        mysql.QuerySet(sql);

        //插入新的数据
        for (int i = 0; i < 11; ++i)
        {
            if (ranking[i].name == null)
            {
                ranking[i].name = i.ToString();
                ranking[i].score = 0;
            }
            string sql2 = "insert into pathtogodrank(userName,gameScore) values('" + ranking[i].name + "','" + ranking[i].score + "')";//sql命令
            mysql.QuerySet(sql2);
        }

        mysql.CloseSql();
    }

    /// <summary>
    /// 添加列表项后保存
    /// </summary>
    /// <param name="name"></param>
    /// <param name="score"></param>
    public void AddRankList(string name, int score)
    {
        ranking.Add(new Ranking(name, score));
        m_ListSort();
        AddSqlData();
        Save();
    }
    /// <summary>
    /// 列表排序
    /// </summary>
    public void m_ListSort()
    {
        if (ranking.Count > 0) ranking.Sort(new My_Sort());
    } 
    /// <summary>
    /// 获得最好成绩
    /// </summary>
    /// <returns></returns>
    public int GetBestScore()  
    {
        //Debug.Log("ranking.Count = " + ranking.Count);
        m_ListSort();
        return ranking[0].score;
    }
    /// <summary>
    /// 初始化游戏数据
    /// </summary>
    private void InitGameData()
    {
        Read();
        if (data != null)
        {
            isFirstGame = data.GetIsFirstGame();
        }
        else
        {
            isFirstGame = true;
        }
        //如果第一次开始游戏
        if (isFirstGame)
        {
            isFirstGame = false;
            isMusicOn = true;
            selectSkin = 0;
            skinUnlocked = new bool[vars.SkinChooseSpriteList.Count];
            skinUnlocked[0] = true;
            diamondCount = 10;
            ranking = new List<Ranking>();

            data = new GameData();
            Save();
        }
        else
        {
            isMusicOn = data.GetIsMusicOn();
            selectSkin = data.GetSelectSkin();
            skinUnlocked = data.GetSkinUnlocked();
            diamondCount = data.GetDiamondCount();
            ranking = data.GetRankList();
        }
    }

    /// <summary>
    /// 储存数据
    /// </summary>
    private void Save()
    {
        UpdateRankFromSql();
        try
        {
            //写入数据，将数据序列化 需要用到BinaryFormatter类
            BinaryFormatter bf = new BinaryFormatter();
            //需要创建一个文字写入流，将类写在文件中
            using (FileStream fs = File.Create(Application.persistentDataPath + "/GameData.data"))
            //using最后会自动释放文件流//FileStream fs = 创建一个文件路径，persistentDataPath在手机上可以用这个路径临时存放文件
            {
                data.SetDiamondCount(diamondCount);
                data.SetIsFirstGame(isFirstGame);
                data.SetIsMusicOn(isMusicOn);
                data.SetSelectSkin(selectSkin);
                data.SetSkinUnlocked(skinUnlocked);
                //data.SetNameArr(NameArr);
                //data.SetBestScoreArr(bestScoreArr);
                data.SetRankList(ranking);

                bf.Serialize(fs, data);
                //将类序列化，将数据写入本地
            }
        }
        catch (System.Exception e)
        { 
            Debug.Log(e.Message);
        }
    }

    /// <summary>
    /// 读取数据
    /// </summary>
    private void Read()
    {
        try
        {
            BinaryFormatter bf = new BinaryFormatter();
            using (FileStream fs = File.Open(Application.persistentDataPath + "/GameData.data", FileMode.Open))
            {
                data = (GameData)bf.Deserialize(fs);
                //经过读取把本地数据还原成Data数据
            }
        }
        catch (System.Exception e)
        {
            Debug.Log(e.Message);
        }
    }
    /// <summary>
    /// 重置数据
    /// </summary>
    public void Reset()
    {
        isFirstGame = false;
        isMusicOn = true;
        selectSkin = 0;
        skinUnlocked = new bool[vars.SkinChooseSpriteList.Count];
        skinUnlocked[0] = true;
        diamondCount = 10;

        data = new GameData();
        Save();
    }
}

---

总结

注意执行顺序