DBSYS 1 – Fundamentals of Database Systems
Understanding Data Types
Data types define the kind of data that can be stored in a column of a database table. Choosing the
right data type is crucial for:
• Data Integrity: Ensuring that only valid data is stored.
• Storage Efficiency: Using the minimum necessary space.
• Query Performance: Optimizing how data is retrieved.
Common Data Types in MySQL (phpMyAdmin)
Here's a breakdown of commonly used data types in MySQL, focusing on their sizes and usage:
1. Numeric Data Types:
• INT (Integer):
o Stores whole numbers (positive, negative, or zero).
o Sizes:
▪ TINYINT: 1 byte (-128 to 127 or 0 to 255 unsigned)
▪ SMALLINT: 2 bytes (-32768 to 32767 or 0 to 65535 unsigned)
▪ MEDIUMINT: 3 bytes (-8388608 to 8388607 or 0 to 16777215 unsigned)
▪ INT (or INTEGER): 4 bytes (-2147483648 to 2147483647 or 0 to 4294967295
unsigned)
▪ BIGINT: 8 bytes (-9223372036854775808 to 9223372036854775807 or 0 to
18446744073709551615 unsigned)
o Use: For storing counts, IDs, and other whole numbers.
• DECIMAL (or NUMERIC):
o Stores exact numeric values with a fixed precision and scale.
o Syntax: DECIMAL(precision, scale)
▪ precision: Total number of digits.
▪ scale: Number of digits after the decimal point.
o Use: For financial data or any data requiring exact precision.
Examples
DECIMAL(5, 2):
Precision: 5
Scale: 2
This means the number can have a total of 5 digits, with 2 of those digits after the
decimal point.
Examples of valid values:
123.45
-99.99
0.01
The range of values that could be stored would be from -999.99 to 999.99.
KENRICK AGUSTIN S. SECUGAL CCS MAIN #iaminfotech
DBSYS 1 – Fundamentals of Database Systems
DECIMAL(10, 3):
Precision: 10
Scale: 3
This means the number can have a total of 10 digits, with 3 of those digits after the
decimal point.
Example valid values:
1234567.890
-1234567.890
1.123
This allows for much larger numbers, and a more precise decimal value.
DECIMAL(3, 0):
Precision: 3
Scale: 0
This means that there will be a total of 3 digits, and none of those digits will be after
the decimal point. Therefore, this acts as an integer value.
Example Valid values:
123
-999
0
• FLOAT and DOUBLE:
o Stores approximate numeric values (floating-point numbers).
o FLOAT: Single-precision.
o DOUBLE (or REAL): Double-precision.
o Use: For scientific calculations or data where approximate values are acceptable.
2. String Data Types:
• VARCHAR:
o Stores variable-length strings.
o Syntax: VARCHAR(length) (length is the maximum number of characters).
o Use: For storing names, addresses, and other text data of varying lengths.
• CHAR:
o Stores fixed-length strings.
o Syntax: CHAR(length) (pads shorter strings with spaces).
o Use: For storing data with consistent lengths, like state abbreviations or postal
codes.
• TEXT:
o Stores large blocks of text.
o Sizes:
▪ TINYTEXT: Up to 255 bytes.
KENRICK AGUSTIN S. SECUGAL CCS MAIN #iaminfotech
DBSYS 1 – Fundamentals of Database Systems
▪ TEXT: Up to 65,535 bytes.
▪ MEDIUMTEXT: Up to 16,777,215 bytes.
▪ LONGTEXT: Up to 4,294,967,295 bytes (38MB)
o Use: For storing articles, descriptions, or other long text.
Key note:
The "VARCHAR(255)" limit is a historical convention that's still commonly seen, and
it's important to understand its origins and how it applies in modern MySQL.
Historical Context:
In older versions of MySQL (prior to 5.0.3), the maximum length for a VARCHAR
column was indeed 255 bytes. This limitation stemmed from how the length of the
variable-length string was stored internally.
Because of this historical limitation, developers commonly used VARCHAR(255) as
a standard. This practice has carried over, even though modern MySQL versions
have greatly expanded the maximum VARCHAR length.
Modern MySQL:
In modern MySQL versions (5.0.3 and later), the maximum length for a VARCHAR
column is 65,535 bytes.However, it's crucial to remember that this is a byte limit,
not a character limit. The number of characters that can be stored depends on the
character set used.
For example, with a single-byte character set like latin1, VARCHAR(255) can
store 255 characters.
With a multi-byte character set like utf8mb4, which can use up to 4 bytes
per character, VARCHAR(255) can store fewer characters.
Also the 65,535 byte limit is a row limit, so all of the columns in a given row,
must not total more than 65,535 bytes.
Why the Convention Persists:
• Habit and Compatibility: Many developers continue to use VARCHAR(255)
out of habit or to ensure compatibility with older systems.
• Reasonable Length: For many common data fields (like names, addresses,
or short descriptions), 255 characters is often sufficient.
• Indexing considerations: There can be indexing considerations, where very
long varchar fields can cause issues with index sizes.
Example:
Imagine you have a database table for storing customer information.
• You might define a column for "customer_name" as VARCHAR(255).
KENRICK AGUSTIN S. SECUGAL CCS MAIN #iaminfotech
DBSYS 1 – Fundamentals of Database Systems
• This would allow you to store customer names up to 255 characters long
(depending on the character set).
3. Date and Time Data Types:
• DATE:
o Stores dates in the format YYYY-MM-DD.
o Use: For storing birth dates, event dates, etc.
• TIME:
o Stores times in the format HH:MM:SS.
o Use: For storing times of day.
• DATETIME:
o Stores both dates and times in the format YYYY-MM-DD HH:MM:SS.
o Use: For storing timestamps of events.
• TIMESTAMP:
o Stores a timestamp representing the number of seconds since the Unix epoch
(January 1, 1970).
o Automatically updates when a row is modified.
o Use: For tracking when rows were last updated.
• YEAR:
o Stores year values.
o Use: Storing year values.
4. Other Data Types:
• BLOB (Binary Large Object):
o Stores binary data (images, audio, video).
o Sizes:
▪ TINYBLOB: Up to 255 bytes.
▪ BLOB: Up to 65,535 bytes.
▪ MEDIUMBLOB: Up to 16,777,215 bytes.
▪ LONGBLOB: Up to 4,294,967,295 bytes.
o Use: Storing large files.
• ENUM:
o A string object that can have only one value, chosen from a list of possible values
that are enumerated explicitly in the column specification at table creation time.
o Use: Storing set values, for example, small, medium, large.
• SET:
o A string object that can have zero or more values, each of which must be chosen
from a list of possible values that are enumerated explicitly in the column
specification at table creation time.
o use: Storing multiple set values, for example, hobbies.
KENRICK AGUSTIN S. SECUGAL CCS MAIN #iaminfotech
DBSYS 1 – Fundamentals of Database Systems
Key Considerations:
• Storage Space: Choose the smallest data type that can accommodate your data to save
storage space.
• Performance: Using appropriate data types can improve query performance.
• Data Validation: Data types help enforce data integrity by restricting the type of data that
can be stored.
• UNSIGNED: Many numeric datatypes can be declared as unsigned. This means that the
column will only store positive numbers and zero, effectively doubling the maximum
positive number that can be stored.
______________
References:
MySQL. (n.d.). MySQL 8.4 Reference Manual: Data Types. Retrieved March 7, 2025, from
https://dev.mysql.com/doc/refman/8.4/en/data-types.html
KENRICK AGUSTIN S. SECUGAL CCS MAIN #iaminfotech
