MySQL

Working with Numeric Data Types in MySQL: A Comprehensive Guide

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!

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

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:

Copy
        
1 SELECT * FROM [table_name] WHERE [column_name] REGEXP '^[0-9]+$';
Checking for Numeric Columns in DbVisualizer.
Checking for Numeric Columns in DbVisualizer.

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 CaseNumeric 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 18MMEDIUMINT
General use cases involving integersINT
Storing data (e.g. salaries) and need to store integers with precisionDECIMAL
Storing approximate numeric dataFLOAT|DOUBLE
Storing 0|1|NULL valuesBIT

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:

Creating a Table in DbVisualizer.
Creating a Table in DbVisualizer.

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.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
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.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SUBSTRING_INDEX in SQL Explained: A Guide

author Lukas Vileikis tags MySQL SQL 8 min 2024-04-08
title

SQL NOT IN: the Good, Bad & the Ugly

author Lukas Vileikis tags MySQL SQL 7 min 2024-04-04
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

MySQL IFNULL – Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-02-26
title

MySQL CREATE DATABASE Statement: Definitive Guide

author Antonello Zanini tags Create database MySQL 7 min 2024-02-08
title

A Complete Guide to SUBSTRING_INDEX in SQL

author Antonello Zanini tags MySQL 6 min 2024-01-16
title

Error: MySQL Shutdown Unexpectedly - The Solution

author Lukas Vileikis tags MySQL 5 MINS 2024-01-04

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