Learn About SQL Data Types

Article SQL

SQL Data Types

Data types are used to represent the kind of data that can be stored in the database table.

Data types mainly classified into three categories.

  1. String Data types
  2. Numeric Datatypes
  3. Date and Time Data type

String Data Types in MySQL

CHAR(Size)

From 0 to 255 characters. The default value is 1.

VARCHAR(Size)

From 0 to 65535 characters.

BINARY(Size)

It is equal to CHAR() but stores binary byte strings. The default value is 1.

VARBINARY(Size)

It is equal to VARCHAR() but stores binary byte strings.

TEXT(Size)

It stores a string that can contain a maximum length of 255 characters.

TINYTEXT

255 characters (fixed)

MEDIUMTEXT

It stores a string with a maximum length of 16,777,215.

LONGTEXT

It stores a string with a maximum length of 4,294,967,295 characters.

TINYBLOB (SIZE)

Stored in the form of bytes. It can store up to 255 bytes.

BLOB(size)

It can stores up to 65,535 bytes.

ENUM(val1, val2, val3,…)

It contains 65535 values in an ENUM list.

SET( val1,val2,val3,….)

You can list up to 64 value at one time in a SET list.

Numeric Data Types in MySQL

BIT(Size)

From 0 to 64. The default value is 1.

TINYINT (SIZE)

From -128 to 127.

INT(size)

Its signed range varies from -2147483648 to 2147483647 and the unsigned range varies from 0 to 4294967295. 

INTEGER(size)

It is equal to INT(size).

SMALLINT (SIZE)

From -32768 to 32767.

MEDIUMINT (SIZE)

From -8388608 to 8388607.

BIGINT (SIZE)

From -9,223,372,036, 854,775,808 to 9,223,372,036, 854,775,807.

FLOAT(size, d)

It is used to specify a floating-point number. The number of digits after the decimal point is specified by the d parameter.

FLOAT(p)

It is used to specify a floating-point number. If p is between 0 to24, the da type becomes FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE().

DOUBLE(size, d)

It is a normal size floating-point number. Its size parameter specifies the total number of digits.

DECIMAL(size, d)

The maximum value for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value is 0.

DEC(size, d)

It is equal to DECIMAL(size, d).

BOOL

It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true.

Date and Time Data Types in MySQL

DATE

It is used to specify date format YYYY-MM- DD. 

DATETIME(fsp)

It is used to specify the date and time combination. Its format is YYYY- MM-DD hh:mm:ss. Its range is from ‘1000-01-01 00:00:00′ to 9999-12- 31 23:59:59’.

TIMESTAMP(fsp)

Its format is YYYY-MM-DD hh:mm:ss. Its supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.

TIME(fsp)

Its format is hh:mm:ss. Its supported range is from ‘-838:59:59’ to ‘838:59:59’.

YEAR

Values allowed in four-digit format from 1901 to 2155, and 0000.

Jyoti Mishra
Latest posts by Jyoti Mishra (see all)

Leave a Reply

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