MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL Arithmetic

From WikiOD

Remarks[edit | edit source]

MySQL, on most machines, uses 64-bit IEEE 754 floating point arithmetic for its calculations.

In integer contexts it uses integer arithmetic.

  • RAND() is not a perfect random number generator. It is mainly used to quickly generate pseudorandom numbers

Arithmetic Operators[edit | edit source]

MySQL provides the following arithmetic operators

Operator Name Example
+ Addition SELECT 3+5; -> 8

SELECT 3.5+2.5; -> 6.0

SELECT 3.5+2; -> 5.5

- Subtraction SELECT 3-5; -> -2
* Multiplication SELECT 3 * 5; -> 15
/ Division SELECT 20 / 4; -> 5

SELECT 355 / 113; -> 3.1416

SELECT 10.0 / 0; -> NULL

DIV Integer Division SELECT 5 DIV 2; -> 2
% or MOD Modulo SELECT 7 % 3; -> 1

SELECT 15 MOD 4 -> 3

SELECT 15 MOD -4 -> 3

SELECT -15 MOD 4 -> -3

SELECT -15 MOD -4 -> -3

SELECT 3 MOD 2.5 -> 0.5

BIGINT[edit | edit source]

If the numbers in your arithmetic are all integers, MySQL uses the BIGINT (signed 64-bit) integer data type to do its work. For example:

select (1024 * 1024 * 1024 * 1024 *1024 * 1024) + 1 -> 1,152,921,504,606,846,977

and

