MySQL Tutorials - Herong's Tutorial Examples
∟Datatypes and Data Literals
∟Data Literal Evaluation
Describes how data literals will be evaluated into values of particular datatypes based on the expression context.
Data literal evaluation is the process converting a data literal to a particular
value of a particular data type. This data type must match the type of
the expression where the literal is located. Expressions will be reviewed in the next
chapter. Now let's look at some of the data literal evaluation rules for each type
of data literals.
1. Character String Literals can be evaluated character strings, exact
numbers, approximate numbers and data and time values. The evaluation rules are:
- A character string literal will be evaluated to a character string in
a string expression. The evaluation is straight forward.
- A character string literal will be evaluated to an exact number or
an approximate number in a numeric expression, if the character string literal
is also a valid numeric literal without the quoting characters. Otherwise,
it will be evaluated to 0. For example, '123' will be evaluated to 123 in numeric
contexts. But 'ABC' will be evaluated to 0.
- A character string literal will be evaluated to a date and time value
in a date and time expression, if the character string can be matched to a
date and time pattern like: "yyyy-mm-dd hh:mm:ss.nnn".
2. Hex String Literals can be evaluated to character strings and exact
numbers. The evaluation rules are:
- A hex string literal will be evaluated to a character string in
a string expression. The hex string literal will be used as an array
of binary representation of encoded characters, and decoded back to
a character string. For example, x'41424344' will be evaluated to character
string "ABCD".
- Before MySQL 5.0 - A hex string literal will be evaluated to an integer in a numeric expression.
The hex string literal will be used to match the binary representation of
8-byte signed integer format. The literal will be evaluated to the matching integer.
For example, x'ffffffffffffffff' will be evaluated to integer -1.
- Since MySQL 5.0 - A hex string literal will be evaluated to an integer in a numeric expression.
The hex string literal will be used to match the binary representation of
an integer with much longer format size.
The literal will be evaluated to the matching integer.
And if the resulting integer is too big, it will be converted to approximate number.
For example, x'ffffffffffffffff' will be evaluated to integer 18446744073709552000 on MySQL 5.0,
because the matching integer is 18446744073709551616, which is too big and approximated to
18446744073709552000.
This behavior is really bad.
3. Numeric Literals can be evaluated to exact numbers and approximate
numbers. The evaluation rules are:
- A numeric literal will be evaluated to an exact number or an approximate
number in a numeric expression. The evaluation is straight forward.
Table of Contents
About This Book
Introduction of SQL
MySQL Introduction and Installation
Introduction of MySQL Programs
PHP Programs and MySQL Server
Perl 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
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Locks Used in MySQL
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
MySQL Server Administration
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Bulk Changes on Large Tables
MySQL Server on macOS
Installing MySQL Server on Linux
Connection, Performance and Second Instance on Linux
Archived Tutorials
References
Full Version in PDF/EPUB