Resolving MySQL data types to avoid confusion in database design

The data types and sizes in the database store are different. Some places store only numeric types, some store only text types, and some both. Many databases support a variety of specialized types: date and time types, binary character types, and Boolean types.

Selecting data types and data matching is the most important part of database design, because this type will affect the efficiency and performance of the RDBMS. Therefore, the choice of the data type of RDBMS should be paid enough attention.

This is the main purpose of writing this article. Table A lists most of the important data types that MySQL supports, and MySQL is the most popular free RDBMS available today. The article also describes when and how to use these data types. This will help to establish a reasonable design of the database.

Table A

Data type	Describe	Bytes	Recommended Use
SMALLINT	integers, ranging from 32000 to +32000	2	Stores a relatively small integer. For example: Age, number
Int	integers, ranging from 2000000000 to +2000000000	4	Store medium integers For example: Distance
BIGINT	An oversized integer that cannot be described with smallint or int.	8	Store oversized integers For example: scientific/Mathematical data
FLOAT	Single-precision floating-point data	4	Storing decimal data For example: measurement, temperature
DOUBLE	Double-precision floating-point data	8	Decimal data to be stored in double precision For example: scientific data
DECIMAL	Floating-point data for user-defined precision	variable, depending on precision and length	Store decimal data in exceptionally high precision. For example: Monetary amount, scientific data
CHAR	Fixed-length strings	Specific string length (up to 255 characters)	Stores variables that typically contain predefined strings For example: regular route, country or postcode
VARCHAR	Variable-length strings with a maximum limit	Variable 1 + Actual string length (up to 255 characters)	Store string values of different lengths (up to a specific maximum). For example: Name, password, short text label
TEXT	Variable-length strings with no maximum length limit	Variable; 2 + Listen actual string length	Storing large text data For example: news stories, product descriptions
Blob	Binary strings	Variable; 2 + actual string length	Storing binary data For example: Picture, attachment, binary document
DATE	Date in YYYY-MM-DD format	3	Store Date For example: birthday, product expiration
Time	Time in HH:MM:SS format	3	Store time or time interval Example: Alarm sound, interval between two times, task start/End time
Datetime	Combine date and time in YYYY-MM-DDHH:MM:SS format	8	Store data that contains dates and times For example: A person who reminds, an event
TIMESTAMP	Combine date and time in YYYY-MM-DDHH:MM:SS format	4	Record Instant time For example: Event reminders, "last entry" time marker
Year	Year in YYYY format	1	Storage year For example: Graduation year, birth year
Enum	A set of data from which a user can select one	1 or 2 bytes	Store character attributes, only one of them can be selected from For example: Boolean selection, such as gender
SET	A set of data from which the user can select 0, 1, or more.	From 1 to 8 bytes, depending on the size of the setting	Stores character attributes from which you can select a union of multiple characters. For example: multiple options, such as hobbies and interests.

For a complete list and detailed description, you can view the MySQL manual. You can also read the article choosing the right Type for a column.