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
