目錄

一、連接外網(wǎng)數(shù)據(jù)庫遇到的問題

二、解決問題的方案

動態(tài)鏈接庫下載地址：

效果圖

---

一、連接外網(wǎng)數(shù)據(jù)庫遇到的問題

先拋出問題，就是連接內(nèi)網(wǎng)數(shù)據(jù)庫不會報錯，連接外網(wǎng)時和查詢時就報這個錯：

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.在內(nèi)網(wǎng)增刪改查都不會有問題，但是連外網(wǎng)時，查詢就又報上面的錯，插入數(shù)據(jù)沒問題！

換了個MySql.Data.dll動態(tài)鏈接庫就解決了！

新的MySql.Data.dll依賴了幾個別的庫，BouncyCastle.Crypto.dll、Google.Protobuf.dll、Renci.SshNet.dll、SshNet.Security.Cryptography.dll

?新的MySql.Data.dll從VS里即可找到，用VS連接過MySql數(shù)據(jù)庫的就有，需要下載一個支持文件VS才能連接Mysql。

動態(tài)鏈接庫下載地址：

Unity連Mysql的MySql.Data.dll，支持Unity2021，Mysql8.0、Mysql5.5的外網(wǎng)增刪改查-Unity3D文檔類資源-CSDN文庫https://download.csdn.net/download/hack_yin/85635678

三、建表，增刪改查

工具類SqlTools

/*----------------------------------------------------------------Created by 王銀文件名: SqlTools.cs創(chuàng)建時間： 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>//插入一條數(shù)據(jù)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>///數(shù)據(jù)庫語法/// 插入數(shù)據(jù)/// 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);}/// 修改數(shù)據(jù)/// 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);}/// 刪除數(shù)據(jù)/// 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 數(shù)據(jù)庫的建表、增刪改查操作

/*----------------------------------------------------------------Created by 王銀文件名: SqlScript.cs創(chuàng)建時間： 2022/6/13 文件功能描述: Unity連接外網(wǎng)數(shù)據(jù)庫，建表，增刪改查等操作Copyright ? 2022年 王銀 All rights reserved. ----------------------------------------------------------------*/ using UnityEngine; using System; using System.Data; public class SqlScript : MonoBehaviour {void Start(){try{SqlTools sql = new SqlTools();//創(chuàng)建數(shù)據(jù)表，各數(shù)據(jù)非空，主鍵id自增加sql.CreateTableAutoID("newtextbook", new string[] { "id", "name", "content", "other" }, new string[] { "int", "text", "longtext", "text" });//插入數(shù)據(jù)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" });//查詢數(shù)據(jù)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]);}}//修改數(shù)據(jù)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]);}//刪除數(shù)據(jù)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]);}}//關閉數(shù)據(jù)連接sql.Close();}catch (Exception e){Debug.LogError(e.Message);}} }

效果圖

輸出的紅色部分為數(shù)據(jù)庫語句，普通的為正常?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

總結(jié)

以上是生活随笔為你收集整理的Unity 2021连接外网数据库，建表，增删改查遇到的问题记录的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。