Chapter 03 of 35

SQL Data Types

When you create a table, every column must have a data type. The data type tells MySQL what kind of value the column holds — a whole number, money, text, a date — and therefore how much space to use, which values to accept and which to reject, and how to compare and sort them.

Choosing types sounds boring, but wrong choices cause real bugs: salaries that add up to 0.30000000000000004, phone numbers that lose their leading zero, timestamps that shift by 5½ hours, or a 12-digit Aadhaar number that simply does not fit.

In this chapter we go through the MySQL data types you will actually use, see each one in action with real output, and finish by explaining why every column in company_db has the type it has.


Why Data Types Matter

A data type does three jobs:

  • Validation – it rejects values that make no sense, like the date 2023-02-30 or the text 'fifty' in a salary column.

  • Storage – a TINYINT takes 1 byte, a BIGINT takes 8. On a table with crores of rows, that difference matters for disk, memory and speed.

  • Behaviour – types decide how values are compared and sorted. As numbers, 9 < 10. As text, '9' > '10', because text is compared character by character and '9' comes after '1'.

The main families are:

Family

Types

Typical use

Integers

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

IDs, counts, ages, quantities

Exact decimals

DECIMAL(M,D) (same as NUMERIC)

Money, marks with decimals, anything that must be exact

Approximate numbers

FLOAT, DOUBLE

Scientific measurements, sensor readings, coordinates

Text

CHAR(n), VARCHAR(n), TEXT family

Names, emails, codes, descriptions

Date and time

DATE, TIME, DATETIME, TIMESTAMP, YEAR

Birthdays, order times, log entries

Others

BOOLEAN, ENUM, JSON, BLOB

Flags, fixed lists, semi-structured data, binary files

How to follow along: the examples below create small throw-away tables. Run them in a separate practice database rather than in company_db: CREATE DATABASE playground; USE playground; (the next chapter explains CREATE DATABASE properly). All examples were run with MySQL 8.4's default settings, which include strict mode — more on that below.


Integer Types

Integer types store whole numbers. They differ only in size, and therefore range:

Type

Bytes

Signed range

UNSIGNED range

TINYINT

1

-128 to 127

0 to 255

SMALLINT

2

-32,768 to 32,767

0 to 65,535

MEDIUMINT

3

-8,388,608 to 8,388,607

0 to 16,777,215

INT / INTEGER

4

-2,147,483,648 to 2,147,483,647 (about ±214 crore)

0 to 4,294,967,295

BIGINT

8

about ±9.22 × 1018

0 to 18,446,744,073,709,551,615

Where do these ranges come from? A type with n bits can hold 2n different values. TINYINT has 8 bits, so 28 = 256 values. Signed splits them between negatives and positives (-128 to 127). UNSIGNED uses all of them for zero and positives (0 to 255).

What Happens When a Value Does Not Fit?

CREATE TABLE int_demo (
  small_signed   TINYINT,
  small_unsigned TINYINT UNSIGNED
);

INSERT INTO int_demo VALUES (127, 255);   -- both at their maximum: OK
INSERT INTO int_demo VALUES (128, 0);     -- 128 is too big for signed TINYINT
INSERT INTO int_demo VALUES (0, -1);      -- negative not allowed in UNSIGNED
ERROR 1264 (22003): Out of range value for column 'small_signed' at row 1
ERROR 1264 (22003): Out of range value for column 'small_unsigned' at row 1
SELECT * FROM int_demo;
+--------------+----------------+
| small_signed | small_unsigned |
+--------------+----------------+
|          127 |            255 |
+--------------+----------------+

Only the valid row was stored. MySQL refused the bad rows with an error instead of silently storing something else. This is because of strict SQL mode, which is on by default in MySQL 8 (STRICT_TRANS_TABLES). On old servers with strict mode turned off, MySQL would instead clip 128 to 127 and only give a warning — a silent data corruption. If you ever see a server running without strict mode, be careful.

