Unity MySql 增删改查

查看：704 |回复：4 | *2021-9-2 09:31:36* 519580 · 2021-9-2 09:31:36

您需要登录才可以下载或查看，没有账号？注册

x

本帖最后由 MrTang1988 于 2021-9-2 09:42 编辑

(仅供参考~~~~~~)
1、安装MysQl
https://blog.csdn.net/weixin_42869365/article/details/83472466
2、下载安装
Navicat for MySQL9.0
3、Unity中从新建表到增删改查（Plugins文件下导入相关DLL）I18N.Dll、I18N.West.Dll、MySql.Data.Dll、System.Data.Dll、System.Drawing.Dll4、代码实现部分

using UnityEngine;
using System;
using System.Data;
using System.Collections;
using MySql.Data.MySqlClient;
using MySql.Data;
using System.IO;
public class SqlAccess
{
public static MySqlConnection dbConnection;
//如果只是在本地的话，写localhost就可以。
static string host = "localhost";
//如果是局域网，那么写上本机的局域网IP
//static string host = "192.168.1.106";
static string id = "root";
static string pwd = "root";
//数据库名
static string database = "";
public SqlAccess()
{
OpenSql();
}
public static void OpenSql()
{
try
{
string connectionString = string.Format("Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3};", host, database, id, pwd, "3306");
dbConnection = new MySqlConnection(connectionString);
dbConnection.Open();
}
catch (Exception e)
{
throw new Exception("服务器连接失败，请重新检查是否打开MySql服务。" + e.Message.ToString());
}
}
/// <summary>
/// 代码新建数据表
/// </summary>
/// <param name="name">新建表名</param>
/// <param name="col">字段数组及具体字段</param>
/// <param name="colType">字段类型参数对应</param>
/// <returns></returns>
public DataSet CreateTableAutoID(string name, string[] col, string[] colType)
{
if (col.Length != colType.Length)
{
throw new Exception("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] + ")" + ")";
Debug.Log(query);
return ExecuteQuery(query);
}
//插入一条数据，包括所有，不适用自动累加ID。
/// <summary>
/// 插入数据
/// </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 += ")";
Debug.Log(query);
return ExecuteQuery(query);
}
//插入部分ID
/// <summary>
/// 插入数据
/// </summary>
/// <param name="tableName">数据表名称</param>
/// <param name="col">字段名称</param>
/// <param name="values">数据值</param>
/// <returns></returns>
public DataSet InsertInto(string tableName, string[] col, string[] values)
{
if (col.Length != values.Length)
{
throw new Exception("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 += ")";
Debug.Log(query);
return ExecuteQuery(query);
}
/// <summary>
/// 查找数据
/// </summary>
/// <param name="tableName">数据表名称</param>
/// <param name="items">需要查找字段名称</param>
/// <param name="col">查找数据字段使用属性字段</param>
/// <param name="operation">判断字符</param>
/// <param name="values">查找数据字段使用属性字段对应的值</param>
/// <returns></returns>
public DataSet SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
{
if (col.Length != operation.Length || operation.Length != values.Length)
{
throw new Exception("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>
///
/// </summary>
/// <param name="tableName">数据表名</param>
/// <param name="cols">需要更新的字段</param>
/// <param name="colsvalues">需要更新的字段对应的值</param>
/// <param name="selectkey">定位关系数据字段</param>
/// <param name="selectvalue">定位关系数据对应的值</param>
/// <returns></returns>
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);
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="tableName">数据表名</param>
/// <param name="cols">字段</param>
/// <param name="colsvalues">字段对应的值</param>
/// <returns></returns>
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];
}
Debug.Log(query);
return ExecuteQuery(query);
}
public void Close()
{
if (dbConnection != null)
{
dbConnection.Close();
dbConnection.Dispose();
dbConnection = null;
}
}
/// <summary>
/// 执行Sq语句
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static DataSet ExecuteQuery(string sqlString)
{
if (dbConnection.State == ConnectionState.Open)
{
DataSet ds = new DataSet();
try
{
MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
da.Fill(ds);
}
catch (Exception ee)
{
throw new Exception("SQL:" + sqlString + "/n" + ee.Message.ToString());
}
finally
{
}
return ds;
}
return null;
}
}
[code]csharpcode：
using UnityEngine;
using System;
using System.Data;
using System.Collections;
using MySql.Data.MySqlClient;
using MySql.Data;
using System.IO;
public class SqlControll : MonoBehaviour
{
string Error = null;
SqlAccess sql;
void Start()
{
}
/// <summary>
/// 新建表单
/// </summary>
/// <param name="_tableName">表单名称</param>
/// <param name="_field">表单中字段</param>
/// <param name="_fieldType">表单中字段的类型</param>
private void CreateTable(string _tableName,string[] _field,string[] _fieldType)
{
//sql.CreateTableAutoID("momo", new string[] { "id", "name", "qq", "email", "blog" }, new string[] { "int", "text", "text", "text", "text" });
try
{
sql = new SqlAccess();
sql.CreateTableAutoID(_tableName, _field, _fieldType);
sql.Close();
}
catch (Exception e)
{
Error = e.Message;
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="_tableName">表单名称</param>
/// <param name="_field">表单中字段</param>
/// <param name="_fieldType">表单中字段赋值</param>
private void InsertIntoData(string _tableName, string[] _field, string[] _fieldValue)
{
//sql.InsertInto("momo", new string[] { "name", "qq", "email", "blog" }, new string[] { "ruoruo", "34546546", "ruoruo@gmail.com", "xuanyusong.com" });
try {
sql.InsertInto(_tableName, _field, _fieldValue);
sql.Close();
}
catch(Exception e)
{
Error = e.Message;
}
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="_tableName">表单名称</param>
/// <param name="_field">表单中字段</param>
/// <param name="_fieldValue">表单中字段赋值</param>
private void DeleteData(string _tableName, string[] _field, string[] _fieldValue)
{
//sql.Delete("momo", new string[] { "qq", "email" }, new string[] { "34546546", "'000@gmail.com'" });
try
{
sql.Delete(_tableName, _field, _fieldValue);
sql.Close();
}
catch (Exception e)
{
Error = e.Message;
}
}
/// <summary>
/// 更新数据（通过关系字段及值确定在表中的数据组，并更新在数据组中需要更新的数据）
/// </summary>
/// <param name="tableName">数据表名</param>
/// <param name="_field">需要更新的字段</param>
/// <param name="_fieldValue">需要更新的字段对应的值</param>
/// <param name="_verifyField">关系字段</param>
/// <param name="_verifyFieldValue">关系字段值</param>
private void UpdateData(string _tableName, string[] _field, string[] _fieldValue,string _verifyField,string _verifyFieldValue)
{
//sql.UpdateInto("momo", new string[] { "name", "qq" }, new string[] { "'ruoruo'", "'11111111'" }, "email", "'xuanyusong@gmail.com'");
try
{
sql.UpdateInto(_tableName, _field, _fieldValue, _verifyField, _verifyFieldValue);
sql.Close();
}
catch (Exception e)
{
Error = e.Message;
}
}
/// <summary>
/// 查找数据（通过关系字段及值确定在表中的数据组，并查找在数据组中需要查找的数据）
/// </summary>
/// <param name="_tableName"></param>
/// <param name="_field">查找的数据字段</param>
/// <param name="_verifyField">关系字段</param>
/// <param name="_verifyFieldValue">关系字段值</param>
private void SelectData(string _tableName, string[] _field, string[] _verifyField, string[] _verifyFieldValue)
{
//DataSet ds = sql.SelectWhere("momo", new string[] { "name", "email" }, new string[] { "qq" }, new string[] { "=" }, new string[] { "34546546" });
try
{
DataSet ds = sql.SelectWhere(_tableName, _field, _verifyField, new string[] { "=" }, _verifyFieldValue);
sql.Close();
if (ds != null)
{
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
Debug.Log(row[column]);
}
}
}
}
catch (Exception e)
{
Error = e.Message;
}
}
// Update is called once per frame
void OnGUI()
{
if (Error != null)
{
GUILayout.Label(Error);
}
}
}

点击此处复制文本

随浪逐波 · 2021-9-2 17:22:05

感谢分享

Pharmacist · 2021-11-17 16:59:21

VS打开SqlAccess卡了一下然后闪退，是什么缘故？

liubing129 · 2023-3-1 12:08:42

qq_欣欣_wUj · 2023-8-30 09:42:09

[计算机] Unity MySql 增删改查

显示全部标签

最新问题

最热回答

浏览过的版块

短杖

学徒法袍

无色原始矿

火元素

绿母翠晶矿

彗星钛晶矿

翡翠剑

赤铁剑

英雄盾

铁剑

钢盾

钢剑

长剑

水晶剑

血晶杖

碧之轨迹

长枪

双杀

龙枪

魔神战甲

元素之铠

裁决

暴风之眼

秘法之钻

血精之钻

波塞冬之叉

小苹果

黄色药水

绿色药水

蓝色药水

赤色药水

紫色药水

翠晶之钻

守望者【EX】

【绝】守望者【死神】

希望人没事

守望者【猎空】

黑珍珠戒指

玫瑰金项链

烈-红龙战甲

【绝】真-玫瑰金项链

【绝】千人纪念徽章

橙色药水

一星珠

赛博空间

天体物理

粒粒皆辛苦

莆田鞋

魔法扫帚

魔法帽

粉御之钻

青戒之钻

金光靴

碧晶之眸

光之杖

水晶杖

短剑

元素铜币

元素银币

元素金币

元素秘币

巨浪

黄金圣衣

神速靴

圣光之钻

钢铁矿镐

阿尔法晶矿

泰坦高能矿

守望者【天使】

宏观物质

怀特紫星矿

飞雷靴

烈火长枪

青息之水

天蓝靴