CodesJava

Easy learning with example program codes

Data Types in MySQL

MySQL Data Types

A data type specifies the type of data to be stored, the possible values, the type of operations that can be performed and the way to process that data. A data type can vary from integer, floating point, boolean, etc., to many more and thus are divided into various categories.

 

Numeric Data Types:

There are mainly eight numeric data types that MySQL supports. These are:

Data TypeRangeMaximum widthDescription
INT-2147483648 to 2147483647 (signed)

0 to 4294967295 (unsigned)

11 digitsNormal sized integer value.
TINYINT-128 to 127(signed)

0 to 255(unsigned)

4 digitsVery small integer values.
SMALLINT-32768 to 32767 (signed)

0 to 65535 (unsigned)

5 digitsSmall integer value.
MEDIUMINT-8388608 to 8388607 (signed)

0 to 16777215 (unsigned)

9 digitsMedium sized integer value.
BIGINT-9223372036854775808 to 9223372036854775807 (signed)

0 to 18446744073709551615 (unsigned)

20 digitsLarge integer value.
FLOAT(m,d)  A floating point number whose decimal precision can go upto 24 places. The display length (m) and the number of decimals (d) can be defined but they have a default value of m=10 and d = 2.

It cannot be unsigned.

DOUBLE(m,d)  A floating point number whose decimal precision can go upto 53 places. The display length (m) and the number of decimals (d) can be defined but they have a default value of m=16 and d = 4.

It cannot be unsigned.

DECIMAL(m,d)  An unpacked floating point number where each decimal is represented by one byte. Here defining m (display length) and d (decimals) is necessary.

Date and Time Data Types:

There are mainly five date and time data types that MySQL supports. These are:

Data TypeRangeFormat
DATE‘1000-01-01’ to ‘9999-12-31’yyyy-mm-dd
DATETIME‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’yyyy-mm-dd hh:mm:ss
TIMESTAMP(m)‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTCYYYY-MM-DD HH:MM:SS
TIME‘-838:59:59’ to ‘838:59:59’HH:MM:SS
YEAR[(2|4)]2 digits or 4 digits value4 digits by default

 

String Data Types:

There are mainly eight string data types that MySQL supports. These are:

Data Type Maximum SizeDescription
CHAR(size)255 charactersHere, size is equal to the number of characters to store. It is used for fixed-length strings with space padded on right to equal size characters.
VARCHAR(size)255 charactersHere, size is equal to the number of characters to store. It is used for variable-length strings.
TINYTEXT(size)255 charactersHere, size is equal to the number of characters to store.
TEXT(size)65,535 charactersHere, size is equal to the number of characters to store.
MEDIUMTEXT(size)16,777,215 charactersHere, size is equal to the number of characters to store.
LONGTEXT(size)4GB or 4,294,967,295 charactersHere, size is equal to the number of characters to store.
BINARY(size)255 charactersHere, size is equal to the number of binary characters to store. It is used for fixed-length strings with space padded on right to equal size characters.
VARBINARY(size)255 charactersHere, size is equal to the number of binary characters to store. It is used for variable-length strings.

 

Large Object (LOB) Data Types:

There are mainly four large object or LOB data types that MySQL supports. These are:

Data TypeMaximum Size
TINYBLOB255 bytes
BLOB(size)65,535 bytes
MEDIUMBLOB16,777,215 bytes
LONGTEXT4GB or 4,294,967,295 characters

 

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status