Numeric Literal Evaluation Examples

This section provides tutorial examples on how numeric literals are evaluated to values of different datatypes.

Numeric literal evaluation example - NumericLiterals.sql:

-- NumericLiterals.sql
-- Copyright (c) 2015, HerongYang.com, All Rights Reserved.
--
SELECT 1 AS LINE_1;
SELECT -2 AS LINE_2;
SELECT 3.3 AS LINE_3;
SELECT -4.4e+4 AS LINE_4;
SELECT 12345678901234567890 AS LINE_5;
SELECT 0.12345678901234567890 AS LINE_6;
SELECT 1234567890.1234567890 AS LINE_7;
SELECT 12345678901234567890.1234567890 AS LINE_8;
SELECT 1234567890.1234567890e+10 AS LINE_9;
SELECT 0.0000000000000000000012345678901234567890 AS LINE_10;
SELECT 1.0e+1234567890 AS LINE_11;

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

C:\herong>set "MySQL=\Program Files\MySQL\MySQL Server 5.7"
C:\herong>"%MySQL%\bin\mysql" --user=root --password=TopSecret test 
   < NumericLiterals.sql
   
LINE_1
1
LINE_2
-2
LINE_3
3.3
LINE_4
-44000
LINE_5
123456789012345678901234567890
LINE_6
0.12345678901234567890
LINE_7
1234567890.1234567890
LINE_8
12345678901234567890.1234567890
LINE_9
1.2345678901234567e19
LINE_10
0.0000000000000000000012345678901234567890
ERROR 1367 (22007) at line 14: Illegal double '1.0e+1234567890' value
found during parsing

A number of interesting notes here:

Run NumericLiterals.sql on MySQL 5.6 and 5.0 servers, you will get the same result as above.

Run NumericLiterals.sql on MySQL 4.0 server, you will get:

LINE_1
1
LINE_2
-2
LINE_3
3.3
LINE_4
-44000
LINE_5
123456789012345680000000000000
LINE_6
0.12345678901234568000
LINE_7
1234567890.1234567000
LINE_8
12345678901234567000.0000000000
LINE_9
1.2345678901235e+019
LINE_10
1.2345678901235e-021
LINE_11
1.#INF

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

Last update: 2015.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

Datatypes and Data Literals

 Introduction of Datatype

 Data Binary Representations

 Data Literals

 Data Literal Evaluation

 Character String Literal Evaluation Examples

 Hex String Literal Evaluation Examples

Numeric Literal Evaluation Examples

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 Outdated Tutorials

 References

 PDF Printing Version