MySQL Data Types with Examples

Numeric Data Types

Numeric Data TypesIn MySQL, numeric data types are used to store numeric values. They can be categorized into exact numeric types, like INTEGER, and approximate numeric types, like FLOAT.

Data Type	Description	Example
INT	A normal-size integer that can be signed or unsigned.	INT(11)
FLOAT	A floating-point number.	FLOAT
Data Type	Description	Example
TINYINT	A very small integer that can range from -128 to 127.	TINYINT
SMALLINT	A small integer that can range from -32,768 to 32,767.	SMALLINT
MEDIUMINT	A medium-sized integer that can range from -8,388,608 to 8,388,607.	MEDIUMINT
INT	A standard integer that can range from -2,147,483,648 to 2,147,483,647.	INT
BIGINT	A large integer that can range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.	BIGINT
DECIMAL	An exact fixed-point number.	DECIMAL(5,2)
FLOAT	A small approximate floating-point number.	FLOAT
DOUBLE	A large approximate floating-point number.	DOUBLE

String Data Types

String Data TypesIn MySQL, string data types are used to store textual or binary data. They come in various forms to suit different requirements for storage and complexity.

Data Type	Description	Example
VARCHAR	A variable-length string.	VARCHAR(255)
TEXT	A text column with a maximum length of (2^{16} – 1) characters.	TEXT
Data Type	Description	Example
CHAR	A fixed-length string that is always right-padded with spaces to the specified length.	CHAR(25)
VARCHAR	A variable-length string. Efficient for storing strings that can have a variable length up to a defined maximum.	VARCHAR(255)
TINYTEXT	A small text column with a maximum length of 255 characters.	TINYTEXT
TEXT	A text column with a maximum length of (2^{16} – 1) characters.	TEXT
MEDIUMTEXT	A text column with a maximum length of (2^{24} – 1) characters.	MEDIUMTEXT
LONGTEXT	A text column with a maximum length of (2^{32} – 1) characters.	LONGTEXT
BINARY	Similar to CHAR but stores binary byte strings.	BINARY(10)
VARBINARY	Similar to VARCHAR but for binary byte strings.	VARBINARY(100)
BLOB	A Binary Large Object column that can store up to (2^{16} – 1) bytes of data.	BLOB

Temporal Data Types

Date and Time Data TypesIn MySQL, date and time data types are used to store date, time, or datetime values. These types allow you to work efficiently with date and time information.

Data Type	Description	Example
DATETIME	Used for values that contain both date and time.	DATETIME
DATE	Used for values with a date part but no time part.	DATE
Data Type	Description	Example
DATE	Used to store date values in the format ‘YYYY-MM-DD’.	DATE
TIME	Used to store time values in the format ‘HH:MM:SS’.	TIME
DATETIME	Used to store date and time values in the format ‘YYYY-MM-DD HH:MM:SS’.	DATETIME
TIMESTAMP	Stores the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC) as a datetime value.	TIMESTAMP
YEAR	Used to store year information, either as a 4-digit or 2-digit format.	YEAR(4)