Data Type in MySQL


This data type is very useful, as it allows MySQL to plan the size of a database and perform lookups and searches more easily.

The downside is that if you ever attempt to assign a string value longer than the length allowed, it will be truncated to the maximum length declared in the table definition.

The year field, however, has more predictable values, so instead of VARCHAR we use the more efficient CHAR(4) data type. The parameter of 4 allows for four bytes of data, supporting all years from –999 to 9999.

Both CHAR and VARCHAR accept text strings and impose a limit on the size of the field.

The difference is that every string in a CHAR field has the specified size. If you put in a smaller string, it is padded with spaces.

A VARCHAR field does not pad the text; it lets the size of the field vary to fit the text that is inserted. But VARCHAR requires a small amount of overhead to keep track of the size of each value.

The CHAR data type

The CHAR offer a parameter that sets the maximum (or exact) length of the string allowed in the field.
The type of VARCHAR between 0 and 255 bytes in length incur a storage overhead of 1 byte, or 2 bytes overhead if 256 bytes or more.
The CHAR data types are:

CHAR(n) (Exactly n (<= 255)), VARCHAR(n) (Up to n (<= 65535))

The BINARY data type

The BINARY data type is used for storing strings of full bytes that do not have an associated character set. For example, you might use the BINARY data type to store a GIF image.
The BINARY data type are:

BINARY(n) or BYTE(n) (Exactly n (<= 255)), VARBINARY(n) (Up to n (<= 65535))

The TEXT and VARCHAR data types

The differences between TEXT and VARCHAR are small:
1. Prior to version 5.0.3, MySQL removed leading and trailing spaces from VARCHAR fields, and they could only be up to 256 bytes in length.
2. TEXT fields cannot have default values.
3. MySQL indexes only the first n characters of a TEXT column (you specify n when you create the index).

The text data types are:

TINTEXT(n) Up to n (<= 255), TEXT(n) Up to n (<= 65535), MEDIUMTEXT(n) Up to n
(<= 16777215), LONGTEXT(n) Up to n (<= 4294967295)

The BLOB data type

The term BLOB stands for Binary Large OBject, and therefore, as you would think, the BLOB data type is most useful for binary data in excess of 65,536 bytes in size. think, the BLOB data type is most useful for binary data in excess of 65,536 bytes in size. The main other difference between the BLOB and BINARY data types is that BLOBs cannot have default values.
The BLOB data type are:

TINYBLOB(n)Up to n (<= 255), BLOB(n) Up to n (<= 65535) , MEDIUMBLOB(n) Up 
to n (<= 16777215), LONGBLOB(n) Up to n (<= 4294967295)

Numeric data types

MySQL supports various numeric data types, from a single byte up to double -precision floating-point numbers.
The Numeric data types is:

TINYINT (1 byte used), SMALLINT(2 byte used) ,MEDIUMINT(3 byte used), INT or 
INTEGER (4 byte used), BIGINT(8 byte used), FLOAT(4 byte used), DOUBLE or REAL(8
byte used)


The main remaining data types supported by MySQL relate to the date and time.
Date Time data types is:

DATETIME ('0000-00-00 00:00:00'), DATE ('0000-00-00'), TIMESTAMP ('0000-00-00 00
:00:00'), TIME ('00:00:00') ,YEAR 0000 (Only years 0000 and 1901 - 2155)