MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / SQL Statements / Question No: 220

How to use numeric expressions in MySQL?

Answer No: 220

Numbers can be exact-value literals or approximate-value literals. Exact-value literals are used just as given in SQL statements when possible and thus are not subject to the inexactness produced by rounding error. On the other hand, approximate-value literals are subject to rounding error and may not necessarily be used exactly as given.

Exact-value literals are written with no exponent. Approximate-value literals are written in scientific notation with an exponent. For example, the numeric values -43, 368.93, and .00214 are exact values, whereas -4.3E1, 3.6893E2, and 2.14E-3 are approximate values. Even though the two sets of numbers look like they have the same values, internally they are represented in different ways:

  • Exact-value numbers are integer values with no fractional part after the decimal point or decimal values with a fractional part. They're represented internally like an integer or DECIMAL data type. Operations on integers are performed with the precision of BIGINT values (that is, 64 bits). Operations on decimal values have a precision of up to 64 decimal digits. Currently, the scale for decimal values allows up to 30 decimal digits after the decimal point.
  • Approximate-value literals are represented as floating-point numbers (like the DOUBLE data type) and have a mantissa and exponent. The mantissa allows up to 53 bits of precision, which is about 15 decimal digits.

When numbers are used in an arithmetic or comparison operation, the result of the operation may depend on whether it involves exact or approximate values. Consider the following two comparisons:

mysql> SELECT 1.1 + 2.2 = 3.3, 1.1E0 + 2.2E0 = 3.3E0;

+-----------------+-------------------------+
| 1.1 + 2.2 = 3.3 | 1.1E0 + 2.2E0 = 3.3E0   |
+-----------------+-------------------------+
|               1 |                     0   |
+-----------------+-------------------------+

In the first expression, exact values are used, so the comparison involves exact calculations. In the second expression, approximate values are used and rounding error is possible. This illustrates that if you use approximate values in comparisons, you cannot expect exact-value precision. The internal representation of floating-point numbers inherently allows for the possibility of rounding error.

If you mix numbers with strings in numeric context, MySQL converts the strings to numbers and performs a numeric operation:

mysql> SELECT 1 + '1', 1 = '1';
+---------+---------+
| 1 + '1' | 1 = '1' |
+---------+---------+
|       2 |       1 |
+---------+---------+

Related MySQL FAQs to the Above FAQ

What-does-alias-means-in-MySQL What does alias means in MySQL?

What-does-SQL-Expressions-mean-in-MySQL What does SQL Expressions mean in MySQL?

How-to-use-string-expressions-in-MySQL How to use string expressions in MySQL?

How-to-manage-case-sensitivity-in-string-comparisons How to manage case sensitivity in string comparisons?

How-to-use-LIKE-pattern-matching-operator How to use LIKE pattern-matching operator?

How-to-use-date-or-temporal-expressions How to use date or temporal expressions?

What-does-NULL-value-mean-in-MySQL What does NULL value mean in MySQL?

How-to-use-comments-in-SQL-statements How to use comments in SQL statements?

How-to-validate-user-login-in-case-sensitive-manner How to validate user login in case sensitive manner?

What-is-difference-between-delete-and-truncate-statements-of-MySQL What is difference between delete and truncate statements of MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.