最近MOMO身心疲惫。。今天是周末在家无聊我还是决定来学习。不知道学什么，就学MySQL吧。本篇主要记录从MySQL安装到局域网内任意机器连接数据库，也算是对自己学习的总结。今天我没用Mac电脑，而是选择Windows，没有别有用心，而是想熟悉一下Windows下操作Unity。
官网上下载MySQL的安装程序，这里有一篇详细的安装文章，http://www.jb51.net/article/23876.htm 为了让中文完美的运行，配置MySQL的时候Character Set处设置成UTF-8，好像默认是不能显示中文。配置完毕后就可以在本机中启动MySQL，也可以在cmd命令行中start和stop 启动与关闭MySQL。
net start mysql
net stop mysql
为了让本机MySQL数据库可以在局域网中任意机器都可以访问，请看 下面这个网址。
http://dzb3688.blog.163.com/blog/static/105068522201292671444891/
文章有一点点讲的不是很清楚，所以我在补充一下、
我用的是Navicat Pewmium查看数据库，我觉得这个数据库挺好的，因为我在Mac上也是用的这个数据库 。（大家可以在网络上下载它，Windows版本居然有汉化的）如下图所示，点击用户，然后双击”root@%” 最后把主机的名字改成 “%”即可、
下面就是重点了，打开cmd 窗口cd到MySQL的路径下，一定要cd到这个路径下，不然mysql 会是无法识别的指令噢。
然后执行命令：
mysql grant all privileges on *.* to root@”%” identified by ‘abc’ with grant option;
flush privileges;
在执行命令：
mysql flush privileges;
OK这样就行了。
然后开始看看代码怎么写，为了方便数据库的创建、增加、删除、修改、查询、我封装了一个类。欢迎大家测试 啦啦啦啦。
SqlAccess.cs
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 = "1234";
static string database = "xuanyusong";
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());
}
}
public DataSet CreateTable (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];
for (int i = 1; i < col.Length; ++i) {
query += ", " + col[i] + " " + colType[i];
}
query += ")";
return ExecuteQuery(query);
}
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。
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
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);
}
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);
}
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);
}
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;
}
}
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;
}
}
然后在来看看调用，把如下脚本绑定在任意对象即可，调用包括、创建表、插入信息、查找信息、删除信息、更新信息。代码比较简单我就不一一注释了，这里我用try catch如果有错误信息将打印在屏幕中。 创建表包括是否递增ID，所以有两种创建表的方式。如果你的数据库是提前预制的话可以这样来读取数据库。
using UnityEngine;
using System;
using System.Data;
using System.Collections;
using MySql.Data.MySqlClient;
using MySql.Data;
using System.IO;
public class NewBehaviourScript : MonoBehaviour {
string Error = null;
void Start ()
{
try
{
SqlAccess sql = new SqlAccess();
sql.CreateTableAutoID("momo",new string[]{"id","name","qq","email","blog"}, new string[]{"int","text","text","text","text"});
//sql.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"});
sql.InsertInto("momo",new string[]{"name","qq","email","blog"},new string[]{"xuanyusong","289187120","xuanyusong@gmail.com","xuanyusong.com"});
sql.InsertInto("momo",new string[]{"name","qq","email","blog"},new string[]{"ruoruo","34546546","ruoruo@gmail.com","xuanyusong.com"});
DataSet ds = sql.SelectWhere("momo",new string[]{"name","qq"},new string []{"id"},new string []{"="},new string []{"1"});
if(ds != null)
{
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
Debug.Log(row[column]);
}
}
}
sql.UpdateInto("momo",new string[]{"name","qq"},new string[]{"'ruoruo'","'11111111'"}, "email", "'xuanyusong@gmail.com'" );
sql.Delete("momo",new string[]{"id","email"}, new string[]{"1","'000@gmail.com'"} );
sql.Close();
}catch(Exception e)
{
Error = e.Message;
}
}
// Update is called once per frame
void OnGUI ()
{
if(Error != null)
{
GUILayout.Label(Error);
}
}
}然后是用到的dll 一个都不能少，不然会出现各种问题。最后的下载地址我会给出，并且包含这些文件。
为了测试局域网的连接， 我还编译到了Android手机上，在Android上访问这个数据库，也是没问题的。当然手机和电脑用的是同一个wifi网络。 目前这个项目在 Windows 和 Android上都可以很好的运行，我感觉在Mac上和iPhone上应该也木有问题，欢迎大家测试，如果发现在别的平台下有问题请告诉我，我会进一步研究的。 欢迎大家留言，一起学习啦啦啦啦 嘿嘿嘿～～。。
下载地址：http://vdisk.weibo.com/s/B5ac9
- 本文固定链接: https://www.xuanyusong.com/archives/2326
- 转载请注明: 雨松MOMO于 雨松MOMO程序研究院 发表
捐 赠写博客不易，如果您想请我喝一杯星巴克的话？就进来看吧!
下载的地址已经没有了~ MOMO
大佬,我在局域网环境下,U3D(2021.1.7)上用IP可以连接成功,用另一台电脑上的Navicat Premium也可以访问我的MySQL.唯独在安卓端(Android11)上用IP连接MySQL,报错”服务器连接失败,请重新检查是否打开MySq|服务。External
component has thrown an exception.”可以指导一下吗?
请问怎么实现调用信息的更新呢（我现在可以调用出一组存储在mysql中的数据，但没办法实现数据的更新，比如紧接着调用另外一组数据）
原来在unity5中用雨松大大的方法，链接sqlsever，很顺利，升级unity2018，报错:
Microsoft (R) Visual C# Compiler version 2.9.1.65535 (9d34608e)
Copyright (C) Microsoft Corporation. All rights reserved.
error CS1703: Multiple assemblies with equivalent identity have been imported: ‘D:\2020Go\Assets\Plugins\System.Data.dll’ and ‘C:\Program Files\2018.4.14f1\Editor\Data\NetStandard\compat\2.0.0\shims\netfx\System.Data.dll’. Remove one of the duplicate references.