select (1024 * 1024 * 1024 * 1024 *1024 * 1024 * 1024 -> BIGINT out of range error

DOUBLE[edit | edit source]

If any numbers in your arithmetic are fractional, MySQL uses 64-bit IEEE 754 floating point arithmetic. You must be careful when using floating point arithmetic, because many floating point numbers are, inherently, approximations rather than exact values.

Mathematical Constants[edit | edit source]

Pi[edit | edit source]

The following returns the value of PI formatted to 6 decimal places. The actual value is good to DOUBLE;

SELECT PI();    -> 3.141593

Trigonometry (SIN, COS)[edit | edit source]

Angles are in Radians, not Degrees. All computations are done in IEEE 754 64-bit floating point. All floating point computations are subject to small errors, known as machine ε (epsilon) errors, so avoid trying to compare them for equality. There is no way to avoid these errors when using floating point; they are built in to the technology.

If you use DECIMAL values in trigonometric computations, they are implicitly converted to floating point, and then back to decimal.

Sine[edit | edit source]

Returns the sine of a number X expressed in radians

SELECT SIN(PI()); -> 1.2246063538224e-16

Cosine[edit | edit source]

Returns the cosine of X when X is given in radians

SELECT COS(PI()); -> -1

Tangent[edit | edit source]

Returns the tangent of a number X expressed in radians. Notice the result is very close to zero, but not exactly zero. This is an example of machine ε.

SELECT TAN(PI());   -> -1.2246063538224e-16

Arc Cosine (inverse cosine)[edit | edit source]

Returns the arc cosine of X if X is in the range -1 to 1

SELECT ACOS(1);    -> 0
SELECT ACOS(1.01); -> NULL

Arc Sine (inverse sine)[edit | edit source]

Returns the arc sine of X if X is in the range -1 to 1

SELECT ASIN(0.2); -> 0.20135792079033

Arc Tangent (inverse tangent)[edit | edit source]

ATAN(x) returns the arc tangent of a single number.

SELECT ATAN(2); -> 1.1071487177941

ATAN2(X, Y) returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X. But it is numerically more robust: t functions correctly when X is near zero, and the signs of both arguments are used to determine the quadrant of the result.

Best practice suggests writing formulas to use ATAN2() rather than ATAN() wherever possible.

 ATAN2(1,1);    -> 0.7853981633974483 (45 degrees)
 ATAN2(1,-1);   -> 2.356194490192345  (135 degrees)
 ATAN2(0, -1);  -> PI  (180 degrees)  don't try ATAN(-1 / 0)... it won't work

Cotangent[edit | edit source]

Returns the cotangent of X

SELECT COT(12); -> -1.5726734063977

Conversion[edit | edit source]

SELECT RADIANS(90) -> 1.5707963267948966
SELECT SIN(RADIANS(90)) -> 1
SELECT DEGREES(1), DEGREES(PI()) -> 57.29577951308232, 180

Rounding (ROUND, FLOOR, CEIL)[edit | edit source]

Round a decimal number to an integer value[edit | edit source]

For exact numeric values (e.g. DECIMAL): If the first decimal place of a number is 5 or higher, this function will round a number to the next integer away from zero. If that decimal place is 4 or lower, this function will round to the next integer value closest to zero.

SELECT ROUND(4.51) -> 5
SELECT ROUND(4.49) -> 4
SELECT ROUND(-4.51) -> -5

For approximate numeric values (e.g. DOUBLE): The result of the ROUND() function depends on the C library; on many systems, this means that ROUND() uses the round to the nearest even rule:

SELECT ROUND(45e-1) -> 4  -- The nearest even value is 4
SELECT ROUND(55e-1) -> 6  -- The nearest even value is 6

Round up a number[edit | edit source]

To round up a number use either the CEIL() or CEILING() function

SELECT CEIL(1.23)    -> 2
SELECT CEILING(4.83) -> 5

Round down a number[edit | edit source]

To round down a number, use the FLOOR() function

SELECT FLOOR(1.99) -> 1

FLOOR and CEIL go toward / away from -infinity:

SELECT FLOOR(-1.01), CEIL(-1.01) -> -2 and -1
SELECT FLOOR(-1.99), CEIL(-1.99) -> -2 and -1

Round a decimal number to a specified number of decimal places.[edit | edit source]

SELECT ROUND(1234.987, 2) -> 1234.99
SELECT ROUND(1234.987, -2) -> 1200

The discussion of up versus down and "5" applies, too.

Raise a number to a power (POW)[edit | edit source]

To raise a number x to a power y, use either the POW() or POWER() functions

SELECT POW(2,2); => 4
SELECT POW(4,2); => 16

Square Root (SQRT)[edit | edit source]

Use the SQRT() function. If the number is negative, NULL will be returned

SELECT SQRT(16); -> 4
SELECT SQRT(-3); -> NULL

Random Numbers (RAND)[edit | edit source]

Generate a random number[edit | edit source]

To generate a pseudorandom floating point number between 0 and 1, use the RAND() function

Suppose you have the following query

SELECT i, RAND() FROM t;

This will return something like this

i RAND()
1 0.6191438870682
2 0.93845168309142
3 0.83482678498591

Random Number in a range[edit | edit source]

To generate a random number in the range a <= n <= b, you can use the following formula

FLOOR(a + RAND() * (b - a + 1))

For example, this will generate a random number between 7 and 12

SELECT FLOOR(7 + (RAND() * 6));

A simple way to randomly return the rows in a table:

SELECT * FROM tbl ORDER BY RAND();

These are pseudorandom numbers.

The pseudorandom number generator in MySQL is not cryptographically secure. That is, if you use MySQL to generate random numbers to be used as secrets, a determined adversary who knows you used MySQL will be able to guess your secrets more easily than you might believe.

Absolute Value and Sign (ABS, SIGN)[edit | edit source]

Return the absolute value of a number

SELECT ABS(2);   -> 2
SELECT ABS(-46); -> 46

The sign of a number compares it to 0.

Sign Result Example
-1 n < 0 SELECT SIGN(42); -> 1
0 n = 0 SELECT SIGN(0); -> 0
1 n > 0 SELECT SIGN(-3); -> -1
SELECT SIGN(-423421); -> -1

Credit:Stack_Overflow_Documentation