Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn about numeric functions in MariaDB Server. This section details SQL functions for performing mathematical calculations, rounding, and manipulating numeric values in your queries.
MariaDB has always included a native unary function CRC32() that computes the CRC-32 of a string using the ISO 3309 polynomial that used by zlib and many others.
InnoDB and MyRocks use a different polynomial, which was implemented in SSE4.2 instructions that were introduced in the Intel Nehalem microarchitecture. This is commonly called CRC-32C (Castagnoli).
The CRC32C function uses the Castagnoli polynomial.
This allows SELECT…INTO DUMPFILE to be used for the creation of files with valid checksums, such as a logically empty InnoDB redo log fileib_logfile0 corresponding to a particular log sequence number.
The optional parameter allows the checksum to be computed in pieces: CRC32C('MariaDB')=CRC32C(CRC32C('Maria'),'DB').
This page is licensed: CC BY-SA / Gnu FDL
CRC32C([par,]expr)CEIL(X)This is the converse of the RADIANS() function.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DEGREES(X)This page is licensed: GPLv2, originally from fill_help_tables.sql
ATAN(X)SELECT ATAN(2);
+--------------------+
| ATAN(2) |
+--------------------+
| 1.1071487177940904 |
+--------------------+
SELECT ATAN(-2);
+---------------------+
| ATAN(-2) |
+---------------------+
| -1.1071487177940904 |
+---------------------+SELECT CEILING(1.23);
+---------------+
| CEILING(1.23) |
+---------------+
| 2 |
+---------------+
SELECT CEILING(-1.23);
+----------------+
| CEILING(-1.23) |
+----------------+
| -1 |
+----------------+Calculate arc tangent of two variables. This function returns the angle in radians between the positive x-axis and the point (X, Y).
Calculate absolute value. This function returns the non-negative value of a number, removing any negative sign.
SELECT CRC32C('MariaDB');
+-------------------+
| CRC32C('MariaDB') |
+-------------------+
| 809606978 |
+-------------------+
SELECT CRC32C(CRC32C('Maria'),'DB');
+------------------------------+
| CRC32C(CRC32C('Maria'),'DB') |
+------------------------------+
| 809606978 |
+------------------------------+SELECT DEGREES(PI());
+---------------+
| DEGREES(PI()) |
+---------------+
| 180 |
+---------------+
SELECT DEGREES(PI() / 2);
+-------------------+
| DEGREES(PI() / 2) |
+-------------------+
| 90 |
+-------------------+
SELECT DEGREES(45);
+-----------------+
| DEGREES(45) |
+-----------------+
| 2578.3100780887 |
+-----------------+XNULLX-11This page is licensed: GPLv2, originally from fill_help_tables.sql
ACOS(X)SELECT ATAN(-2,2);
+---------------------+
| ATAN(-2,2) |
+---------------------+
| -0.7853981633974483 |
+---------------------+
SELECT ATAN2(PI(),0);
+--------------------+
| ATAN2(PI(),0) |
+--------------------+
| 1.5707963267948966 |
+--------------------+SELECT ASIN(0.2);
+--------------------+
| ASIN(0.2) |
+--------------------+
| 0.2013579207903308 |
+--------------------+
SELECT ASIN('foo');
+-------------+
| ASIN('foo') |
+-------------+
| 0 |
+-------------+
SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+SELECT COS(PI());
+-----------+
| COS(PI()) |
+-----------+
| -1 |
+-----------+Compute cyclic redundancy check. This function returns a 32-bit unsigned integer representing the CRC32 checksum of a string.
CRC32([par,]expr)CRC32(expr)Computes a cyclic redundancy check (CRC) value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected to be a string and (if possible) is treated as one if it is not.
Uses the alternate Castagnoli polynomia.
Often, CRC is computed in pieces. To facilitate this, there's an optional parameter: CRC32('MariaDB')=CRC32(CRC32('Maria'),'DB').
Uses the ISO 3309 polynomial that used by zlib and many others.
This page is licensed: GPLv2, originally from
SELECT ACOS(1);
+---------+
| ACOS(1) |
+---------+
| 0 |
+---------+
SELECT ACOS(1.0001);
+--------------+
| ACOS(1.0001) |
+--------------+
| NULL |
+--------------+
SELECT ACOS(0);
+-----------------+
| ACOS(0) |
+-----------------+
| 1.5707963267949 |
+-----------------+
SELECT ACOS(0.234);
+------------------+
| ACOS(0.234) |
+------------------+
| 1.33460644244679 |
+------------------+SELECT ABS(42);
+---------+
| ABS(42) |
+---------+
| 42 |
+---------+
SELECT ABS(-42);
+----------+
| ABS(-42) |
+----------+
| 42 |
+----------+
SELECT ABS(DATE '1994-01-01');
+------------------------+
| ABS(DATE '1994-01-01') |
+------------------------+
| 19940101 |
+------------------------+SELECT CRC32(CRC32('Maria'),'DB');
+----------------------------+
| CRC32(CRC32('Maria'),'DB') |
+----------------------------+
| 4227209140 |
+----------------------------+SELECT CRC32('MariaDB');
+------------------+
| CRC32('MariaDB') |
+------------------+
| 4227209140 |
+------------------+
SELECT CRC32('mariadb');
+------------------+
| CRC32('mariadb') |
+------------------+
| 2594253378 |
+------------------+Calculate exponential value. This function returns the value of e (the base of natural logarithms) raised to the power of the argument.
Calculate power. This function returns the value of a number raised to the specified exponent.
POWER(X,Y)This is a synonym for POW(), which returns the value of X raised to the power of Y.
This page is licensed: GPLv2, originally from
Return the value of pi. This function returns the mathematical constant π (approximately 3.141593).
SELECT LOG10(2);
+-------------------+
| LOG10(2) |
+-------------------+
| 0.301029995663981 |
+-------------------+
SELECT LOG10(100);
+------------+
| LOG10(100) |
+------------+
| 2 |
+------------+
SELECT LOG10(-100);
+-------------+
| LOG10(-100) |
+-------------+
| NULL |
+-------------+SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
SELECT PI()+0.0000000000000000000000;
+-------------------------------+
| PI()+0.0000000000000000000000 |
+-------------------------------+
| 3.1415926535897931159980 |
+-------------------------------+SELECT POW(2,3);
+----------+
| POW(2,3) |
+----------+
| 8 |
+----------+
SELECT POW(2,-2);
+-----------+
| POW(2,-2) |
+-----------+
| 0.25 |
+-----------+This page is licensed: GPLv2, originally from fill_help_tables.sql
LOG2(X)SELECT LOG2(4398046511104);
+---------------------+
| LOG2(4398046511104) |
+---------------------+
| 42 |
+---------------------+
SELECT LOG2(65536);
+-------------+
| LOG2(65536) |
+-------------+
| 16 |
+-------------+
SELECT LOG2(-100);
+------------+
| LOG2(-100) |
+------------+
| NULL |
+------------+If called with two parameters, it returns the logarithm of X to the base B. If B is <= 1 or X <= 0, the function returns NULL.
If any argument is NULL, the function returns NULL.
The inverse of this function (when called with a single argument) is the EXP() function.
LOG(X):
LOG(B,X)
This page is licensed: GPLv2, originally from fill_help_tables.sql
LOG(X), LOG(B,X)NULLThe inverse of this function is EXP().
This page is licensed: GPLv2, originally from fill_help_tables.sql
LN(X)This page is licensed: GPLv2, originally from fill_help_tables.sql
TAN(X)ERROR_ON_DIVISION_BY_ZERO SQL_MODE is used, any number modulus zero produces an error. Otherwise, it returns NULL.The integer part of a division can be obtained using DIV.
This page is licensed: GPLv2, originally from fill_help_tables.sql
This is the converse of the DEGREES() function.
This page is licensed: GPLv2, originally from fill_help_tables.sql
RADIANS(X)This page is licensed: GPLv2, originally from fill_help_tables.sql
FLOOR(X)SELECT FLOOR(1.23);
+-------------+
| FLOOR(1.23) |
+-------------+
| 1 |
+-------------+
SELECT FLOOR(-1.23);
+--------------+
| FLOOR(-1.23) |
+--------------+
| -2 |
+--------------+SELECT EXP(2);
+------------------+
| EXP(2) |
+------------------+
| 7.38905609893065 |
+------------------+
SELECT EXP(-2);
+--------------------+
| EXP(-2) |
+--------------------+
| 0.1353352832366127 |
+--------------------+
SELECT EXP(0);
+--------+
| EXP(0) |
+--------+
| 1 |
+--------+
SELECT EXP(NULL);
+-----------+
| EXP(NULL) |
+-----------+
| NULL |
+-----------+SELECT LOG(2);
+-------------------+
| LOG(2) |
+-------------------+
| 0.693147180559945 |
+-------------------+
SELECT LOG(-2);
+---------+
| LOG(-2) |
+---------+
| NULL |
+---------+SELECT LOG(2,16);
+-----------+
| LOG(2,16) |
+-----------+
| 4 |
+-----------+
SELECT LOG(3,27);
+-----------+
| LOG(3,27) |
+-----------+
| 3 |
+-----------+
SELECT LOG(3,1);
+----------+
| LOG(3,1) |
+----------+
| 0 |
+----------+
SELECT LOG(3,0);
+----------+
| LOG(3,0) |
+----------+
| NULL |
+----------+SELECT LN(2);
+-------------------+
| LN(2) |
+-------------------+
| 0.693147180559945 |
+-------------------+
SELECT LN(-2);
+--------+
| LN(-2) |
+--------+
| NULL |
+--------+SELECT TAN(0.7853981633974483);
+-------------------------+
| TAN(0.7853981633974483) |
+-------------------------+
| 0.9999999999999999 |
+-------------------------+
SELECT TAN(PI());
+-----------------------+
| TAN(PI()) |
+-----------------------+
| -1.22460635382238e-16 |
+-----------------------+
SELECT TAN(PI()+1);
+-----------------+
| TAN(PI()+1) |
+-----------------+
| 1.5574077246549 |
+-----------------+
SELECT TAN(RADIANS(PI()));
+--------------------+
| TAN(RADIANS(PI())) |
+--------------------+
| 0.0548861508080033 |
+--------------------+MOD(N,M), N % M, N MOD MSELECT 1042 % 50;
+-----------+
| 1042 % 50 |
+-----------+
| 42 |
+-----------+
SELECT MOD(234, 10);
+--------------+
| MOD(234, 10) |
+--------------+
| 4 |
+--------------+
SELECT 253 % 7;
+---------+
| 253 % 7 |
+---------+
| 1 |
+---------+
SELECT MOD(29,9);
+-----------+
| MOD(29,9) |
+-----------+
| 2 |
+-----------+
SELECT 29 MOD 9;
+----------+
| 29 MOD 9 |
+----------+
| 2 |
+----------+SELECT RADIANS(45);
+-------------------+
| RADIANS(45) |
+-------------------+
| 0.785398163397448 |
+-------------------+
SELECT RADIANS(90);
+-----------------+
| RADIANS(90) |
+-----------------+
| 1.5707963267949 |
+-----------------+
SELECT RADIANS(PI());
+--------------------+
| RADIANS(PI()) |
+--------------------+
| 0.0548311355616075 |
+--------------------+
SELECT RADIANS(180);
+------------------+
| RADIANS(180) |
+------------------+
| 3.14159265358979 |
+------------------+Round a number. This function rounds a number to a specified number of decimal places.
ROUND(X), ROUND(X,D)Rounds the argument X to D decimal places. D defaults to 0 if not specified.D can be negative to cause D digits left of the decimal point of the value X to become zero.
The rounding algorithm depends on the data type of X:
For floating-point types (, ) the C libraries rounding function is used, so the behavior may differ between operating systems.
For fixed-point types (, ) the "round half up" rule is used, meaning that e.g. a value ending in exactly .5 is always rounded up.
This page is licensed: GPLv2, originally from
Generate a random number. This function returns a random floating-point value between 0 and 1.
SELECT SIN(1.5707963267948966);
+-------------------------+
| SIN(1.5707963267948966) |
+-------------------------+
| 1 |
+-------------------------+
SELECT SIN(PI());
+----------------------+
| SIN(PI()) |
+----------------------+
| 1.22460635382238e-16 |
+----------------------+
SELECT ROUND(SIN(PI()));
+------------------+
| ROUND(SIN(PI())) |
+------------------+
| 0 |
+------------------+RAND(3)In a WHERE clause, RAND() is evaluated each time the WHERE is executed.
Statements using the RAND() function are not safe for statement-based replication.
The expression to get a random integer from a given range is the following:
RAND() is often used to read random rows from a table, as follows:
Note, however, that this technique should never be used on a large table as it will be extremely slow. MariaDB will read all rows in the table, generate a random value for each of them, order them, and finally will apply the LIMIT clause.
Using the same seed, the same sequence will be returned:
Generating a random number from 5 to 15:
This page is licensed: GPLv2, originally from fill_help_tables.sql
BIGINTIf the ERROR_ON_DIVISION_BY_ZERO SQL_MODE is used, a division by zero produces an error. Otherwise, it returns NULL.
The remainder of a division can be obtained using the MOD operator.
This page is licensed: GPLv2, originally from fill_help_tables.sql
This page is licensed: GPLv2, originally from fill_help_tables.sql
SIGN(X)Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero.
This page is licensed: GPLv2, originally from fill_help_tables.sql
TRUNCATE(X,D)SELECT ROUND(-1.23);
+--------------+
| ROUND(-1.23) |
+--------------+
| -1 |
+--------------+
SELECT ROUND(-1.58);
+--------------+
| ROUND(-1.58) |
+--------------+
| -2 |
+--------------+
SELECT ROUND(1.58);
+-------------+
| ROUND(1.58) |
+-------------+
| 2 |
+-------------+
SELECT ROUND(1.298, 1);
+-----------------+
| ROUND(1.298, 1) |
+-----------------+
| 1.3 |
+-----------------+
SELECT ROUND(1.298, 0);
+-----------------+
| ROUND(1.298, 0) |
+-----------------+
| 1 |
+-----------------+
SELECT ROUND(23.298, -1);
+-------------------+
| ROUND(23.298, -1) |
+-------------------+
| 20 |
+-------------------+RAND(), RAND(N)FLOOR(min_value + RAND() * (max_value - min_value +1))SELECT * FROM my_table ORDER BY RAND() LIMIT 10;CREATE TABLE t (i INT);
INSERT INTO t VALUES(1),(2),(3);
SELECT i, RAND() FROM t;
+------+-------------------+
| i | RAND() |
+------+-------------------+
| 1 | 0.255651095188829 |
| 2 | 0.833920199269355 |
| 3 | 0.40264774151393 |
+------+-------------------+
SELECT i, RAND(3) FROM t;
+------+-------------------+
| i | RAND(3) |
+------+-------------------+
| 1 | 0.90576975597606 |
| 2 | 0.373079058130345 |
| 3 | 0.148086053457191 |
+------+-------------------+
SELECT i, RAND() FROM t;
+------+-------------------+
| i | RAND() |
+------+-------------------+
| 1 | 0.511478140495232 |
| 2 | 0.349447508668012 |
| 3 | 0.212803152588013 |
+------+-------------------+SELECT i, RAND(3) FROM t;
+------+-------------------+
| i | RAND(3) |
+------+-------------------+
| 1 | 0.90576975597606 |
| 2 | 0.373079058130345 |
| 3 | 0.148086053457191 |
+------+-------------------+SELECT FLOOR(5 + (RAND() * 11));DIVSELECT 300 DIV 7;
+-----------+
| 300 DIV 7 |
+-----------+
| 42 |
+-----------+
SELECT 300 DIV 0;
+-----------+
| 300 DIV 0 |
+-----------+
| NULL |
+-----------+SELECT SIGN(-32);
+-----------+
| SIGN(-32) |
+-----------+
| -1 |
+-----------+
SELECT SIGN(0);
+---------+
| SIGN(0) |
+---------+
| 0 |
+---------+
SELECT SIGN(234);
+-----------+
| SIGN(234) |
+-----------+
| 1 |
+-----------+SELECT TRUNCATE(1.223,1);
+-------------------+
| TRUNCATE(1.223,1) |
+-------------------+
| 1.2 |
+-------------------+
SELECT TRUNCATE(1.999,1);
+-------------------+
| TRUNCATE(1.999,1) |
+-------------------+
| 1.9 |
+-------------------+
SELECT TRUNCATE(1.999,0);
+-------------------+
| TRUNCATE(1.999,0) |
+-------------------+
| 1 |
+-------------------+
SELECT TRUNCATE(-1.999,1);
+--------------------+
| TRUNCATE(-1.999,1) |
+--------------------+
| -1.9 |
+--------------------+
SELECT TRUNCATE(122,-2);
+------------------+
| TRUNCATE(122,-2) |
+------------------+
| 100 |
+------------------+
SELECT TRUNCATE(10.28*100,0);
+-----------------------+
| TRUNCATE(10.28*100,0) |
+-----------------------+
| 1028 |
+-----------------------+The function returns the DOUBLE data type for all signatures and input data types.
The format parser understands the following components:
Digits: 0, 9
Hex digits: X
Group separators: comma (,) and G
Decimal delimiters: period (.) and D
Approximate number signature: EEEE
Currency/numeric flags: $ and B
Currency signatures: C, L, U
Sign signatures: S, MI, PR
Special format signatures: V, TM, TM9, TME
Format flag: FM
The function was introduced for Oracle compatibility, but does not include the following features:
The ON CONVERSION ERROR clause
The third parameter (nlsparam)
Internationalized components: G, D, C, L, U
These features are planned to be be implemented via MDEV-36978.
This page is licensed: CC BY-SA / Gnu FDL
TO_NUMBER(number_or_string_subject)
TO_NUMBER(string_subject,string_format)SELECT TO_NUMBER('100.00');
+---------------------+
| TO_NUMBER('100.00') |
+---------------------+
| 100 |
+---------------------+Convert numbers between bases. This function transforms a number from one numeric base system to another.
Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base.
Returns NULL if any argument is NULL, or if the second or third argument are not in the allowed range.
The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 62. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.
The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise,
Some shortcuts for this function are also available: , , , . Also, MariaDB allows literal values and literal values.
This page is licensed: GPLv2, originally from
CONV(N,from_base,to_base)NCONV()This page is licensed: GPLv2, originally from fill_help_tables.sql
COT(X)SELECT COT(42);
+--------------------+
| COT(42) |
+--------------------+
| 0.4364167060752729 |
+--------------------+
SELECT COT(12);
+---------------------+
| COT(12) |
+---------------------+
| -1.5726734063976893 |
+---------------------+
SELECT COT(0);
ERROR 1690 (22003): DOUBLE value is out of range in 'cot(0)'SELECT CONV('a',16,2);
+----------------+
| CONV('a',16,2) |
+----------------+
| 1010 |
+----------------+
SELECT CONV('6E',18,8);
+-----------------+
| CONV('6E',18,8) |
+-----------------+
| 172 |
+-----------------+
SELECT CONV(-17,10,-18);
+------------------+
| CONV(-17,10,-18) |
+------------------+
| -H |
+------------------+
SELECT CONV(12+'10'+'10'+0xa,10,10);
+------------------------------+
| CONV(12+'10'+'10'+0xa,10,10) |
+------------------------------+
| 42 |
+------------------------------+SELECT SQRT(4);
+---------+
| SQRT(4) |
+---------+
| 2 |
+---------+
SELECT SQRT(20);
+------------------+
| SQRT(20) |
+------------------+
| 4.47213595499958 |
+------------------+
SELECT SQRT(-16);
+-----------+
| SQRT(-16) |
+-----------+
| NULL |
+-----------+
SELECT SQRT(1764);
+------------+
| SQRT(1764) |
+------------+
| 42 |
+------------+Convert to octal. This function returns the octal string representation of a numeric argument.
OCT(N)Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.
This page is licensed: GPLv2, originally from
SELECT OCT(34);
+---------+
| OCT(34) |
+---------+
| 42 |
+---------+
SELECT OCT(12);
+---------+
| OCT(12) |
+---------+
| 14 |
+---------+