Data Binary Representations

MySQL Tutorials - Herong's Tutorial Examples

∟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.