Unity MySql安装部署与Unity连接下篇

一、前言

上篇讲到了如何安装与部署本地MySql；本篇主要讲Unity与MySql连接、创建表、删除表，然后就是对表中数据的增、删、改、查等操作。再讲这些之前会说一些安装MySql碰到的一些问题和Unity连接的问题。

当把本地MySql部署好之后，我们可能会遇到只有本机能访问的问题，如果碰到这个问题，我们需要把当前数据库的权限修改一下，如图所示，这是我们新建一个数据库后自动生成的一个user表，里面包含一些访问权限，如果没开放权限，那么当我们在局域网下访问装有mysql的机器时会发现只有本机可以访问到，其他机器访问不了，这时候我们就需要执行一些命令来修改访问权限。

第一步：cd MySql路径

第二步：登录 mysql -u root -p

第三步：进入数据库 use mysql

第四步：select user,host from user；

第五步：uptate user set host='%' where user='root'；

这样就ok了，最后在刷新一下

flush privileges；这样就可以在局域网下，所有机器都能访问到这个数据库

二、Unity连接MySql

可以先在网上找到与自己.net或者mysql对应版本的MySql.Data.dll文件，将这个文件下载下来放到Unity Plugins文件夹下，我这里用的是5.7.38版本

下一步就是熟悉MySql的命令语句，这个可以去MySql官网查询，这里就不多讲了；直接来看一下unity怎么连接MySql和对数据库中的数据进行操作。

连接MySql：

//连接类对象
        MySqlConnection msc;

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="serverIP">IP地址</param>
        /// <param name="port">端口</param>
        /// <param name="user">用户名</param>
        /// <param name="passwd">密码</param>
        /// <param name="database">数据库名称</param>
        /// <param name="format">字体默认UTF8</param>
        public void Connect(string serverIP, string port, string user, string password, string sqlName)
        {
            string connectStr = $"server = {serverIP};port = {port};user = {user};database = {sqlName};password = {password};charset = utf8";
            msc = new MySqlConnection(connectStr);
        }

打开和关闭连接：

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            msc.Open();
        }
 
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            msc.Close();
        }

完成上述操作，我们基本上就可以连接、打开、关闭数据库了。再下面就是对数据库中表的一些操作。

表查找 --> 创建 --> 删除

#region 表 --> 查找 --> 创建  --> 删除
        /// <summary>
        /// 查找表是否存在
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public bool IsTableExist(string tablename)
        {
            bool tableExists = false;
            string query = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";

            using (MySqlCommand command = new MySqlCommand(query, msc))
            {
                command.Parameters.AddWithValue("@TableName", tablename);
                tableExists = Convert.ToInt32(command.ExecuteScalar()) > 0;
            }
            return tableExists;
        }
        
        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="name"></param>
        /// <param name="property"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public int CreateTable(string name, string[] property, string[] type)
        {
            string query = $"CREATE TABLE IF NOT EXISTS UserMgr (ID VARCHAR(255), Type VARCHAR(255), UserName VARCHAR(255), Password VARCHAR(255))";
            
            return ExecuteNonQuery(query);
        }
        
        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int DeleteTable(string tableName)
        {
            string query = $"DROP TABLE IF EXISTS {tableName};";
 
            return ExecuteNonQuery(query);
        }
        #endregion

增：

#region 增
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="type"></param>
        /// <param name="username"></param>
        /// <param name="password"></param>
        public void AddTableData(string ID, string type, string username, string password)
        {
            string sql = "insert into usermgr(ID,Type,UserName,Password) values('" + ID + "','" + type + "','" + username + "','" + password + "')";
            MySqlCommand cmd = new MySqlCommand(sql, msc);
            //返回更改数据行数
            int result = cmd.ExecuteNonQuery();
            Debug.Log("添加数据成功:"+ result);
        }
        #endregion

删：

 #region 删
        /// <summary>
        /// 删除指定ID的数据
        /// </summary>
        /// <param name="ID"></param>
        public void DeleteTableData(string ID)
        {
            //删除的sql命令，这里是删除player中id=2的一行数据
            string sql = $"delete from usermgr where ID={ID}";                                     
            MySqlCommand cmd = new MySqlCommand(sql, msc);
            int result = cmd.ExecuteNonQuery();
            Debug.Log("删除数据成功:" + result);
        }
        #endregion

改：

#region 改
        /// <summary>
        /// 根据id去修改 用户类型 用户名 用户密码
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="ID"></param>
        /// <param name="username"></param>
        /// <param name="password"></param>
        public void AmendTableData(string ID, string type, string username, string password)
        {
            //更新
            string sql = $"update UserMgr set UserName='{username}',Password='{password}',Type='{type}' where ID={ID}";//更改的sql命令
            MySqlCommand cmd = new MySqlCommand(sql, msc);
            //返回值是数据库中修改的行数
            int result = cmd.ExecuteNonQuery();
            Debug.Log("更新数据成功:" + result);
        }
        #endregion

查：

#region 查
        /// <summary>
        /// 查找所有表中所有的数据
        /// </summary>
        public List<UserData> FindTableData()
        {
            string query = "SELECT * FROM usermgr";
            List<UserData> userDatas = new List<UserData>();
            using (MySqlCommand command = new MySqlCommand(query,msc))
            {
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        UserData data = new UserData();
                        data.id = int.Parse(reader.GetString("ID"));
                        data.type = int.Parse(reader.GetString("Type"));
                        data.username = reader.GetString("UserName");
                        data.password = reader.GetString("Password");
                        
                        //Debug.LogError(data.id + data.type + data.username + data.password);
                        
                        userDatas.Add(data);
                    }
                }
            }

            return userDatas;
        }
        #endregion

