Number types and DECIMAL for money
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A number column needs two decisions: is the value a whole number or does it have a fractional part, and how big can it get? MySQL has a type for each answer, and one of them, DECIMAL, is the only safe choice for rupees and paise.
TINYINT, SMALLINT, INT and BIGINT
The integer types TINYINT, SMALLINT, INT and BIGINT store whole numbers. They differ only in size: a bigger type takes more bytes per row and holds a wider range.
The integer types store whole numbers. They differ only in size: a bigger type takes more bytes per row and holds a wider range.
TINYINT: 1 byte, from -128 to 127.
Example 1 · Integer types and their limits
CREATE TABLE cricket_stats ( player VARCHAR(40), wickets TINYINT, matches SMALLINT, career_runs INT, video_views BIGINT);
INSERT INTO cricket_stats (player, wickets, matches, career_runs, video_views) VALUES ('Jasprit Bumrah', 6, 210, 1210, 4500000000), ('Biggest values', 127, 32767, 2147483647, 9223372036854775807);
SELECT * FROM cricket_stats ORDER BY wickets;+----------------+---------+---------+-------------+---------------------+ | player | wickets | matches | career_runs | video_views | +----------------+---------+---------+-------------+---------------------+ | Jasprit Bumrah | 6 | 210 | 1210 | 4500000000 | | Biggest values | 127 | 32767 | 2147483647 | 9223372036854775807 | +----------------+---------+---------+-------------+---------------------+
What this code does
- The second row holds the largest value each type allows. One more in any column would fail.
- 4,500,000,000 views fit only because
video_viewsisBIGINT; in anINTcolumn that insert would be refused.
TINYINT suits wickets in a match, a year of study, marks out of 100.
SMALLINT: 2 bytes, from -32,768 to 32,767. Seats in a stadium, pages in a book.INT: 4 bytes, from -2,147,483,648 to 2,147,483,647 (about 2.1 billion). Ids, stock counts, runs in a career.BIGINT: 8 bytes, up to about 9.2 × 10¹⁸. Video view counts, money stored as whole paise in very large systems.
Pick the smallest type that safely holds the largest value the column will ever need, with room to grow. When unsure between two, take the bigger one: a few extra bytes cost little, a full column stops every insert. A 10-digit mobile number does not fit in INT (9876500001 is more than 2,147,483,647), and you never add or subtract phone numbers anyway. Phone numbers, PIN codes and Aadhaar numbers are text (VARCHAR or CHAR), which also keeps leading zeros and a +91 prefix.
MEDIUMINT (3 bytes, up to about 8.3 million). It is rarely used; INT is the usual default.UNSIGNED: no negative numbers
UNSIGNED after an integer type allows only zero and positive values and moves the whole range up, so TINYINT UNSIGNED holds 0 to 255. It suits counts that can never be negative.
UNSIGNED after an integer type allows only zero and positive values, and moves the whole range up: TINYINT UNSIGNED holds 0 to 255, and INT UNSIGNED holds 0 to 4,294,967,295. It suits counts that can never be negative: seats, stock, quantities.
Example 2 · UNSIGNED doubles the positive range
CREATE TABLE cinema_screens ( screen_no TINYINT UNSIGNED, seats TINYINT UNSIGNED, shows_run INT UNSIGNED);
INSERT INTO cinema_screens (screen_no, seats, shows_run) VALUES (1, 250, 3000000000), (2, 180, 125);
SELECT * FROM cinema_screens ORDER BY screen_no;
INSERT INTO cinema_screens (screen_no, seats, shows_run) VALUES (3, -1, 0);+-----------+-------+------------+ | screen_no | seats | shows_run | +-----------+-------+------------+ | 1 | 250 | 3000000000 | | 2 | 180 | 125 | +-----------+-------+------------+ ERROR 1264 (22003): Out of range value for column 'seats' at row 1
What this code does
- 250 does not fit in a signed
TINYINT(max 127) but fits inTINYINT UNSIGNED(max 255).INT UNSIGNEDholds 3,000,000,000, beyond the signedINTlimit. - A negative seat count is refused with error 1264: an
UNSIGNEDcolumn has no room for negatives.
There is one trap. Subtracting two UNSIGNED values that would give a negative answer is an error, not a negative number. With a = 5 and b = 8 in INT UNSIGNED columns, SELECT a - b fails with ERROR 1690 (22003): BIGINT UNSIGNED value is out of range. Many teams therefore keep plain signed INT and protect the value with a rule, which the Keys & Constraints module shows (CHECK (stock >= 0)).
Predict the output
Predict the insert
The column is TINYINT UNSIGNED and strict mode is on. What happens?
UNSIGNED on DECIMAL, FLOAT and DOUBLE is deprecated and gives a warning. Use it only on integer types.DECIMAL(p, s): exact values for prices and fees
DECIMAL(p, s) stores an exact decimal number with p digits in total, of which s come after the decimal point. DECIMAL(10,2) is the safe choice for prices, fees and salaries.
DECIMAL(p, s) stores an exact decimal number with p digits in total, of which s come after the decimal point. DECIMAL(10,2) holds up to 99,999,999.99: eight digits before the point and two after, which is rupees and paise.
Example 3 · DECIMAL keeps fees exact
CREATE TABLE fee_payments ( roll_no INT, item VARCHAR(30), amount DECIMAL(8,2));
INSERT INTO fee_payments (roll_no, item, amount) VALUES (21, 'Tuition fee', 45000.00), (21, 'Hostel fee', 32500.50), (21, 'Library fine', 12.345);SHOW WARNINGS;
SELECT * FROM fee_payments ORDER BY amount;SELECT SUM(amount) AS total, SUM(amount) * 3 AS three_terms FROM fee_payments;+-------+------+---------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------+ | Note | 1265 | Data truncated for column 'amount' at row 3 | +-------+------+---------------------------------------------+ +---------+--------------+----------+ | roll_no | item | amount | +---------+--------------+----------+ | 21 | Library fine | 12.35 | | 21 | Hostel fee | 32500.50 | | 21 | Tuition fee | 45000.00 | +---------+--------------+----------+ +----------+-------------+ | total | three_terms | +----------+-------------+ | 77512.85 | 232538.55 | +----------+-------------+
What this code does
12.345has three digits after the point, soDECIMAL(8,2)rounds it to12.35. MySQL leaves only a note (code 1265), whichSHOW WARNINGSdisplays.SUM(amount)adds the column (the Aggregates module covers it). The total is exact to the paisa: 77512.85, and three times it is exactly 232538.55.- The largest value
DECIMAL(8,2)can hold is 999999.99, enough for one fee payment but not for a whole college's fee collection.
The practice tables use it for products.price and employees.salary.
Why not a normal "decimal" type? Most programming languages and the FLOAT/DOUBLE types store numbers in binary, and many decimal values such as 0.1 cannot be written exactly in binary. The tiny errors add up over thousands of bills. DECIMAL stores the decimal digits themselves, so 0.10 + 0.20 is exactly 0.30. Two rules to remember:
- Extra digits after the point are rounded:
12.345in aDECIMAL(5,2)column becomes12.35, with only a note, not an error. - Too many digits before the point is an out of range error, because the value does not fit.
DECIMAL and NUMERIC are the same type in MySQL. Writing just DECIMAL means DECIMAL(10,0): no paise at all, so always give both numbers.FLOAT and DOUBLE and their rounding errors
FLOAT and DOUBLE store approximate numbers in binary floating point. They are fast and hold huge or tiny values, but most fractions are stored as the nearest binary value, so they are wrong for money.
FLOAT (4 bytes, about 7 significant digits) and DOUBLE (8 bytes, about 15 significant digits) store approximate numbers in binary floating point, the same format most programming languages use for their decimal numbers.
Example 4 · FLOAT and DOUBLE drift, DECIMAL does not
CREATE TABLE upi_wallet_test ( as_float FLOAT, as_double DOUBLE, as_decimal DECIMAL(10,2));
INSERT INTO upi_wallet_test VALUES (0.1, 0.1, 0.1), (0.2, 0.2, 0.2);
SELECT SUM(as_float), SUM(as_double), SUM(as_decimal) FROM upi_wallet_test;
SELECT as_float, as_float = 0.1, as_double = 0.1, as_decimal = 0.1FROM upi_wallet_testORDER BY as_decimal;+---------------------+---------------------+-----------------+ | SUM(as_float) | SUM(as_double) | SUM(as_decimal) | +---------------------+---------------------+-----------------+ | 0.30000000447034836 | 0.30000000000000004 | 0.30 | +---------------------+---------------------+-----------------+ +----------+----------------+-----------------+------------------+ | as_float | as_float = 0.1 | as_double = 0.1 | as_decimal = 0.1 | +----------+----------------+-----------------+------------------+ | 0.1 | 0 | 1 | 1 | | 0.2 | 0 | 0 | 0 | +----------+----------------+-----------------+------------------+
What this code does
- Adding 0.1 and 0.2 gives 0.30000000447034836 as
FLOATand 0.30000000000000004 asDOUBLE, but exactly 0.30 asDECIMAL. - The
FLOATcolumn shows0.1, butas_float = 0.1is0(false): the stored value is only close to 0.1.1means true and0false in MySQL. - Imagine a wallet app adding thousands of such payments a day: the
FLOATandDOUBLEtotals drift; theDECIMALtotal never does.
The errors are small but real: a FLOAT cannot even hold 123456789 exactly; it keeps 123456792. Use FLOAT or DOUBLE for measured values where a tiny error does not matter: temperature, distance, a GPS position, a sensor reading. Never use them for money, marks that must match exactly, or anything you compare with =.
Find the bug
Find the wrong type
Each table has one column with a type that will cause trouble. Find it.
Store the price of each product.
Round 1 of 2FLOAT(7,2) and DOUBLE(10,2) still appear in old code. The digits form is deprecated from MySQL 8.0.17 and does not make the value exact. Use DECIMAL(10,2) instead.Out of range errors in strict mode
Strict mode is a server setting that turns a value that does not fit into an error, so the insert is refused. It is on by default in MySQL and should stay on.
Strict mode is a server setting that turns a value that does not fit into an error, so the insert is refused. It is on by default from MySQL 5.7 (the STRICT_TRANS_TABLES part of sql_mode).
Example 5 · Out of range in strict mode
CREATE TABLE match_scores ( team VARCHAR(30), wickets TINYINT, runs SMALLINT);
INSERT INTO match_scores (team, wickets, runs) VALUES ('India', 10, 12.7);SELECT * FROM match_scores;
INSERT INTO match_scores (team, wickets, runs) VALUES ('Australia', 128, 250);+-------+---------+------+ | team | wickets | runs | +-------+---------+------+ | India | 10 | 13 | +-------+---------+------+ ERROR 1264 (22003): Out of range value for column 'wickets' at row 1
What this code does
12.7in an integer column is rounded to13with no error. Integer columns quietly drop fractions, so do not use them for values that have paise.- 128 is one more than the
TINYINTmaximum, so strict mode refuses the whole row with error 1264. Nothing from that insert is stored.
Example 6 · What happens without strict mode
SET SESSION sql_mode = '';
CREATE TABLE match_scores ( team VARCHAR(30), wickets TINYINT);
INSERT INTO match_scores (team, wickets) VALUES ('Australia', 128);SHOW WARNINGS;SELECT * FROM match_scores;+---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 1264 | Out of range value for column 'wickets' at row 1 | +---------+------+--------------------------------------------------+ +-----------+---------+ | team | wickets | +-----------+---------+ | Australia | 127 | +-----------+---------+
What this code does
SET SESSION sql_mode = ''turns strict mode off for this connection only, to show the old behaviour. Never do this in a real app.- The insert "works", but 128 is stored as 127 and only a warning (code 1264) records it. The data is now wrong and nobody was told.
In strict mode, putting 128 into a TINYINT fails with ERROR 1264 (22003): Out of range value for column 'wickets' at row 1.
Without strict mode, MySQL stores the nearest allowed value instead (127 for a TINYINT) and only adds a warning that most apps never read. That silent change is how wrong data creeps in. Other checks on numbers work the same way. Text that is not a number gives ERROR 1366 (HY000): Incorrect integer value: 'abc' for column ....
SELECT @@sql_mode;. If STRICT_TRANS_TABLES is missing on your server, someone turned it off; ask before changing it on a shared server.Next step
Check your understanding
11 quick questions