Working with Numeric Data Types in MySQL: A Comprehensive Guide

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.

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:

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:

1. 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.

1. 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.
2. 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:

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.