What are the differences between the BLOB and TEXT datatypes in MySQL?

mysql database text blob
nencor picture nencor · Jul 24, 2012 · Viewed 141.4k times · Source

What is blob and what is text? What are the differences?

When do I need to use blob and when do I need text as data type?

Because for blob and text, there are mediumblob == mediumtext, smallblob == small text. Do they even have the same meaning?

And look at this MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 224.

What is L?

Answer

Darcey picture Darcey · Jul 24, 2012

TEXT and CHAR will convert to/from the character set they have associated with time. BLOB and BINARY simply store bytes.

BLOB is used for storing binary data while Text is used to store large string.

BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values.

TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set.

http://dev.mysql.com/doc/refman/5.0/en/blob.html

Related questions

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More
How do I quickly rename a MySQL database (change schema name)?

The MySQL manual at MySQL covers this. Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does …

Read More
MySQL Error: : 'Access denied for user 'root'@'localhost'

$ ./mysqladmin -u root -p 'redacted' Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' How can I fix this?

Read More