Navigating MySQL Numeric Data Types

Introduction

In the previous article of this series, we examined MySQL data types for storing date and time values. However, even in the seemingly simpler area of ​​storing numeric values, there are some unobvious pitfalls. Even experienced developers can fall into traps when it comes to integer overflows, floating-point rounding, or legacy attributes like ZEROFILL.

In this article I will analyze the features of the numeric data types available in MySQL, including integer, fixed-point, floating-point, and bit types. As a bonus, I’ll share tips I’ve found helpful in choosing the right type for the job and avoiding common pitfalls.

Data types overview

Integer types

MySQL supports five exact integer types of different widths, all of which can be signed or unsigned. These types are: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT. Here is some other highlight of these types:

• All five support UNSIGNED option.
• Display-width declarations like INT(11) no longer affect storage and are deprecated.
• ZEROFILL pads values with leading zeros and forces UNSIGNED; it too is deprecated.

Fixed-point types

You must use DECIMAL(p,s) for exact numeric values like currency and quantities, as it stores each digit precisely.

Floating-point types

Use these for approximate numeric values where exact precision isn’t critical, such as averages. You can use DOUBLE by default, or choose FLOAT if you are ok with smaller value range or want to save memory and storage usage. Here is some other highlight of these types:

• FLOAT/DOUBLE are binary fractions; 0.1 cannot be represented exactly, so expect rounding.
• Use DOUBLE unless space or network payload is critical.

Bit type

This is the only special numeric type, which is intended to store defined amount of bits. It can be used to store some “compressed” flags lists, e.g. access rights.

Other numeric aliases

• BOOL/BOOLEAN are purely syntax sugar for TINYINT(1).
• SERIAL expands to BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Tips & Tricks

1. Pick the smallest integer that fits, as it shrinks indexes, caches, and backups.
2. Use UNSIGNED for naturally non-negative values (IDs, counts) and you instantly double the positive range.
3. Money? Choose DECIMAL, not FLOAT, as accountants hate fractions of cents.
4. Add CHECK constraints (MySQL 8.0+) for business ranges: salary DECIMAL(9,2) CHECK (salary >= 0).
5. Enable a strict SQL mode while developing:
   SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';
6. Planning billions of rows? Start with BIGINT UNSIGNED or think about UUID v7 to avoid early overflow.

Summary

Choosing the right numeric type is half storage efficiency, half data integrity. Know the byte cost, watch for deprecated attributes, and lean on DECIMAL or DOUBLE where appropriate. Follow the strict-mode and range-check tips above and you’ll avoid the silent truncations, overflows, and precision traps that catch so many teams in production.