Table Column Types for LOB (Large OBject)

MySQL Tutorials - Herong's Tutorial Examples

∟Table Column Types for Different Types of Values

∟Table Column Types for LOB (Large OBject)

Describes 8 types of table columns to store LOB (Large OBject) data: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

There are 8 types of table columns to store LOB (Large OBject) data: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

"TINYBLOB" - BLOB (Binary Large OBject) data with less than 2⁸ bytes.

"BLOB" - BLOB (Binary Large OBject) data with less than 2¹⁶ bytes.

"MEDIUMBLOB" - BLOB (Binary Large OBject) data with less than 2²⁴ bytes.

"LONGBLOB" - BLOB (Binary Large OBject) data with less than 2³² bytes.

"TINYTEXT" - CLOB (Character Large OBject) data with less than 2⁸ bytes.

"TEXT" - CLOB (Character Large OBject) data with less than 2¹⁶ bytes.

"MEDIUMTEXT" - CLOB (Character Large OBject) data with less than 2²⁴ bytes.

"LONGTEXT" - CLOB (Character Large OBject) data with less than 2³² bytes.

Examples of BLOB/CLOB column types, BlobClobColumns.sql:

-- BlobClobColumns.sql
-- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
--
CREATE TABLE BlobClob (
   Line VARCHAR(2),
   B BLOB,
   T TEXT
   );
INSERT INTO BlobClob VALUES (
   '1',
   'AAA',
   'AAA'
   );
INSERT INTO BlobClob VALUES (
   '2',
   X'424242',
   X'424242'
   );
INSERT INTO BlobClob VALUES (
   '3',
   BINARY(X'434343'),
   BINARY(X'434343')
   );
SELECT * FROM BlobClob;
DROP TABLE BlobClob;

Running BlobClobColumns.sql on MySQL 8.0 server gives you:

herong> %mysql%\bin\mysql --user=root --password=TopSecret test \
   < BlobClobColumns.sql

Line    B       T
1       AAA     AAA
2       BBB     BBB
3       CCC     CCC