Choosing an Integer Type

  • Use INT by default for IDs and counts. 214 crore is plenty for most tables.

  • Use BIGINT when values can exceed about 214 crore: row IDs of huge tables (likes, events, transactions), amounts stored in paise, or any 12-digit number.

  • Use TINYINT/SMALLINT for small bounded values like age or a rating out of 5, if you care about space.

Old syntax alert: you will see INT(11) in old tutorials. The number in brackets is not a size limit — it was only a display hint. It is deprecated in MySQL 8 (you get warning 1681, "Integer display width is deprecated"), so just write INT.


Exact vs Approximate Numbers: DECIMAL vs FLOAT/DOUBLE

This is the single most important data-type lesson, and a favourite interview question.

  • FLOAT (4 bytes) and DOUBLE (8 bytes) store numbers in binary floating point. They are fast and cover huge ranges, but many decimal fractions such as 0.1 cannot be represented exactly in binary — just as 1/3 cannot be written exactly in decimal (0.3333...).

  • DECIMAL(M,D) stores numbers exactly, digit by digit. M is the total number of digits (precision, up to 65) and D is how many of them are after the decimal point (scale, up to 30).

Let us store 0.1 and 0.2 in all three types and add them:

CREATE TABLE money_demo (
  f FLOAT,
  d DOUBLE,
  m DECIMAL(10,2)
);
INSERT INTO money_demo VALUES (0.1, 0.1, 0.1), (0.2, 0.2, 0.2);

SELECT SUM(f), SUM(d), SUM(m) FROM money_demo;
+---------------------+---------------------+--------+
| SUM(f)              | SUM(d)              | SUM(m) |
+---------------------+---------------------+--------+
| 0.30000000447034836 | 0.30000000000000004 |   0.30 |
+---------------------+---------------------+--------+

Only DECIMAL gives exactly 0.30. And this is not just a display issue — equality checks break too:

SELECT SUM(d) = 0.3 AS double_ok, SUM(m) = 0.3 AS decimal_ok FROM money_demo;
+-----------+------------+
| double_ok | decimal_ok |
+-----------+------------+
|         0 |          1 |
+-----------+------------+

MySQL shows comparison results as 1 (true) and 0 (false). The DOUBLE total is not equal to 0.3. Imagine a banking report checking whether accounts balance — this is exactly how such reports go wrong.

Rule: use DECIMAL for money and anything that must be exact. Use FLOAT/DOUBLE only for measured, approximate quantities (temperature, GPS coordinates, scientific data), and never compare them with =.

How DECIMAL(M,D) Handles Extra Digits and Big Values

DECIMAL(5,2) means 5 digits in total, 2 after the point, so it holds -999.99 to 999.99.

CREATE TABLE price_demo (price DECIMAL(5,2));
INSERT INTO price_demo VALUES (999.99), (12.345), (12.344);
SELECT * FROM price_demo;
+--------+
| price  |
+--------+
| 999.99 |
|  12.35 |
|  12.34 |
+--------+

Extra digits after the point are rounded (12.345 became 12.35) and MySQL records a note (code 1265, "Data truncated"). Too many digits before the point is a real error:

INSERT INTO price_demo VALUES (1000.00);
ERROR 1264 (22003): Out of range value for column 'price' at row 1

So when choosing M and D, think about the largest value you will ever need. Our salary DECIMAL(10,2) allows up to 99,999,999.99 — about 10 crore rupees.


Text Types: CHAR, VARCHAR and TEXT

Type

Max length

How it is stored

Use for

CHAR(n)

255 characters

Fixed length: always padded with spaces to n characters

Values that are always the same length: country code 'IN', gender code, fixed-format codes

VARCHAR(n)

up to 65,535 bytes (shared by all columns of a row)

Variable length: only the actual characters plus a 1–2 byte length prefix

Most text: names, emails, cities, titles

