MySQL Data Types

MySQL is a most popular Open Source SQL database management system. MySQL is one of the best RDBMS being used for developing open source web applications and is a central component of the widely used LAMP open source web application software stack.

MySQL supports a many different data types in several categories: numeric types, date and time types, string (character and byte) types, spatial types.

Numeric Data Types:

MySQL uses all the numeric SQL data types, here shows following list of the common numeric data types and their descriptions:

 

INT – Int is a normal-sized integer that can be signed or unsigned and width of bigint is 11 digits. the allowable range is from -2147483648 to 2147483647 for SIGNED integer and the allowable range 0 to 4294967295 for UNSIGNED Integer.

TINYINT – Tinyint i a very small integer that can be signed or unsigned and width of bigint is 4 digits. The allowed range for SIGNED TINYINT if from -128 to +127 and Another allowed range is from 0 to 255 for UNSIGNED.

BIGINT – Bigint is a large integer that can be signed or unsigned and width of bigint is 20 digits. The allowable range is from -9223372036854775808 to 9223372036854775807 for SIGNED and another allowed range is from 0 to 18446744073709551615 for UNSIGNED.

FLOAT(M,D) – A floating-point number is a data type and it cannot be unsigned. You can define display length (M) of data type and the number of decimals (D) places value. Default range is 10,2, where 10 is the total number of digits(including decimals) and 2 is the number of decimals.

DECIMAL(M,D) – Decimal is a DOUBLE stored as a string and allow for a fixed decimal point. You can define display length (M) of data type and number of decimals (D).

String Data Types :

You will store most data in string format. Below describes list of common string datatypes in MySQL.

VARCHAR(size) – Varchar is a variable-length string data types and store data between 1 and 255 characters in length. Always must to define a length when creating a VARCHAR type field. For example VARCHAR(25), it will store data string of length 25 characters .

CHAR(size) – Char is a fixed length string between 1 and 255 characters in data string length. Defining of length is not required, but the default length for Char data type is 1 character.

TEXT or BLOB – Text or Blog is a field with a maximum length of 65535 characters store. BLOBs is used to store large amounts of binary data, such as images or other types of files.

ENUM(x,y,z,etc.) – Enum is a enumeration type of data fields, which is store any defined list of items. When defining an ENUM, you are creating a list of items. You can create list upto 65535 values in ENUM data list. If any value is insert which is not in the ENUM list in that case blank value insert in column. ENUM value format : ENUM(‘P’,’Q’,’R’,’S’)

Date and Time Data Types :

Below describes list of common MySQL date and time datatypes.

DATETIME – A date and time combination in YYYY-MM-DD HH:MM:SS format, Supported datetime between ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

DATE – Default a date store in YYYY-MM-DD format. Supported range of date between ‘1000-01-01’ to ‘9999-12-31’.

TIME – Stores the time in HH:MM:SS format. Supported range of time is  ‘-838:59:59’ to ‘838:59:59’.

TIMESTAMP – A timestamp value stored in a number of seconds since midnight, January 1, 1970. Supported range is ‘1970-01-01 00:00:01’ to ‘2038-01-09 03:14:07’.

YEAR(M) – Stores year value in 2-digit or 4-digit format in database. Allowed value for four-digit format is 1901 to 2155. and allowed value for two-digit format is 70 to 69.