MySql - Numeric data types

Numeric Data Types (INT, FLOAT, DECIMAL, etc.) in MySQL

MySQL provides a wide range of numeric data types that are designed to store different kinds of numeric values. These include integers, floating-point numbers, and fixed-point numbers. Choosing the right numeric data type is essential for efficient storage, accurate calculations, and optimal database performance. This guide provides a comprehensive overview of MySQL's numeric data types and how to define them in tables.

1. Introduction to Numeric Data Types

MySQL's numeric data types can be divided into three main categories:

• Integer types – store whole numbers (e.g., 1, 100, -99)
• Floating-point types – store approximate values with fractional parts (e.g., 3.1415)
• Fixed-point types – store exact fractional numbers, typically used for currency

Each numeric data type in MySQL has its own size, range, precision, and storage requirements. Let’s look at each category in detail.

2. Integer Data Types

Integer types store whole numbers without any fractional or decimal part. MySQL offers several integer types with varying storage sizes and value ranges.

2.1 Types of Integer Data Types

• TINYINT – 1 byte, range: -128 to 127 or 0 to 255 (unsigned)
• SMALLINT – 2 bytes, range: -32,768 to 32,767 or 0 to 65,535 (unsigned)
• MEDIUMINT – 3 bytes, range: -8,388,608 to 8,388,607 or 0 to 16,777,215 (unsigned)
• INT or INTEGER – 4 bytes, range: -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 (unsigned)
• BIGINT – 8 bytes, range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 (unsigned)

2.2 Syntax and Example

Here is how to define integer columns in a table:

CREATE TABLE employees (
  emp_id INT AUTO_INCREMENT PRIMARY KEY,
  age TINYINT UNSIGNED,
  department_id SMALLINT,
  salary BIGINT
);

This table includes four integer columns with varying types suited to their expected data size.

2.3 Signed vs. Unsigned

By default, integer types are signed, meaning they can hold both positive and negative values. You can use the UNSIGNED keyword to allow only positive values and double the upper limit.

age TINYINT UNSIGNED

2.4 Storage and Performance

Choosing the smallest sufficient integer type can optimize storage and improve performance, especially in large datasets. For example, using TINYINT for storing age or boolean flags is more efficient than using INT.

3. Floating-Point Data Types

Floating-point data types are used for storing approximate numeric values with fractional parts. MySQL provides two main floating-point types: FLOAT and DOUBLE.

3.1 FLOAT and DOUBLE

• FLOAT(M,D): Approximate numeric data type with single-precision (4 bytes)
• DOUBLE(M,D): Approximate numeric data type with double-precision (8 bytes)

Where:

• M is the maximum number of digits (precision)
• D is the number of digits to the right of the decimal point (scale)

Example:

CREATE TABLE products (
  price FLOAT(8,2),
  weight DOUBLE(10,4)
);

This table stores product price with two decimal places and weight with four decimal places.

3.2 Precision and Rounding

FLOAT and DOUBLE types are not exact; they can introduce rounding errors due to how numbers are stored in binary format. This makes them less suitable for financial data where accuracy is critical.

3.3 When to Use FLOAT or DOUBLE

• Use FLOAT or DOUBLE for scientific calculations where performance is more important than precision.
• Do not use them for monetary values or financial applications.

4. Fixed-Point Data Type: DECIMAL

The DECIMAL type (also known as NUMERIC) is used for storing exact numeric values, such as financial amounts. It avoids the rounding errors associated with FLOAT and DOUBLE.

4.1 Syntax and Parameters

DECIMAL(M,D)

Where:

• M is the total number of digits (precision)
• D is the number of digits after the decimal point (scale)

Example:

CREATE TABLE invoices (
  invoice_id INT PRIMARY KEY,
  total_amount DECIMAL(10,2)
);

This means the total_amount column can store up to 10 digits, with 2 after the decimal point (e.g., 99999999.99).

4.2 Use Cases for DECIMAL

• Financial and monetary calculations
• Exact arithmetic operations
• Any application where rounding is unacceptable

4.3 Storage

MySQL stores DECIMAL values as strings (packed format), not as binary floating-point numbers. This increases accuracy but may have performance trade-offs compared to FLOAT or DOUBLE.

5. Additional Numeric Data Types

5.1 BIT

The BIT type stores bit-field values. It is often used for flags or binary data.

CREATE TABLE settings (
  setting_id INT,
  is_active BIT(1)
);

A value of BIT(1) can store 0 or 1.

5.2 BOOLEAN

MySQL has a BOOLEAN keyword, but it is just an alias for TINYINT(1).

CREATE TABLE users (
  user_id INT,
  is_verified BOOLEAN
);

The BOOLEAN type accepts values like 0 (false) and 1 (true), but under the hood it is a tiny integer.

5.3 SERIAL

SERIAL is a shorthand for:

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

It is commonly used for primary keys in large datasets.

CREATE TABLE logs (
  log_id SERIAL,
  message TEXT
);

6. Choosing the Right Numeric Type

When designing your database schema, consider the following factors in selecting the appropriate numeric data type:

6.1 Range of Values

• Choose the smallest integer type that can store your expected values
• Use UNSIGNED to extend the positive range when negative values are not needed

6.2 Precision Requirements

• Use DECIMAL for precise values (e.g., financial data)
• Use FLOAT or DOUBLE for approximations or scientific use

6.3 Performance and Storage

• Smaller data types use less storage and can improve performance
• However, over-optimization can lead to overflow errors and data loss

7. Default Values and NULL

You can specify default values and whether a column allows NULL values:

CREATE TABLE accounts (
  account_id INT AUTO_INCREMENT PRIMARY KEY,
  balance DECIMAL(10,2) DEFAULT 0.00 NOT NULL
);

8. Modifying Numeric Columns

You can change column types with the ALTER TABLE command:

ALTER TABLE accounts
MODIFY balance DECIMAL(12,2);

Note: Always back up your data before modifying column types, especially for numeric conversions.

9. Numeric Functions and Expressions

MySQL supports various numeric operations and functions:

9.1 Arithmetic Operators

• + Addition
• - Subtraction
• * Multiplication
• / Division
• % Modulus

9.2 Aggregate Functions

• SUM()
• AVG()
• MIN()
• MAX()
• COUNT()

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

Numeric data types are an integral part of MySQL database design. Whether you're storing simple counts, large identifiers, or precise monetary values, MySQL offers a type that matches your needs. Choosing the right numeric type improves data integrity, application performance, and reduces storage usage. This guide covered:

• Integer types (TINYINT to BIGINT)
• Floating-point types (FLOAT and DOUBLE)
• Fixed-point DECIMAL types
• Special types like BOOLEAN, BIT, and SERIAL
• Usage scenarios and syntax examples
• Best practices and optimization tips

With a solid understanding of these types, you are well-equipped to design efficient and reliable databases using MySQL.