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)
DATE and TIME
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)