For both small free database space and large e-commerce websites, reasonable database table structure design is essential. To achieve this, it requires us to have a full understanding of commonly used data types in database system. Below we share some knowledge about data types in MySQL.
1. Numeric types
The numeric types can be classified as : integer, float and decimal type.
The so-called "decimal" refers DECIMAL and NUMERIC, they are of the same type. Strictly speaking it is not a numeric type, because they are actually stored as strings; Every single digit of its value(including the decimal point) accounts for one byte of storage space, so this type consumes much storage space But its advantage is its value will not lose accuracy when doing floating point number calculation, it is more suitable for some calculations with high accuracy requirements such as price calculation .
Float type depending on the accuracy can be FLOAT or DOUBLE. Their advantage is fractional accuracy, FLOAT can express very small value, the smallest value can be about 1.17E-38 (0.000 ... 0117, 37 zero after the decimal point), and the DOUBLE can express much smaller number, the smallest number can be about 2.22E-308 (0.000 ... 0222, a decimal point followed by 307 zeros) decimal. FLOAT and DOUBLE take storage space of 4 bytes and 8 bytes respectively.
Integer type. there are many different types of integers in MySQL, while designing database table, we can have one byte TINYINT or 8 bytes BIGINT etc. So we should put much consideration on which type to use to get the minimal storage space without losing any accuracy of the value. TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT take 1 byte, 2 bytes, 3 bytes, 4 byte and 8-byte respectively. For unsigned integer, these types can represent the largest integer number are 255,65535,16777215,4294967295 and 18446744073709551615 respectively. If we need to save the user's age, TINYINT is enough; If we want to save an AUTO_INCREMENT IDENTIFY field which will not be more than 16 million rows, we should use MEDIUMINT instead of INT.
2. Date Time type
The date and time type is relatively simple, there are some types like DATE, TIME, DATETIME, TIMESTAMP, and YEAR. If we only need to care about the date but not the time, we should use DATE instead of DATETIME, sometimes we may need to have time alone with TIME; but DATETIME is the most frequently used among them.
3. Character type
Do not think that the character type is only CHAR, the difference between CHAR and VARCHAR is that CHAR is a fixed length. if you define a field CHAR(10), then no matter how many bytes the data has, it will take 10 bytes of space; VARCHAR is variable-length, if we have a field whose values have different lengths, then we should use VARCHAR.
4. Enumeration and collection types
Enumeration (ENUM) type, you can define up to 65,535 kinds of different string from which to make a choice only and must choose one. It may take one or two bytes depending on how many values in the ENUM type. The collection(SET) type, it can have up to 64 different members, you can choose zero or multiple members from it, the number of members of the collection may decide.
For example, in SQLServer, you can use BIT type to represent gender (male / female), but in MySQL, there is no BIT type, it's a waste to use TINTINT, you can use ENUM('male', 'female'), there are only two options, so just needs one byte which has the same size of TINYINT, but with the convenience of saving string 'male' and 'female'.
Source : http://www.ccvita.com/106.html
