Data Binary Representations

Describes how data of each datatype is represented in binary forms. SQL supports multiple binary representations of the same datatype using different storage sizes.

Now we know what types of data SQL must work with. The next step is to understand how different types of data are represented in binary forms. Since computers can only work with binary digits, we have to represent all data in computer memory in binary forms.

1. Character String - A character string is usually represented in memory as an array of characters. Each character is represented in 8 bits (one byte) or 16 bits (two bytes) based on the character set and the character encoding schema. For example, with ASCII character set and its encoding schema, character "A" will be represented as "01000001". Character "1" will be represented as "00110001". Character string "ABC" will be represented as "010000010100001001000011".

2. Bit String - The binary representation of a bit string should be easy. A bit string should be represented in memory as it is. Bit string "01000001" should be represented as "01000001". There might an issue with memory allocation, because computer allocates memory in units of bytes (8 bits per byte). If the length of a bit string is not multiples of 8 bits, the last allocated byte is not full. How to handle the empty space in the last byte? I guess different SQL implementation will have different rules.

3. Exact Number - Exact numbers can be divided into two groups: integers and non-integers. An integer is an exact number with scale of 0. An integer is represented in either 4 bytes or 8 bytes based on the signed binary value system. For example, with 4 bytes, integer "1" will be represented as "00000000000000000000000000000001". Integer "-1" will be represented as "1111111111111111111111111111111".

As for exact non-integer numbers, I don't know exactly how they will be represented in binary forms.

4. Approximate Number - An approximate number is normally represented in binary form according to the IEEE 754 single-precision or double-precision standards in either 4 bytes or 8 bytes. The binary representation is divided into 3 components with different number of bits assigned to each components:

                   Sign   Exponent   Fraction   Total
Single-Precision      1          8         23      32
Double-Precision      1         11         52      64

With the double precision standard, the mantissa precision can go up to 52 binary digits, about 15 decimal digits. For more details, see section "Binary Representation of 'float' and 'double' Values" of my other book "Herong's Notes on C#".

5. Data and Time - A date and time value is usually stored in memory as an exact integer number with 8 bytes representing an instance by measuring the time period between this instance and a reference time point in millisecond precision, second fraction precision of 3. How MySQL is store date and time values? We will try to find out later.

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


 Full Version in PDF/EPUB