MySQL Data Type

 MySQL organizes its data types into several main categories based on the kind of information they store. Choosing the right one is essential for optimizing storage and ensuring your database runs smoothly.

1. Numeric Data Types

These are used for storing numbers, ranging from small counters to large financial figures.

• Integer Types:
  • TINYINT: Very small integer (1 byte).
  • SMALLINT: Small integer (2 bytes).
  • MEDIUMINT: Medium-sized integer (3 bytes).
  • INT or INTEGER: Standard integer (4 bytes).
  • BIGINT: Large integer (8 bytes).
• Fixed-Point:
  • DECIMAL: Used for exact precision, like currency. You define it as DECIMAL(P, D), where $P$ is precision and $D$ is scale.
• Floating-Point:
  • FLOAT: Small, approximate precision (4 bytes).
  • DOUBLE: Large, approximate precision (8 bytes).
• Bit-Value:
  • BIT: Stores bit values (1 to 64 bits).

2. String (Character) Data Types

These store text, ranging from single characters to massive documents.

• Standard Strings:
  • CHAR: Fixed-length non-binary string.
  • VARCHAR: Variable-length non-binary string (most common for names/emails).
• Text Types (Large Text):
  • TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.
• Binary Strings:
  • BINARY: Fixed-length binary.
  • VARBINARY: Variable-length binary.
• Large Binary (BLOB):
  • TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB.

3. Date and Time Data Types

Used for tracking when events occur.

• DATE: Format YYYY-MM-DD.
• DATETIME: Format YYYY-MM-DD HH:MM:SS.
• TIMESTAMP: Format YYYY-MM-DD HH:MM:SS. It is timezone-dependent and usually tracks when a row was last updated.
• TIME: Format HH:MM:SS.
• YEAR: Format YYYY or YY.

4. Spatial Data Types

MySQL supports Geographic Information System (GIS) data for mapping and coordinates.

• GEOMETRY: A point, line, or polygon.
• POINT: A single coordinate $(x, y)$.
• LINESTRING: A curve with one or more line segments.
• POLYGON: A planar surface.

5. Other Special Types

• JSON: Since version 5.7.8, MySQL supports a native JSON data type that allows for efficient access to data in JSON documents.
• ENUM: An enumeration; a string object that can have only one value chosen from a list of permitted values.
• SET: A string object that can have zero or more values, each of which must be chosen from a list of permitted values.

Pro Tip: Use VARCHAR instead of CHAR if the length of your text varies significantly, as it saves space by only storing the characters actually used. However, CHAR is slightly faster for fixed-length data like "Country Codes" (e.g., "US", "UK").