以上就是Unity与MySql连接进行一些数据库的基本操作。喜欢的小伙伴点个赞、关注一下吧，写这么多不容易。下面是完整的代码及用法。

完整代码：

        var mySql = new SqlHelper();
        mySql.Connect("192.168.3.250","3306","root","root","kcsql");
        mySql.Open();
        //查找表
        if (!mySql.IsTableExist("UserMgr"))
        {
            //创建表
            mySql.CreateTable("UserMgr", new[] {"Type", "User", "Password"}, new[] {"varchar", "varchar", "varchar"});
        }
        //删除表
        //mySql.DeleteTable("UserMgr");
        //增
        //mySql.AddTableData("2","1","lisi","123456");
        //删
        //mySql.DeleteTableData("2");
        //改
        //mySql.AmendTableData("0","0","pqy","pqy666");
        //查
        //mySql.FindTableData();
        
        mySql.Close();

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

namespace KC.MySql
{
    public class SqlHelper
    { 
        string connectStr;
        //连接类对象
        MySqlConnection msc;

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="serverIP">IP地址</param>
        /// <param name="port">端口</param>
        /// <param name="user">用户名</param>
        /// <param name="passwd">密码</param>
        /// <param name="database">数据库名称</param>
        /// <param name="format">字体默认UTF8</param>
        public void Connect(string serverIP, string port, string user, string password, string sqlName)
        {
            connectStr = $"server = {serverIP};port = {port};user = {user};database = {sqlName};password = {password};charset = utf8";
            msc = new MySqlConnection(connectStr);
        }
 
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            msc.Open();
        }
 
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            msc.Close();
        }

        #region 表 --> 查找 --> 创建  --> 删除
        /// <summary>
        /// 查找表是否存在
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public bool IsTableExist(string tablename)
        {
            bool tableExists = false;
            string query = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName";

            using (MySqlCommand command = new MySqlCommand(query, msc))
            {
                command.Parameters.AddWithValue("@TableName", tablename);
                tableExists = Convert.ToInt32(command.ExecuteScalar()) > 0;
            }
            return tableExists;
        }
        
        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="name"></param>
        /// <param name="property"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public int CreateTable(string name, string[] property, string[] type)
        {
            string query = $"CREATE TABLE IF NOT EXISTS UserMgr (ID VARCHAR(255), Type VARCHAR(255), UserName VARCHAR(255), Password VARCHAR(255))";
            
            return ExecuteNonQuery(query);
        }
        
        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int DeleteTable(string tableName)
        {
            string query = $"DROP TABLE IF EXISTS {tableName};";
 
            return ExecuteNonQuery(query);
        }
        #endregion

        #region 增
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="type"></param>
        /// <param name="username"></param>
        /// <param name="password"></param>
        public void AddTableData(string ID, string type, string username, string password)
        {
            string sql = "insert into usermgr(ID,Type,UserName,Password) values('" + ID + "','" + type + "','" + username + "','" + password + "')";
            MySqlCommand cmd = new MySqlCommand(sql, msc);
            //返回更改数据行数
            int result = cmd.ExecuteNonQuery();
            Debug.Log("添加数据成功:"+ result);
        }
        #endregion
        
        #region 删
        /// <summary>
        /// 删除指定ID的数据
        /// </summary>
        /// <param name="ID"></param>
        public void DeleteTableData(string ID)
        {
            //删除的sql命令，这里是删除player中id=2的一行数据
            string sql = $"delete from usermgr where ID={ID}";                                     
            MySqlCommand cmd = new MySqlCommand(sql, msc);
            int result = cmd.ExecuteNonQuery();
            Debug.Log("删除数据成功:" + result);
        }
        #endregion
        
        #region 改
        /// <summary>
        /// 根据id去修改 用户类型 用户名 用户密码
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="ID"></param>
        /// <param name="username"></param>
        /// <param name="password"></param>
        public void AmendTableData(string ID, string type, string username, string password)
        {
            //更新
            string sql = $"update UserMgr set UserName='{username}',Password='{password}',Type='{type}' where ID={ID}";//更改的sql命令
            MySqlCommand cmd = new MySqlCommand(sql, msc);
            //返回值是数据库中修改的行数
            int result = cmd.ExecuteNonQuery();
            Debug.Log("更新数据成功:" + result);
        }
        #endregion
        
        #region 查
        /// <summary>
        /// 查找所有表中所有的数据
        /// </summary>
        public List<UserData> FindTableData()
        {
            string query = "SELECT * FROM usermgr";
            List<UserData> userDatas = new List<UserData>();
            using (MySqlCommand command = new MySqlCommand(query,msc))
            {
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        UserData data = new UserData();
                        data.id = int.Parse(reader.GetString("ID"));
                        data.type = int.Parse(reader.GetString("Type"));
                        data.username = reader.GetString("UserName");
                        data.password = reader.GetString("Password");
                        
                        //Debug.LogError(data.id + data.type + data.username + data.password);
                        
                        userDatas.Add(data);
                    }
                }
            }

            return userDatas;
        }
        #endregion

        public int ExecuteNonQuery(string SQLString)
        {
 
            using (MySqlCommand cmd = new MySqlCommand(SQLString, msc))
            {
                try
                {
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (MySqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    //cmd.Dispose();
                }
            }
        }
    }
    
    /// <summary>
    /// 用户信息类
    /// </summary>
    public class UserData
    {
        public int id;
        public int type;
        public string username;
        public string password;
    }
}