Understanding MySQL's TINYINT: A Closer Look at TINYINT(2) vs TINYINT(1)

When working with MySQL, choosing the appropriate data type is crucial for ensuring both the efficiency and accuracy of your database operations. Among the numerical data types, TINYINT is often used for storing small integer values. However, the way MySQL represents TINYINT with display width can be a little perplexing, particularly when examining TINYINT(2) versus TINYINT(1). Let's dive deeper into these distinctions.

Basics of TINYINT

The TINYINT data type in MySQL is designed to hold very small integer values. The default and maximum storage size is 1 byte, which equates to 8 bits. This allows an unsigned range of 0 to 255 and a signed range of -128 to 127.

Example:

1-- Create a table using TINYINT
2CREATE TABLE example (
3    id TINYINT(1),
4    status TINYINT(2),
5    age TINYINT
6);

Display Width in TINYINT

In MySQL, a specification such as TINYINT(1) or TINYINT(2) refers to the display width of the data, a feature more relevant with the optional ZEROFILL attribute.

• Display width does not affect the storage size. Both TINYINT(1) and TINYINT(2) are stored using 1 byte.
• Display width specifies the number of characters MySQL should expect to display. This means for numbers that have fewer digits than the display width and ZEROFILL is applied, zeros are prepended. However, without ZEROFILL, display width has no effect on the stored data.

Example:

1-- When ZEROFILL is used
2CREATE TABLE example_with_zerofill (
3    id TINYINT(2) ZEROFILL UNSIGNED
4);
5
6-- Insert a value
7INSERT INTO example_with_zerofill VALUES (5);
8
9-- Querying:
10SELECT id FROM example_with_zerofill; 
11-- Output: 05

Notice that the value 5 is displayed as 05 due to the ZEROFILL option.

Practical Considerations

• Default Behavior: When not using ZEROFILL, the display width (TINYINT(2) vs TINYINT(1)) has no impact.
• Usage of ZEROFILL: This is particularly useful for formatting numbers without post-processing, though not commonly used in modern applications since display formatting can typically be achieved within application logic.

Key Differences Summarized

Additional Details

• Deprecated Practice: The concept of display width for integer types, including TINYINT, is deprecated in MySQL 8.0 and later. This means that the TINYINT(1) versus TINYINT(2) practice is less pertinent than before.
• Boolean Compatibility: Despite the display width, TINYINT(1) is often used for holding boolean values due to its compactness, though any integer type could theoretically serve this purpose with proper enumeration logic.

Conclusion

Understanding TINYINT display width is crucial primarily for legacy systems or database structures that aim for specific data formatting using ZEROFILL. In modern applications, especially with MySQL 8.0 and forward, the practice of specifying display width has less importance, guiding a shift towards using TINYINT more for its storage efficiency rather than its display traits. In most cases today, integer display formatting is best managed in the application layer rather than the database.