TEXT

65,535 bytes (MEDIUMTEXT 16 MB, LONGTEXT 4 GB)

Stored separately from the row when large; cannot have a normal DEFAULT

Long content: blog posts, reviews, descriptions

In both CHAR(n) and VARCHAR(n), n counts characters, not bytes.

CHAR Strips Trailing Spaces, VARCHAR Keeps Them

CREATE TABLE str_demo (
  c CHAR(5),
  v VARCHAR(5)
);
INSERT INTO str_demo VALUES ('ab  ', 'ab  ');   -- 'ab' followed by two spaces

SELECT CONCAT('[', c, ']') AS char_value,
       CONCAT('[', v, ']') AS varchar_value
FROM str_demo;
+------------+---------------+
| char_value | varchar_value |
+------------+---------------+
| [ab]       | [ab  ]        |
+------------+---------------+

We wrapped the values in brackets to make spaces visible. CHAR pads values with spaces when storing and removes trailing spaces when reading, so the spaces we inserted are gone. VARCHAR stores exactly what you gave it.

Too-Long Text Is Rejected

INSERT INTO str_demo VALUES ('x', 'abcdef');   -- 6 characters into VARCHAR(5)
ERROR 1406 (22001): Data too long for column 'v' at row 1

Again, strict mode protects you. Without it, MySQL would silently cut the value to 'abcde'.

Characters vs Bytes: Unicode and utf8mb4

MySQL 8's default character set is utf8mb4, which can store every Unicode character — Hindi, Tamil, Chinese, even emojis. English letters take 1 byte each, but other scripts take more:

CREATE TABLE hindi_demo (word VARCHAR(10));
INSERT INTO hindi_demo VALUES ('नमस्ते');

SELECT CHAR_LENGTH(word) AS characters, LENGTH(word) AS bytes FROM hindi_demo;
+------------+-------+
| characters | bytes |
+------------+-------+
|          6 |    18 |
+------------+-------+

The word नमस्ते is 6 Unicode characters (code points) but 18 bytes, because each Devanagari character takes 3 bytes in UTF-8. Two lessons:

  • VARCHAR(10) means 10 characters, so this value fits even though it is 18 bytes.

  • LENGTH() returns bytes and CHAR_LENGTH() returns characters. For English text they are equal, which hides the difference until your app gets its first Hindi user. (More string functions in String Functions.)

Note: MySQL also has a charset called utf8 (an alias of utf8mb3) which stores at most 3 bytes per character and therefore cannot store emojis. It is deprecated. Always use utf8mb4 — the default in MySQL 8.


Date and Time Types

Type

Format

Range

Example use

DATE

'YYYY-MM-DD'

1000-01-01 to 9999-12-31

Date of birth, hire date

TIME

'HH:MM:SS'

-838:59:59 to 838:59:59

Duration, shop opening time

DATETIME

'YYYY-MM-DD HH:MM:SS'

1000-01-01 00:00:00 to 9999-12-31 23:59:59

Exam slot, appointment

TIMESTAMP

'YYYY-MM-DD HH:MM:SS'

1970-01-01 00:00:01 to 2038-01-19 03:14:07 (UTC)

"Row created at", "last login at"

YEAR

YYYY

1901 to 2155

Graduation year

TIME, DATETIME and TIMESTAMP can also store fractions of a second, for example DATETIME(3) for milliseconds.

Dates Are Validated

CREATE TABLE date_demo (d DATE);
INSERT INTO date_demo VALUES ('2024-02-29');   -- 2024 is a leap year: OK
INSERT INTO date_demo VALUES ('2023-02-29');   -- 2023 is not a leap year
INSERT INTO date_demo VALUES ('15-08-2024');   -- Indian DD-MM-YYYY style
ERROR 1292 (22007): Incorrect date value: '2023-02-29' for column 'd' at row 1
ERROR 1292 (22007): Incorrect date value: '15-08-2024' for column 'd' at row 1

