Introduction
A Data Type defines a specific type of data, such as integers, floating points, Booleans, and so on. It also identifies the possible values for that type, the operations that can be performed on that type, and the manner in which that type's values are stored. Each database table in MySQL has many columns and specific data types for each column.
The proper definition of a table's fields is critical to the overall optimization of your database which means that you should only utilize the type and size of the field that you truly require.
For example, if you know you'll only use two characters, don't make a field 10 characters large.
Now let’s deep dive into the data types present in MySQL:
MySQL data types are as follows:
- Numeric Data type
- Date and Time Data type
- String Data Type
- Binary Large Object Types(BLOB)
Let’s understand them one by one:
Numeric Data Type
MySQL comes with all of the SQL numeric data types you'll need. These data types can be exact numeric data types (for example, integer, decimal, numeric, and so on) or approximate numeric data types (for example, integer, decimal, numeric, and so on). It also has a BIT data type for storing bit values. Numeric data types in MySQL are divided into two categories: signed and unsigned, with the exception of bit data types.
Data Type | Description |
BIT(size) | This is a bit-value type. The size specifies the number of bits per value. The size option can be set to any number between 1 and 64. Size is set to 1 by default. |
TINYINT(size) | An extremely tiny number. From -128 to 127 is the signed range. From 0 to 255, the unsigned range is used. The maximum display width is specified by the size option (which is 255). |
SMALLINT(size) | An insignificant number. From -32768 to 32767 is the signed range. 0 to 65535 is the unsigned range. The maximum display width is specified by the size option (which is 255). |
MEDIUMINT(size) | A medium-sized number. -8388608 to 8388607 is the signed range. 0 to 16777215 is the unsigned range. The maximum display width is specified by the size option (which is 255). |
INT(size) | A medium-sized number. -2147483648 to 2147483647 is the signed range. 0 to 4294967295 is the unsigned range. The maximum display width is specified by the size option (which is 255). |
INTEGER(size) | Same as INT(size) |
BIGINT(size) | A significant number. -9223372036854775808 to 9223372036854775807 is the signed range. 0 to 18446744073709551615 is the unsigned range. The maximum display width is specified by the size option (which is 255). |
FLOAT(size,p) | A number that can be expressed as a floating-point number. The size of the total number of digits is specified. The d option specifies the number of digits after the decimal point. In MySQL 8.0.17, this syntax was deprecated, and it will be eliminated in future MySQL versions. |
FLOAT(p) | A number that can be expressed as a floating-point number. The p-value is used by MySQL to determine whether the final data type should be FLOAT or DOUBLE. If p is between 0 and 24, the data type is FLOAT (). If p is between 25 and 53, the data type is DOUBLE (). |
DOUBLE(size,d) | A floating-point number of typical size. The size of the total number of digits is specified. The d option specifies the number of digits after the decimal point. |
DOUBLE PRECISION(size,d) | A double precision floating point number where size is the total digits and d is the number of digits after decimal. |
DECIMAL(size,d) | A fixed-point number that is exact. The size of the total number of digits is specified. The d option specifies the number of digits after the decimal point. Size has a maximum limit of 65. The highest value for d is thirty. Size is set to 10 by default. The value of d is set to 0 by default. |
DEC(size,d) | Same as DECIMAL(size,d) |
BOOL | Nonzero values are deemed true, while zero values are considered false. |
BOOLEAN | Same as BOOL |