Unity之连接Mysql

一、下载dll

不同版本的dll文件也不同，我使用的是2019版本Unity

链接：https://pan.baidu.com/s/1wnbryEvMBkkLJJ9DjwMbiQ?pwd=syq1 
提取码：syq1

二、 脚本

1.数据库控制器 

using System;
using System.Data;
using MySql.Data.MySqlClient;
using UnityEngine;
using System.Text;


/// <summary>
/// MySql控制器
/// </summary>
public class MySqlController 
{
	public static MySqlConnection dbConnection;
	/// <summary>
	/// 构造连接数据库
	/// </summary>
	/// <param name="host">主机名 localhost</param>
	/// <param name="port">端口</param>
	/// <param name="username">用户名</param>
	/// <param name="pwd">名字</param>
	/// <param name="database">数据库名称</param>
	public MySqlController(string host, string port, string username, string pwd, string database)
	{
		//连接数据库
		try
		{
			string connectionString = string.Format("server = {0};port={1};database = {2};user = {3};password = {4};", host, port, database, username, pwd);
			Debug.Log(connectionString);
			dbConnection = new MySqlConnection(connectionString);
			dbConnection.Open();
			Debug.Log("连接成功！");
		}
		catch (Exception e)
		{
			throw new Exception("连接失败！" + e.Message.ToString());
		}
	}

	#region 关闭连接
	/// <summary>
	/// 关闭连接
	/// </summary>
	public void Close()
	{
		if (dbConnection != null)
		{
			dbConnection.Close();
			dbConnection.Dispose();
			dbConnection = null;
		}
	}
	#endregion

	#region 执行sql语句
	/// <summary>
	/// 执行sql语句
	/// </summary>
	/// <param name="sqlString">sql语句</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;
	}
	#endregion

}

2.测试

using System.Data;
using UnityEngine;

public class TestSql : MonoBehaviour
{
    public string host = "localhost"; //主机
    public string port = "3306";      //端口
    public string username = "root";  //用户名
    public string pwd = "admin";      //名字
    public string database = "zhaocha";  //数据库名称

    void Start()
    {
        MySqlController sql = new MySqlController(host, port, username, pwd, database);
        string sqlPath;
        //string sqlPath = $"select * FROM user"; //查询
        //sqlPath = $"select user,password FROM user"; //查询2
        //sqlPath = $"select user,password FROM user where id=1"; //条件查询2

        sqlPath = $"INSERT INTO user VALUES(NULL, \"孙\",123456)"; //插入
        //sqlPath = $"INSERT INTO user(id,user,password) values (null,\"孙\",123456)"; //插入2
        //sqlPath = $"INSERT INTO user(id,user,password) values (null,\"孙3\",123456),(null,\"孙2\",123456)"; //多字段插入2

        //sqlPath = $"UPDATE user SET user=\"孙2\", password=777777 WHERE id=3"; //修改

        //sqlPath = $"Delete from user"; //删除数据
        //sqlPath = $"Delete from user WHERE id=3"; //条件删除数据




        DataSet ds = MySqlController.ExecuteQuery(sqlPath);//替换表名test
        Debug.Log(ds.HasErrors); //是否有错误
        if (ds != null&& ds.Tables.Count>0)
        {
            DataTable table = ds.Tables[0];//选择表
            foreach (DataRow row in table.Rows) //循环每一行
            {
                string str = "";
                foreach (DataColumn column in table.Columns) //循环每一列
                    str += row[column] + " ";
                Debug.Log(str);
            }
        }
    }

    private void OnApplicationQuit()
    {
        MySqlController.Close();
    }

}

三、数据库sql语句

1.创建表： CREATE TABLE "Test" ("id" INTEGER, "name" TEXT);

2.查看表数据：SELECT * FROM "TestTabel";

3.删除表的所有数据：Delete from TestTabel

4.删除数据库中的表：Drop table TestTable

5.插入数据：INSERT INTO student VALUES(1, "张三")  **方式2**INSERT INTO user(user,parssword,type) values ("孙",123456,"用户");

6.更新数据：UPDATE user SET user="孙2", password=777777 WHERE id=3

7.获取表结构：PRAGMA table_info testTabel

8.获取当前刚插入的id自增值：select last_insert_id(); 注意事项1.字段id是自增属性，2.刚刚执行完插入操作

9.去重插入数据 NOT EXISTS：INSERT INTO namelibrary(id,user,state) SELECT null, '222',TRUE WHERE NOT EXISTS(SELECT id,`user`,state FROM namelibrary WHERE `user` = '222');

此效果是在namelibrary表中不存在user等于222的值时插入，否则不执行