Related Articles Date, Time & Timestamp
Integer, Float and Bit (this one)
Enums and Sets Date, Time & Timestamp Date, Time & Timestamp Integer, Float and Bit (this one) (this one) Enums and Sets Enums and Sets 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. previous article 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: TINYINT SMALLINT MEDIUMINT INT INTEGER BIGINT 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. All five support UNSIGNED option. UNSIGNED Display-width declarations like INT(11) no longer affect storage and are deprecated. INT(11) ZEROFILL pads values with leading zeros and forces UNSIGNED; it too is deprecated. ZEROFILL UNSIGNED Fixed-point types You must use DECIMAL(p,s) for exact numeric values like currency and quantities, as it stores each digit precisely. must DECIMAL(p,s) 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: DOUBLE FLOAT FLOAT/DOUBLE are binary fractions; 0.1 cannot be represented exactly, so expect rounding.
Use DOUBLE unless space or network payload is critical. FLOAT/DOUBLE are binary fractions; 0.1 cannot be represented exactly, so expect rounding. FLOAT DOUBLE Use DOUBLE unless space or network payload is critical. DOUBLE 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. BOOL/BOOLEAN are purely syntax sugar for TINYINT(1). BOOL BOOLEAN syntax sugar TINYINT(1) SERIAL expands to BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. SERIAL BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE Tips & Tricks Pick the smallest integer that fits, as it shrinks indexes, caches, and backups.
Use UNSIGNED for naturally non-negative values (IDs, counts) and you instantly double the positive range.
Money? Choose DECIMAL, not FLOAT, as accountants hate fractions of cents.
Add CHECK constraints (MySQL 8.0+) for business ranges: salary DECIMAL(9,2) CHECK (salary >= 0).
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';
Planning billions of rows? Start with BIGINT UNSIGNED or think about UUID v7 to avoid early overflow. Pick the smallest integer that fits, as it shrinks indexes, caches, and backups. Use UNSIGNED for naturally non-negative values (IDs, counts) and you instantly double the positive range. UNSIGNED Money? Choose DECIMAL, not FLOAT, as accountants hate fractions of cents. DECIMAL FLOAT Add CHECK constraints (MySQL 8.0+) for business ranges: salary DECIMAL(9,2) CHECK (salary >= 0). CHECK salary DECIMAL(9,2) CHECK (salary >= 0) 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'; SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'; Planning billions of rows? Start with BIGINT UNSIGNED or think about UUID v7 to avoid early overflow. BIGINT UNSIGNED UUID v7 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. DECIMAL DOUBLE

This story contains new, firsthand information uncovered by the writer.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Navigating MySQL data types: numeric

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Asynchronous Runtimes: A Primer for the Perplexed Developer

10 Steps To Digital Transformation While Simultaneously Cutting Costs

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

10 Cool CI/CD Tools For Your Project

121 Stories To Learn About Databases

14 Open Source SQL Parsers

Asynchronous Runtimes: A Primer for the Perplexed Developer

10 Steps To Digital Transformation While Simultaneously Cutting Costs

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

10 Cool CI/CD Tools For Your Project

121 Stories To Learn About Databases

14 Open Source SQL Parsers

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps