The numeric data types in MySQL include the following:
- INT: An INT value can range from -2147483648 to 2147483647. An INT can only store whole numbers. It can't store numbers with decimal places. Any value you try to place with decimal places will be rounded up or down depending on the decimal value. If you try to store a number outside the range, the number won't be stored, and you will receive an error.
Value to insert | INT value |
12.34 | 12 |
12.76 | 13 |
INT values can be signed or unsigned. Unsigned only stores positive numbers and signed ones can store both positive and negative numbers.
With INT, there are different varieties from which to choose. They are BIGINT, SMALLINT, MEDIUMINT, INT, and TINYINT. The following table outlines the range of values that each can store:
Types |
Signed Range |
Unsigned Range |
TINYINT |
-128 to 127 |
0 to 255 |
SMALLINT |
-32768 to 32767 |
0 to 65535 |
MEDIUMINT |
-8388608 to 8388607 |
0 to 16777215 |
INT |
-2147483648 to 2147483647 |
0 to 4294967295 |
BIGINT |
-9223372036854775808 to 9223372036854775807 |
0 to 18446744073709551615 |
When choosing an INT data type, it's vital to select the smallest size INT value that will accommodate your data to reduce storage space:
- FLOAT: FLOAT is a floating-point number. A floating-point number means that there isn't a fixed number of digits before and after the decimal place so that the decimal point can float. To use the FLOAT data type, you use FLOAT(size, p), where size is the total number of digits, and p is the number of digits after the decimal place. The maximum size is 24. If your value's size is above 24, use DOUBLE (see below) instead. FLOAT is accurate up to approximately 7 decimal places.
To store a number like 1234.5678, you would create the data type as FLOAT(8,4). FLOAT is useful for scientific kinds of calculations where extra decimal places are helpful. Don't use FLOAT for financial data since FLOAT isn't as accurate as DECIMAL.
- DOUBLE: DOUBLE is a floating-point number. To use the DOUBLE data type, you use DOUBLE(size, d), where size is the total number of digits, and d is the number of digits after the decimal place.
DOUBLE is accurate up to approximately 14 decimal places. To store a number like 1234567890123456789012345.1234567, you would create the data type as DOUBLE(33,7). DOUBLE is useful for scientific kinds of calculations where extra decimal places are helpful. Don't use DOUBLE for financial data since FLOAT isn't as accurate as DECIMAL.
- DECIMAL: DECIMAL is an exact fixed-point number. To use the DECIMAL data type, you use DECIMAL(size, d), where size is the total number of digits, and d is the number of digits after the decimal place. The maximum size is 65, and the maximum d is 30. If size and d aren't specified, the default size is 10, and the default d is 0.
To store a number like 1234567.89, you would create the data type as DECIMAL(9,2). DECIMAL is suitable for financial data because it more accurately represents all numbers, whereas FLOAT and DOUBLE aren't as accurate. The storage usage of a DECIMAL data type varies based on the size of the value stored.
- BIT: The BIT data type stores binary values. The syntax is BIT(size), where size is the number in bits a value can have. The value range is 1 to 64. If you leave off the size on BIT, the default size will be 1. Generally, BIT stores 0 and 1 boolean values, where 0 is false, and 1 is true. There are other edge use cases, such as storing values as binary, such as months that have 30 days in them, like so: 000101001010. The 1's in this BIT represent the months that have 30 days, and the 0's represent the months that don't. The following table shows you what the 0's and 1's correspond to in the bit value 000101001010:
Month |
Number of days |
Bit stored |
January |
31 days |
0 |
February |
28 or 29 days |
0 |
March |
31 days |
0 |
April |
30 days |
1 |
May |
31 days |
0 |
June |
30 days |
1 |
July |
31 days |
0 |
August |
31 days |
0 |
September |
30 days |
1 |
October |
31 days |
0 |
November |
30 days |
1 |
December |
31 days |
0 |