MySQL data type

In MySQL, Each data type has several characteristics:

• What kind of values it can represent 
• How much space values take up 
• Whether values are fixed-length (all values of the type take the same amount of space) or variable-length (the amount of space depends on the particular value being stored) 
• How MySQL compares and sorts values of the type 
• Whether the type can be indexed 

Character String Types

• CHAR(n)
• VARCHAR(n)
• BINARY(n)
• VARBINARY(n)
• TEXT(n)
• BLOB(n)

we need to specify the lenth of the characters. char has a fixed length, When the stored string is less than the number you specify, it will be filled with 'null', when we use varchar,the database will specify the length of the value, additional cost will be incurred.

blob -- case sensitive

text/char -- case insensitive

numeric type:

Exact numeric type
integer:

• TINYINT
• SMALLINT
• MEDIUMINT
• INT
• BIGINT

Integer has some attributes.

• UNSIGNED: the value is just for zero or positive value.
• NOT NULL: the field does not allow NULL value
• AUTO_INCREMENT: the field's value will be increase for each line. usually leave it to system and don't need to change it manually. DBA can use LAST_INSERT_ID() to get the current value

the exact numeric has another type:
DECIMAL

Approximate numeric type:

FLOAT
DOUBLE

The FLOAT data type is a single-precision floating-point number.A FLOAT is limited in how many significant digits it can store

The DOUBLE data type is a double-precision floating-point number.Like a FLOAT, a DOUBLE is limited in how many significant digits it can store

Datetime:

• DATE: only store date
• TIME: only store time
• DATETIME: store the time eclipsed from 1970-01-01
• TIMESTAMP: store the date and time

vThe TIMESTAMP field is stored in UTC. When the value is stored, it is converted to UTC, using the mysqld system variable time_zone to figure out the proper value. When a TIMESTAMP value is retrieved, it is converted to the current time_zone of the mysqld server

Bool:

  True or False

Internal：

ENUM， SET

MySQL adds in two data types that are very different from standard data types

• The ENUM data type is an enumerated list of 1 to 65,535 strings, which indicate the allowed values for the field. Only one of the allowed values can be stored in an ENUM field
• The SET data type is an enumerated list of 1 to 64 strings, which indicate the allowed values for the field.In the SET data type, any combination of the strings in the enumerated list can be stored as a comma-delimited list