MySQL knows the calendar, including leap years. It also expects dates as 'YYYY-MM-DD'. The Indian DD-MM-YYYY format is rejected, which is a very common beginner error. (If you receive data in that format, convert it with STR_TO_DATE(), covered in Date & Time Functions.)

Why store dates in a DATE column instead of VARCHAR? Because then MySQL can do date arithmetic for you:

SELECT d, d + INTERVAL 1 DAY AS next_day, DAYNAME(d) AS day_name FROM date_demo;
+------------+------------+----------+
| d          | next_day   | day_name |
+------------+------------+----------+
| 2024-02-29 | 2024-03-01 | Thursday |
+------------+------------+----------+

With text, '2024-02-29' + 1 day would be meaningless, and invalid dates like '2024-02-31' would be stored without complaint.

DATETIME vs TIMESTAMP: The Time-Zone Difference

Both look the same, but they behave very differently:

  • DATETIME stores the value exactly as given, like writing it on paper. It knows nothing about time zones.

  • TIMESTAMP converts the value from the session's time zone to UTC when storing, and back to the session's time zone when reading. It represents an exact moment in time.

Let us insert 9:00 AM Indian time (IST is UTC+05:30) into both, then read it from a session set to UTC:

CREATE TABLE time_demo (
  dt DATETIME,
  ts TIMESTAMP
);

SET time_zone = '+05:30';            -- this session is in IST
INSERT INTO time_demo VALUES ('2024-08-15 09:00:00', '2024-08-15 09:00:00');
SELECT * FROM time_demo;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2024-08-15 09:00:00 | 2024-08-15 09:00:00 |
+---------------------+---------------------+
SET time_zone = '+00:00';            -- now pretend we are a server in UTC
SELECT * FROM time_demo;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2024-08-15 09:00:00 | 2024-08-15 03:30:00 |
+---------------------+---------------------+

The TIMESTAMP shifted by 5½ hours because it means "this exact moment", and 9:00 IST is 3:30 UTC. The DATETIME did not change.

Which is right depends on meaning:

  • "Order was placed at this instant" → TIMESTAMP (or store UTC in DATETIME consistently).

  • "Exam starts at 10:00 AM local time" or a date of birth → DATETIME / DATE.

The Year 2038 Problem

TIMESTAMP is stored internally as seconds since 1970-01-01 UTC in a 32-bit number, so it ends at 2038-01-19 03:14:07 UTC:

