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-30or the text'fifty'in a salary column.Storage – a
TINYINTtakes 1 byte, aBIGINTtakes 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 |
| IDs, counts, ages, quantities |
Exact decimals |
| Money, marks with decimals, anything that must be exact |
Approximate numbers |
| Scientific measurements, sensor readings, coordinates |
Text |
| Names, emails, codes, descriptions |
Date and time |
| Birthdays, order times, log entries |
Others |
| 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 explainsCREATE DATABASEproperly). 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 |
|---|---|---|---|
| 1 | -128 to 127 | 0 to 255 |
| 2 | -32,768 to 32,767 | 0 to 65,535 |
| 3 | -8,388,608 to 8,388,607 | 0 to 16,777,215 |
| 4 | -2,147,483,648 to 2,147,483,647 (about ±214 crore) | 0 to 4,294,967,295 |
| 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 UNSIGNEDERROR 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 1SELECT * 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
INTby default for IDs and counts. 214 crore is plenty for most tables.Use
BIGINTwhen 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/SMALLINTfor 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 writeINT.
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) andDOUBLE(8 bytes) store numbers in binary floating point. They are fast and cover huge ranges, but many decimal fractions such as0.1cannot 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.Mis the total number of digits (precision, up to 65) andDis 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 1So 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 |
|---|---|---|---|
| 255 characters | Fixed length: always padded with spaces to | Values that are always the same length: country code |
| 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 |
| 65,535 bytes ( | Stored separately from the row when large; cannot have a normal | 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 1Again, 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 andCHAR_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 ofutf8mb3) which stores at most 3 bytes per character and therefore cannot store emojis. It is deprecated. Always useutf8mb4— the default in MySQL 8.
Date and Time Types
Type | Format | Range | Example use |
|---|---|---|---|
|
| 1000-01-01 to 9999-12-31 | Date of birth, hire date |
|
| -838:59:59 to 838:59:59 | Duration, shop opening time |
|
| 1000-01-01 00:00:00 to 9999-12-31 23:59:59 | Exam slot, appointment |
|
| 1970-01-01 00:00:01 to 2038-01-19 03:14:07 (UTC) | "Row created at", "last login at" |
|
| 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 styleERROR 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 1MySQL 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:
DATETIMEstores the value exactly as given, like writing it on paper. It knows nothing about time zones.TIMESTAMPconverts 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 inDATETIMEconsistently)."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 1So 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_ciINSERT 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 1SELECT 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 quotesERROR 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 |
|---|---|---|
|
| Whole-number identifiers. A foreign key column must have the same type as the column it refers to. |
|
| Variable-length text; 50 characters is enough for most names. |
|
| Emails can be long and vary in length. |
|
| Money must be exact: never |
|
| 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 |
|
| Has |
Aadhaar number (12 digits) |
|
| Does not fit in |
Roll number |
|
| A number column would store 42 and lose the leading zeros. |
Product price |
|
| Must be exact. |
Date of birth |
|
| Validation, sorting and age calculation. |
Common Mistakes
Using
FLOAT/DOUBLEfor money. Totals drift and equality checks fail. UseDECIMAL.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
TIMESTAMPfor 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 changedChoosing 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.