Choosing the Right MySQL Data Type

This article explores MySQL data types, like integers, decimals, strings, and dates, used to define the information stored in table columns. Selecting the correct data type is crucial for ensuring data integrity, optimizing storage and performance, and performing calculations and comparisons.

Alexander S. Ricciardi

April 29, 2025

---

In relational databases, information is organized in tables referred to as schemas, the table rows act as records, and the columns act as attributes. To manage and manipulate these tables, developers use Structured Query Language (SQL), with MySQL being the most popular relational database management system (RDBMS). In the context of MySQL, selecting the appropriate data types for table columns, in other words, the data attributes, is essential for the RDBMS and the applications relying on it, as it directly impacts data integrity, storage efficiency, query performance, and the accuracy of data operations like calculations and comparisons. This article explores the data types available in MySQL, discusses their uses, and illustrates the importance of choosing the correct type.

MySQL Data Types

In the context of MySQL, data types define the nature of the information that can be accepted and stored within each specific column of a table. MySQL provides a wide range of data types, each designed to fit a specific kind of data characteristics, such as storage space and allowed values. See the table below for more information.

Table 1

MySQL Data Type

Note: The table lists the different MySQL. Data from “An introduction to MySQL data types” by Ellingwood (n.d.).

The Importance of Data Type Selection

Selecting the right data type in RDBMS is very important as it ensures that the data is stored efficiently and accurately (Sakshijain1, 2025). It impacts several aspects of RDBMS functionality, such as data integrity, storage, and query, as well as operations such as SUM and AVG calculation performed on numeric types, or DATEDIFF and DATE_ADD operations performed on temporal data types.

The following examples illustrate the impact of selecting appropriate data types versus inappropriate ones.

MySQL script below creates an events table to store concert dates:

CREATE TABLE Events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100),
    event_start DATETIME,     -- Using DATETIME type
    ticket_price DECIMAL(8, 2) -- Using DECIMAL for currency
);

INSERT INTO Events (event_name, event_start, ticket_price) VALUES 
('Spring Gala', '2025-05-15 19:00:00', 75.50),
('Summer Concert', '2025-07-20 20:30:00', 45.00),
('Autumn Workshop', '2025-10-10 09:00:00', 120.00);

Note that the column event_start was defined having a DATETIME data type, which allows performing temporal operations, for example, finding events happening in the next 90 days.

The following code snippet queries/retrieves the name and start date/time for all events listed in the Events table where the events are scheduled to start on or after today's date and on or before the date 90 days from today. This logic works because the event_start column was defined as a DATETIME data type.

SELECT event_name, event_start
FROM Events
WHERE event_start BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY);

Another example of properly using data types is defining DECIMAL(8, 2) for ticket_price. This allows calculating the average ticket price without potential floating-point rounding errors:

SELECT AVG(ticket_price) FROM Events;

Now, if event_start were stored as VARCHAR(20) (e.g., 'May 15, 2025 7PM'), using a function similar to DATE_ADD would be very difficult and prone to errors due to the text formats. Similarly, storing ticket_price as VARCHAR (e.g., ‘$75.50’, ‘$40.25’) and when using functions like AVG() or SUM().

Although MySQL implicitly converts the VARCHAR to the DOUBLE type, the output of the conversion is not always what is expected, as AVG(ticket_price) (calculating ticket price average) will compute as 0.0. This happens because MySQL will read the first character for the values, '$' (which is not a number), and interpret the entire expression as not being numeric and translate VARCHAR data type values to the DOUBLE data type value of 0.0.

For example, when using a column:

score_text VARCHAR(20),

Storing the folowing data:

‘100'
'p85'
'Failed'
'90 points'
'70'

and performing the following operation on the column (Computing scores average):

SELECT AVG(score_text)

The MYSQL code line above will first convert the VARCHAR data to DOUBLE data, resulting on the following:

100.0  -- ‘100’
0.0   -- ‘p85’
0.0    -- ‘Failed’
90.0   --’90 Points’
70.0   -- ‘70’

Note that MySQL will generate warnings for the rows containing 'Failed' and '90 points'.

And then it will performed the following calculation (100.0 + 0.0 + 0.0 + 90.0 + 70.0) / 5 = 345.0 / 5 = 52.0

This average output is probably not what was expected, or it is meaningful, because the non-numeric value 'Failed' was converted to 0, lowering the calculated average compared to the average of only the actual numerical scores.

Another example, in scientific measurements where precision is crucial, DECIMAL is preferred over approximate numeric data types like FLOAT.

Let’s say we add reagents with masses like 1.1250g, 0.0750g, and 2.5000g.

When using the FLOAT data type to compute ExperimentComponents_Approx, these values will be stored as close approximations, not the exact numbers, as 1.1250, 0.0750, and 2.5000 are defined as FLOAT; each value is rounded to the nearest representable binary fraction. Then, when calculating the total mass:

SELECT SUM(mass_grams) FROM ExperimentComponents_Approx;

The computation output will be slightly off due to the “accumulated representation errors” (binary rounding error), resulting in 3.7000000000000002 or 3.6999999999999997 instead of exactly 3.7000. For instance, in laboratories or manufacturing quality control settings, this discrepancy could result in incorrect solution concentrations, failed reactions, or items not meeting specifications.

However, when using DECIMAL(10, 4) in ExperimentComponents_Exact guarantees that 1.1250, 0.0750, and 2.5000 are stored precisely, as the computations are performed in base-10 with no hidden representation error. Then, when calculating the sum:

SELECT SUM(mass_grams) FROM ExperimentComponents_Exact;

This computation outputs an exact result of 3.7000 with an exact decimal precision of 4. This precision and accuracy in scientific calculations are essential for meeting a correct product quality standard or scientific specification.

Note that the numeric DOUBLE data type (8 bytes) outperforms the FLOAT data type (4 bytes) by offering roughly twice the bit-width, about 15 to 17 decimal digits versus 7 for the FLOAT. However, the DOUBLE type still has some inexact precision when compared to the DECIMAL type, which has exact precision.

Therefore:

• Use DECIMAL whenever exact decimal precision is needed (e.g., important financial calculations with a high volume of transactions or calculations, quality-control measurements, laboratory concentrations).

• Use DOUBLE when precision matters, but it does not have to be exact.

• Use FLOAT when a slight rounding error is acceptable.

In summary, MySQL provides a set of data types that can be used to store various kinds of information, from numbers and text to dates and spatial data. Selecting the most appropriate data type is crucial, as it significantly impacts data integrity, storage usage, query speed, and the accuracy of operations.

---

References:

Ellingwood, J. (n.d.). An introduction to MySQL data types. https://www.prisma.io/dataguide/mysql/introduction-to-data-types

Sakshijain1 (2025, January 30). SQL data types. GeeksForGeeks. https://www.geeksforgeeks.org/sql-data-types/