Skip to content

A Complete Guide to MySQL Column Types and Their Limits

Blog Categories

When designing a database, one of the critical aspects to consider is the choice of column types. In MySQL, different column types come with specific storage capacities and limitations. Understanding these limits is crucial for optimizing the performance and scalability of your database. This guide will walk you through the various MySQL column types and their respective limits, helping you make informed decisions for your database schema.

1. Numeric Data Types

MySQL offers several numeric data types, each with its own storage size and range of values. Here’s a breakdown:

Integer Types

  • TINYINT: Stores small integers.
    • Range: -128 to 127 (signed) or 0 to 255 (unsigned).
    • Storage: 1 byte.
  • SMALLINT: Stores slightly larger integers.
    • Range: -32,768 to 32,767 (signed) or 0 to 65,535 (unsigned).
    • Storage: 2 bytes.
  • MEDIUMINT: A medium-sized integer.
    • Range: -8,388,608 to 8,388,607 (signed) or 0 to 16,777,215 (unsigned).
    • Storage: 3 bytes.
  • INT (or INTEGER): A standard integer type.
    • Range: -2,147,483,648 to 2,147,483,647 (signed) or 0 to 4,294,967,295 (unsigned).
    • Storage: 4 bytes.
  • BIGINT: For very large integers.
    • Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (signed) or 0 to 18,446,744,073,709,551,615 (unsigned).
    • Storage: 8 bytes.

Floating-Point Types

  • FLOAT: A single-precision floating-point number.
    • Range: Approx. ±3.402823466E+38 to ±1.175494351E-38.
    • Storage: 4 bytes.
  • DOUBLE: A double-precision floating-point number.
    • Range: Approx. ±1.7976931348623157E+308 to ±2.2250738585072014E-308.
    • Storage: 8 bytes.

Decimal Types

  • DECIMAL: For storing exact numeric values.
    • Range: Depends on the specified precision (up to 65 digits).
    • Storage: Varies, based on the precision and scale.

2. String Data Types

MySQL provides various string types, each designed for different use cases:

Character String Types

  • CHAR: A fixed-length string.
    • Size: 0 to 255 characters.
    • Storage: Depends on the character set (1 byte per character for latin1, up to 4 bytes per character for utf8mb4).
  • VARCHAR: A variable-length string.
    • Size: 0 to 65,535 characters.
    • Storage: 1 or 2 bytes for length prefix + string length.

Text Types

  • TINYTEXT: Stores very small text strings.
    • Size: Up to 255 characters.
    • Storage: Length of string + 1 byte.
  • TEXT: Stores smaller text strings.
    • Size: Up to 65,535 characters.
    • Storage: Length of string + 2 bytes.
  • MEDIUMTEXT: Stores medium-sized text strings.
    • Size: Up to 16,777,215 characters.
    • Storage: Length of string + 3 bytes.
  • LONGTEXT: Stores large text strings.
    • Size: Up to 4,294,967,295 characters (4 GB).
    • Storage: Length of string + 4 bytes.

Binary Data Types

  • BINARY: A fixed-length binary string.
    • Size: 0 to 255 bytes.
    • Storage: Same as CHAR.
  • VARBINARY: A variable-length binary string.
    • Size: 0 to 65,535 bytes.
    • Storage: Same as VARCHAR.

Blob Types

  • TINYBLOB: Stores very small binary data.
    • Size: Up to 255 bytes.
    • Storage: Length of data + 1 byte.
  • BLOB: Stores small binary data.
    • Size: Up to 65,535 bytes.
    • Storage: Length of data + 2 bytes.
  • MEDIUMBLOB: Stores medium-sized binary data.
    • Size: Up to 16,777,215 bytes.
    • Storage: Length of data + 3 bytes.
  • LONGBLOB: Stores large binary data.
    • Size: Up to 4,294,967,295 bytes (4 GB).
    • Storage: Length of data + 4 bytes.

3. Date and Time Data Types

Date and time data types are used to store temporal values:

  • DATE: Stores a date value.
    • Range: ‘1000-01-01’ to ‘9999-12-31’.
    • Storage: 3 bytes.
  • DATETIME: Stores date and time.
    • Range: ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
    • Storage: 5 bytes.
  • TIMESTAMP: Stores a timestamp.
    • Range: ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.
    • Storage: 4 bytes.
  • TIME: Stores time only.
    • Range: ‘-838:59:59’ to ‘838:59:59’.
    • Storage: 3 bytes.
  • YEAR: Stores a year value.
    • Range: 1901 to 2155.
    • Storage: 1 byte.

4. JSON Data Type

  • JSON: Stores JSON data.
    • Size: Limited by the maximum size of a LONGBLOB (4 GB).
    • Storage: Depends on the complexity of the JSON data.

Conclusion

Selecting the appropriate column type is a crucial decision in database design. MySQL offers a range of column types, each suited for specific data storage requirements. Understanding these types and their limits ensures that your database can handle the data you expect it to store while remaining performant and efficient.

Whether you’re storing small integers, large text files, or complex JSON objects, MySQL has a column type that fits your needs. By carefully considering the size limits and storage requirements, you can optimize your database schema for the best performance.