MySQL Data Types

In this tutorial you will learn about the MySQL Data Types and its application with practical example.

A data type specifies the possible values for that type, the operations that can be performed on that type and the way the values of that type are stored.MySQL uses many different data types, broken into three categories: numeric, date and time, and string types.

Table Of Contents−

 

MySQL Numeric Types

Integer types

Type Length in Bytes Minimum Value(Signed/Unsigned) Maximum Value(Signed/Unsigned)
TINYINT 1 -128 to 0 127 to 255
SMALLINT 2 -32768 to 0 32767 to 65535
MEDIUMINT 3 -8388608 to 0 8388607 to 16777215
INT 4 -2147483648 to 0 2147483647 to 4294967295
BIGINT 8 -9223372036854775808 to 0 9223372036854775807 to 18446744073709551615

Floating-Point Types

Types Description
FLOAT A precision from 0 to 23 results in a four-byte single-precision FLOAT column
DOUBLE A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column.

Fixed-Point Types

Types Description
DECIMAL In the format DECIMAL(precision,scale). Maximum number of digits allowed are 65 before MySQL 5.03 and 64 after 5.03.
NUMERIC Same as DECIMAL

Bit Value Types

Types Description
BIT In the format b BIT(N), where N is an integer.

Numeric type attributes

Types Description
TYPE(N) Where N is an integer and display width of the type is upto N digits.
ZEROFILL The default padding of spaces is replaced with zeros. So, for a column INT(3) ZEROFILL, 7 is displayed as 007.

MySQL Date and Time Types

DATETIME, DATE, and TIMESTAMP Types

Types Description Display Format Range
DATETIME Use when you need values containing both date and time information. YYYY-MM-DD HH:MM:SS ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
DATE Use when you need only date information. YYYY-MM-DD ‘1000-01-01’ to ‘9999-12-31’.
TIMESTAMP Values are converted from the current time zone to UTC while storing, and converted back from UTC to the current time zone when retrieved. YYYY-MM-DD HH:MM:SS ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

String Types

CHAR and VARCHAR Types

Types Description Display Format Range in characters
CHAR Contains non-binary strings. Length is fixed as you declare while creating a table. When stored, they are right-padded with spaces to the specified length. Trailing spaces are removed. The length can be any value from 0 to 255.
VARCHAR Contains non-binary strings. Columns are variable-length strings. As stored. A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

BINARY and VARBINARY Types

Types Description Range in bytes
BINARY Contains binary strings. 0 to 255
VARBINARY Contains binary strings. A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

BLOB and TEXT Types

Types Description Categories Range
BLOB Large binary object that containing a variable amount of data. Values are treated as binary strings.You don’t need to specify length while creating a column. TINYBLOB Maximum length of 255 characters.
MEDIUMBLOB Maximum length of 16777215 characters.
LONGBLOB Maximum length of 4294967295 characters
TEXT Values are treated as character strings having a character set. TINYBLOB Maximum length of 255 characters.
MEDIUMBLOB Maximum length of 16777215 characters.
LONGBLOB Maximum length of 4294967295 characters

ENUM Types

A string object whose value is chosen from a list of values given at the time of table creation. For example –

In this tutorial we have learn about the MySQL Data Types and its application with practical example. I hope you will like this tutorial.