Parsing MySQL Data Types to avoid confusion in Database Design

The data types and sizes in database storage vary. In some cases, only the numeric type is stored, and some only the text type is stored. Many databases support various specialized types: Date and Time, binary character, and Boolean.

Matching data types with data is the most important part in database design, because this type will affect the efficiency and performance of RDBMS. Therefore, you should pay enough attention to the selection of RDBMS data types.

This is the main purpose of writing this article. Table A lists most important data types supported by MySQL, which is currently the most popular free RDBMS. This article also describes when and how to use these data types. This will facilitate the rational design of a database.

Table

Data Type	Description	Bytes	Recommended
SMALLINT	Integer in the range of-32000 to + 32000	2	Store relatively small integers. For example, age and quantity
INT	Integer in the range of-2000000000 to + 2000000000	4	Store medium Integers For example, distance
BIGINT	An integer that cannot be described by SMALLINT or INT.	8	Store large Integers Example: scientific/mathematical data
FLOAT	Single-precision floating point data	4	Store decimal data For example: measurement, temperature
DOUBLE	Double Precision Floating Point Data	8	Decimal data that requires double-precision Storage For example, scientific data
DECIMAL	User-Defined Precision Floating Point Data	Variable; depends on precision and length	Stores fractional data with high precision. Example: monetary amount, scientific data
CHAR	Fixed-length string	Specific String Length (up to 255 characters)	Store variables that typically contain predefined strings Example: regular route, country or zip code
VARCHAR	Variable-length string with maximum limit	Variable; 1 + actual String Length (up to 255 characters)	Stores string values of different lengths (up to a specific maximum ). Example: name, password, short text tag
TEXT	Variable-length string with no maximum length limit	Variable; 2 + actual string length	Store large text data For example, news stories and product descriptions
BLOB	Binary string	Variable; 2 + actual String Length	Store binary data Example: image, attachment, binary document
DATE	Date in yyyy-mm-dd format	3	Storage date For example, birthday, product expiration
TIME	Time in hh: mm: ss format	3	Storage Time or interval For example, alarm, interval between two times, task start/End Time
DATETIME	Date and time are combined in yyyy-mm-ddhh: mm: ss format	8	Store data containing dates and times For example, the person who reminds you of the event.
TIMESTAMP	Date and time are combined in yyyy-mm-ddhh: mm: ss format	4	Record Real-Time For example, the "Last entry" time mark of the event daemon
YEAR	Year in yyyy format	1	Storage year Example: Graduation Year, birth year
ENUM	A group of data from which you can select	1 or 2 bytes	Stores character attributes. Only one of them can be selected. Example: Boolean selection, such as gender
SET	A group of data from which you can select 0, 1 or more.	The value ranges from 1 to 8 bytes, depending on the configured size.	Stores character attributes, from which you can select the Union of multiple characters. For example, multiple options, such as hobbies and interests.

For a complete list and detailed description, you can view MySQL manual. You can also read the articleChoosing the Right Type for a Column.

(