This tutorial will help you to understand about “MYSQL String Types”. It contains the storage required for string types, CHAR type, VARCHAR type, TEXT types, BLOB types, ENUM type and SET TYPE.
Storage Requirements for String Types
In the following table, M represents the declared column length in 1.characters for nonbinary string types and 2.bytes for binary string types. L represents the actual length in bytes of a given string value.For the string ‘abcd’, L is 4 and the storage requirement is five bytes.
Data Type | Storage Required |
---|---|
CHAR(M) | M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set |
BINARY(M) | 0 <= M <= 255 |
VARCHAR (M), VARBINARY (M) | L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes |
TINYBLOG, TINYTEXT | L + 1 bytes, where L < 28 |
BLOG, TEXT | L + 2 bytes, where L < 216 |
MEDIUMBLOB, MEDIUMTEXT | L + 3 bytes, where L < 224 |
LONGBLOB, LONGTEXT | L + 4 bytes, where L < 232 |
ENUM(‘value1′,’value2’,…) | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET(‘value1′,’value2’,…) | 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
CHAR ,VARCHAR TYPE
Differences between CHAR and VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns
Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required |
---|---|---|---|---|
” | ‘ ‘ | 4 bytes | ” | 1 byte |
‘ab’ | ‘ab ‘ | 4 bytes | ‘ab’ | 2 bytes |
‘abcd’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 bytes |
‘abcdefgh’ | ‘abcd’ | 4 bytes | ‘abcd’ | 1 bytes |
TEXT TYPES
TEXT column is 256 if size <= 256 otherwise, the size is 256 + size + (2000 – (size – 256) % 2000). TINYTEXT - 255 bytes TEXT - 65535 bytes MEDIUMTEXT - 16,777,215 bytes (2^24 - 1) LONGTEXT - 4G bytes (2^32 – 1)
BLOB TYPES
A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold.
TINYBLOB - 255 bytes BLOB - 65535 bytes MEDIUMBLOB - 16,777,215 bytes (2^24 - 1) LONGBLOB - 4G bytes (2^32 – 1)
ENUM TYPE
One byte is used for enumerations with up to 255 possible values. Two bytes are used for enumerations having between 256 and 65,535 possible values.
CREATE TABLE user ( name VARCHAR(40), type ENUM('A', 'B', 'C') );
we can store only ‘A’,’B’ and ‘C’ in ‘type’ column of table.
SET Type
A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (“,”). A consequence of this is that SET member values should not themselves contain commas. For example:
SET('one', 'two') NOT NULL can have any of these values: '' 'one' 'two' 'one,two'
A SET column can have a maximum of 64 distinct members. A table can have no more than 255 unique element list definitions among its ENUM and SET columns considered as a group.