Database Data Types


A data type is a named set of values from which column values are drawn.

Categories

  • Integer
    • represent positive and negative integers
    • multiple types that vary on the number of bytes allocated
      • e.g., SMALLINT
  • Decimal
    • represent numbers with fractional values
    • vary by number of digits after the decimal point and maximum size
    • syntax: DECIMAL(M digits, D digits after decimal)
    • e.g., FLOAT and DECIMAL
  • Character
    • represent textual characters
    • CHAR = fixed string of characters
    • VARCHAR = variable length string specified to a max size
  • Date and time
    • represent date, time, or both
    • some include a time zone or specify a time interval
    • e.g., DATE, TIME, DATETIME, TIMESTAMP
  • Binary
    • store data exactly as the data appears in memory or computer files, bit for bit
    • e.g., BLOB, BINARY, VARBINARY, and IMAGE
  • Spatial
    • store geometric information, such as lines, polygons, and map coordinates
    • e.g., POLYGON, POINT, and GEOMETRY
    • new and vary greatly across database systems
  • Document
    • contain textual data in a structured format such as XML or JSON

MySQL Data Types

  • Most databases allow integer and decimal numbers to be signed or unsigned
  • A signed number may be negative
  • An unsigned number cannot be negative
  • data types vary in storage requirements:
    • Character data types use one or two bytes per character
    • Integer data types use a fixed number of bytes per number
    • Unsigned data types can store larger numbers than the signed version of the same data type
  • To minimize table size,
    • data type with the smallest storage requirements should be used

My SQL Data Types