MySQL Data Types

Every column in a MySQL table has a data type: a promise about what kind of value it holds. A type decides how much space a value takes, which values are allowed, and how values are compared, sorted and calculated.

Choosing types well prevents real bugs: prices that drift by a paisa, phone numbers that lose their leading zero, dates that sort in the wrong order. This lesson walks through the types you will use in almost every table and tells you which one to pick.

Why the type matters

Imagine storing order totals as text. The text '9' sorts after '10', because text is compared character by character and '9' is bigger than '1'. As a number, 9 is correctly smaller than 10. The same goes for dates: stored as a real DATE, they sort and compare correctly, and you can ask for a date range:

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2025-03-01' AND '2025-03-31'
ORDER BY order_date;

order_id | order_date
---------+-----------
107      | 2025-03-09
108      | 2025-03-15
109      | 2025-03-22
(3 rows)

The type also protects your data. In its default strict mode, MySQL rejects a value that does not fit: text in a number column, a number that is too large, or text longer than the column allows.

Whole numbers: INT and friends

Integer types store whole numbers. They differ only in size:

Adding UNSIGNED removes negative numbers and doubles the top of the range, so INT UNSIGNED reaches about 4.29 billion. For most ID columns, INT UNSIGNED is plenty; use BIGINT UNSIGNED when a table could pass a few billion rows, such as logs or page views. Storing 300 in a TINYINT fails with ERROR 1264 (22003): Out of range value for column.

Money: always DECIMAL

DECIMAL(p, s) stores exact decimal numbers with p digits in total, s of them after the decimal point. The practice database stores prices as DECIMAL(10,2), which allows values up to 99,999,999.99, enough for any product the shop sells.

FLOAT and DOUBLE are approximate types. They store numbers in binary, and many decimal fractions cannot be stored exactly, so adding 0.1 and 0.2 as DOUBLE values can give 0.30000000000000004 instead of 0.3. That is fine for a temperature or a GPS position, but a bill must add up to the paisa. Use DECIMAL for money, every time.

Calculations mix types sensibly: a DECIMAL price times an INT stock gives an exact DECIMAL result.

SELECT product_name, price, stock,
       price * stock AS stock_value
FROM products
WHERE category = 'Storage';

product_name     | price | stock | stock_value
-----------------+-------+-------+------------
1TB External SSD | 6499  | 30    | 194970
64GB Pen Drive   | 549   | 200   | 109800
(2 rows)

MySQL shows 6499.00 and 194970.00, keeping the two decimal places. The browser stores these prices as whole numbers, so it prints them without decimals.

Text: CHAR, VARCHAR and TEXT

With utf8mb4, the n in VARCHAR(n) counts characters, not bytes, so VARCHAR(40) holds 40 Hindi letters just as well as 40 English ones.

VARCHAR or TEXT? Use VARCHAR with a sensible limit for anything short that you search, sort or index: names, emails, phone numbers, statuses. The limit is also a free check: an email of 500 characters is a mistake, and MySQL will report ERROR 1406 (22001): Data too long for column 'email'. Use TEXT for long writing where you cannot predict the length. TEXT columns are harder to index (an index on one must give a prefix length), so do not make every column TEXT "just in case".

Store phone numbers, PIN codes and Aadhaar-style ID numbers as text, never as numbers. You never calculate with them, and a number column would drop the leading 0 or +91.

Dates and times

DATETIME and TIMESTAMP look the same but behave differently:

• DATETIME stores exactly what you give it. It covers the years 1000 to 9999.
• TIMESTAMP converts the value from your session's time zone to UTC when saving and back when reading. If your app serves users in India and abroad, that conversion is useful. But its range ends on 19 January 2038, which is not far away for data such as loan end dates.

For "when was this row created or changed", both work with automatic defaults:

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP refreshes updated_at every time the row changes. Always write dates in the YYYY-MM-DD order; '15-08-2025' is not a valid date.

Booleans, ENUM and other types

MySQL has a BOOLEAN type, but it is a synonym for TINYINT(1). TRUE is stored as 1 and FALSE as 0, so query results show numbers:

CREATE TABLE events (
  event_id INT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  entry_fee DECIMAL(8,2) NOT NULL,
  seats SMALLINT NOT NULL,
  is_online BOOLEAN NOT NULL,
  event_date DATE NOT NULL,
  updated_at DATETIME NOT NULL
);

INSERT INTO events (event_id, title, entry_fee, seats, is_online, event_date, updated_at) VALUES
  (1, 'Inter-college Coding Contest', 150.00, 120, TRUE, '2025-08-23', '2025-08-01 18:45:00'),
  (2, 'Cricket Quiz Night', 99.50, 60, FALSE, '2025-08-30', '2025-08-02 10:05:30');

SELECT title, entry_fee, seats, is_online, event_date, updated_at FROM events;
SELECT title FROM events WHERE is_online = TRUE;

title                        | entry_fee | seats | is_online | event_date | updated_at
-----------------------------+-----------+-------+-----------+------------+--------------------
Inter-college Coding Contest | 150       | 120   | 1         | 2025-08-23 | 2025-08-01 18:45:00
Cricket Quiz Night           | 99.5      | 60    | 0         | 2025-08-30 | 2025-08-02 10:05:30
(2 rows)

title
----------------------------
Inter-college Coding Contest
(1 row)

MySQL prints the fees as 150.00 and 99.50. The browser editor is also much more relaxed about types than MySQL: it accepts text in a number column or a too-long string without complaint. Always test type limits on a real MySQL server.

A few more types you will meet:

• ENUM('pending', 'shipped', 'delivered', 'cancelled') allows only the listed values. It is compact, but adding a value later needs an ALTER TABLE, so many teams prefer a VARCHAR with a CHECK constraint (see Constraints & Keys) or a small lookup table.
• JSON stores validated JSON documents; see JSON in MySQL.
• BLOB stores binary data. Avoid storing images and PDFs in the database; save the file on disk or in cloud storage and keep only its path in a VARCHAR.

Key points

• The data type controls what a column accepts, how much space it uses, and how values sort and calculate.
• Use INT for most whole numbers and IDs, and BIGINT for tables that may pass a few billion rows.
• Always use DECIMAL for money; FLOAT and DOUBLE are approximate.
• Use VARCHAR(n) for short text with a sensible limit and TEXT for long free text.
• Use DATE, DATETIME or TIMESTAMP for dates; TIMESTAMP converts time zones but ends in 2038.
• BOOLEAN is TINYINT(1); phone numbers and PIN codes are text.

CREATE TABLE train_bookings (
  booking_id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  passenger_name VARCHAR(80) NOT NULL,
  pnr            CHAR(10) NOT NULL UNIQUE,
  journey_date   DATE NOT NULL,
  fare           DECIMAL(8,2) NOT NULL,
  passengers     TINYINT UNSIGNED NOT NULL,
  is_ac          BOOLEAN NOT NULL DEFAULT FALSE,
  booked_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);