1.unity连接数据库(mysql)需要的dll文件
下载链接: 直接复制下载即可
如果是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();
}
}
增删改插还有分时间段取数据
数据库表截图
下面把完整项目包(登录注册)附上
下载地址:
下载完成,新建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