Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it good to store long strings in a database?

I need to store long strings in a database. the string may be 5 or 6 sentences long. do you think this is a good design strategy. or should I store an id for that string and then create a relationship with another table that contains the location of the file storing the string. could you please give advantages and disadvantages of both.

the strings have been preprocessed and stored in the database. any modification would read the entire string and replace it completely. so you can assume that the string is indivisible.

like image 713
Rohit Banga Avatar asked Sep 17 '09 12:09

Rohit Banga


People also ask

What type of data you should not store in database?

Finally, you shouldn't store credit card information in your database unless you absolutely need to. This includes credit card owner names, numbers, CVV numbers, and expiration dates.

How can we store long string in database?

To store strings in a database, use OpenROAD to add them to a table. OpenROAD automatically creates a special string storage table, called ii_stored_strings.

How long a string can you store SQL?

In practical scenarios, varchar(n) is used to store variable length value as a string, here 'n' denotes the string length in bytes and it can go up to 8000 characters.

What is the best way to store an attribute of large string values in SQL?

We can use varchar(<maximum_limit>) . The maximum limit that we can pass is 65535 bytes. Note: This maximum length of a VARCHAR is shared among all columns except TEXT/BLOB columns and the character set used.


3 Answers

It should be fine to store the string in the database. If you store a file pointer instead, that means you need to do File I/O every time you want to read the string. A few sentences isn't terribly long and you can always use a longtext data field if you need to. Obviously your database will be a little bit larger because you have the text, but that's ok. It is certainly a better alternative than having to store the files.

like image 87
Jeff Storey Avatar answered Sep 22 '22 22:09

Jeff Storey


The strings you mention are not at all long.

When you refered to "long" strings, I was thinking about 32kB and above -- some sentences are <1kb -- that is nothing today.

Your trick, storing an Id makes the things slower since you have to make an indirect access.

The only thing I would recommend, when maximum performance is needed, you should select only those columns that you need (omit SELECT *) -- so omit the text column, when not needed, since the transport of the string from server to application costs the most time. It is a good praxis, not to touch columns not needed (specially when they might contain much data).

like image 24
Juergen Avatar answered Sep 22 '22 22:09

Juergen


The only reason I would create a separate table is if those long strings will be the same for many records. Otherwise its just an extra complication that isn't likely to provide any payback.

like image 21
AnthonyWJones Avatar answered Sep 22 '22 22:09

AnthonyWJones