MySQL data types: strings, numbers, and dates detailed explanation

Detailed explanation of MySQL data types: string, number, date

In MySQL, select the appropriate data type for the databaseStorage efficiencyandQuery performanceIt is crucial. MySQL provides three major data types: String, Number, and Date, and Time. Each type has a different subtype to meet different business needs.

1. String data type

String type is used to store text data, mainly divided intoFixed length (CHAR)andVariable length (VARCHAR),as well asLarge text (TEXT and BLOB)。

1.1 Fixed and variable length strings

CHAR vs. VARCHAR

• CHAR(10)Always take up 10 bytes (not enough to fill spaces).
• VARCHAR(10)Store only actual characters, up to 10 bytes, and an additional 1-2 bytes are used to store length information.

Generally speaking:

• Short, fixed-length text(such as country code, MD5 hash)CHAR。
• Text with unfixed length(such as name and address)VARCHAR。

1.2 TEXT and BLOB

When storing longer text or binary data, useTEXT and BLOBtype:

TEXT vs. VARCHAR

• TEXTNot supporteddefault value, also unavailableIndex (only prefix indexing is supported), but can store a lot of text.
• VARCHARThe entire field can be indexed and is suitable for text fields that require frequent searches.

2. Numeric data type

Number types are used to store integers or decimals, mainly divided intoInteger TypeandFloating point/fixed point type。

2.1 Integer Type

Unsigned (UNSIGNED) vs. Signed

• TEXTNot supporteddefault value, also unavailableIndex (only prefix indexing is supported), but can store a lot of text.
• VARCHARThe entire field can be indexed and is suitable for text fields that require frequent searches.

2. Numeric data type

Number types are used to store integers or decimals, mainly divided intoInteger TypeandFloating point/fixed point type。

2.1 Integer Type

Unsigned (UNSIGNED) vs. Signed

• Integers are signed by default(i.e., negative numbers are supported).
• UNSIGNEDKeywords can make integers store onlyNon-negative number, thereby expanding the available range.

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

2.2 Floating and fixed-point types

FLOAT vs. DECIMAL

FLOAT/DOUBLE uses binary storage, which may result in accuracy errors:

SELECT 0.1 + 0.2; -- The result may not be 0.3

DECIMAL uses string storage without losing precision:

CREATE TABLE transactions (
    amount DECIMAL(10,2) NOT NULL
);

suggestion:

• Use of financial dataDECIMAL, avoid calculation errors.
• Non-critical calculations (such as statistics) are availableFLOATorDOUBLE。

3. Date & Time

MySQL provides multiple date and time types:

DATETIME vs. TIMESTAMP

• TIMESTAMP is affected by time zone, suitable for storing the time of event occurrence.
• DATETIME is not affected by time zone, suitable for storage time.

CREATE TABLE events (
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

4. Best practices for choosing a data type

• If you can use integers, don't use strings (such as gender is availableTINYINT）。
• When storing amounts and financial data, useDECIMALInsteadFLOAT。
• Short text (<255 characters)VARCHAR, for long textTEXT。
• Try to avoid index fieldsTEXTandBLOB, affects query performance.
• Try to use timestamp dataTIMESTAMPreplaceDATETIME, save storage space.

Summarize

MySQL provides a variety of data types, each with its applicable scenarios. Reasonable selection of data types can improve storage efficiency, optimize query performance, and avoid accuracy losses. I hope this article can help you make better choices when designing databases! 🚀

This is the end of this article about MySQL data type detailed explanation: strings, numbers, and dates. For more related content of mysql data type, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!