INSERT INTO time_demo VALUES ('2040-01-01 00:00:00', '2040-01-01 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '2040-01-01 00:00:00' for column 'ts' at row 1

So do not use TIMESTAMP for far-future dates such as loan maturity dates or policy end dates. DATETIME goes up to the year 9999.


BOOLEAN, ENUM and JSON

BOOLEAN Is Really TINYINT(1)

MySQL has no true boolean type. BOOLEAN (or BOOL) is just a synonym for TINYINT(1), where 0 means false and any non-zero value means true:

CREATE TABLE bool_demo (is_active BOOLEAN);
SHOW CREATE TABLE bool_demo;

(Only the Create Table text of the result is shown.)

CREATE TABLE `bool_demo` (
  `is_active` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO bool_demo VALUES (TRUE), (FALSE), (5);

SELECT is_active,
       is_active = TRUE           AS equals_true,
       IF(is_active, 'yes', 'no') AS treated_as
FROM bool_demo;
+-----------+-------------+------------+
| is_active | equals_true | treated_as |
+-----------+-------------+------------+
|         1 |           1 | yes        |
|         0 |           0 | no         |
|         5 |           0 | yes        |
+-----------+-------------+------------+

Look at the last row. TRUE is just the number 1, so 5 = TRUE is false, even though 5 counts as "true" in a condition. Lesson: test booleans with WHERE is_active or WHERE is_active = 1, and only ever store 0 or 1 in them.

ENUM: One Value From a Fixed List

ENUM lets a column accept only one of a predefined list of strings:

CREATE TABLE enum_demo (status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled'));
INSERT INTO enum_demo VALUES ('Shipped');
INSERT INTO enum_demo VALUES ('Lost');
ERROR 1265 (01000): Data truncated for column 'status' at row 1
SELECT status, status + 0 AS position FROM enum_demo;
+---------+----------+
| status  | position |
+---------+----------+
| Shipped |        2 |
+---------+----------+

'Lost' is not in the list, so it is rejected (the error message is a bit misleading, but it means "invalid value"). Internally each value is stored as its position in the list (1, 2, 3 ...), which is compact. The drawbacks: adding a new status later needs an ALTER TABLE, and sorting follows list position, not alphabetical order. That is why many teams prefer a VARCHAR column plus a CHECK constraint or a lookup table (see SQL Constraints). Our orders.status is a plain VARCHAR(20) for exactly this reason.

JSON: Semi-Structured Data

MySQL 8 has a native JSON type. It validates the JSON on insert and lets you pull values out with the ->> operator:

CREATE TABLE json_demo (profile JSON);
INSERT INTO json_demo VALUES ('{"skills": ["SQL", "Java"], "remote": true}');

SELECT profile->>'$.skills[0]' AS first_skill,
       profile->>'$.remote'    AS remote
FROM json_demo;
+-------------+--------+
| first_skill | remote |
+-------------+--------+
| SQL         | true   |
+-------------+--------+
INSERT INTO json_demo VALUES ('{skills: SQL}');   -- keys must be in double quotes
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'json_demo.profile'.

JSON is handy for data whose shape varies (user preferences, API responses). But do not use it to avoid designing tables — values you filter, join or aggregate on regularly belong in normal columns.

For completeness: BLOB types (BLOB, MEDIUMBLOB, LONGBLOB) store raw binary data. In practice, images and PDFs are usually stored as files (or in cloud storage), and the table only stores their path or URL in a VARCHAR.


Implicit Type Conversion: A Hidden Trap

When you mix types in an expression, MySQL silently converts one of them:

SELECT '10' + 5 AS result1, 'abc' = 0 AS result2;
+---------+---------+
| result1 | result2 |
+---------+---------+
|      15 |       1 |
+---------+---------+
SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
+---------+------+-----------------------------------------+

'10' + 5 gives 15 because the string is converted to a number. Worse, 'abc' = 0 is true: a string that does not start with a digit converts to 0. MySQL only gives a warning, not an error. This is how a condition like WHERE some_text_column = 0 can match rows you never intended. The fix is simple: compare values of the same type — numbers with numbers, quoted strings with text columns.


Why company_db Uses the Types It Uses

Here is the structure of our employees table:

DESCRIBE employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_id     | int           | NO   | PRI | NULL    |       |
| name       | varchar(50)   | NO   |     | NULL    |       |
| email      | varchar(100)  | YES  | UNI | NULL    |       |
| dept_id    | int           | YES  | MUL | NULL    |       |
| manager_id | int           | YES  | MUL | NULL    |       |
| salary     | decimal(10,2) | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
| city       | varchar(50)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

Column

Type

Why

emp_id, dept_id, manager_id

INT

Whole-number identifiers. A foreign key column must have the same type as the column it refers to.

name, city

VARCHAR(50)

Variable-length text; 50 characters is enough for most names.

email

VARCHAR(100)

Emails can be long and vary in length.

salary

DECIMAL(10,2)

Money must be exact: never FLOAT.

hire_date

DATE

Only the day matters, and we want validation and date arithmetic.

Text or Number? The "Looks Like a Number" Trap

Some values are made of digits but are not numbers. Ask: "Will I ever do arithmetic on this?" If not, it is probably text.

Value

Wrong type

Right type

Reason

Mobile number +91 98765 43210

INT

VARCHAR(15)

Has + and spaces; a 10-digit number like 9876543210 is also larger than the INT maximum of 2,147,483,647.

Aadhaar number (12 digits)

INT

CHAR(12)

Does not fit in INT; never used in arithmetic; fixed length.

Roll number 0042

INT

VARCHAR

A number column would store 42 and lose the leading zeros.

Product price

FLOAT

DECIMAL(10,2)

Must be exact.

Date of birth

VARCHAR

DATE

Validation, sorting and age calculation.


Common Mistakes

  • Using FLOAT/DOUBLE for money. Totals drift and equality checks fail. Use DECIMAL.

  • Storing dates as VARCHAR. You lose validation and date functions, and text sorting puts '15-08-2024' after '01-12-2025'.

  • Writing dates as DD-MM-YYYY. MySQL expects 'YYYY-MM-DD'.

  • Storing phone numbers, PIN codes or IDs with leading zeros as INT.

  • Making every column VARCHAR(255) "just in case". Sensible limits document intent and catch bad data early.

  • Using TIMESTAMP for dates after 2038, or forgetting that it shifts with the session time zone.

  • Comparing a text column with a number, like WHERE code = 0, which silently converts text to numbers.


Interview Questions

What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed-length: values are padded with spaces to n characters (and trailing spaces are removed when read). VARCHAR(n) is variable-length: it stores only the actual characters plus a 1–2 byte length prefix. Use CHAR for values that always have the same length, VARCHAR for everything else.

Why should you use DECIMAL instead of FLOAT for money?

FLOAT and DOUBLE are binary floating-point types and cannot represent many decimal fractions exactly (0.1 + 0.2 gives 0.30000000000000004). DECIMAL stores exact decimal digits, so sums and comparisons are exact.

What does DECIMAL(10,2) mean?

10 digits in total, of which 2 are after the decimal point. So the range is -99,999,999.99 to 99,999,999.99.

What is the difference between DATETIME and TIMESTAMP in MySQL?

DATETIME stores the value as given, independent of time zone, with range 1000–9999. TIMESTAMP converts from the session time zone to UTC on storage and back on retrieval, uses 4 bytes (plus fractional seconds) and only covers 1970 to 2038-01-19 03:14:07 UTC.

Does MySQL have a BOOLEAN type?

Only as a synonym: BOOLEAN is TINYINT(1). TRUE is 1 and FALSE is 0; any non-zero value is treated as true in conditions.

What is the difference between LENGTH() and CHAR_LENGTH()?

LENGTH() returns the size in bytes; CHAR_LENGTH() returns the number of characters. They differ for multi-byte characters, e.g. नमस्ते is 6 characters but 18 bytes in utf8mb4.

What does INT(11) mean?

The 11 was only a display width hint, not a storage limit; INT always uses 4 bytes. Display width for integer types is deprecated in MySQL 8.


The Main Idea

Whole numbers   → INT (default), BIGINT (beyond ~214 crore), TINYINT/SMALLINT (small)
Exact decimals  → DECIMAL(M,D)   ← money, always
Approximate     → FLOAT / DOUBLE ← measurements, never compare with =
Text            → VARCHAR(n) (most text), CHAR(n) (fixed length), TEXT (long)
Dates           → DATE 'YYYY-MM-DD', DATETIME (as written), TIMESTAMP (UTC moment, ≤ 2038)
Others          → BOOLEAN = TINYINT(1), ENUM (fixed list), JSON (validated documents)
Charset         → utf8mb4 (default); LENGTH = bytes, CHAR_LENGTH = characters
Strict mode     → bad values are rejected with an error instead of silently changed

Choosing a type is choosing which values are allowed. Pick the type that matches the meaning of the data, and MySQL will catch a whole class of bugs for you. Next, in CREATE DATABASE & CREATE TABLE, we use these types to build the company_db tables ourselves.