MySQL | How to store insanely long strings ?

[Aleksa Djordjic]

(We use C#)

We are making an app which tracks your jobs from Euro Truck Simulator 2.

Currently, we have our database setup and how we are tracking our jobs.

 

How we intended to save them was in this format for example (All in 1 long string) :

[J]source/destination/distance/income[J]source/destination..... etc , you get the point ...

So that we can use string.Split("[J]") and get all of the jobs, and then string.Split('/') got split each job in to its data.

 

But the problem is , as far as i understand , types like Varchar , Text, LongText (or whatever they are called) have a maximum data limit.... right ?

So if someone has a ton of jobs, he will eventually reach that limit...... that is bad ......

 

How can we avoid this data limit ?

[vorticalbox]

Use a noSql database and store everyrhing as json objects sun as mongo db, which has a document size of 13mb

[Aleksa Djordjic]

1 minute ago, vorticalbox said:

Use a noSql database and store everyrhing as json objects sun as mongo db, which has a document size of 13mb

But we need all of the data to be stored in a single MySQL database ....

[colonel_mortis]

The maximum size of LONGTEXT is 4GB, which you're not going to hit if you're storing data in that format (and MEDIUMTEXT is 16MB, which will still be plenty).

 

However, you're almost certainly doing things wrong to store it in that format - the conventional format is to have a table with one row per job, and one column per field (source, distance, etc). That way, you're not limited in how many jobs you can store (aside from by the size of your disk), and you can choose to only load the records that are relevant, rather than every record every time (which would become a bottleneck before you hit the 4GB limit).

[Aleksa Djordjic]

14 minutes ago, James Evens said:

Why not use multiple datapoints? each job get's its own line

Idk what that means ..... im new to MySQL or Databases in general ...

10 minutes ago, colonel_mortis said:

The maximum size of LONGTEXT is 4GB, which you're not going to hit if you're storing data in that format (and MEDIUMTEXT is 16MB, which will still be plenty).

 

However, you're almost certainly doing things wrong to store it in that format - the conventional format is to have a table with one row per job, and one column per field (source, distance, etc). That way, you're not limited in how many jobs you can store (aside from by the size of your disk), and you can choose to only load the records that are relevant, rather than every record every time (which would become a bottleneck before you hit the 4GB limit).

Idk how to do that ..... (like i said above to the previous quote) ...

Thank you for your answer, i will be using LongText ....  ... simpler for me 

 

What i was concerned was the amount of characters stored , as far as i know Varchar limit is ~65000

And i dont thing that the jobs would ever get bigger than maybe 10mb ... we arent storing that much data ...
And how we are and should be displaying the data is to display all of them at once ... sooo ....

[Stealhelm]

There's no way you'll ever exceed the data limit of something like LONGTEXT; even MEDIUMTEXT can be hard to cap.

 

Anyways, as previously mentioned you're going about this the wrong way, and I can only suggest that you reconsider.

[lufihengr]

1 minute ago, Stealhelm said:

There's no way you'll ever exceed the data limit of something like LONGTEXT; even MEDIUMTEXT can be hard to cap.

 

Anyways, as previously mentioned you're going about this the wrong way, and I can only suggest that you reconsider refactor.

FTFY

[Aleksa Djordjic]

18 minutes ago, colonel_mortis said:

The maximum size of LONGTEXT is 4GB, which you're not going to hit if you're storing data in that format (and MEDIUMTEXT is 16MB, which will still be plenty).

 

However, you're almost certainly doing things wrong to store it in that format - the conventional format is to have a table with one row per job, and one column per field (source, distance, etc). That way, you're not limited in how many jobs you can store (aside from by the size of your disk), and you can choose to only load the records that are relevant, rather than every record every time (which would become a bottleneck before you hit the 4GB limit).

Also .... one question ..... how do i declare a LongText .... i use Navicat , and i know that stuff like varchars are declared like this "varchar(255)" , but in Navicat it is Type "varchar", length "255" .... what do i put in as a length for a LongText ?

.... Actualy ... i will make it how you said ... but will do that later , for now , we will use it like this

Scrap that , doing it now .....we have IDs for Users , so i can use that same ID for a Job table ... so it shouldnt be hard .... right ?

[colonel_mortis]

8 minutes ago, Aleksa Djordjic said:

Also .... one question ..... how do i declare a LongText .... i use Navicat , and i know that stuff like varchars are declared like this "varchar(255)" , but in Navicat it is Type "varchar", length "255" .... what do i put in as a length for a LongText ?

.... Actualy ... i will make it how you said ... but will do that later , for now , we will use it like this

Scrap that , doing it now .....we have IDs for Users , so i can use that same ID for a Job table ... so it shouldnt be hard .... right ?

I don't know how it works in Navicat, but in SQL you would just replace VARCHAR(255) by LONGTEXT, which has no associated length variable.

I would really encourage you to learn a little bit about relational databases, and split into rows and columns, because it will be much easier to work with. If you would consider that, let me know what language you're using and I can give you a brief introduction if you like (or just use a guide online).

[Aleksa Djordjic]

11 minutes ago, colonel_mortis said:

I don't know how it works in Navicat, but in SQL you would just replace VARCHAR(255) by LONGTEXT, which has no associated length variable.

I would really encourage you to learn a little bit about relational databases, and split into rows and columns, because it will be much easier to work with. If you would consider that, let me know what language you're using and I can give you a brief introduction if you like (or just use a guide online).

We use C# , and we already have a User table which stores User data ..... and i kind of know how to Add/Edit/Delete data from the database .... we dont make Queries in the database and execute them , we generate them in our code , make them what we need and then call the ExecuteNonQuery() function built in to MySQL.Data