MySQL Data Types INT, VARCHAR, DATE, JSON: Tutorial, Examples, FAQs & Interview Tips

MySQL Data Types INT, VARCHAR, DATE, JSON

MySQL in MySQL is best learned by connecting the rule to an order-management database. Start with the smallest SQL statement, observe the output, and then add one realistic constraint so the concept becomes practical.

The key habit for this lesson is to watch table rows, constraints, and indexes as it changes. That makes the topic easier to debug, easier to explain in interviews, and easier to use in real code without memorizing isolated syntax.

Numeric Data Types

MySQL provides a full range of integer and floating-point types. Choose the smallest type that fits your data to save storage and improve performance.

Type	Storage	Range (Signed)	Use Case
TINYINT	1 byte	-128 to 127	Flags, boolean-like values
SMALLINT	2 bytes	-32,768 to 32,767	Small counters
MEDIUMINT	3 bytes	-8,388,608 to 8,388,607	Medium-range IDs
INT	4 bytes	-2,147,483,648 to 2,147,483,647	General-purpose IDs
BIGINT	8 bytes	-9.2 × 10^18 to 9.2 × 10^18	Large IDs, timestamps
FLOAT	4 bytes	~7 decimal digits precision	Approximate decimals
DOUBLE	8 bytes	~15 decimal digits precision	Scientific calculations
DECIMAL(M,D)	Variable	Exact precision	Money, financial data

Numeric Column Definitions

CREATE table products (
    product_id   INT            UNSIGNED NOT NULL AUTO_INCREMENT,
    stock        SMALLINT       UNSIGNED NOT NULL DEFAULT 0,
    weight_kg    FLOAT          NOT NULL,
    rating       DECIMAL(3, 2)  NOT NULL DEFAULT 0.00,  -- e.g. 4.75
    price        DECIMAL(10, 2) NOT NULL,               -- e.g. 1999.99
    is_active    TINYINT(1)     NOT NULL DEFAULT 1,     -- boolean flag
    view_count   BIGINT         UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (product_id)
);

String Data Types

String types store text data. The key distinction is between fixed-length (CHAR) and variable-length (VARCHAR) types, and between inline storage and large object storage (TEXT family).

Type	Max Length	Use Case
CHAR(n)	255 chars	Fixed-length strings (country codes, hashes)
VARCHAR(n)	65,535 bytes	Variable-length strings (names, emails, URLs)
TINYTEXT	255 bytes	Very short text
TEXT	65,535 bytes	Articles, descriptions
MEDIUMTEXT	16 MB	Blog posts, HTML content
LONGTEXT	4 GB	Very large documents
ENUM	65,535 members	Fixed set of string values
SET	64 members	Multiple values from a fixed set

String Column Definitions

CREATE table customers (
    customer_id  INT          UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    email        VARCHAR(255) NOT NULL,
    country_code CHAR(2)      NOT NULL DEFAULT 'US',  -- fixed 2 chars
    status       ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active',
    preferences  SET('newsletter', 'sms', 'push')     NOT NULL DEFAULT '',
    bio          TEXT,
    password_hash CHAR(64)    NOT NULL,               -- SHA-256 hex = 64 chars
    PRIMARY KEY (customer_id)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Date and Time Data Types

Type	Format	Range	Use Case
DATE	YYYY-MM-DD	1000-01-01 to 9999-12-31	Birthdates, deadlines
TIME	HH:MM:SS	-838:59:59 to 838:59:59	Durations, schedules
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 to 9999-12-31	Event timestamps
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 to 2038-01-19	Row created/updated times
YEAR	YYYY	1901 to 2155	Year-only values

Date/Time, Binary, and JSON Column Definitions

CREATE table orders (
    order_id     INT       UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id  INT       UNSIGNED NOT NULL,
    order_date   DATE      NOT NULL,
    ship_time    TIME,
    ordered_at   DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    delivery_year YEAR,
    PRIMARY KEY (order_id)
);

-- Binary types: store raw bytes (images, files, hashes)
CREATE table files (
    file_id    INT          UNSIGNED NOT NULL AUTO_INCREMENT,
    file_name  VARCHAR(255) NOT NULL,
    mime_type  VARCHAR(100) NOT NULL,
    file_data  MEDIUMBLOB,           -- up to 16 MB binary data
    checksum   BINARY(16),           -- fixed 16-byte MD5 hash
    PRIMARY KEY (file_id)
);

-- JSON type (MySQL 5.7+): store structured JSON documents
CREATE table user_settings (
    user_id    INT  UNSIGNED NOT NULL,
    settings   JSON NOT NULL,
    PRIMARY KEY (user_id)
);

-- Insert and query JSON data
INSERT INTO user_settings (user_id, settings)
VALUES (1, '{"theme": "dark", "lang": "en", "notifications": true}');

SELECT user_id,
       settings->>'$.theme'         AS theme,
       settings->>'$.lang'          AS lang,
       settings->>'$.notifications' AS notifications
FROM user_settings
WHERE user_id = 1;

Applied guide for MySQL

Use MySQL when the program needs a clear answer to a specific problem, not because the keyword looks familiar. In a real MySQL task, first name the input, then name the transformation, then name the output. This small discipline shows whether the topic is being used correctly or only copied from an example.

A reliable practice flow is: create the smallest working SQL statement, add one normal case, add one edge case such as missing, repeated, empty, or boundary input, and then confirm the result with result set and EXPLAIN output. If the result surprises you, reduce the code until the behavior is visible again.

The most common trap here is copying the syntax before understanding the behavior. Avoid it by writing one sentence before the code that explains why MySQL is the right choice. After the code runs, verify the lesson by doing this: change one input and explain the changed output.

Identify the exact problem solved by MySQL.
Trace table rows, constraints, and indexes before and after the main operation.
Keep one intentionally broken version and explain the fix.
Connect the example to an order-management database so the idea feels concrete.

Key Takeaways

I can explain where MySQL fits inside an order-management database.
I can point to the exact table rows, constraints, and indexes affected by this topic.
I tested a normal case and an edge case involving missing, repeated, empty, or boundary input.
I verified the result with result set and EXPLAIN output instead of assuming it worked.
I can describe the main mistake: copying the syntax before understanding the behavior.

Common Mistakes to Avoid

WRONG Copying the syntax before understanding the behavior.

RIGHT Write the expected behavior first, then make the example prove it.

A one-line expectation turns the code from copied syntax into a testable idea.

WRONG Practicing only the perfect input.

RIGHT Also test missing, repeated, empty, or boundary input before considering the lesson complete.

The edge case is where most interview follow-up questions begin.

WRONG Looking only at the final output.

RIGHT Trace table rows, constraints, and indexes through each important step.

Tracing makes debugging faster because you can see the first incorrect state.

Practice Tasks

Build one small SQL statement that demonstrates MySQL in an order-management database.
Change the example to include missing, repeated, empty, or boundary input and record the difference.
Break the example by deliberately copying the syntax before understanding the behavior, then write the corrected version.
Explain the finished example in five bullet points: input, operation, output, failure case, and verification.

Frequently Asked Questions

When should I use MySQL?

Use it when the problem matches the behavior shown in the example and when the result can be verified through result set and EXPLAIN output.

How do I avoid mistakes in MySQL?

Start with a tiny case, then test missing, repeated, empty, or boundary input. The main warning sign is copying the syntax before understanding the behavior.

How can I revise MySQL quickly?

Trace table rows, constraints, and indexes, predict the result, run the example, and compare your prediction with the actual output.

Previous Next

MySQL Data Types INT, VARCHAR, DATE, JSON: Tutorial, Examples, FAQs & Interview Tips