MySQL online course

MySQL Data Types

Learn how MySQL data types work and how to choose CHAR, VARCHAR, INT, DECIMAL, DATE, DATETIME, BINARY, BLOB, and other common column types.

A data type is a column definition that determines the category of values MySQL stores and processes. Every table column has a data type, such as text, an integer, a date, or binary bytes.

Types are assigned in a CREATE TABLE definition. They constrain inserted and updated values and influence storage requirements, validation, indexing, sorting, comparisons, and query behavior. Choosing a suitable type helps MySQL handle data consistently and efficiently.

For background, review MySQL database terms, creating tables, and inserting new records.

Main MySQL Data-Type Families

MySQL supplies many specialized types, but common applications mainly use these families:

• String and text types: character data such as names, labels, and descriptions.
• Numeric types: whole numbers, approximate measurements, and exact decimal amounts.
• Date and time types: calendar dates, times, durations, and event moments.
• Binary types: raw bytes such as hashes, file data, and encoded attachments.
• Spatial types: geographic and geometric values such as points and shapes.

This lesson concentrates on the types most frequently used in ordinary tables. MySQL also includes additional integer sizes, text types, JSON, enumerated types, set types, and spatial types.

Common MySQL Data Types at a Glance

Character String Types

CHAR

CHAR(n) stores a fixed-length character string. A value in the column is treated as having the declared width, so it is a good fit when values have a predictable size.

Use CHAR for fixed codes such as a two-letter country code, a fixed-width product code, or a short status code. MySQL may pad shorter values to the fixed width; trailing-space behavior can also depend on the column's collation and SQL settings.

VARCHAR

VARCHAR(n) stores a variable-length character string with a declared maximum length. It stores the value's length along with its characters rather than treating every value as the full fixed width.

VARCHAR is usually appropriate for names, titles, usernames, URLs, and email addresses. The value must still fit the declared maximum. The maximum is expressed in characters, although the number of stored bytes also depends on the character set.

CHAR versus VARCHAR

Text columns also have a character set, which defines how characters map to stored bytes, and a collation, which defines comparison and sorting rules. Check these settings when case sensitivity, accent sensitivity, or multilingual text matters.

Binary and Large Binary Data

BINARY(n) stores a fixed-length byte string. Its contents are not interpreted through a character set, so the bytes are compared as binary data rather than as ordinary text.

Use binary types for raw file bytes, cryptographic hashes, encrypted values, or other data that should not be interpreted as characters. A fixed-length hash can be a sensible use of BINARY.

BLOB means binary large object. It stores substantial binary content such as image bytes, document content, or an uploaded file. BLOB variants provide different capacity limits:

• TINYBLOB: up to 255 bytes.
• BLOB: up to 65,535 bytes.
• MEDIUMBLOB: up to 16,777,215 bytes.
• LONGBLOB: up to 4,294,967,295 bytes, subject to server and practical limits.

Binary data is different from text. Do not put arbitrary file bytes in a character column or apply text encoding assumptions to a BLOB. Large media stored in the database can increase backup size, query cost, and storage-engine workload. For very large files, a common design is to store the file in dedicated object or file storage and keep its identifier, filename, size, and metadata in MySQL.

Numeric Types

INT

INT stores whole-number values. It is commonly used for identifiers, quantities, counts, ratings, and inventory levels. An ordinary signed INT supports negative and positive values within its range.

Signed numeric columns permit negative and positive values. UNSIGNED disallows negative values and changes the available range toward nonnegative values. For example, a stock quantity cannot normally be negative, so INT UNSIGNED may express that rule.

FLOAT and DECIMAL

FLOAT is a single-precision approximate floating-point type. It can represent a wide range of measurements efficiently, but some decimal fractions cannot be represented exactly in binary floating-point format.

Use FLOAT for approximate measurements such as sensor readings, coordinates with acceptable approximation, or scientific calculations. Do not use it for currency or any value that must remain exact after arithmetic.

DECIMAL(p,s) is an exact fixed-point type. p is the total number of digits and s is the number of digits after the decimal point. For example, DECIMAL(10,2) can represent amounts with two fractional digits and is a usual choice for prices.

Date and Time Types

Use valid, consistent values so that MySQL can validate, compare, sort, and calculate with them.

• DATE stores a calendar date, normally written as YYYY-MM-DD, such as '2026-08-18'. Use it for birthdays, publication dates, and deadlines when a time of day is not needed.
• TIME stores a time of day or an elapsed-time value, normally written as HH:MM:SS, such as '09:30:00'. Use it for daily opening times, event start times, or durations.
• DATETIME stores a combined date and time, normally written as YYYY-MM-DD HH:MM:SS, such as '2026-08-18 09:30:00'. Use it for bookings, appointments, and event records.
• TIMESTAMP is also often used for recorded moments such as row creation or update times. It can have different timezone conversion and automatic-default behavior from DATETIME, so choose deliberately when applications run across time zones.

