Unity 2021连接外网数据库，建表，增删改查遇到的问题记录

目录

一、连接外网数据库遇到的问题

二、解决问题的方案

动态链接库下载地址：

效果图

---

一、连接外网数据库遇到的问题

先抛出问题，就是连接内网数据库不会报错，连接外网时和查询时就报这个错：

The given key 'utf8mb4' was not present in the dictionary.

二、解决问题的方案

1、建立连接时报以上的错，在建立连接时添加这句 charset=utf8，即可解决。

public static void OpenSql()
    {
        try
        {
            string connectionString = string.Format("Server = {0};port={1};Database = {2}; User ID = {3}; Password = {4};charset=utf8", host, "3306", database, id, pwd);
            dbConnection = new MySqlConnection(connectionString);
            dbConnection.Open();
        }
        catch (Exception e)
        {
            Debug.LogError("服务器连接失败！  " + e.Message);
        }
    }

2.在内网增删改查都不会有问题，但是连外网时，查询就又报上面的错，插入数据没问题！

换了个MySql.Data.dll动态链接库就解决了！

新的MySql.Data.dll依赖了几个别的库，BouncyCastle.Crypto.dll、Google.Protobuf.dll、Renci.SshNet.dll、SshNet.Security.Cryptography.dll

 新的MySql.Data.dll从VS里即可找到，用VS连接过MySql数据库的就有，需要下载一个支持文件VS才能连接Mysql。

动态链接库下载地址：

Unity连Mysql的MySql.Data.dll，支持Unity2021，Mysql8.0、Mysql5.5的外网增删改查-Unity3D文档类资源-CSDN文库https://download.csdn.net/download/hack_yin/85635678

三、建表，增删改查

工具类SqlTools

