MYSQL The Data Type of Field

There are some type of MySQL field data, such as:

Numeric data type

It differentiates into two kinds of group, there are integer and floating point data type. For the most information of numeric data type, you just take a look at the table beneath:

Data type
Gyration assess
TINYINT
(-128) – 127 or 0 – 255
SMALLINT
(-32768) – 32767 or 0 – 65535
MEDIUMINT
(-8388608) – 8388607 or 0 – 16777215
INT
(-2147683648) – 2147683647 or 0 – 4294967295
BIGINT
(-9223372036854775808) – 9223372036854775807 or 0 – 18446744073709551615
FLOAT(X)
(-3.402823466E+38) – (-1.175494351E-38), 0, and 1.175494351E-38 – 3.402823466E+38.
FLOAT
The same as above
DOUBLE
(-1.7976931348623157E+308) – (-2.2250738585072014E-308), 0, and 2.2250738585072014E-308 – 1.7976931348623157E+308


Those data type needs memory requirement as follows:

Data type
Memory requirement needs
TINYINT
1 byte
SMALLINT
2 bytes
MEDIUMINT
3 bytes
INT
4 bytes
INTEGER
4 bytes
BIGINT
8 bytes
FLOAT(X)
4 ifX <=24 or 8 if 25 <=X <=53
FLOAT
4 bytes
DOUBLE
8 bytes
DOUBLE PRECISION
8 bytes
REAL
8 bytes
DECIMAL(M,D)
M+2 bytes if D>0, M+1 bytes if D=0(D+2, if M<d)< td=”"> </d)<>
NUMERIC(M,D)
M+2 bytes if D>0, M+1 bytes if D=0(D+2, if M<d)< td=”"> </d)<>


String data type

The types of data that is included into String data such as:

Data type
Gyration assess
CHAR
1-255 character
VARCHAR
1-255 character
TINYLOB, TINYTEXT
1-255 character
BLOB, TEXT
1-65535 character
MEDIUMLOB, MEDIUMTEXT
1-16777215 character
LONGLOB, LONGTEXT
1-4294967295 character
ENUM(‘element1′,’element2′,…)
Maximal 65535 character
SET(‘element1′,’element2′,…)
Maximal 64 element


Char and Varchar Data Type

Char() and Varchar() data type is the same in the principal but the amount of the memori requirement needs is different. The memory requirement needs for the type of char() data has the static character and the level of this need depends to the amount of character that is specified at the time of field declaration. Then, the level of the memory requirement needs of varchar() data type depends on how much the character weared plus 1 byte that is contain of the data of the amount of character weared.

For further information, see the example below:

Value
CHAR(4)
Depository memory
VARCHAR(4)
Depository memory
“
”
4 bytes
“
1 byte
‘ab’
‘ab ‘
4 bytes
‘ab’
3 bytes
‘abcd’
‘abcd’
4 bytes
‘abcd’
5 bytes
‘abcdefgh’
‘abcd’
4 bytes
‘abcd’
5 bytes


Data Type of Date

There are few type of data field for date and time(hour) data as follow:

Data type
Gyration assess
DATETIME
’1000-01-01 00:00:00′ to ’9999-12-31 23:59:59′
DATE
’1000-01-01′ to ’9999-12-31′
TIMESTAMP
’1970-01-01 00:00:00′ – 2037
TIME
‘-838:59:59′ to ’838:59:59′
YEAR
1901 – 2155


Those data date type needs memory requirement as follows:

Data type
Memory requirement needs
DATE
3 bytes
DATETIME
8 bytes
TIMESTAMP
4 bytes
TIME
3 bytes
YEAR
1 bytes