Unity连接Mysql数据库 增 删 改 查(分时间段取数据)
1.unity连接数据库(mysql)需要的dll文件
下载链接: 直接复制下载即可
https://files.cnblogs.com/files/qq2351194611/Plugins%E8%BF%9E%E6%8E%A5mysqldll%E6%96%87%E4%BB%B6.zip
如果是android端需要全部放入,并且需要对应unity版本,去unity安装目录下找到拖进去即可,
如果是PC端只需要一个Mysql.data.dll 文件就行 ,我的版本是Unity2019.4.5f1的, 设置里面.net设置成4.x,不然会报错
下面上代码
两个脚本都能连接成功并读取添加修改(别同时挂载,同时在应该会出BUG,可以分开试验)
1.
using MySql.Data.MySqlClient; using System.Collections; using System.Collections.Generic; using UnityEngine; using UnityEngine.UI; public class Connect_Mysql { public bool login_success = false; public bool register_success = false; //数据库表中的数据 名字必须是一样的 要不然上传不上去 public int id; public string username; public string password; //登录验证 public void Click_login(string username, string password) { // server连接地址 port端口号 database表单名 user用户名 password数据库密码 // 用户名一般都是root 你修改了就换成你修改的名称跟密码 string sqlSer = "server =127.0.0.1;database = tool;user = root;password =123456;"; //string sqlSer = "server = localhost" + // "port = 3306;" + // "database = tool;" + // "user =root;" + // "password =123456;"; MySqlConnection conn = new MySqlConnection(sqlSer); try { conn.Open(); Debug.Log("------连接成功------"); //user 表名 string sqlQuary = "select * from user where username =@paral1 and password = @paral2"; MySqlCommand comd = new MySqlCommand(sqlQuary, conn); comd.Parameters.AddWithValue("paral1", username); comd.Parameters.AddWithValue("paral2", password); MySqlDataReader reader = comd.ExecuteReader(); if (reader.Read()) { Debug.Log("------用户存在，登录成功！------"); //进行登入成功后的操作，例如进入新场景。。。 login_success = true; } else { Debug.Log("------用户不存在，请注册。或请检查用户名或和密码！------"); login_success = false; } } catch (System.Exception e) { Debug.Log(e.Message); } finally { conn.Close(); } } //注册验证 public void Click_register(string username, string password) { string sqlSer = "server =127.0.0.1;database = tool;user = root;password =123456;"; MySqlConnection conn = new MySqlConnection(sqlSer); try { conn.Open(); Debug.Log("-----连接成功！------"); string sqlQuary = "select * from user where username =@paral1 and password = @paral2"; MySqlCommand comd = new MySqlCommand(sqlQuary, conn); comd.Parameters.AddWithValue("paral1", username); comd.Parameters.AddWithValue("paral2", password); //comd.Parameters.AddWithValue("paral0", 0);id=@para10 and MySqlDataReader reader = comd.ExecuteReader(); if (reader.Read()) { Debug.Log("-----用户名已存在，请重新输！------"); register_success = false; } else { Insert_User(username, password); Debug.Log("------注册成功，请进行登入------"); register_success = true; } } catch (System.Exception e) { Debug.Log(e.Message); } finally { conn.Close(); } } //插入用户 private void Insert_User(string username, string password) { string sqlSer = "server =127.0.0.1;database = tool;user = root;password =123456;"; MySqlConnection conn = new MySqlConnection(sqlSer); try { conn.Open(); string sqlInsert = "insert into user(username,password) values('" + username + "','" + password + "')"; MySqlCommand comd2 = new MySqlCommand(sqlInsert, conn); int resule = comd2.ExecuteNonQuery(); Debug.Log("添加成功"); } catch (System.Exception e) { Debug.Log(e.Message); } finally { conn.Close(); } } } public class LoginZhuCe : MonoBehaviour { public static LoginZhuCe _instance; public Text login_tips; public GameObject login_panel; public GameObject zuce_panel; //登录 输入框 public InputField input_username; public InputField input_password; //注册 输入框 public InputField iusername; public InputField ipassword; public string username; public string password; Connect_Mysql connect = new Connect_Mysql(); private void Awake() { _instance = this; } public void Panel_bool(bool ol) { if (ol) { login_panel.SetActive(false); zuce_panel.SetActive(true); } else { login_panel.SetActive(true); zuce_panel.SetActive(false); } } //当点击登录按钮时 public void WhenClick_login() { username = input_username.text.ToString(); password = input_password.text.ToString(); connect.Click_login(username, password); if (connect.login_success) { login_tips.text = "登录成功！"; When_login_success(); } else { login_tips.text = "登录失败！"; Invoke("InvokeFouncation", 1.5f); } } //当点击注册按钮时 public void WhenClick_register() { username = iusername.text; password = ipassword.text; connect.Click_register(username, password); if (connect.register_success) { login_tips.text = "注册成功！"; Invoke("InvokeFouncation", 1.5f); } else { login_tips.text = "注册失败！"; Invoke("InvokeFouncation", 1.5f); } } //点击登录与注册按钮出现的成功与失败提示 private void InvokeFouncation() { login_tips.text = ""; } //登入成功 private void When_login_success() { Debug.Log("登入成功哦"); } }
2.
using MySql.Data.MySqlClient; using System.Collections; using System.Collections.Generic; using UnityEngine; /// <summary> /// 连接数据库 包括增删改查功能 /// </summary> public class LianJie : MonoBehaviour { //database代表你要连接的数据库的名字 private const string datebase = "server=127.0.0.1;user=wcl;password=123456;database=tool;"; private MySqlConnection connection = null; string sql; public bool kaiqi = false; private int time, temperature, humidity; private string timee; void Start() { connection = new MySqlConnection(datebase); connection.Open(); kaiqi = true; //查询 表中数据 // sql = "select * from user"; //查询某一段时间的数据的第一种方法 from大小写无影响 //sql = "select * FROM user where time between '2021-6-21 09:45:00' and '2021-6-21 09:47:00'"; //query(sql); //查询某一段时间的数据的第二种方法 sql = "SELECT * from user where time >= '2021-6-21 09:45:00' and time < '2021-6-21 09:47:00'"; query(sql); //插入 //sql = "insert into user values('3','我的','123')"; //insert(sql); //sql = "delete from user where id = 2"; //delete(sql); //sql = "select * from user"; //query(sql); //修改 //sql = "update user set username = '100' where id = 78"; //updateData(sql); //StartCoroutine("ZengJia"); } //写入点 public void Insertting(string time,int username,int password) { //string sqlInsert = "insert into user values('"+ time+"','" +username+"','"+password+"')"; connection = new MySqlConnection(datebase); connection.Open(); string sqlInsert = "insert into user(time,username,password) values('" + time + "','" + username + "','" + password + "')"; MySqlCommand comd2 = new MySqlCommand(sqlInsert, connection); int resule = comd2.ExecuteNonQuery(); Debug.Log("添加成功"); //connection.Close(); } IEnumerator ZengJia() { while (kaiqi) { time += 15; timee = System.DateTime.Now.ToLocalTime().ToString ("yyyy-MM-dd HH:mm:ss"); temperature = Random.Range(0, 301); humidity = Random.Range(0, 50); Insertting(timee,temperature, humidity); yield return new WaitForSeconds(5f); } StopAllCoroutines(); connection.Close(); } private void OnDisable() { kaiqi = false; connection.Close(); } //查询 private void query(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { print("id:" + reader.GetInt32("id") + "time:" + reader.GetString("time") + "username:" + reader.GetString("username") + " password" + reader.GetString("password")); } command.Dispose(); } //插入 private void insert(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); int n = command.ExecuteNonQuery(); if (n > 0) print("插入成功"); else print("插入失败"); command.Dispose(); } //删除 private void delete(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); int n = command.ExecuteNonQuery(); command.Dispose(); } //改 private void updateData(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); int n = command.ExecuteNonQuery(); command.Dispose(); } }
数据库表截图
下面把完整项目包(登录注册)附上
下载地址:
https://files.cnblogs.com/files/qq2351194611/%E7%99%BB%E5%BD%95%E6%B3%A8%E5%86%8C%E8%BF%9E%E6%8E%A5Mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E8%B5%84%E6%BA%90%E5%8C%85.rar
下载完成,新建unity项目导入即可
windform连接数据库操作多行数据(增 删 改 查) 原理都一样 记录一下方便使用
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; using System.Data; using MySql.Data.MySqlClient; using System.Windows.Forms; using System.Configuration; using System.IO; namespace WindowsFormsDemo { public class YF { public string Oilname; public string Oilexplain; } public class Airft { public string Oilname; public string aircraftName; public string aircraftExplain; } public class Device { public string aircraftName; public string deviceName; public string deviceExplain; } public class Accessory { public string deviceName; public string accessoryType; public string accessoryName; public string accessoryExplain; public string accessoryFile; public string accessoryMatter; public string accessoryStructure; public string accessoryAdmin; } public class Dao { public List<YF> YF_List = new List<YF>(); //油类型列表 public List<Airft> Airfr_List = new List<Airft>(); //飞机类型列表 public List<Device> Device_List = new List<Device>(); //类型列表 public List<Accessory> Accessory_List = new List<Accessory>(); //附件列表 public String connetStr = "server=127.0.0.1;user=wcl;password=123456;database=tool;"; //127.0.0.1代表localhost本地；port端口号；user 和 pwd 按自己的数据库连;数据库名称也写自己的。 private MySqlConnection connection = null; public void Connect_() { connection = new MySqlConnection(connetStr); try { connection.Open(); System.Diagnostics.Debug.WriteLine("数据库连接成功"); //insert("insert into yf values('1','我的','123')"); //query("select * from yf"); } catch (MySqlException ex) { System.Diagnostics.Debug.WriteLine(ex.Message ); MessageBox.Show("数据库连接失败，请稍后重试！"); } } //插入 public void insert(string sql) { connection = new MySqlConnection(connetStr); connection.Open(); MySqlCommand command = new MySqlCommand(sql, connection); int n = command.ExecuteNonQuery(); if (n > 0) System.Diagnostics.Debug.WriteLine("插入成功"); else System.Diagnostics.Debug.WriteLine("插入失败"); command.Dispose(); } public string images; public void query_photo(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { images = reader.GetString ("image_"); } reader.Close(); command.Dispose(); } //查询 油封的 public void query(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { YF yF = new YF(); yF.Oilname = reader.GetString("Oilname"); yF.Oilexplain = reader.GetString("Oilexplain"); YF_List.Add(yF); //System.Diagnostics.Debug.WriteLine(reader.GetString("Oilname") + "Oilexplain:" + reader.GetString("Oilexplain")+" "+ reader.GetString("aircraftName") // + " " + reader.GetString("deviceName")); //System.Diagnostics.Debug.WriteLine( // "Oilname:" + reader.GetString("aircraftName") + "Oilexplain:" + reader.GetString("aircraftExplain")); } reader.Close(); command.Dispose(); } //查询 飞机的 public void query_feiji(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Airft yF = new Airft(); yF.Oilname = reader.GetString("Oilname"); yF.aircraftName = reader.GetString("aircraftName"); yF.aircraftExplain = reader.GetString("aircraftExplain"); Airfr_List.Add(yF); } reader.Close(); command.Dispose(); } //查询 类型的 public void query_leixing(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Device yF = new Device(); yF.aircraftName = reader.GetString("aircraftName"); yF.deviceName = reader.GetString("deviceName"); if (reader.GetString("deviceExplain")!="") { yF.deviceExplain = reader.GetString("deviceExplain"); } Device_List.Add(yF); } reader.Close(); command.Dispose(); } //查询附件表 把附件数据全部保存下来 public void query_fujian(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Accessory yF = new Accessory(); yF.deviceName = reader.GetString("deviceName"); yF.accessoryType = reader.GetString("accessoryType"); yF.accessoryName = reader.GetString("accessoryName"); yF.accessoryExplain = reader.GetString("accessoryExplain"); yF.accessoryFile = reader.GetString("accessoryFile"); yF.accessoryMatter = reader.GetString("accessoryMatter"); yF.accessoryStructure = reader.GetString("accessoryStructure"); yF.accessoryAdmin = reader.GetString("accessoryAdmin"); Accessory_List.Add(yF); } reader.Close(); command.Dispose(); } //查询附件数据库直接显示到form上面 public void query_fujianXianShi(ref string accessoryFile,ref string accessoryMatter,ref string accessoryStructure,ref string accessoryAdmin,string congfu) { MySqlCommand command = new MySqlCommand("select * from accessory", connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { if (congfu == reader.GetString("accessoryType")) { accessoryFile = reader.GetString("accessoryFile"); accessoryMatter = reader.GetString("accessoryMatter"); accessoryStructure = reader.GetString("accessoryStructure"); accessoryAdmin = reader.GetString("accessoryAdmin"); } } reader.Close(); command.Dispose(); } //写入油封的点 sql = "insert into imgtable values(@buffer)"; //sql="insert into imgtable (names,password) values(@name,@psw)" //cmd.Parameters.Add("@buffer",buffer); public void Insertting_Youfeng(string Oilname, string Oilexplain) { connection = new MySqlConnection(connetStr); connection.Open(); string sqlInsert = "insert into yf(Oilname,Oilexplain) values('" + Oilname + "','" + Oilexplain + "')"; MySqlCommand comd2 = new MySqlCommand(sqlInsert, connection); int resule = comd2.ExecuteNonQuery(); System.Diagnostics.Debug.WriteLine("添加成功"); //connection.Close(); } //写入飞机的点 public void Insertting_Feiji(string Oilname, string aircraftName,string aircraftExplain) { connection = new MySqlConnection(connetStr); connection.Open(); string sqlInsert = "insert into aircraft(Oilname,aircraftName,aircraftExplain) values('" + Oilname + "','" + aircraftName + "','" + aircraftExplain + "')"; MySqlCommand comd2 = new MySqlCommand(sqlInsert, connection); int resule = comd2.ExecuteNonQuery(); System.Diagnostics.Debug.WriteLine("添加成功"); //connection.Close(); } //写入设备类型的点 public void Insertting_shebei(string aircraftName, string deviceName, string deviceExplain) { connection = new MySqlConnection(connetStr); connection.Open(); string sqlInsert = "insert into devicetype(aircraftName,deviceName,deviceExplain) values('" + aircraftName + "','" + deviceName + "','" + deviceExplain + "')"; MySqlCommand comd2 = new MySqlCommand(sqlInsert, connection); int resule = comd2.ExecuteNonQuery(); System.Diagnostics.Debug.WriteLine("添加成功"); //connection.Close(); } //写入附件的点 public void Insertting_fujian(string deviceName, string accessoryType, string accessoryName,string accessoryExplain,string accessoryFile,string accessoryMatter, string accessoryStructure, string accessoryAdmin) { connection = new MySqlConnection(connetStr); connection.Open(); string sqlInsert = "insert into accessory(deviceName,accessoryType,accessoryName,accessoryExplain," + "accessoryFile,accessoryMatter,accessoryStructure,accessoryAdmin) values('" + deviceName + "','" + accessoryType + "','" + accessoryName + "'" + ",'" + accessoryExplain + "','" + accessoryFile + "','" + accessoryMatter + "','" + accessoryStructure + "','" + accessoryAdmin + "')"; MySqlCommand comd2 = new MySqlCommand(sqlInsert, connection); //comd2.Parameters comd2.ExecuteNonQuery(); System.Diagnostics.Debug.WriteLine("添加成功"); //connection.Close(); } //删除 sql = "delete from user where id = 2"; 删除user 表里id是2的所有数据 public void delete(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); command.ExecuteNonQuery(); command.Dispose(); } //改 //sql = "update user set username = '100' where id = 78"; //更新 某一行的数据 //dao.updateData("update accessory set accessoryExplain ='" + Accessory_._instance.附件说明textBox4.Text + "'," + // "accessoryFile ='" + Accessory_._instance.Path_PinJie(Accessory_._instance.pathfile) + "'," + // "accessoryMatter ='" + Accessory_._instance.stream1 + "'," + // "accessoryStructure ='" + Accessory_._instance.stream2 + "'," + // "accessoryAdmin ='" + Accessory_._instance.stream3 + "' where " + // "accessoryType ='" + Form1._instance.treeView1.SelectedNode.Text + "'"); public void updateData(string sql) { MySqlCommand command = new MySqlCommand(sql, connection); int n = command.ExecuteNonQuery(); command.Dispose(); } } }
关于mysql的操作指令
登录mysql:mysql -u root -p
mysql密码:123456
开启服务:net start mysql
关闭服务:net stop mysql
修改密码：alter user 'root'@'localhost' identified by 'root'; ('root'是密码 修改这个即可)
打包数据库 :mysqldump -uroot -p dbname >db.sql
