Unity连接Mysql数据库，小项目学习

制作中可能用到的资料：

https://dev.mysql.com/downloads/

视频教学

https://www.bilibili.com/video/BV1S7411M7P7?from=search&seid=1780740931854569426

MySQL Connector/NET的下载以及安装教程

https://blog.csdn.net/weixin_42220953/article/details/108341921

下载地址：

https://downloads.mysql.com/archives/c-net/

Mysql 8.0.11 出现1251- Client does not support authentication protocol 错误解决办法

https://blog.csdn.net/qq_41030861/article/details/100012275

https://blog.csdn.net/qq_34735841/article/details/100896095

https://www.bilibili.com/read/cv4790659

mysql服务启动

数据库设计

unity GUI 界面设计：

写好unity 连接mysql的包装类：

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

public class MySqlAccess {

    private static MySqlConnection mySqlConnection;//连接类对象
    private static string host;//IP地址
    private static string port;//端口号
    private static string userName;//用户名
    private static string password;//密码
    private static string databaseName;//数据库名称

    enum Operate {
        Select, Insert
    }
    /// <summary>
    /// 构造方法
    /// </summary>
    /// <param name="_host">ip地址</param>
    /// <param name="_userName">用户名</param>
    /// <param name="_password">密码</param>
    /// <param name="_databaseName">数据库名称</param>
    public MySqlAccess(string _host, string _port, string _userName, string _password, string _databaseName) {
        host = _host;
        port = _port;
        userName = _userName;
        password = _password;
        databaseName = _databaseName;
        OpenSql();
    }
    // 打开数据库
    public void OpenSql() {
        try {
            string mySqlString =
                "Database=" + databaseName + ";DataSource=" + host + ";User=" + userName + ";Password=" + password + ";port=" + port + ";";
            mySqlConnection = new MySqlConnection(mySqlString);
            UnityEngine.Debug.Log("连接成功");
            //if(mySqlConnection.State == ConnectionState.Closed)
            mySqlConnection.Open();

        }
        catch (Exception e) {
            throw new Exception("服务器连接失败，请重新检查MySql服务是否打开。" + e.Message.ToString());
        }

    }
    // 关闭数据库
    public void CloseSql() {
        if (mySqlConnection != null) {
            mySqlConnection.Close();
            mySqlConnection.Dispose();
            mySqlConnection = null;
        }
    }

    /// <summary>
    /// 查询数据
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="items">要查询的列</param>
    /// <param name="whereColumnName">查询的条件列</param>
    /// <param name="operation">条件操作符</param>
    /// <param name="value">条件的值</param>
    /// <returns></returns>
    public DataSet Select(string tableName, string[] items, string[] whereColumnName, string[] operation, string[] value) {
        if (whereColumnName.Length != operation.Length || operation.Length != value.Length) {
            throw new Exception("输入不正确：" + "要查询的条件、条件操作符、条件值 的数量不一致！");
        }
        string query = "Select " + items[0];
        for (int i = 1; i < items.Length; i++) {
            query += "," + items[i];
        }

        query += " FROM " + tableName + " WHERE " + whereColumnName[0] + " " + operation[0] + " '" + value[0] + "'";
        for (int i = 1; i < whereColumnName.Length; i++) {
            query += " and " + whereColumnName[i] + " " + operation[i] + " '" + value[i] + "'";
        }
        return QuerySet(query, Operate.Select);

    }

    /// <summary>
    /// 执行SQL语句
    /// </summary>
    /// <param name="sqlString">sql语句</param>
    /// <returns></returns>
    private DataSet QuerySet(string sqlString, Operate op) {
        
        if (mySqlConnection.State == ConnectionState.Open) {
            DataSet ds = new DataSet();
            MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter(sqlString, mySqlConnection);
            mySqlAdapter.Fill(ds);
            switch (op) {
                case Operate.Select: return ds.Tables[0].Rows.Count > 0 ? ds : null;
                case Operate.Insert:return null;
            }
            mySqlAdapter.Dispose();
        }
        return null;
    }


    public bool Insert(string tableName, string[] items, string[] value) {
        DataSet ds = QuerySet("Select level from test where name='" + value[0] + "'", Operate.Select);
        if (ds != null) {
            return false;
        }
        else {
            if (items.Length != value.Length) {
                throw new Exception("输入不正确：" + "要查询的条件、条件操作符、条件值 的数量不一致！");
            }
            string query = "Insert Into " + tableName + " ( " + items[0];
            for (int i = 1; i < items.Length; i++) {
                query += "," + items[i];
            }
            query += " ) Values ( '" + value[0] + "'";
            for (int i = 1; i < value.Length; i++) {
                query += ",'" + value[i] + "'";
            }
            query += " )";
            UnityEngine.Debug.Log(query);
            QuerySet(query, Operate.Insert);
        }
        return true;
    }
}

登录按钮绑定脚本User Log In.js

当点击登录后调用脚本中OnLogInBtnClick()方法：

UserLogIn.js：

using System.Collections;
using System.Collections.Generic;
using System.Data;
using UnityEngine;
using UnityEngine.UI;

public class UserLogIn : MonoBehaviour
{
    public InputField userNameInput;
    public InputField passwordInput;
    public Image mes;
    public string host;//IP地址
    public string port;//端口号
    public string userName;//用户名
    public string password;//密码
    public string databaseName;//数据库名称
    protected MySqlAccess mysql;//封装好的数据库类
    // Start is called before the first frame update
    void Start()
    {
        mysql = new MySqlAccess(host, port, userName, password, databaseName);
    }

    // Update is called once per frame
    void Update()
    {
        
    }

    public void OnLogInBtnClick() {
        mysql.OpenSql();
        //获取数据
        DataSet ds = mysql.Select("test", new string[] { "password","level" }, new string[] { "name"}, new string[] { "="}, new string[] { userNameInput.text});
        if (ds != null) {
            DataTable table = ds.Tables[0];
            List<string>data=new List<string>();
            foreach (DataRow row in table.Rows) {
                foreach (DataColumn col in table.Columns) {
                    data.Add(row[col].ToString());
                }
            }
            if (data[0]==passwordInput.text) {
                Debug.Log("登陆成功");
                mes.color = Color.green;
            }
            else {
                Debug.Log("登陆失败");
                mes.color = Color.red;
            }
        }
        else {
            Debug.Log("登陆失败");
            mes.color = Color.red;
        }
        mysql.CloseSql();
    }
}

启动后：

初始状态：

登录成功：

登录失败：

扩展：注册功能，有时间写