Working with Numeric Data Types in MySQL: A Comprehensive Guide

Understanding and utilizing numeric data types in MySQL is essential for everyone working with this database system. In this guide, we’ll walk you through their numeric counterparts. Excited? So are we!

Tools used in this tutorial
DbVisualizer, top rated database management tool and SQL client
The MySQL database version 8 or later

Do you work with data inside of your database instance? You work with data types! Data types are a usual part of daily life of every database administrator as well as developer, no matter what language the developer speaks. Data types are especially important for your databases because they allow you to store all kinds of values - from strings to data related to time. However, one data type stands out and that’s the data type related to numbers: numeric data types are the most widely used data types to store numeric values inside of your database. In this blog, we’ll walk you through what they are, what they do, and how best to use them – our friends over at Database Dive can teach you a thing or two about these subjects as well, so if you’re interested in video content around databases, make sure to have a look.

What Are Numeric Data Types?

MySQL supports the following numeric data types:

• Various types of integers with INT, SMALLINT, TINYINT, MEDIUMINT, and BIGINT.
• Fixed-point numbers with DECIMAL or NUMERIC.
• Approximate numeric values with FLOAT and DOUBLE.
• The BIT data type is supported as well.

Here’s how to check if a column is numeric with regex:

SELECT * 
FROM [table_name] 
WHERE [column_name] REGEXP '^[0-9]+$';

Checking if a column is numeric won’t do us too many favors, though - we must also ensure that if we have numeric values, we work with them appropriately. Don’t fret – doing so is not too difficult of a task!

How to Work with Numeric Data Types?

Prerequisites

Before you start working with numeric data types in MySQL, ask yourself these important questions:

What kind of data am I storing in the column? An answer to this question will let you choose the proper data type.

• If you have numbers smaller than 255, use TINYINT.
• If you have numbers with values smaller than 65535, use SMALLINT.
• If you have numbers with values greater than 65535 but smaller than 16777215, the MEDIUMINT data type is for you.
• For numbers smaller than 4294967295, use the INT data type. For anything bigger, use BIGINT.
• If you’re storing exact values, use DECIMAL after you specify a scale (see examples below for an example.)
• If you need to store floating-point data types, FLOAT or DOUBLE should be used instead.
• Finally, if you want to store any bit values, use the BIT data type.

Am I working with bigger data sets? How much disk space do I have on my server? The answer to this question is paramount because if you’re working with bigger data sets, the length of your columns becomes more important.

What do I want to achieve? Finally, the answer to this question is also very important because you cannot achieve any of your goals without having a clear vision – perhaps you don’t even need to use integer-based data types and having only VARCHAR or TEXT would suffice?

Choosing a Numeric Data Type

Finally, after you’ve already asked yourself these questions, it’s time to choose a proper data type for your use case. Here’s what to choose and when:

| Use Case                                                                  | Numeric Data Type     |
|------------------------------------------------------------------------   |-------------------    |
| Very small integers (not bigger than 255)                                 | TINYINT               |
| Small integers (not bigger than 65K)                                      | SMALLINT              |
| Integers bigger than 65K but not larger than 18M                          | MEDIUMINT             |
| General use cases involving integers                                      | INT                   |
| Storing data (e.g. salaries) and need to store integers with precision    | DECIMAL               |
| Storing approximate numeric data                                          | FLOAT\|DOUBLE         |
| Storing 0\|1\|NULL values                                                 | BIT                   |

There are quite a few things you should take away:

1. There’s no need to memorize what numeric data type houses what range of integers. It’s much easier to just have general guidelines in mind, and if necessary – refer to the documentation.
2. The DECIMAL data type lets you define what precision to store the integers in (i.e. it lets you define how many numbers after the “.” sign should be displayed.)
3. The BIT data type is very useful if you’re running low on storage space and want to store TRUE|FALSE values.

Already chosen a numeric data type? Don’t hurry too much – have a look into examples.

Examples

Here’s an example of a table:

On this table, we have:

• An automatically incrementing user_id column.
• A salary column that can hold 5 digits for values and 1 digit after the decimal point.
• A true/false column that holds 0|1|NULL values.
• A math_result column that can hold integer values up to 153 characters in length.

This example is what you would most likely see in real-world scenarios. Not too damning, huh?

MySQL also offers support for floating-point data types – these are FLOAT, REAL, and DOUBLE PRECISION. Their syntax looks like so:

DATA_TYPE(A,B) where DATA_TYPE is the data type (FLOAT|REAL|DOUBLE PRECISION), A means digits to store in the column and B means digits after the decimal point. Such syntax is deprecated as of MySQL 8 though.

Other Things to Consider

After you’ve chosen the data type appropriate for your use case, keep in mind that there are a couple of additional things you may want to consider. One of the most crucial aspects to consider when working with numeric data types in MySQL is the use of SQL clients. SQL clients like DbVisualizer offer a variety of tools such as visual query builders, ways to explore your databases and optimize their performance.

Good SQL clients are also widely used by companies that have a name for themselves (think Tesla, NASA, Google, Netflix, etc.) – did we mention that they also have free trial periods available for everyone? Give DbVisualizer a shot, follow the advice given above, and you will see your database performance skyrocket – we promise.

Summary

In this blog, we’ve walked you through numeric data types and their acquaintances in MySQL. We hope that you’ve enjoyed reading this blog and taken some time to reflect on the data types in your database instance, and until next time!

Frequently Asked Questions

What Numeric Data Type Should I Use in MySQL?

Take a look into the table given above – we think that SMALLINT would in most cases suffice, but you’d have to decide for yourself.

What’s an Optimal Length for a Numeric Data Type?

There’s no definite answer for this one – you’d need to evaluate your requirements, take a look into your database performance and characteristics, and choose for yourself.

Why Should I Use a SQL Client?

You should use a SQL client because SQL clients like the one provided by DbVisualizer let you monitor your database performance, security, and other metrics to ensure that your database always stays on top of its game.

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.