intro
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!
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:
Here’s how to check if a column is numeric with regex:
1
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.
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:
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:
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.
