Understanding the different data types we have at our disposal is key when designing database tables. We need to ensure that the data types we assign will meet our needs. The below gives an overview of the key datatypes in MySQL.
Signed Integers
Signed integers are the default in MySQL, so when you create a table with an integer field, it will be, by default a signed integer. They can store both positive and negative numbers.
TINYINT (range -128 to +127)
SMALLINT (range -32768 to +32767)
MEDIUMINT (range -8388608 to 8388607)
INT (range -2147483648 to 2147483647)
BIG INT (range -9223372036854775808 to 9223372036854775807)
Unsigned Integers
Unsigned integers store only numbers greater than or equal to zero. When creating a table, you should define your field like FIELDNAME INT UNSIGNED.
TINYINT (range 0 to 255)
SMALLINT (range 0 to 65535)
MEDIUMINT (range 0 to 16777215)
INT (range 0 to 4294967295)
BIG INT (range 0 to 18446744073709551615)
Floating Point Data Types
We have a concept in our floating point data of summarization. Consider that 150 is the same as 1.5*10^2. In this case, we could store 1.5 in addition to a scaling factor. This is in essence what FLOAT and DOUBLE do. They have lower precision, though.
Where you care about absolute precision, you should use the DECIMAL data type.
If you have the number 19.683219 then it will be stored as something like 19.68322 as an approximation of the value. FLOAT and DECIMAL round the values to the precision that they can store. Whereas in a DECIMAL field, it will be stored as the exact value (you declare how many decimal places to store when creating a DECIMAL field).
FLOAT - 4 bytes (accuracy up to 8 places)
DOUBLE - 8 bytes (accuacy up to 18 places)
DECIMAL - stores a maximum of 65 digits (30 digits after decimal). Exact precision
String Data Types
We have a few options when it comes to storing string data. Note: BLOB can hold a variable amount of data and can store images, multimedia etc.
TEXT has a max size of 65535 characters, storage required is 2 x string_length
TINYTEXT has a max size of 255 characters
MEDIUMTEXT has a max size of 16,777,215 characters and up to 64MB in size
LONGTEXT has a max size of 4,294,967,295 characters and up to 4GB in size
VARCHAR has variable size (up to 65535 chars), defined by table definition.
TINYBLOB - 255 bytes of data max
MEDIUMBLOB - 16,777,215 bytes of data max
LONGBLOB - 4,294,967,295 bytes of data max
BLOB - 65,535 bytes of data max
Date/Time Data Types
Working with Dates and Times can be moderately painful. I’ve covered this in some detail in relation to HiveSQL here. Below are the data types we have available with regards to dates and times.
DATE - stores YYYY-MM-DD data
TIME - stores the time of data in hh:mm:ss format
DATETIME - stores the combination of date and time YYYY-MM-DD hh:mm:ss
TIMESTAMP - stores data in seconds (epoch time)
Hopefully this has been a useful cheatsheet.
