Numeric data type

Numerical data are divided into exact and approximate, integer and real. Bit values ​​are a separate category.

Exact integers

TypeMemory sizeRange
TINYINT1 bytefrom -128 to 127 (from -27 to 27-1)
from 0 to 255 (from 0 to 28-1)
SMALLINT2 bytefrom -32768 to 32767 (from -215 to 215-1)
from 0 to 65535 (from 0 to 216-1)
MEDIUMINT3 bytefrom -223 to 223-1
from 0 to 224-1
INT
INTEGER
(aliases)		4 bytefrom -231 to 231-1
from 0 to 232-1
BIGINT8 bytefrom -263 to 263-1
from 0 to 264-1

Integers can be declared with the keyword UNSIGNED. In this case, the elements of this column it will not be possible to assign negative values, and the valid range, which takes on the type doubles. So, type TINYINT can take values from -128 to 127, and TINYINT UNSIGNED — from 0 to 255.

Exact real numbers

TypeRange
DEC[(M,D)]
DECIMAL[(M,D)]
NUMERUC[(M,D)]
(aliases)		Depends on M and D parameters

Type DECIMAL stores exact real values data. It is used when accuracy is critical. For example, when storing financial data.

Usage example:

MySQL
salary DECIMAL(5,2)

This example declares that the salary column will store numbers, having a maximum of 5 digits, 2 of which are reserved for decimal part. That is, this column will store values ​​in the range from -999.99 to 999.99.

The syntax DECIMAL is equivalent toDECIMAL(M) and DECIMAL(M,0). By default, the parameter M is 10.

The whole part and the part after the point are stored as 2 separate integers. On Based on this fact, the amount of memory consumed can be easily calculated. So for DECIMAL(5,2) the integer part contains 3 digits and takes 2 byte, part after the point have 2 digits - 1 byte is enough. Total for storage will be spent 3 byte.

Bit numbers

TypeMemory sizeRange
BIT[(M)]M bitFrom 1 to 64 bitов, depend on the M parameter
BOOL
BOOLEAN
(aliases)		1 bitOr 0, or 1

Type BIT(M)stores a sequence of bits a given length. By default, the length is 8 bit. If assigned the value in a column of this type uses less than M bit, then zeros are padded on the left. For example, when trying to write the value b'101' to BIT(6) will eventually be stored b'000101'.

Approximate numbers

TypeMemory sizeRange
FLOAT[(M, D)]
4 byteMinimum value ±1.17·10-39
Maximum value ±3.4·1038
REAL[(M, D)]
DOUBLE[(M, D)]
(aliases)		8 byteMinimum value ±2.22·10-308
Maximum value ±1.79·10308

Numeric floating point data types can also have a parameter UNSIGNED. As with integer types, this attribute prevents negative storage in the marked column values, but, unlike integer types, the maximum interval for column values ​​remains the same.

Popular functions for working with numbers

GREATEST

Function GREATEST(num1, num2, ...) return maximum value from list.

MySQL
SELECT GREATEST(1,2,-1);
GREATEST(1,2,-1)
2
LEAST

Function LEAST(num1, num2, ...) return minimum value from list.

MySQL
SELECT LEAST(1,2,-1);
LEAST(1,2,-1)
-1
INTERVAL

Function INTERVAL(num, num1, num2, ...) returns 0 if num < num1, and 1 if num < num2, etc. All arguments are treated as integers.

MySQL
SELECT INTERVAL(2,2,4,-1);
INTERVAL(2,2,4,-1)
1
BIT_COUNT

Function BIT_COUNT(num)returns the number of ones in the binary representation of a number.

MySQL
SELECT BIT_COUNT(2), BIT_COUNT(3), BIT_COUNT(7);
BIT_COUNT(2)BIT_COUNT(3)BIT_COUNT(7)
123
ABS

Function ABS(num) return absolute numbers.

MySQL
SELECT ABS(-2), ABS(3.2), ABS(7);
ABS(-2)ABS(3.2)ABS(7)
237
MOD

Function MOD(num1, num2)returns remainder from dividing num1 by num2.

MySQL
SELECT MOD(1,2), MOD(2,2), MOD(7,3);
MOD(1,2)MOD(2,2)MOD(7,3)
101
CEILING

Function CEILING(num)takes fractional number num and returns the smallest integer not less than num.

MySQL
SELECT CEILING(-0.2), CEILING(3.2), CEILING(7);
CEILING(-0.2)CEILING(3.2)CEILING(7)
047
FLOOR

Function FLOOR(num)accepts fractional number num and returns the largest integer not greater than num.

MySQL
SELECT FLOOR(-0.2), FLOOR(3.2), FLOOR(7);
FLOOR(-0.2)FLOOR(3.2)FLOOR(7)
-137
ROUND

Function ROUND(num, [D]) takes fractional number num and returns the nearest integer value. If the second argument is D, then will round to a number with D decimal places.

MySQL
SELECT ROUND(-0.2), ROUND(3.2), ROUND(7.223,1);
ROUND(-0.2)ROUND(3.2)ROUND(7.223,1)
037.2
TRUNCATE

Function TRUNCATE(num, D)takes fractional number num and returns it with D in number of decimal places.

MySQL
SELECT TRUNCATE(-0.2, 0), TRUNCATE(3.2, 3), TRUNCATE(7.223,1);
TRUNCATE(-0.2, 0)TRUNCATE(3.2, 3)TRUNCATE(7.223,1)
03.2007.2
EXP

Function EXP(num) return enum.

MySQL
SELECT EXP(1), EXP(3), EXP(7);
EXP(1)EXP(3)EXP(7)
2.71828182845904520.0855369231876681096.6331584284585
LOG

Function LOG(num, [base])returns the logarithm over base (default is e) of number num.

MySQL
SELECT LOG(1), LOG(10, 100), LOG(EXP(1));
LOG(1)LOG(10, 100)LOG(EXP(1))
021
POW

Function POW(num, power)calculates the power of a number

MySQL
SELECT POW(2,2), POW(10, -2);
POW(2,2)POW(10, -2)
40.01
SQRT

Function SQRT(num)calculates the square root of a number.

MySQL
SELECT SQRT(4), SQRT(10);
SQRT(4)SQRT(10)
23.1622776601683795
PI

Function PI()returns the value number π.

MySQL
SELECT PI();
PI()
3.141593
RAND

Function RAND([num]) returns a random floating point value in the range from 0.0 to 1.0. You can pass an optional argument, and for one and the function will return the same value value.

MySQL
SELECT RAND();
RAND()
0.9858524566384521
SIGN

Function SIGN(num)returns sign passed number: -1 for negative values, 1 for positive and 0 for 0.

MySQL
SELECT SIGN(2), SIGN(-1), SIGN(0);
SIGN(2)SIGN(-1)SIGN(0)
1-10

Self-test tasks

String data type