Unity and databases, MySQL to be specific? (Unity, C#, MySQL)

Hello there!
(I hope I chose the right section, scripting with this question.)

I'd like to ask around, weather there were any ways to connect Unity with MySQL?
Most threads I've read suggested using scripts to function as a database, and avoid using actual databases.

The reason I'm still asking about the topic is that, I'm expected to create a functioning application using C# and MySQL during my studies, however I wanted to choose Unity as the main focus.

I'm still in the process of learning Unity basics, however MySQL is a must and unfortunately I'm not allowed to use any scripts for such purposes instead.

So hence, I'd be really grateful for your answers, as I'm pretty new to all of this.

Thanks for reading through it all, and for your answers in advance!
Parathor

 

I don't know why you were told to avoid MySQL -- probably because it's usually overkill for most people, but if you need it, well then you need it.

And yes, using MySQL and Unity together is definitely doable. It's not really a trivial process as there's a fair amount of plumbing involved to get the two to work seamlessly with each other, not to mention setting up the server itself, but it's doable.

Normally you'd need to create a server that communicates with the DB, and your Unity client communicates with the server using the WWW class... but if this is for a school project and you don't expect to host this on the internet anywhere, then you can probably get away with cutting the middleman out and letting Unity and MySQL work directly with each other.

That's one hell of a learning curve tackling Unity, MySQL, and client-server communication though... normally I'd say "Google Unity MySql tutorial" but I did that just now, and found a lot of dead ends.

I'd probably start with this: https://github.com/Hanslen/Unity-with-MYSQL

And then pick it apart to see how it's done.

 

Exactly my case.
This is only supposed to run on localhost, I'm not planning to host it.

I'll go take a brief look at the link.
Thanks for the rapid reply!

p.s: At first I didn't understand why people would rather avoid MySQL either, but I guess by doing that they have less components to worry about and the project is simpler as a whole. (Speaking of local games, which are not supposed to be hosted of course.)

p.p.s: I took a look and I'm even more grateful, it contains everything I'd need, I'll just have to go and understand it all. Takes some time I guess!
Huge thanks to you!!

 

Most games simply don't have the database needs to require a separate standalone database. Often something like SQLite would work just fine or even storing in flat files, and then people attempting to host the game wouldn't have to setup MySQL on their own. I'd only consider using MySQL for a game where multiple servers need access to the same dataset, and even then only if I ruled out a few other alternatives.

 

You can find a procedure in Unity Wiki with PHP, MySQL and C#/JavaScript.

It consists of three steps

1. Create a blank MySQL Database and a table.
2. Create a PHP server side script (This will connect to the MySQL table, receive data from a Unity script (step 3), and query the database (the examples given are either inserting data or selecting)).
3. Create the Unity Controller Script (This will connect to the PHP script created in step 2).

 

I have been looking for this for so long as a direct connect. It was always easy to work with in LiveCode to connect to MySQL directly without having to setup a server or use PHP. The GitHub script though gives me errors when i load it in.


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: 'C:\Users\St Francis 01\Desktop\AGD\MySQL\Link To MySQL\Assets\Plugins\System.Drawing.dll' and 'C:\Program Files\Unity\Hub\Editor\2019.3.13f1\Editor\Data\NetStandard\compat\2.0.0\shims\netfx\System.Drawing.dll'. Remove one of the duplicate references.
error CS1703: Multiple assemblies with equivalent identity have been imported: 'C:\Users\St Francis 01\Desktop\AGD\MySQL\Link To MySQL\Assets\Plugins\System.Data.dll' and 'C:\Program Files\Unity\Hub\Editor\2019.3.13f1\Editor\Data\NetStandard\compat\2.0.0\shims\netfx\System.Data.dll'. Remove one of the duplicate references.


Does this mean that Unity had already implemented it directly? i cant find documentation on it

 

Well, see https://www.mono-project.com/docs/database-access/providers/mysql/
Many people uses PHP to do bridge, but the correct is to use the driver and connect directly,(why PHP ? No sense) so you can have your game server linked directly to MySQL server and your client game connecting your game server using Mirror or Telephaty p.e.
(good too, to see https://github.com/freseco/Unity3d_MySQL_Client)

 

And what about security? The data in the database can be hacked by having the connection data directly on the client right?

 

For a local database (I.E: hosted on the client's machine), yes, though it's not really "hacking".
If you need data to be secure, never save it client-side.

Secure data should be stored on a server-side database, and the client shouldn't directly connect to that database, rather it should connect to another server-side web API that acts as the middle-man between the client and the database.

But, this really all depends on what data you want to secure, and if it's even worth the trouble setting up a server hosting environment.

If you need to save personal user information such as emails, passwords, addresses, identity, etc. then yes, server-side data storage is an absolute must.
If you're just saving general game data such as player information, levels, unlocks, items, etc. then server-side data is not really needed. Sure, the user could modify the data in the client database to cheat, but if it's just a single-player game, then it's really not an issue. They aren't doing any real harm by cheating in a single-player game.

 

Thanks for reply, i am referring to that @RNOVAKOSKY refers to the fact that the MYSQL driver must be used directly in the project, the thing is ... Then a server and a client must be made separately, right? Because if the connection data is visible on the client it is possible that they will be able to reverse engineer and extract the data from the database.

Regarding the type of data ... Let's say it would be for an MMORPG

 

For an MMO, I'd only let the servers talk to the DB. The client talks to the game servers, the game servers talk to the DB, the game servers tell the client only what data from the DB the client needs to know. The client doesn't have direct access to the DB, in fact the DB should be configured so only the game server IP's can even talk to it.

YMMV

 

Anyone get this github link to work. i removed the assemblies that gave errors. looks like unity HUB has similar ones. i could try and remove them instead.

But i'm getting error connecting to Mysql localhost. it appears to be up and running. i can connect to it. i tried localhost. mysql@localhost and mysql@localhost:3306 in case it needed the port. yes i named mine mysql. i just want to make it work for me. not going to be in game. only need for backend editor app im working on.

oh error i get is could not resolve host

p.s. im trying 127.0.0.1 with same error. Since that is local host ip

oh 127.0.0.1 works. then utf8 needed to be set.

now it seems to run but nothing is adding to db.....getting closer. This code could work

Haha so far so good, successful writing to DB. All i kept from that github was the MySql.data.dll. so the github is probably not needed. could of just grabbed mysql.data.dll from the mysql installer? But the simple script in the repository can show how to do basic sql setup in C# so that is good.. The other two DLL's are already built into unity HUB. so if you use that it should already be in your project.

p.s. I'm running Unity beta 2021.2.0b2.3053

p.p.s. im writing texture images to db in thumbnail size so i can view in editor window faster. 4k files are large!