Add ₹0.10 and ₹0.20 in a FLOAT column and MySQL will tell you the total is not equal to ₹0.30. That is not a bug. It is what the type was designed to do, and it is the clearest example of why data types deserve a few minutes of thought before you create a table. A type decides what values a column accepts, how much space they take, how they sort and compare, and which mistakes MySQL will refuse to store.
Every result below comes from MySQL 8.0 in its default strict mode (STRICT_TRANS_TABLES is part of the default sql_mode).
Numbers: whole numbers
| 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 | about ±8.3 million | 0 to 16,777,215 |
INT | 4 | about ±2.1 billion | 0 to 4,294,967,295 |
BIGINT | 8 | about ±9.2 × 1018 | 0 to about 1.8 × 1019 |
CREATE TABLE t_int (tiny TINYINT, utiny TINYINT UNSIGNED);
INSERT INTO t_int (tiny) VALUES (128);
INSERT INTO t_int (utiny) VALUES (-1);
ERROR 1264 (22003): Out of range value for column 'tiny' at row 1
ERROR 1264 (22003): Out of range value for column 'utiny' at row 1
In strict mode an out-of-range value is rejected, not cut down to the limit. The number in INT(11) is only a display width and is deprecated in 8.0; it does not limit the value.
Numbers: money and decimals
CREATE TABLE money (f FLOAT, d DOUBLE, dec2 DECIMAL(10,2));
INSERT INTO money VALUES (0.1, 0.1, 0.1), (0.2, 0.2, 0.2);
SELECT SUM(f) AS float_sum, SUM(d) AS double_sum, SUM(dec2) AS decimal_sum FROM money;
SELECT SUM(f) = 0.3 AS float_ok, SUM(d) = 0.3 AS double_ok, SUM(dec2) = 0.3 AS decimal_ok FROM money;
| float_sum | double_sum | decimal_sum |
|---|---|---|
| 0.30000000447034836 | 0.30000000000000004 | 0.30 |
| float_ok | double_ok | decimal_ok |
|---|---|---|
| 0 | 0 | 1 |
FLOAT and DOUBLE store binary approximations. They are fine for sensor readings or scientific values, and wrong for prices, balances and GST amounts. DECIMAL(p, s) stores exact values. It holds p digits in total, s of them after the point, so DECIMAL(10,2) reaches 99,999,999.99.
Text
CREATE TABLE t_str (code CHAR(5), name VARCHAR(5));
INSERT INTO t_str VALUES ('ab ', 'ab ');
SELECT CONCAT('[',code,']') AS char_val, CONCAT('[',name,']') AS varchar_val,
LENGTH(code), LENGTH(name)
FROM t_str;
| char_val | varchar_val | LENGTH(code) | LENGTH(name) |
|---|---|---|---|
| [ab] | [ab ] | 2 | 4 |
CHAR(n) pads with spaces on disk and strips trailing spaces when read back. VARCHAR(n) keeps exactly what you stored. Too-long values are rejected with ERROR 1406 (22001): Data too long for column 'code'. Use CHAR only for truly fixed-length codes, such as two-letter state codes. The full comparison is in CHAR vs VARCHAR.
ncounts characters, not bytes. Withutf8mb4, an English letter takes 1 byte, a Devanagari character 3, and an emoji 4.TEXTtypes (TEXTup to 64 KB,MEDIUMTEXT,LONGTEXT) suit long content. They cannot have a plainDEFAULTvalue, and indexing them needs a prefix length.- Phone numbers, PIN codes and Aadhaar-style IDs are text, even though they contain digits. You never do arithmetic on them, and numbers drop leading zeros:
'0612345678' + 0returns612345678.
Dates and times
| Type | Holds | Range |
|---|---|---|
DATE | a day | 1000-01-01 to 9999-12-31 |
DATETIME | day + time, stored as written | 1000-01-01 00:00:00 to 9999-12-31 23:59:59 |
TIMESTAMP | day + time, stored as UTC | 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC |
TIME | a time or a duration | -838:59:59 to 838:59:59 |
YEAR | a year | 1901 to 2155 |
CREATE TABLE t_time (dt DATETIME, ts TIMESTAMP);
SET time_zone = '+00:00';
INSERT INTO t_time VALUES ('2026-11-16 10:00:00', '2026-11-16 10:00:00');
SET time_zone = '+05:30';
SELECT dt, ts FROM t_time;
| dt | ts |
|---|---|
| 2026-11-16 10:00:00 | 2026-11-16 15:30:00 |
Both columns received the same text. DATETIME gives it back unchanged. TIMESTAMP converts from the session time zone to UTC when storing, and back when reading, so the same instant now shows in IST. That is useful for "when did this happen" columns in apps used across time zones. The 2038 limit is real, though: inserting '2039-01-01 00:00:00' into the TIMESTAMP column fails with ERROR 1292. For dates far in the future (loan end dates, expiry dates) use DATETIME. The functions for working with these types are on MySQL date and time functions.
ENUM, BOOLEAN and JSON
CREATE TABLE t_enum (size ENUM('S','M','L'));
INSERT INTO t_enum VALUES ('M'), ('S'), ('L');
SELECT size FROM t_enum ORDER BY size;
INSERT INTO t_enum VALUES ('XL');
| size |
|---|
| S |
| M |
| L |
ERROR 1265 (01000): Data truncated for column 'size' at row 1
An ENUM sorts by its position in the list, not alphabetically. Adding a new value later means an ALTER TABLE. A small lookup table with a foreign key is often easier to maintain.
BOOLEAN is only an alias for TINYINT(1). TRUE is stored as 1 and FALSE as 0, and nothing stops you inserting 5. Add a CHECK (flag IN (0,1)) if that matters. JSON stores validated JSON documents and is covered in the JSON data type tutorial.
Choosing quickly
| Data | Sensible type |
|---|---|
| Surrogate ID | INT UNSIGNED AUTO_INCREMENT; BIGINT if billions of rows are possible |
| Price, salary, balance | DECIMAL(12,2) or as precision needs |
| Quantity, age, rating | SMALLINT / TINYINT UNSIGNED |
| Name, email, city | VARCHAR sized to realistic data |
| Phone, PIN code | VARCHAR(15), CHAR(6) |
| Date of birth | DATE |
| Created / updated time | DATETIME or TIMESTAMP (watch the 2038 limit) |
| Article body, comments | TEXT |
| Yes/no flag | BOOLEAN with a CHECK if needed |
Smaller types are not only about saving disk space. Indexes and the buffer pool hold more rows in the same memory, so the right type helps performance as well. For how these choices fit into a full table definition, see how to create a table in MySQL.