Detailed Explanation of MySQL Data Types: Basic Type Selection for Beginners

In MySQL, data types are like “labels” for data. Different labels determine how data is stored, how much space it occupies, and what operations it can perform. Choosing the right data type not only makes the database more efficient and saves storage space but also avoids data errors and overflow. For beginners, mastering the selection of basic data types is the first step in learning MySQL.

I. Why is it important to pay attention to data types?¶

Incorrectly choosing a data type can have serious consequences: For example, using INT to store a phone number (11 digits, as INT only supports about 10 digits) will cause data overflow; using CHAR(20) to store a short name (e.g., “Zhang San”) will waste 19 spaces. Therefore, understanding data types is fundamental to writing good SQL.

II. Numeric Types: How to store numbers?¶

Numeric types are the most commonly used data types, divided into integers, floating-point numbers, and fixed-point numbers, each with its own application scenarios.

1. Integer Types¶

TINYINT: 1 byte, range (signed: -128~127; unsigned: 0~255). Commonly used for small-range data, such as gender (1=male, 0=female) or status (0=disabled, 1=enabled).
Note: The number in parentheses (e.g., TINYINT(1)) only indicates the display width and has no relation to the storage range (TINYINT(1) and TINYINT(3) occupy the same storage size).
SMALLINT: 2 bytes, range (signed: -32768~32767; unsigned: 0~65535). Suitable for medium-range data, such as age (assuming maximum age is 100).
INT (INTEGER): 4 bytes, the most commonly used integer type, range (signed: -2147483648~2147483647; unsigned: 0~4294967295). Used for user IDs, order numbers, etc.
BIGINT: 8 bytes, range (signed: -9223372036854775808~9223372036854775807; unsigned: 0~18446744073709551615). Only used for extremely large datasets, such as millions of user IDs.
Unsigned Integers: Added with UNSIGNED to indicate non-negative values. For example, TINYINT UNSIGNED can store 0~255, avoiding space waste from negative numbers (e.g., TINYINT UNSIGNED is sufficient for age up to 255).

2. Floating-Point Types¶

Used to store decimals, such as prices, heights, and weights.

FLOAT: 4 bytes, low precision (about 7 significant digits). Suitable for scenarios with low precision requirements, such as temperature (FLOAT(5,2) represents up to 5 digits with 2 decimal places, range -999.99~999.99).
DOUBLE: 8 bytes, high precision (about 15 significant digits). Suitable for scenarios with higher precision requirements, such as exam scores (DOUBLE(4,2) represents 4 total digits with 2 decimal places, range -99.99~99.99).
Note: Floating-point numbers may have precision errors (e.g., 0.1+0.2=0.30000000000000004). For financial data (e.g., amounts), use fixed-point numbers.

3. Fixed-Point Types¶

DECIMAL(M,D): Stores high-precision decimals, where M is the total number of digits and D is the number of decimal places. For example, DECIMAL(10,2) means “10 digits with 2 decimal places” (range determined by M, precise up to 30 decimal places).
Application Scenarios: Amounts (e.g., order prices), banking data, and other fields requiring precise calculations.

III. String Types: How to store text?¶

String types are divided into fixed-length and variable-length, and should be chosen based on text length.

1. Fixed-Length: CHAR(M)¶

CHAR(M): M is the fixed length (1~255 bytes). Insufficient length is padded with spaces. Suitable for short and fixed text.
Application Scenarios: Gender (CHAR(1) for “male/female”), postal codes (CHAR(6)), fixed-length mobile phone prefixes, etc.
Advantages: High storage efficiency and fast query speed; Disadvantages: Wastes space (e.g., storing “Zhang” will pad 24 spaces to CHAR(25)).

2. Variable-Length: VARCHAR(M)¶

VARCHAR(M): M is the maximum length (1~65535 bytes, depending on the character set). Only stores actual content (no padding with spaces). Suitable for text with variable lengths.
Application Scenarios: Names (VARCHAR(50)), emails (VARCHAR(100)), addresses (VARCHAR(200)), etc.
Advantages: Saves space; Disadvantages: Takes 1-2 extra bytes to store the length compared to CHAR (if length exceeds 255 bytes, additional bytes are needed to record the length).

3. Ultra-Long Text: TEXT¶

TEXT: No fixed length limit (maximum 65535 bytes). Used for storing long text, such as article content, comments, and notes.
Note: TEXT cannot have default values and should be used with VARCHAR—use VARCHAR for short text and TEXT for long text.

IV. Date and Time Types: How to store time?¶

MySQL provides multiple time types, and the choice depends on requirements:

1. DATE: Stores only the date¶

Format: YYYY-MM-DD, range: 1000-01-01~9999-12-31.
Application Scenarios: Fields requiring only dates, such as birthdays and hire dates.

2. DATETIME: Stores date + time¶

Format: YYYY-MM-DD HH:MM:SS, range: 1000-01-01~9999-12-31, occupies 8 bytes.
Application Scenarios: Order creation time, meeting start time, and other fields requiring complete date and time.

3. TIMESTAMP: Timestamp (auto-update)¶

Format: YYYY-MM-DD HH:MM:SS, range: 1970-01-01 00:00:01~2038-01-19 03:14:07 (32-bit timestamp), occupies 4 bytes.
Features:
Automatically updates with the server time (e.g., ON UPDATE CURRENT_TIMESTAMP);
Occupies less space, suitable for time-sensitive data (e.g., user login time, order payment time).

V. Other Common Types¶

1. Boolean Type (Replaced by TINYINT)¶

MySQL has no dedicated BOOLEAN type; instead, TINYINT(1) is used to represent “true/false”:
- 0 = false (FALSE), 1 = true (TRUE). Commonly used for status fields (e.g., “whether enabled”).

2. Enumerated Type (ENUM)¶

ENUM('Male','Female'): Can only select one value from a preset list, avoiding input errors (e.g., more standardized for gender fields).
Example: status ENUM('active','inactive') stores “active” or “inactive”.

3. Set Type (SET)¶

SET('A','B','C'): Can select multiple values from a preset list (separated by commas), suitable for multi-selection scenarios.
Example: permission SET('read','write','delete') stores “read,write” to indicate simultaneous read and write permissions.

VI. How to Choose Data Types?¶

Prioritize the smallest type: Use TINYINT instead of INT when possible to save space (e.g., TINYINT UNSIGNED is sufficient for ages 0-255).
Fixed length first: Use CHAR for short text (e.g., gender) and TEXT for long text (e.g., content).
Based on business scenarios:
- Use VARCHAR(11) for phone numbers (cannot be stored in INT);
- Use INT or BIGINT for order IDs (avoid overflow);
- Use DECIMAL(10,2) for amounts (avoid floating-point errors).
Avoid over-engineering: Use INT for IDs when sufficient (no need for BIGINT); status fields can use TINYINT(1) instead of ENUM for simplicity.
Avoid NULL abuse: Primary keys/unique fields must be NOT NULL; nullable fields should use default values (e.g., age defaults to 0).

VII. Common Error Summary¶

❌ Using INT to store phone numbers (11 digits exceed INT range);
❌ Using CHAR(255) for long text (wastes space and reduces efficiency);
❌ Using DATE to store current time (use DATETIME or TIMESTAMP);
❌ Using VARCHAR to store IDs (INT is faster for queries and more standardized).

VIII. Conclusion¶

Data types are fundamental to MySQL. Choosing the right type makes the table structure more reasonable, queries faster, and data safer. Beginners can start with the three main categories: “integers + short text + dates”, and gradually learn advanced types like ENUM and SET after gaining proficiency. By practicing more and observing table structures in production environments, you can master the skills of type selection.