Using Data Types in CREATE TABLE

The general column declaration contains a name, a data type, and optional length or precision. Constraints such as NOT NULL, DEFAULT, and PRIMARY KEY add rules beyond the type itself.

CREATE TABLE product_events (
    event_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_code    CHAR(8) NOT NULL,
    product_name    VARCHAR(150) NOT NULL,
    stock_quantity  INT UNSIGNED NOT NULL DEFAULT 0,
    price           DECIMAL(10,2) NOT NULL,
    temperature     FLOAT NULL,
    available_on    DATE,
    opening_time    TIME,
    created_at      DATETIME NOT NULL,
    checksum        BINARY(32),
    attachment      BLOB,
    PRIMARY KEY (event_id)
);

Here, CHAR(8) is suitable for a fixed-width product code, while VARCHAR(150) allows names to vary. The quantity is nonnegative, the price is exact, and the temperature may be approximate. The binary checksum is fixed-size, while the attachment can contain substantial raw bytes.

For character types, a declaration such as VARCHAR(150) sets a maximum character length. Numeric declarations follow different rules: DECIMAL(10,2) specifies total precision and scale, while an integer's size determines its whole-number range. The exact range should match the application's requirements.

Inserting and Retrieving Typed Values

Text and date/time literals are generally quoted. Numeric literals are normally unquoted.

INSERT INTO product_events
    (product_code, product_name, stock_quantity, price,
     temperature, available_on, opening_time, created_at, checksum)
VALUES
    ('AB12X900', 'Desk Lamp', 25, 39.95,
     21.5, '2026-09-01', '09:00:00',
     '2026-08-18 14:30:00', 0x0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF);

SELECT event_id, product_code, product_name, stock_quantity,
       price, available_on, opening_time, created_at
FROM product_events;

You can inspect the table definition with:

DESCRIBE product_events;
SHOW COLUMNS FROM product_events;

For more practice, see querying a database and MySQL date functions.

Choosing an Appropriate Data Type

1. Start with semantic meaning. Store a birthday as DATE, a count as an integer, and a price as DECIMAL instead of putting each value in text.
2. Choose the smallest suitable type. It should preserve the required range, precision, and character capacity without wasting space.
3. Consider character requirements. Select an appropriate character set for the languages you store and a collation that matches comparison and sorting rules.
4. Consider query patterns. Values that are filtered, joined, sorted, or indexed should use types that represent their actual meaning. Numeric text sorts lexically, not numerically.
5. Keep binary data binary. Use BINARY or a BLOB variant for raw bytes, not a character column.
6. Add constraints. Use NOT NULL, suitable DEFAULT values, and a PRIMARY KEY when the data model requires them.

For example, a product catalog might use VARCHAR for a product name, CHAR for a fixed product code, INT UNSIGNED for stock, DECIMAL for price, and DATE or DATETIME for availability. An event scheduler might use VARCHAR for the title, DATE for the event date, TIME for its start time, and DATETIME for the booking time.

Troubleshooting Data-Type Problems

Text is too long

A value that exceeds a CHAR or VARCHAR limit can produce a data-truncation or value-too-long error. Increase the limit only when the business requirement supports it, or validate and shorten the input appropriately. Do not choose an oversized text type merely to hide invalid input.

Currency has unexpected fractional results

Floating-point values are approximate, so calculations with FLOAT can produce surprising fractional results. Use DECIMAL with suitable precision and scale for currency.

Date or datetime insertion fails

Check that dates use YYYY-MM-DD and datetimes use YYYY-MM-DD HH:MM:SS. Check for invalid calendar dates, a missing time portion, and the server's SQL mode, which affects how invalid values are handled.

A negative value cannot be stored

Inspect whether the column is UNSIGNED. If negative values are legitimate, use a signed numeric type with a range that fits the requirement.

Text comparison or sorting is unexpected

Check the column's character set and collation. Confirm whether comparisons should be case-sensitive or accent-sensitive and whether the selected collation provides those rules.

Binary content is corrupted

Use binary types for raw bytes and text types for character data. Avoid applying text encoding or character-set assumptions to BLOB or BINARY values.

Exam-Relevant Notes

• CHAR is fixed length; VARCHAR is variable length with a declared maximum.
• INT stores exact whole numbers; FLOAT stores approximate values; DECIMAL stores exact fixed-point values.
• DATE stores a date, TIME stores a time or duration, and DATETIME stores both.
• BINARY and BLOB store bytes rather than character data.
• UNSIGNED disallows negative numeric values and changes the available range.
• Column types affect validation, storage, comparisons, sorting, indexing, and the behavior of inserted or updated data.