Unity連接MySQL及寫入資料與查詢

請下載 Xmpp 軟體
如果要解決中文上的問題，安裝後請到: D:\xmpp\mysql\bin (本文章是安裝在D曹)
此路徑下找到my.ini檔案下進行更改。
更改內容:
[mysqld]
default-character-set=utf8

[client]
default-character-set=utf8
init_connect='SET NAMES utf8'

再開啟MySql:

開啟後建立自己的資料庫: 本文資料庫名稱 corpus
再建立兩欄資料表 指令:

create table 資料表名稱(
ID integer auto_increment primary key,
Message VARCHAR(20));

若是資料表調整錯誤可以進行網頁頁面更正:

標記框框(解碼中文)

補充:
自動編號排列指令:
ALTER TABLE test AUTO_INCREMENT = 0

凍仁大大整理的筆記之分享(MySql 指令)~~

Unity(2018版本) 連接 MySQL 套件這裡提供兩個地方載點:

載點1 

提取码：6rbi

載點2

----------------------------

Unity 操作(Script):

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

public class DataBaseTest : MonoBehaviour
{
    private bool writeDataBaseTable = false;
    private string id = "ID(不必輸入)", message = "請輸入訊息";

    void OnGUI()
    {
        // DataBase 連線按鈕
        GUILayout.BeginArea(new Rect(Screen.width / Screen.width, Screen.height / Screen.height, 200, 30));
        if (GUILayout.Button("Open DataBase InterNet"))
            openDataBase();
        GUILayout.EndArea();

        // DataBase 連線訊息
        GUILayout.BeginArea(new Rect(Screen.width / Screen.width, Screen.height / Screen.height * 30, 200, 30));
        GUILayout.Label(CMySql.result);
        GUILayout.EndArea();

        // 連接DataBase後事件
        if (CMySql.result != "")
        {
            GUILayout.BeginArea(new Rect(Screen.width / Screen.width, Screen.height / Screen.height * 60, 100, 30));
            if(GUILayout.Button("寫入資料")){
                writeDataBaseTable = true;
            }
            GUILayout.EndArea();
        }

        // 寫入資料庫 UI
        if (writeDataBaseTable)
            writeDataUI(); 
    }

    /// 

    /// 寫入資料
    /// 
    void writeDataUI()
    {
        GUILayout.BeginArea(new Rect(Screen.width / Screen.width, Screen.height / Screen.height * 90, 100, 30));
        id = GUILayout.TextField(id, 200);
        GUILayout.EndArea();

        GUILayout.BeginArea(new Rect(Screen.width / Screen.width * 100, Screen.height / Screen.height * 90, 100, 30));
        message = GUILayout.TextField(message, 200);
        GUILayout.EndArea();

        GUILayout.BeginArea(new Rect(Screen.width / Screen.width, Screen.height / Screen.height * 120, 100, 30));
        if (GUILayout.Button("寫入"))
            CMySql.WriteTable( "test" ,id, message);
        GUILayout.EndArea();

        GUILayout.BeginArea(new Rect(Screen.width / Screen.width * 100, Screen.height / Screen.height * 120, 100, 30));
        if (GUILayout.Button("取消"))
            writeDataBaseTable = false;
        GUILayout.EndArea();

    }
    

    /// 

    /// 關閉應用程式
    /// 
    void OnApplicationQuit()
    {
        CMySql.closeSqlConnection();
    }

    /// 

    /// openDataBase
    /// 
    void openDataBase()
    {
        string connectionString = string.Format("Server = {0}; Database = {1}; UserID = {2}; Password = {3};", CMySql.hostType, CMySql.dataBaseType, CMySql.idType, CMySql.passWordTpye);
        CMySql.openSqlConnection(connectionString);
        CMySql.myObjType = CMySql.GetDataSet(connectionString);
        Debug.Log(CMySql.result);
    }
}

