Store large content in MySql

Question

Hi I am trying to store large data content in mysql database. For that i set Datatype as TEXT but still it limits to store the data. So what will be the solution for string large content in Mysql tables and retrieve the same.

I am storing news description in one of my mysql table.

Suggest!!! Thanks.

http://stackoverflow.com/questions/6766781/maximum-length-for-mysql-type-text — Mark Baker, Jan 04 '14 at 09:24
TEXT can handle 64k bytes, is that a simple description, or the full text of a long news article? — Mark Baker, Jan 04 '14 at 09:25
http://stackoverflow.com/questions/5458376/what-column-data-type-should-i-use-for-storing-large-amounts-of-text-or-html — Nagaraj S, Jan 04 '14 at 09:28
@MarkBaker, Yeah that is what i too mentioned. `TEXT` is far enough. — Shankar Narayana Damodaran, Jan 04 '14 at 09:29
Define `large`. Answer would heavily depend if that is bytes, kilobytes, megabytes or gigabytes. — mvp, Jan 04 '14 at 09:36

score 2 · Answer 1 · answered Feb 07 '14 at 01:56

See more in : http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

TINYBLOB, TINYTEXT       L + 1 bytes, where L < 2^8    (255 Bytes)
BLOB, TEXT               L + 2 bytes, where L < 2^16   (64 Kilobytes)
MEDIUMBLOB, MEDIUMTEXT   L + 3 bytes, where L < 2^24   (16 Megabytes)
LONGBLOB, LONGTEXT       L + 4 bytes, where L < 2^32   (4 Gigabytes)

Shankar Narayana Damodaran · Answer 2 · 2014-01-22T10:40:56.620

0

The TEXT datatype is far more than enough. Seems like you are not satisfied, so go for BLOB (Binary Large Objects)

edited Jan 22 '14 at 10:40

answered Jan 04 '14 at 09:23

Shankar Narayana Damodaran

68,075
43
96
126

Jenson M John · Answer 3 · 2014-01-04T09:36:54.193

0

VARCHAR is limited to 255 characters in all mysql versions <= 5.0.3

Various Types of TEXT fields : TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

TINYTEXT    256 bytes    
TEXT    65,535 bytes    ~64kb
MEDIUMTEXT   16,777,215 bytes   ~16MB
LONGTEXT    4,294,967,295 bytes     ~4GB

A BLOB can be 65535 bytes maximum. MEDIUMBLOB for 16777215 bytes or a LONGBLOB for 4294967295 bytes.

Difference between TEXT & BLOB : BLOB is used for storing binary data & TEXT is used to store large strings.

More Details: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

edited Jan 04 '14 at 09:36

answered Jan 04 '14 at 09:24

Jenson M John

5,499
5
30
46

1

Have you ever tried to store anything more than few megabytes? LONGTEXT limit of 4GB is purely theoretical and is not attainable in practice – mvp Jan 04 '14 at 09:38
@mvp I've Stored Maximum of few MB's But this is what they written in their official documentation : http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html – Jenson M John Jan 04 '14 at 09:41
1

Typically hard problems start at 16MB and anything over that is so painful as to be completely unusable. Read [this article](http://www.moojuice.net/posts/inserting-large-blobs-in-mysql) as someone tried to insert largest blob possible - they gave up at 200MB. – mvp Jan 04 '14 at 09:46

score 0 · Answer 4 · answered Jan 04 '14 at 09:27

0

you may need to change maximum allowed packet size: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet

answered Jan 04 '14 at 09:27

dev

439
2
6

Store large content in MySql

4 Answers4