Comprehensive Cheat Sheet for Character Limits of Various MySQL Data Types

Here’s a cheat sheet for character limits of various MySQL data types:

Photo by Rubaitul Azad on Unsplash

Character Types

• CHAR(n)
  Storage: Fixed-length.
  Maximum Length: 255 characters.
• VARCHAR(n)
  Storage: Variable-length.
  Maximum Length: 65,535 characters (depending on row size and encoding).

Text Types

• TINYTEXT
  Storage: Variable-length.
  Maximum Length: 255 characters (255 bytes).
• TEXT
  Storage: Variable-length.
  Maximum Length: 65,535 characters (64 KiB).
• MEDIUMTEXT
  Storage: Variable-length.
  Maximum Length: 16,777,215 characters (16 MiB).
• LONGTEXT
  Storage: Variable-length.
  Maximum Length: 4,294,967,295 characters (4 GiB).

Binary Types

• BINARY(n)
  Storage: Fixed-length binary data.
  Maximum Length: 255 bytes.
• VARBINARY(n)
  Storage: Variable-length binary data.
  Maximum Length: 65,535 bytes.
• TINYBLOB
  Storage: Variable-length binary data.
  Maximum Length: 255 bytes.
• BLOB
  Storage: Variable-length binary data.
  Maximum Length: 65,535 bytes (64 KiB).
• MEDIUMBLOB
  Storage: Variable-length binary data.
  Maximum Length: 16,777,215 bytes (16 MiB).
• LONGBLOB
  Storage: Variable-length binary data.
  Maximum Length: 4,294,967,295 bytes (4 GiB).

Date and Time Types

• DATE
  Storage: Fixed-length.
  Range: 1000-01-01 to 9999-12-31.
• DATETIME
  Storage: Fixed-length.
  Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
• TIMESTAMP
  Storage: Fixed-length.
  Range: 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.
• TIME
  Storage: Fixed-length.
  Range: -838:59:59 to 838:59:59.
• YEAR
  Storage: Fixed-length.
  Range: 1901 to 2155.

Numeric Types

• TINYINT
  Storage: 1 byte.
  Range: -128 to 127 (signed), 0 to 255 (unsigned).
• SMALLINT
  Storage: 2 bytes.
  Range: -32,768 to 32,767 (signed), 0 to 65,535 (unsigned).
• MEDIUMINT
  Storage: 3 bytes.
  Range: -8,388,608 to 8,388,607 (signed), 0 to 16,777,215 (unsigned).
• INT
  Storage: 4 bytes.
  Range: -2,147,483,648 to 2,147,483,647 (signed), 0 to 4,294,967,295 (unsigned).
• BIGINT
  Storage: 8 bytes.
  Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (signed), 0 to 18,446,744,073,709,551,615 (unsigned).
• FLOAT
  Storage: 4 bytes.
  Range: Approx. -3.402823466E+38 to 3.402823466E+38.
• DOUBLE
  Storage: 8 bytes.
  Range: Approx. -1.7976931348623157E+308 to 1.7976931348623157E+308.
• DECIMAL(p, s)
  Storage: Variable, depends on precision.
  Range: Up to 65 digits (including digits before and after the decimal point), with precision p and scale s.

Other Types

• ENUM
  Storage: 1 or 2 bytes.
  Range: Up to 65,535 distinct values.
• SET
  Storage: 1 to 8 bytes.
  Range: Up to 64 distinct values.

Hope you found this helpful!

Book a session with me: https://adplist.org/mentors/mohasin-hossain