資料庫 Script (C#):

/**
 * 2017/8/30 8:10
 * 連接Mysql
 * 寫入資料(中文解決), 讀取資料
 * MySql.Data.MySqlClient download path: https://dev.mysql.com/downloads/connector/net/1.0.html
 * MySql.Data.MySqlClient download path: http://pan.baidu.com/s/1gePtERT 
 */

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

public class CMySql
{

    // Just like MyConn.conn in Story Tolls before
    public static MySqlConnection dbConnection;

    // DataBase Ip
    private static string host = "127.0.0.1";
    public static string hostType
    {
        set { host = value; }
        get { return host; }
    }

    // DataBase user Id
    private static string id = "root";
    public static string idType
    {
        set { id = value; }
        get { return id; }
    }

    // DataBase Password
    private static string passWord = "";
    public static string passWordTpye
    {
        set { passWord = value; }
        get { return passWord; }
    }

    // DataBase Name
    private static string dataBase = "corpus";
    public static string dataBaseType
    {
        set { dataBase = value; }
        get { return dataBase; }
    }

    // 連線結果
    public static string result = "";

    // 讀取資 (欄位2)
    static List MessageField2 = new List();

    // DataSet
    private static DataSet myObj;
    public static DataSet myObjType
    {
        set { myObj = value; }
        get { return myObj; }
    }

    // Connect to database
    public static void openSqlConnection(string connectionString)
    {
        dbConnection = new MySqlConnection(connectionString);
        dbConnection.Open();
        result = dbConnection.ServerVersion;
    }

    // closeSql
    public static void closeSqlConnection()
    {
        dbConnection.Close();
        dbConnection = null;
    }

    // MySQL Query
    public static void doQuery(string sqlQuery)
    {
        IDbCommand dbCommand = dbConnection.CreateCommand();
        dbCommand.CommandText = sqlQuery;
        IDataReader reader = dbCommand.ExecuteReader();
        reader.Close();
        reader = null;
        dbCommand.Dispose();
        dbCommand = null;
    }

    #region Get DataSet
    public static DataSet GetDataSet(string sqlString)
    {
        DataSet ds = new DataSet();

        try
        {
            MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
        }
        catch (Exception e)
        {
            throw new Exception("SQL:" + sqlString + "\n");
            e.Message.ToString();
        }
        return ds;
    }
    #endregion

    // 轉utf8
    public static string messageToUtf8(string message)
    {
        UTF8Encoding encoder = new UTF8Encoding();
        byte[] bytes = Encoding.UTF8.GetBytes(message);
        string utf8ReturnString = encoder.GetString(bytes);

        return utf8ReturnString;
    }

    static string ID;
    static string Message;

    // 寫入資料表
    public static void WriteTable(string DataBaseTable ,string id, string message)
    {
        MySqlCommand command = dbConnection.CreateCommand();
       
        string sqlText = "select count(1) from " + DataBaseTable + " where Message=('" + message + "')";

        MySqlCommand cmd1 = new MySqlCommand(sqlText, dbConnection);
        int count = (int)(long)cmd1.ExecuteScalar();
        
        if (count > 0)
        {
            Debug.Log("已經有資料囉");
        }
        else
        {
            ID = id;
            Message = messageToUtf8(message);

            command.CommandText = "Insert into " + DataBaseTable + "(ID, Message) value('" + ID + "','" + Message + "')";
            command.ExecuteNonQuery();

            Debug.Log("載入資料成功");
        }
    }

    /// 

    /// 資料庫查詢
    /// 
    /// 
    /// 
    public static void INQUIRE_TABLE(string dataBaseTitle, string message)
    {
        string sqlText = "select * from " + dataBaseTitle + " where Message='" + message + "'";

        MySqlCommand cmd = new MySqlCommand(sqlText, mySqlConnection);
        MySqlDataReader data = cmd.ExecuteReader();

        while (data.Read())
        {
            try
            {
                UnityEngine.Debug.Log((data[0] + " -- " + data[1]));
            }catch(Exception e)
            {
                data.Close();
                closeSqlConnection();
            }
        }

        data.Close();
    }
}

結果圖:
※寫入資料後，記得Mysql 要重新讀取一次才會出現。

第二筆資輸入:

若是資料重復: