MySQL Data Types

In MySQL there are three main Data types: text, number, Date/Time Data Types.

Text Types

Data typeDescription
CHAR(size)It Holds a fixed length string (can contain letters, numbers, and special
characters). The fixed size is specified in parenthesis.It Can store up to
255 characters
VARCHAR(size)It Holds a variable length string (can contain letters, numbers, and
special characters). The maximum size is specified in parenthesis. Can
store up to 255 characters.
Info: If you put a greater value than
255 it will be converted to a TEXT type
TINYTEXTIt can hold a string with a maximum length of 255 characters
TEXTIt can hold a string with a maximum length of 65,535 characters
BLOBBLOB stand for Binary Large OBjects.It can hold up to 65,535 bytes of data
MEDIUMTEXTIt can hold a string with a maximum length of 16,777,215 characters
MEDIUMBLOBBLOB stand for Binary Large OBjects.It can hold up to 16,777,215 bytes of data
LONGTEXTIt can hold a string with a maximum length of 4,294,967,295 characters
LONGBLOBBLOB stand for Binary Large OBjects. Holds up to 4,294,967,295 bytes of
data
ENUM(x,y,z,etc.)Let you enter a list of possible values. You can list up to 65535
values in an ENUM list. If a value is inserted that is not in the list, a
blank value will be inserted.
Info: The values are sorted in the order you enter them.

You enter the possible values in this format: ENUM(‘X’,’Y’,’Z’)

SETSimilar to ENUM except that SET may contain up to 64 list items and
can store more than one choice

Number type:

Info: *The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.
Data typeDescription
TINYINT(size)-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits
may be specified in parenthesis
SMALLINT(size)-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of
digits may be specified in parenthesis
MEDIUMINT(size)-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum
number of digits may be specified in parenthesis
INT(size)-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The
maximum number of digits may be specified in parenthesis
BIGINT(size)-9223372036854775808 to 9223372036854775807 normal. 0 to
18446744073709551615 UNSIGNED*. The maximum number of digits may be
specified in parenthesis
FLOAT(size,decimal)A small number with a floating decimal point. The maximum number of
digits may be specified in the size parameter. The maximum number of
digits to the right of the decimal point is specified in the decimal parameter
DOUBLE(size,decimal)A large number with a floating decimal point. The maximum number of
digits may be specified in the size parameter. The maximum number of
digits to the right of the decimal point is specified in the decimal parameter
DECIMAL(size,decimal)An unpacked floating-point number that cannot be UNSIGNED*. The
maximum number of digits may be specified in the size parameter. The
maximum number of digits to the right of the decimal point is specified in
the decimal parameter. In unpacked decimals, each decimal corresponds to one byte.

Date/Time Type

Info: *Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD, or YYMMDD.
Data typeDescription
DATE()A date. Format: YYYY-MM-DD
Info: The supported range is from
1000-01-01‘ to ‘9999-12-31
DATETIME()*A date and time combination. Format: YYYY-MM-DD HH:MI:SS
Note: The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
TIMESTAMP()*A timestamp. TIMESTAMP values are stored as the number of seconds
since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD
HH:MI:SS
Info: The supported range is from ‘1970-01-01 00:00:01
UTC to ‘2038-01-09 03:14:07‘ UTC
TIME()A time. Format: HH:MI:SS
Note: The supported range is from
-838:59:59‘ to ‘838:59:59
YEAR()A year in two-digit or four-digit format.
Info: Values allowed in four-digit format: 1901 to 2155. Values
allowed in two-digit format: 70 to 69, representing years from 1970 to
2069

Shubham Kumar

Hey, I am Shubham and i love Blogging, Coding and exploring new things and obviously sharing my experience with you.

You may also like...

Leave a Reply

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