Data type defines the type of data held by the MySQL tables. As there are different types of values that are needed be stored in the databasewhich include simple integer values, decimal values, images, long texts etc. Each data type has different properties like its possible values, its range and its size. Thus various data types are built in MySQL so that the values can be stored efficiently according to space needed for each data type.
MySQL data types broadly divided into three types:
- Numeric values
- Date and time values
- String Values
Numeric Data type
Numeric data type includes integer, floating point and BIT values.
BIT values are defined as BIT(M) where m denotes the number from 1to 64 and by defaults its value is 1.
Very small integer value. Its range is 255(if unsigned) and -128 to 127 (if signed)
Its small integer values. Its range is 65535(if unsigned) and -32768 to 32767 (if signed)
Its medium-sized integer. Its range is 16777215(if unsigned) and -8388608 to 8388607 (if signed).
Its normal integer. Its range is4294967295 (if unsigned) and -2147483648 to 2147483647 (if signed).
Its large integer values. Its range is 18446744073709551615 (if unsigned) and -9223372036854775808 to 9223372036854775807 (if signed)
Floating Point Types
It has precision of 0 to 23 thus stores 4-Byte single-precision values. Its length is 4 Bytes.
It has precision of 24 to 53 thus stores 8-Byte single-precision values. Its length is 8 Bytes.
It is defined as DECIMAL (M, D). M is number of digits before decimal and D is number of digits after decimal point. Maximum value of M is 65 maximum value of D is 30.
NOTE: You can extend the size of your integer columns by making it unsigned instead of signed integers.
Date and Time Data Types
These are temporal data types used to store a date and time in MySQL tables. Each data type has its own range and format with some default values.
This data type stores the date in YYYY-MM-DD format. Its range is 1000-01-01 to 9999-12-31. Default value is 0000-00-00.
This data types stores the date and time in the YYYY-MM-DD HH:MM:SS. Its range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59. Default value is 0000-00-00 00:00:00.
It just like the DateTime but without colon and space in it. Its format is YYYYMMDDHHMMSS. For example the datetime is 2013-02-03 12:09:12 then the TIMESTAMP would be 20130203120912. Default value is 00000000000000.
It store the time in format HH:MM:SS its range is 00:00:00 to 23:59:59. Default value is 00:00:00.
It stores the year in two formats. It is defined as YEAR(2) or YEAR(4) and values as 92 or 1992. Default value is YEAR(4).
String Data Types
String data type has special properties and features like Character set and collation for storing the data in different languages and types. Character set is the set of some character in particular language where collation defines the rules applied on that character set.
NOTE: String data type sometime changes automatically after creating the table with CREATE TABLE statement as mysql manage the total row length and changes accordingly.
Char stands for Character. It is defined as CHAR(M), M is the number between 0 to 255. Char(0) means it stores only NULL and ‘’ (blank).
Varchar is variable length characters, it is defined as VARCHAR(M), M is the number of characters, ranging 0 to 65535. It occupies space only when data is entered unlike Char which occupy the space always irrespective of the data presence.
Similar to Char data type although it stores binary data from 0 to 255 instead of non-binary data.
Similar to VARCHAR although it stores binary data from 0 to 65535 instead of non-binary data.
Blob contains a large binary object can be text, image or any document. There is no need to specify any length. Blob is further divided into TINYBLOB, MEDIUMBLOB and LONGBLOB according to length required for data.
Text data type is used for large text values only not the binary objects. Length is not specified, and it also subdivided into TINYTEXT, MEDIUMTEXT and LONGTEXT.
ENUM stands for Enumeration values. A list of values is supplied during creation and it must have only one single value from the list nothing else. It helps in cleanliness of data as none other value is stored in that column. For example the column is defined as enum(‘A’,’B’,’C’) then only A, B and C are allowed in this column.
Like Enum we need to define some values during creation of the table but the difference is that SET can take one or more values at a time unlike enum that takes only one value. For example if we define the set as SET(‘A’,’B’,’C’) then it can take ‘A,B’ or ‘A,B,C’ or ‘A’ etc.
Choosing the correct data type is very crucial in database designing for optimal results. Some data type has special features like:
- TIMESTAMP has ability to automatically update the current date time on updating of that row using ON UPDATE CURRENT_TIMESTAMP value.
- Integer has ZERO FILL ability to pad the remaining digits to ZERO for display. But the usage of ZEROFILL also implies the unsigned values means only positive values.