/*----------------------------------------------------------------
 Created by 王银
 文件名: SqlTools.cs
 创建时间： 2022/6/13 
 文件功能描述: SqlTools工具类
 Copyright © 2022年 王银 All rights reserved.
----------------------------------------------------------------*/
using UnityEngine;
using System;
using System.Data;
using MySql.Data.MySqlClient;
public class SqlTools
{
    public static MySqlConnection dbConnection;
    static string host = "114.55.165.42";
    static string id = "zhouyi";
    static string pwd = "xZMGeGKwA8k43bNj";
    static string database = "zhouyi";
    public SqlTools()
    {
        OpenSql();
    }
    public static void OpenSql()
    {
        try
        {
            string connectionString = string.Format("Server = {0};port={1};Database = {2}; User ID = {3}; Password = {4};charset=utf8", host, "3306", database, id, pwd);
            dbConnection = new MySqlConnection(connectionString);
            dbConnection.Open();
        }
        catch (Exception e)
        {
            Debug.LogError("服务器连接失败！  " + e.Message);
        }
    }
    public DataSet CreateTable(string name, string[] col, string[] colType)
    {
        if (col.Length != colType.Length)
        {
            Debug.LogError("columns.Length != colType.Length");
        }
        string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
        for (int i = 1; i < col.Length; ++i)
        {
            query += ", " + col[i] + " " + colType[i];
        }
        query += ")";
        return ExecuteQuery(query);
    }
    /// <summary>
    ///  CREATE TABLE `NewTable` (`id` int NOT NULL AUTO_INCREMENT,`content` longtext NOT NULL ,PRIMARY KEY(`id`));
    /// 建表时设置主键，让id自己增加
    /// </summary>
    /// <returns></returns>
    public DataSet CreateTableAutoID(string name, string[] col, string[] colType)
    {
        if (col.Length != colType.Length)
        {
            Debug.LogError("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] + ")" + ")";
        return ExecuteQuery(query);
    }
    /// <summary>
    ///  INSERT INTO `testTable` VALUES (0, 'aa', 'ss', 'dd');
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    //插入一条数据
    public DataSet InsertInto(string tableName, string[] values)
    {
        string query = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
        for (int i = 1; i < values.Length; ++i)
        {
            query += ", " + "'" + values[i] + "'";
        }
        query += ")";
        return ExecuteQuery(query);
    }
    //插入部分ID
    /// <summary>
    ///数据库语法
    /// 插入数据
    ///    INSERT INTO tableName(id, name) VALUES('s_1001', 'zhangSan');
    ///    INSERT INTO tableName VALUES('s_1002', 'liSi', 32, 'female');
    /// <param name="tableName"></param>
    /// <param name="col"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public DataSet InsertDataIntoTable(string tableName, string[] col, string[] values)
    {
        if (col.Length != values.Length)
        {
            Debug.LogError("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 ExecuteQuery(query);
    }
    public DataSet SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
    {
        if (col.Length != operation.Length || operation.Length != values.Length)
        {
            Debug.LogError("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 " + col[0] + operation[0] + "" + values[0] + "";
        for (int i = 1; i < col.Length; ++i)
        {
            query += " AND " + col[i] + operation[i] + "" + values[0] + "";
        }
        return ExecuteQuery(query);
    }
    /// <summary>
    /// 用id从表中查找
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public DataSet SelectWhereId(string tableName, int id)
    {
        string query = "select * from " + tableName + " where id=" + id;
        return ExecuteQuery(query);
    }
    public DataSet SelectWhereId(int id)
    {
        string query = "select * from textbook where id=" + id;
        return ExecuteQuery(query);
    }
    public DataSet SelectAllTable(string tableName)
    {
        string query = "select * from " + tableName;
        return ExecuteQuery(query);
    }
    /// 修改数据
    ///    UPDATE tableName SET name =’liSi’, age=’20’ WHERE age>50 AND gender =’male’;
    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 ExecuteQuery(query);
    }
    /// 删除数据
    ///    DELETE FROM tableName WHERE name=’changHao’ OR age > 30;
    ///    DELETE FROM tableName; 
    ///    truncate 是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的
    /// TRUNCATE TABLE stu;
    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 ExecuteQuery(query);
    }
    public void Close()
    {
        if (dbConnection != null)
        {
            dbConnection.Close();
            dbConnection.Dispose();
            dbConnection = null;
        }
    }
    public static DataSet ExecuteQuery(string sqlString)
    {
        if (dbConnection.State == ConnectionState.Open)
        {
            DataSet ds = new DataSet();
            try
            {
                Debug.Log("<color=red>" + sqlString + "</color>");
                MySqlCommand sqlCmd = new MySqlCommand(sqlString, dbConnection);
                MySqlDataAdapter da = new MySqlDataAdapter(sqlCmd);
                da.Fill(ds);
            }
            catch (Exception ee)
            {
                Debug.LogError(sqlString + "\r" + ee.Message);
            }
            return ds;
        }
        return null;
    }
}

SqlScript.cs 数据库的建表、增删改查操作

/*----------------------------------------------------------------
 Created by 王银
 文件名: SqlScript.cs
 创建时间： 2022/6/13 
 文件功能描述: Unity连接外网数据库，建表，增删改查等操作
 Copyright © 2022年 王银 All rights reserved.
----------------------------------------------------------------*/
using UnityEngine;
using System;
using System.Data;
public class SqlScript : MonoBehaviour
{
    void Start()
    {
        try
        {
            SqlTools sql = new SqlTools();
            //创建数据表，各数据非空，主键id自增加
            sql.CreateTableAutoID("newtextbook", new string[] { "id", "name", "content", "other" }, new string[] { "int", "text", "longtext", "text" });
            //插入数据
            sql.InsertDataIntoTable("newtextbook", new string[] { "name", "content", "other" }, new string[] { "test1", "This is test info 1.", "no.1" });
            sql.InsertDataIntoTable("newtextbook", new string[] { "name", "content", "other" }, new string[] { "test2", "This is test info 2.", "no.2" });
            sql.InsertDataIntoTable("newtextbook", new string[] { "name", "content", "other" }, new string[] { "test3", "This is test info 3.", "no.3" });
            //查询数据
            DataSet ds = sql.SelectWhere("newtextbook", new string[] { "name", "content" }, new string[] { "id" }, new string[] { "=" }, new string[] { "1" });
            if (ds != null)
            {
                DataTable table = ds.Tables[0];
                Debug.Log("Select1-> name: " + table.Rows[0][0] + "content:  " + table.Rows[0][1]);
                
            }
            //查询整表
            ds = sql.SelectAllTable("newtextbook");
            if (ds != null)
            {
                DataTable table = ds.Tables[0];
                //foreach (DataRow row in table.Rows)
                //{
                //    foreach (DataColumn column in table.Columns)
                //    {
                //        Debug.Log("SelectAllTable : "+row[column]);
                //    }
                //}
                int count = table.Rows.Count;
                for (int i = 0; i < count; i++)
                {
                    Debug.Log("SelectAllTable id: " + table.Rows[i][0] + ", name:  " + table.Rows[i][1] + ", content:  " + table.Rows[i][2]);
                }
            }
            //修改数据
            sql.UpdateInto("newtextbook", new string[] { "name", "content" }, new string[] { "'testupdate'", "'uptate info'" }, "id", "2");
            //新建查询
            ds = sql.SelectWhere("newtextbook", new string[] { "name", "content" }, new string[] { "id" }, new string[] { "=" }, new string[] { "2" });
            if (ds != null)
            {
                DataTable table = ds.Tables[0];
                Debug.Log("name: "+table.Rows[0][0] + "   content:  " + table.Rows[0][1]);
            }
            //删除数据
            sql.Delete("newtextbook", new string[] { "id" }, new string[] { "3"});
            //查询整表
            ds = sql.SelectAllTable("newtextbook");
            if (ds != null)
            {
                DataTable table = ds.Tables[0];
                int count = table.Rows.Count;
                for (int i = 0; i < count; i++)
                {
                    Debug.Log("SelectAllTable id: " + table.Rows[i][0] + ", name:  " + table.Rows[i][1] + ", content:  " + table.Rows[i][2]);
                }
            }
            //关闭数据连接
            sql.Close();
        }
        catch (Exception e)
        {
            Debug.LogError(e.Message);
        }
    }
}

效果图

输出的红色部分为数据库语句，普通的为正常 Debug.Log

参考文章：c# - "The given key 'utf8mb4' was not present in the dictionary - Stack Overflowhttps://stackoverflow.com/questions/61783752/the-given-key-utf8mb4-was-not-present-in-the-dictionary