List of numeric functions
As we said above, it is not enough for a DBMS to simply store data of a certain type, it also needs to support an extensive set of super-fast operations on data of these types.
And, as you know, all DBMSs support a variety of numeric types and operations on them. Today you will get acquainted with the most popular of them.
All numeric functions in MySQL can be conditionally divided into 5 groups:
- Arithmetically operations
- Bit operations
- Rounding functions
- Algebraic functions
- Trigonometric functions
By the way, you are familiar with many of them thanks to the Java language, so I think they will not come as a surprise to you. But let's start in order.
The full list of features can be found here .
Arithmetic operations
There is, in principle, nothing unexpected, all the same standard operations as elsewhere:
| # | Example | ||
|---|---|---|---|
| 1 | +, -, *, / | 2+2*2 | Addition, subtraction, multiplication, division (not perfect!) |
| 2 | % | 13% 5 | Remainder of the division |
| 3 | MOD | 13MOD5 | Remainder of the division |
| 4 | DIV | 13 DIV 5 | Division by integer |
From the interesting: when dividing two integers, you get a real number. To divide by integer, you need to use the DIV operator.
If you want to get the remainder of a division by an integer, then you need to use either the MOD or % operator, as in the Java language. I will not give examples, as for me everything is obvious here.
Bit operations
You can also perform bitwise operations on numbers in SQL, much like in Java. Although there are nuances. The list of available bit operations is presented in the table below:
| # | Example | Note | ||
|---|---|---|---|---|
| 1 | & | 0b1111 & 0b1000 | Bitwise AND | |
| 2 | | | 0b1111 | 0b0001 | Bitwise OR | |
| 3 | ^ | 0b1111^0b1111 | Bit XOR | |
| 4 | ~ | ~0b1111 | bitwise inversion | |
| 5 | >> | 128 >> 5 | Bit shift right | |
| 6 | << | 2 << 5 | Bit shift left | |
| 7 | BIT_COUNT() | BIT_COUNT(255) | Returns the number of bits that are 1 |
Prior to MySQL version 8.0, such operations could only be performed on the BIGINT data type, which was 64 bits long and was similar to Java's long type. However, in MySQL 8.0 (which we are considering), the concept has changed. Now these operations can be performed on special binary types:
- BINARY
- VARBINARY
- And also over BLOB types (which are an array of bytes)
All other types are simply converted to the BIGINT type and the operations are performed as before (only on the first 64 bits).
Rounding functions
For rounding in MySQL functions painfully familiar to you are used. A list of them is given in the table below:
| # | Example | Note | |
|---|---|---|---|
| 1 | CEIL(), CEILING() | CEIL (5.1) = 6 | Rounds a real number up |
| 2 | FLOOR() | FLOOR(5.9) = 5 | Rounds a real number down |
| 3 | ROUND() | ROUND(4.1) = 4 | Rounds a real number to the nearest integer |
| 4 | TRUNCATE() | TRUNCATE(4.123, 2) = 4.12 | Truncates a number to N decimal places |
| 5 | RAND() | 0.61914388706828 | Returns a random real number between 0 and 1 |
| 6 | RAND(N) | 0.93845168309142 | Returns a random real number between 0 and 1. N is used as the seed value |
You have been familiar with all the rounding functions since the first quest in Java. Only the TRUNCATE() and RANDOM(N) functions can be of interest here.
The TRUNCATE (number, quantity) function takes as its first parameter a real number, and as its second parameter the number of decimal places to be left. The number is trimmed to the desired number of decimal places.
As for the RAND(N) function, things are a bit more complicated. As you probably know, a computer cannot generate random numbers. Instead, it generates a so-called pseudo-random number sequence. That is, looking at one number, it is not clear whether it is random or not, but the sequence of numbers may already be similar to random. Developers take advantage of this by generating a sequence of numbers that is very similar to random.
In this case, each new number of the sequence is generated according to a tricky rule based on the previous number of this sequence . Therefore, if you pass a certain starting number (also called a seed) to the random number generation algorithm, then each time you will receive the same sequence of random numbers .