Unlocking the Power of BIT(7) for Compact Binary Storage in MariaDB

2024-07-27

Binary code is a system of representing information using just two digits: 0 and 1. Each digit is called a bit. In this case, you want to store a 7-bit code, which means it can represent 2 to the power of 7 (128) different values.

MariaDB and BIT Data Type

MariaDB is a relational database management system. It allows you to store data in various formats, including a data type called BIT. The BIT data type can hold a single binary digit (0 or 1).

Storing a 7-Bit Code

To store a 7-bit code in MariaDB, you can use the BIT(7) data type when creating a table column. This creates a column that can hold a 7-bit binary value.

For example, the following SQL statement creates a table named data with a column named binary_code that can store a 7-bit binary code:

CREATE TABLE data (
  binary_code BIT(7)
);

Using the 7-Bit Code

Once you have a column defined as BIT(7), you can insert 7-bit binary values directly into the column. You can enter the binary code in two ways:

Decimal Equivalent: Since a 7-bit code can only hold values from 0 to 127, you can also enter the decimal equivalent of the binary code. For instance, the number 47 represents the binary code 00101111.
Binary Literal: Enclose the 7-bit code in single quotes and prefix it with b. For example, b'1010111' represents the binary code 1010111.

Example: Storing Days of Operation

A common use case for storing a 7-bit code is to represent flags. For instance, imagine you have a table to store information about flights. You could create a BIT(7) column named operates_on to represent the days of the week a flight operates. In this case, each bit position would correspond to a specific day:

Bit 6: Sunday
Bit 4: Friday
Bit 3: Thursday
Bit 2: Wednesday

The value stored in the operates_on column would then be a 7-bit binary code where a 1 in a specific bit position indicates the corresponding day the flight operates. For example, the binary code b'1010111' would represent a flight that operates on Wednesday, Friday, Saturday, and Sunday.

This code creates a table named data with two columns:

binary_code (BIT(7)): A column to store a 7-bit binary code.
id (int): An auto-incrementing integer to uniquely identify each row.

CREATE TABLE data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  binary_code BIT(7)
);

Inserting Binary Codes

This code inserts two rows into the data table. The first row inserts the binary code 1010011 directly as a binary literal. The second row inserts the decimal equivalent (83) of the binary code 01010011.

INSERT INTO data (binary_code) VALUES (b'1010011');
INSERT INTO data (binary_code) VALUES (83);

Retrieving Binary Codes

This code retrieves all rows from the data table and displays the contents of the binary_code column.

SELECT id, binary_code FROM data;

Expected Output

The output of the above query will depend on the values you inserted previously. But, for the example inserts above, it would be something like:

| id | binary_code |
|---|---|
| 1  | b'1010011'  |
| 2  | 1010011     |

The TINYINT data type in MariaDB can store integer values from -128 to 127. This range perfectly accommodates a 7-bit binary code since its decimal equivalent also falls within this range.

Here's how you can use TINYINT to store a 7-bit code:

Table Creation:

CREATE TABLE data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  binary_code TINYINT
);

Since TINYINT stores integers, you would insert the decimal equivalent of your binary code. For example, to insert the binary code 1010011 (which is 83 in decimal), you would use:

INSERT INTO data (binary_code) VALUES (83);

The retrieved value from the binary_code column will be the decimal equivalent. You can convert it back to binary code if needed using string functions like LPAD or bitwise operations (available in MariaDB versions >= 10.2).

Using VARCHAR with Conversion

You can store the 7-bit binary code as a string in a VARCHAR(7) column. However, you'll need to perform conversions between the binary representation and the string representation before storing and retrieving data.

CREATE TABLE data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  binary_code VARCHAR(7)
);

Convert your 7-bit binary code (e.g., 1010011) to a string using string functions like LPAD to ensure it's always 7 characters long with leading zeros if needed. Then insert the string into the column.

INSERT INTO data (binary_code) VALUES (LPAD('1010011', 7, '0'));

The retrieved value from the binary_code column will be a string. You can convert it back to a binary code using string manipulation functions.

Choosing the Right Method

If you need more flexibility and want to store the code as a human-readable string, using VARCHAR with appropriate conversion functions is an option. However, this method requires additional processing before storing and retrieving data.
If storage space is a major concern, and you only need to store the code and don't need to perform database-level operations on it, TINYINT is a viable alternative.
If you plan to perform logical operations or bitwise operations on the binary code directly in the database, using BIT(7) is the most efficient method.

mariadb