Examples of Different Types of Operation

MySQL Tutorials - Herong's Tutorial Examples

∟Examples of Different Types of Operation

This section provides tutorial examples of different types of operations: numeric, predicate, date and time.

Example 1 - NumericOps.sql:

-- NumericOps.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
SELECT 1 + 2 AS LINE_1;
SELECT 2 / 3 AS LINE_2;
SELECT 2.0000 / 3 AS LINE_3;
SELECT 1234567890/10000 AS LINE_4;
SELECT 1234567890/1000000000 AS LINE_5;
SELECT 1234567890/1e10 AS LINE_6;
SELECT 1234567890/1e13 AS LINE_7;
SELECT 1234567890/1e14 AS LINE_8;
SELECT 1 / 0 AS LINE_9;
SELECT 1.2345 - 0.2345 AS LINE_10;
SELECT 2.00 / 1 AS LINE_11;
SELECT 2.00 - 1 AS LINE_12;

Run NumericOps.sql on MySQL 8.0, 5.7, 5.6 and 5.0 servers, you will get:

herong> set mysql="\Program Files\MySQL\MySQL Server 8.0"

herong> %mysql%\bin\mysql --user=root --password=TopSecret test \
   < NumericOps.sql

LINE_1
3
LINE_2
0.6667
LINE_3
0.66666667
LINE_4
123456.7890
LINE_5
1.2346
LINE_6
0.123456789
LINE_7
0.000123456789
LINE_8
0.0000123456789
LINE_9
NULL
LINE_10
1.0000
LINE_11
2.000000
LINE_12
1.00

Note that:

The data type of the resulting value depends on the data types of the operands.
Line 1 tells us that if both operands are integers and the resulting value is a whole number, the resulting data type will be integer.
Line 2 and 4 tell us that if both operands are integers and the resulting value is not a whole number, the resulting data type will be exact number with a scale of 2.
Line 9 is a surprise to me. Why MySQL is not giving a "divide-by-zero" exception? Or a "overflow" exception?
It seems to be no rules to describe the behaviors of the other lines.

But run NumericOps.sql on MySQL 4.0 server, you will get:

LINE_1
3
LINE_2
0.67
LINE_3
0.666667
LINE_4
123456.79
LINE_5
1.23
LINE_6
0.123456789
LINE_7
0.000123456789
LINE_8
1.23456789e-005
LINE_9
NULL
LINE_10
1.0000
LINE_11
2.0000
LINE_12
1.00

As you can see, older version of MySQL are not as accurate as newer versions.

Example 2 - PredicateOps.sql

-- PredicateOps.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
SELECT 1 = 1 AS LINE_1;
SELECT 'abc' = 'ABC' AS LINE_2;
SELECT 'abc' < 'xyz' AS LINE_3;
SELECT 'abc' < 'abcd' AS LINE_4;
SELECT 0.67 = 2/3 AS LINE_5;
SELECT 2/3 BETWEEN 0.66 AND 0.67 AS LINE_6;
SELECT 'APPLE' IN ('apple', 'orange', 'banana') AS LINE_7;
SELECT 'abc' LIKE 'Abc' AS LINE_8;
SELECT '3.14' LIKE '_.__' AS LINE_9;
SELECT '3.14159' LIKE '3.%' AS LINE_10;
SELECT 'herong_yang' LIKE 'herong\_yang' AS LINE_11;
SELECT '4.75%' LIKE '_.__\%' AS LINE_12;

Run PredicateOps.sql on MySQL 8.0 server, you will get:

herong> %mysql%\bin\mysql --user=root --password=TopSecret test \
   < PredicateOps.sql

LINE_1
1
LINE_2
1
LINE_3
1
LINE_4
1
LINE_5
0
LINE_6
1
LINE_7
1
LINE_8
1
LINE_9
1
LINE_10
1
LINE_11
1
LINE_12
1

Note that:

Line 1 confirms that MySQL is not case sensitive with character string operations. If you want to compare character strings with case, you can use BINARY() function. For example, BINARY('abc') = BINARY('ABC') will return false.
Line 4 tells us that string comparison also consider the length of the string. The longer string has a higher order, if the common portion is a match.
Line 5 is interesting. It tells us that expression "2/3" is evaluated with a scale higher than 2. And resulting value is not truncated to 0.67 when it is used in the predicate operation.

Example 3 - DateTimeOps.sql

-- DateTimeOps.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
SELECT DATE '1999-01-01' + INTERVAL '3' MONTH AS LINE_1;
SELECT DATE '2000-03-01' - INTERVAL '1' DAY AS LINE_2;
SELECT INTERVAL '48' HOUR + TIMESTAMP '1999-01-01 00:00:00' AS LINE_3;

Run DateTimeOps.sql on MySQL 5.7 server, you will get:

herong> %mysql%\bin\mysql --user=root --password=TopSecret test \
   < PredicateOps.sql

LINE_1
1999-04-01
LINE_2
2000-02-29
LINE_3
1999-01-03 00:00:00