MYSQL String Data Types

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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *