VMware ESXi and vSphere Cluster Management

MySQL Data Types: Strings, Numbers, Binary Data, and Date/Time Values

Learn how to choose MySQL data types for text, numbers, binary content, dates, times, and datetimes when designing tables.

A MySQL data type is the rule that determines what kind of value a column may store. A column is a named field in a table, and each column receives a data type when the table is created.

For example, a product name needs character data, a stock quantity needs whole numbers, and a publication date needs a calendar date. Choosing types that match the meaning of your data helps MySQL validate values, allocate storage, compare and sort values, use indexes effectively, and produce predictable query results. Good type choices also make a schema more efficient and keep data consistent.

A table's structural definition, including its columns and data types, is part of its schema.

Common MySQL Data Type Categories

Common MySQL types belong to several broad categories:

• String and text types: character data such as names, codes, titles, and addresses.
• Numeric types: whole numbers and values with fractional parts.
• Binary-data types: raw bytes such as hashes, images, and documents.
• Temporal types: dates, times, and combined date-time values.
• Spatial types: geographic and geometric data. These are another MySQL type family, but are outside this lesson's main scope.

Character String Types

CHAR: Fixed-Length Character Strings

CHAR stores character data with a declared fixed length. For example, CHAR(2) is suitable for a two-character state or region code, and CHAR(8) can represent a fixed-width product code.

Useful examples include country codes, abbreviations, fixed-width identifiers, and short status flags. Its fixed-length behavior contrasts with VARCHAR: CHAR is designed for values with a known, consistent size, while VARCHAR is designed for values whose lengths vary.

CHAR values are textual values, so their character set determines how characters are represented. Their collation supplies rules for comparing and sorting them. For example, language-aware comparisons can differ from byte-for-byte comparisons.

VARCHAR: Variable-Length Character Strings

VARCHAR stores strings whose lengths may vary up to a declared maximum. Examples include:

• VARCHAR(100) for a customer name.
• VARCHAR(255) for a file name or email address.
• VARCHAR(200) for an event title.
• VARCHAR(500) for a street address.

The number in VARCHAR(150) is a maximum, not a requirement. A value may contain fewer than 150 characters. VARCHAR is usually the better choice when input lengths naturally vary, because it represents the data's variable-length meaning rather than pretending every value has the same width.

Like CHAR, VARCHAR uses a character set for textual representation and a collation for comparison and sorting.

Binary Data Types

BINARY: Fixed-Length Byte Strings

BINARY holds a fixed-length sequence of bytes. It is not ordinary text interpreted through a character set. Use it for fixed-size hashes, byte identifiers, or small binary payloads.

For example, a hash that always contains 16 bytes can be stored in BINARY(16). A binary column compares byte values directly, which is different from a character column whose comparison may follow collation rules.

BLOB: Binary Large Objects

BLOB means binary large object. The BLOB family stores raw bytes such as image files, PDF documents, audio data, or other non-text file content. A standard BLOB supports values up to approximately the 65,536-byte threshold; MySQL also provides smaller and larger BLOB variants for different maximum sizes.

BLOB data is bytes rather than character-set text. This distinguishes it from both VARCHAR and text-oriented types. If a file's size varies or is larger than a small fixed binary value, a BLOB-family type is generally more appropriate than BINARY.

Storing files in MySQL keeps metadata and content together and can simplify transactional backups. However, large BLOB values can increase database size, backup duration, replication traffic, and query transfer costs. Another design is to store the file in a file service or object storage and keep its location, identifier, size, and checksum in MySQL. Choose based on transaction needs, access patterns, backup requirements, and operational limits.

Numeric Data Types

INT: Whole Numbers

INT stores whole numbers without fractions. Common uses include quantities, counts, ages, and identifiers.

An integer can be signed or unsigned. A signed integer permits negative and positive values. UNSIGNED excludes negative values and gives more room to the nonnegative range. Use INT UNSIGNED for values such as inventory counts when a negative value has no valid meaning. Use a signed type when negative values are meaningful, such as a temperature change or account adjustment.

Choose an integer type from the required numeric range. A display-width specification is not a way to limit the number of digits stored; it does not impose a digit limit on an integer.

FLOAT: Approximate Fractional Values

FLOAT stores single-precision floating-point values. It is useful for measurements, scientific values, and sensor readings where small rounding differences are acceptable.

Floating-point values are approximate because many decimal fractions cannot be represented exactly in binary floating-point format. Consequently, calculations and comparisons may produce small differences. Do not generally use FLOAT for currency, balances, tax amounts, or other values requiring exact decimal arithmetic. Use DECIMAL instead.

For example, FLOAT can represent a weight such as 0.35, while a monetary price can use DECIMAL(10,2) to represent exact cents within the chosen precision and scale.

Date and Time Types

DATE

DATE stores a calendar date without a time of day. Its conventional literal form is YYYY-MM-DD, such as '2026-08-19'.

Use DATE for birth dates, publication dates, due dates, and other date-only facts. It is preferable when time-of-day and timezone details are not needed.

TIME

TIME stores a time or a time-like duration. Its conventional representation is HH:MM:SS, such as '09:30:00'.

Use TIME for opening times, appointment times when the date is stored separately, elapsed durations, and shift lengths. TIME differs from DATE because it has no calendar date, and it differs from DATETIME because it has no date component.

DATETIME

DATETIME stores a date and a time together. Its conventional literal representation is YYYY-MM-DD HH:MM:SS, such as '2026-08-19 09:30:00'.

Use DATETIME for order placement times, appointments, audit records, and record creation times when both components are needed. Use DATE for date-only information and TIME for time-only or duration information.

When an application operates across time zones, decide deliberately how values are created, stored, displayed, and converted. A DATETIME column contains a date and time value, but timezone policy is an application and schema-design responsibility.

Choosing an Appropriate Column Type

Select a type based on the data's meaning, not merely how the value happens to look. A number-like code may still be text if leading zeros or nonnumeric characters matter. Similarly, a date-looking value should use a temporal type when date operations are required.

1. Identify the category: character, numeric, binary, date, time, or combined date-time.
2. Estimate the maximum expected size or numeric range.
3. Decide whether the value is fixed-length or variable-length.
4. For numbers, decide whether fractions are needed and whether approximation is acceptable.
5. For temporal data, decide whether the value needs a date, a time, or both.
6. Consider comparison rules, indexing, validation, storage, and future growth.
7. Check whether the application needs timezone-aware handling or exact decimal arithmetic.

Common field choices include CHAR(2) for a two-character region code, VARCHAR(100) for a customer name, INT UNSIGNED for a nonnegative item count, FLOAT for an approximate sensor reading, DECIMAL for a financial balance, DATE for a date-only event, TIME for a time-only value, DATETIME for a timestamped event, and BLOB for raw file data.

Using Data Types in a Table Definition

The following table combines character, binary, numeric, and temporal types:

CREATE TABLE inventory_item (
  item_code CHAR(8) NOT NULL,
  item_name VARCHAR(150) NOT NULL,
  signature BINARY(16),
  attachment BLOB,
  quantity INT UNSIGNED NOT NULL,
  weight_kg FLOAT,
  release_date DATE,
  opening_time TIME,
  created_at DATETIME NOT NULL
);

An INSERT should provide values compatible with the target columns. The binary columns are omitted here, so they receive their default nullable value.

INSERT INTO inventory_item
  (item_code, item_name, quantity, weight_kg, release_date, opening_time, created_at)
VALUES
  ('AB123456', 'Travel bottle', 24, 0.35, '2026-08-19', '09:30:00', '2026-08-19 09:30:00');

Use DESCRIBE to inspect the resulting definition:

DESCRIBE inventory_item;

For exact monetary values, define an appropriate DECIMAL column:

CREATE TABLE product_price (
  product_name VARCHAR(150) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

Inserted values must be compatible with their column types. A type mismatch, oversized string, malformed date or time literal, impossible calendar value, or nonnumeric numeric value may produce an error or an undesirable conversion. The exact result depends partly on MySQL's SQL mode, so applications should validate input and use a strict configuration where appropriate.

Practical Type-Selection Examples

Product Catalog

product_code  CHAR(8)
product_name  VARCHAR(150)
stock_quantity INT UNSIGNED
weight_kg     FLOAT
available_from DATE

This design combines a fixed code, variable text, a nonnegative count, an approximate measurement, and a date-only value.

Event Scheduling

event_title VARCHAR(200)
event_date  DATE
start_time  TIME
created_at  DATETIME

Separate DATE and TIME values describe the scheduled event, while DATETIME records when the row was created.

Digital Asset Record

file_name    VARCHAR(255)
content_hash BINARY(32)
file_data    BLOB

The file name is text, the hash is a fixed-size byte sequence, and the file content is variable-size raw binary data.

Troubleshooting Type Problems

String Is Too Long

If a value exceeds a CHAR or VARCHAR limit, the declared maximum is smaller than the input. Increase the column limit when the business requirement supports it, or validate and shorten the input before insertion. Do not silently discard meaningful data.

Negative Value Rejected by INT UNSIGNED

UNSIGNED permits zero and positive values only. Use a signed integer when negative values are meaningful, or correct the source data when the field should never be negative.

Unexpected Currency Differences

Unexpected fractional differences commonly result from using FLOAT for currency. FLOAT is approximate. Use DECIMAL with suitable precision and scale for exact decimal arithmetic.

Date or Datetime Rejected

Use YYYY-MM-DD for DATE, HH:MM:SS for TIME, and YYYY-MM-DD HH:MM:SS for DATETIME. Also check for impossible calendar values and validate application input before sending it to MySQL.

Text Comparison Does Not Match Byte-for-Byte Expectations

Character columns use a character set and collation, while binary columns compare raw bytes. Choose a suitable text collation for language-aware text, or use a binary type when byte-level behavior is required.

Large Files Cause Operational Problems

Large BLOB values can increase database size, backup duration, transfer cost, and query overhead. Reassess whether the content must be stored in MySQL. In some systems, external file or object storage is more suitable, with MySQL retaining metadata and a reference.

Exam-Relevant Notes

• CHAR is fixed-length character data; VARCHAR is variable-length character data with a maximum.
• BINARY stores fixed-length bytes; BLOB stores variable-size raw binary content.
• INT stores whole numbers. UNSIGNED excludes negative values; it is not a display-width setting.
• FLOAT is approximate. Use DECIMAL when exact decimal calculations are required.
• DATE stores a date, TIME stores a time or duration, and DATETIME stores both date and time.
• Character set and collation affect how textual values are represented, compared, and sorted.
• Choose types according to semantic meaning, range, length, precision, and temporal requirements.

For related schema work, continue with MySQL data types while considering constraints, indexes, character sets, and timezone design alongside each column definition.