MySql String Data Type

MySQL has the following string data types:

  • CHAR
  • VARCHAR
  • BINARY
  • VARBINARY
  • BLOB
  • TEXT
  • ENUM
  • SET

Char
A CHAR is a fixed length character data type. It is declared with a length, CHAR(x), where x can be between 0 to 255. CHAR always uses the same amount of storage space per entry. In case we specify an item which is shorter than the declared length, the value is right-padded with spaces to the specified length. Trailing spaces are removed when the value is retrieved.

mysql> CREATE TABLE Chars(Id TINYINT PRIMARY KEY, Chars CHAR(3));
mysql> INSERT INTO Chars VALUES (1, 'a'), (2, 'ab'), (3, 'abc'), (4, 'abb');

Varchar : 

mysql> CREATE TABLE FirstNames(Id TINYINT, Firstname VARCHAR(20));
mysql> INSERT INTO FirstNames VALUES (1, 'Tom'), (2, 'Lucy'), (3, 'Alice'),
-> (4, 'Robert'), (5, 'Timothy'), (6, 'Alexander');

Binary and varbinary :
BINARY and VARBINARY are binary byte data types. They contain byte strings rather than character strings. They have no character sets. Sorting and comparison are based on the numeric values of the bytes in the values. The range of the BINARY data types is from 0 to 255. It stores values in fixed length. The range of the VARBINARY is from 0 to 65535.

Blob
A BLOB is a binary large object data type. It can hold a variable amount of binary data. It can be used to store binary data like images or documents.

ENUM :

The ENUM is a string object with a value chosen from a permitted list of values. They are enumerated explicitly in the column specification. We can insert only one value from the list.

mysql> CREATE TABLE Sizes(Size ENUM('S', 'M', 'L', 'XL', 'XXL'));

Set :
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. It is similar to the ENUM data type. The difference is that it can contain zero or more values from the list of permitted values.

mysql> CREATE TABLE Letters(Let SET('a', 'b', 'c', 'd', 'e'));

You may also like...

Leave a Reply

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