Jump to content
Search In
  • More options...
Find results that contain...
Find results in...

MySQL | How to store insanely long strings ?

Go to solution Solved by 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).

(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 ?

Link to comment
Share on other sites

Link to post
Share on other sites

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

                     ¸„»°'´¸„»°'´ Vorticalbox `'°«„¸`'°«„¸
`'°«„¸¸„»°'´¸„»°'´`'°«„¸Scientia Potentia est  ¸„»°'´`'°«„¸`'°«„¸¸„»°'´

Link to comment
Share on other sites

Link to post
Share on other sites

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 ....

Link to comment
Share on other sites

Link to post
Share on other sites

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).

HTTP/2 203

Link to comment
Share on other sites

Link to post
Share on other sites

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 .... xD ... 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 ....

Link to comment
Share on other sites

Link to post
Share on other sites

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.

Link to comment
Share on other sites

Link to post
Share on other sites

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 xD

My PC CPU: 2600K@4.5GHz 1.3v Cooler: Noctua NH-U12P SE2 MB: ASUS Maximus IV RAM: Kingston 1600MHz 8GB & Corsair 1600MHz 16GB GPU: 780Ti Storage: Samsung 850 Evo 500GB SSD, Samsung 830 256GB SSD, Kingston 128GB SSD, WD Black 1TB,WD Green 1TB. PSU: Corsair AX850 Case: CM HAF X. Optical drive: LG Bluray burner  MacBook Pro, Hackintosh

Link to comment
Share on other sites

Link to post
Share on other sites

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 xD

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 ?

Link to comment
Share on other sites

Link to post
Share on other sites

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 xD

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).

HTTP/2 203

Link to comment
Share on other sites

Link to post
Share on other sites

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

Link to comment
Share on other sites

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share


×