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

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

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:

• Binary Literal: Enclose the 7-bit code in single quotes and prefix it with b. For example, b'1010111' represents the binary code 1010111.
• 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.

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 0: Monday
• Bit 1: Tuesday
• Bit 2: Wednesday
• Bit 3: Thursday
• Bit 4: Friday
• Bit 5: Saturday
• Bit 6: Sunday

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.

By using BIT(7) you can efficiently store information like this in a compact way.

Creating a Table

This code creates a table named data with two columns:

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

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     |

In the output, you see the first row stores the binary code b'1010011', which is how it was inserted. The second row stores the decimal value (83), but MariaDB automatically converts it back to the corresponding binary code when retrieved.

Using TINYINT

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
);

• Inserting Binary Codes:

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);

• Retrieving Binary Codes:

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.

• Table Creation:

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

• Inserting Binary Codes:

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'));

• Retrieving Binary Codes:

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 plan to perform logical operations or bitwise operations on the binary code directly in the database, using BIT(7) is the most efficient method.
• 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 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.