Data types

INT

INT[(width)] [UNSIGNED] [ZEROFILL]

• 4 bytes in storage
• width = number of digits displayed != storage width
• unsigned = positive number
• zerofill: fill left with 0 until width

Example:

CREATE TABLE test_bigint (id BIGINT UNSIGNED);

BIGINT

BIGINT[(width)] [UNSIGNED] [ZEROFILL]

• 8 byte INT

SMALLINT

SMALLINT[(width)] [UNSIGNED] [ZEROFILL]

• 2 byte INT

TINYINT

TINYINT[(width)] [UNSIGNED] [ZEROFILL]

• 1 byte INT

BOOL

BOOL[(width)]

• store in fact as tinyint(1)
• allow range -128 ~ 127, also true and false
• 0 will be treated as false, and all nonzero values as true (including negative)

DECIMAL

DECIMAL[(width[,decimals])] [UNSIGNED] [ZEROFILL]

• a.k.a: DEC, NUMERIC, and FIXED
• Fixed point: the value retrieved is identical to the value stored
• 4 byte of storage for every 9 digits
• width: number of digits decimal & non-decimal
• decimals: number of digits after decimal point

DOUBLE

• Floating points: approximate value
• a.k.a: real
• use round() to get a result to a certain precision

Example:

CREATE TABLE wage (monthly DOUBLE);