MySQL

Data Types in MySQL
Previous Home Next

The data types in mysql is divided into few types, they are list as below:

  1. Numeric
  2. String
  3. Date or time
  4. Large Object(LOB)

Numeric data types

The following are the numeric datatypes in mysql are:

Data TypeDescription
BITVery small integer value that is equivalent to TINYINT(1). Signed values range from -128 to 127. Unsigned values range from 0 to 255.
TINYINT(m)Very small integer value. Signed values range from -128 to 127. Unsigned values range from 0 to 255.
SMALLINT(m)Small integer value. Signed values range from -32768 to 32767. Unsigned values range from 0 to 65535.
MEDIUMINT(m)Medium integer value. Signed values range from -8388608 to 8388607. Unsigned values range from 0 to 16777215.
INT(m)Standard integer value. Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295.
INTEGER(m)Standard integer value. Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295.
BIGINT(m)Big integer value. Signed values range from -9223372036854775808 to 9223372036854775807. Unsigned values range from 0 to 18446744073709551615.
DECIMAL(m,d)Unpacked fixed point number. m defaults to 10, if not specified. d defaults to 0, if not specified.
DEC(m,d)Unpacked fixed point number. m defaults to 10, if not specified. d defaults to 0, if not specified.
NUMERIC(m,d)Unpacked fixed-point number. m defaults to 10, if not specified. d defaults to 0, if not specified.
FIXED(m,d)Unpacked fixed-point number. m defaults to 10, if not specified.d defaults to 0, if not specified(Introduced in MySQL 4.1).
FLOAT(m,d)Single precision floating point number.
DOUBLE(m,d)Double precision floating point number.
DOUBLE PRECISION(m,d)Double precision floating point number.
REAL(m,d)Double precision floating point number.
FLOAT(p)Floating point number. Where p is the precision.This is a synonym for the DOUBLE datatype.
BOOL Synonym for TINYINT(1). Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE.
BOOLEANSynonym for TINYINT(1). Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE.

 

String data types

The following are the string datatypes in mysql are:

Data TypeDescription
CHAR(size)Maximum size of 255 characters. Where size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
VARCHAR(size)Maximum size of 255 characters. Where size is the number of characters to store. Variable-length string.
TINYTEXT(size)Maximum size of 255 characters. Where size is the number of characters to store.
TEXT(size)Maximum size of 65,535 characters. Where size is the number of characters to store.
MEDIUMTEXT(size)Maximum size of 16,777,215 characters. Where size is the number of characters to store.
LONGTEXT(size)Maximum size of 4GB or 4,294,967,295 characters. Where size is the number of characters to store.
BINARY(size)Maximum size of 255 characters. Where size is the number of binary characters to store. Fixed-length strings. Space padded on right to equal size characters. (Introduced in MySQL 4.1.2)
VARBINARY(size)Maximum size of 255 characters. Where size is the number of characters to store. Variable-length string.(Introduced in MySQL 4.1.2)

Date or Time data types

The following are the date or time datatypes in mysql are:

Data TypeDescription
DATE Values range from '1000-01-01' to '9999-12-31'. Displayed as 'YYYY-MM-DD'.
DATETIMEValues range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Displayed as 'YYYY-MM-DD HH:MM:SS'.
TIMESTAMP(m)Values range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. Displayed as 'YYYY-MM-DD HH:MM:SS'.
TIMEValues range from '-838:59:59' to '838:59:59'. Displayed as 'HH:MM:SS'.
YEAR[(2|4)] Year value as 2 digits or 4 digits. Default is 4 digits.

Large Object(LOB) data types

The following are the large object datatypes in mysql are:

Data TypeDescription
TINYBLOBMaximum size of 255 bytes.
BLOB(size)Maximum size of 65,535 bytes. Where size is the number of characters to store (size is optional and was introduced in MySQL 4.1)
MEDIUMBLOBMaximum size of 16,777,215 bytes.
LONGTEXTMaximum size of 4GB or 4,294,967,295 characters.
Previous Home Next

Tolal:0 Click:

Show All Comments

Did not find what you were looking for leave your name and message. We will revert within 24 hours
Name:
eMail:
Comment / Feedback: