77

What are the differences, advantages and disadvantages of these different data-types both from a performance standpoint as well as a usability standpoint?

Jake Wilson
  • 88,616
  • 93
  • 252
  • 370

2 Answers2

86

TEXT is a data-type for text based input. On the other hand, you have BLOB and CLOB which are more suitable for data storage (images, etc) due to their larger capacity limits (4GB for example).

As for the difference between BLOB and CLOB, I believe CLOB has character encoding associated with it, which implies it can be suited well for very large amounts of text.

BLOB and CLOB data can take a long time to retrieve, relative to how quick data from a TEXT field can be retrieved. So, use only what you need.

Baseer
  • 1,187
  • 10
  • 10
  • 15
    According to http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html , TEXT and BLOB can hold the same amount of data (which is 64K). Use LONGTEXT and LONGBLOB to hold up to 4GB of data. – Vladimir Panteleev Jul 27 '12 at 20:56
  • 8
    I agree with CyberShadow but you have to notice as well the CLOB type is only present in Oracle, TEXT (with MEDIUMTEXT and LONGTEXT) is the CLOB equivalent in mysql. – рüффп Jul 18 '13 at 11:56
  • 2
    Other important thing: If MySQL is in strict mode and you are trying to insert value that is longer than column size then MySQL is going to throw an exception if the column's type is BLOB. In other hands, if the column is TEXT then the value will be truncated. – Nico Jun 10 '16 at 01:18
  • JDBC returns type as Types.VARBINARY, then how to differ text from other varbinary via JDBC API (e.g. when I try to write a generic persistence tools, I don't know its DDL script) ? why MySQL driver doesn't returns its type as Types.CLOB? – Daniel Yang Jun 07 '20 at 06:34
23

It's worth to mention that CLOB / BLOB data types and their sizes are supported by MySQL 5.0+, so you can choose the proper data type for your need.

http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

Data Type   Date Type   Storage Required
(CLOB)      (BLOB)

TINYTEXT    TINYBLOB    L + 1 bytes, where L < 2**8  (255)
TEXT        BLOB        L + 2 bytes, where L < 2**16 (64 K)
MEDIUMTEXT  MEDIUMBLOB  L + 3 bytes, where L < 2**24 (16 MB)
LONGTEXT    LONGBLOB    L + 4 bytes, where L < 2**32 (4 GB)

where L stands for the byte length of a string
Erik Ghonyan
  • 427
  • 4
  • 12
Jonathan L
  • 9,552
  • 4
  • 49
  • 38