MySQL – BIT
”;
A bit represents the basic unit of data in programming languages. It can store only two values, represented as 0 or 1.
The MySQL BIT Data Type
The MySQL BIT data type is used to store binary values within a specified range. The range is determined by the number of bits you allocate to the BIT column.
If we try to insert an integer value instead of BIT values, MySQL automatically converts them into BIT values. We have to ensure that the integer value we are adding must be within the range for conversion to BIT values.
For instance, if you have a BIT(3) column, it can store values from 000 to 111 in binary, which corresponds to 0 to 7 in integer format. If you try to insert the integer 8 into this BIT(3) column, you”ll get an error because 8 in binary is 1000, which is outside the valid range of the column.
Syntax
Following is the syntax of the MySQL BIT datatype −
BIT(n)
Here, the range of n value is from 1 to 64. If you don”t provide the “n” value, the default is 1, resulting in a single-bit BIT column. Hence, the following queries will give the same output −
Column_name BIT(1); and Column_name BIT;
Bit Value Literal
-
To specify bit value literals, you can use the b”val or 0bval notations, where val is a binary value containing only 0s and 1s. The leading ”b” is case-insensitive.
b01 B11
Note that the 0b notation is case-sensitive, so 0B”1000” is an invalid bit literal value.
0B''1000''
Example
Let us create a table named STUDENTS and use the BIT data type for the AGE column as shown below −
CREATE TABLE STUDENTS( ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) );
Following is the output obtained −
Query OK, 0 rows affected (0.01 sec)
Now, we are inserting the values “5” and “3” into the AGE column of the STUDENTS table −
INSERT INTO STUDENTS (NAME, AGE) VALUES (''Varun'', 5), (''Akash'', 3);
Output of the above query is as shown below −
Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
We can use the following query to display the inserted values in the AGE column of the STUDENTS table −
SELECT * from STUDENTS;
We can see in the output below that the values “5” and “3” are stored in binary format −
ID | NAME | AGE |
---|---|---|
1 | Varun | 0x05 |
2 | Akash | 0x03 |
Now, let us insert another value “10”. In binary format, “10” is represented as “1010”. However, we defined the AGE column to have a range of only three bits. Therefore, the following query will generate an error because the value 10 is greater than 7 −
INSERT INTO STUDENTS (NAME, AGE) VALUES (''Priya'', 10);
The output indicates that the data is too long for the AGE column.
ERROR 1406 (22001): Data too long for column ''AGE'' at row 1
To insert bit value literals into the “AGE” column, you can use the B”val notation. Here, we are inserting “110” which is equivalent to the integer value “6” as shown below −
INSERT INTO STUDENTS (NAME, AGE) VALUES(''Priya'', B''110'');
The result obtained is as follows −
Query OK, 1 row affected (0.01 sec)
Let us display all the records in the “STUDENTS” table using the following query −
SELECT * from STUDENTS;
We can see in the output below that the value “6” has been inserted in binary format as “0x06” −
ID | NAME | AGE |
---|---|---|
1 | Varun | 0x05 |
2 | Akash | 0x03 |
3 | Priya | 0x06 |
Verification
To verify and display the inserted values in the “AGE” column in binary/bit format, you can use the MySQL BIN() function −
SELECT ID, NAME, BIN(AGE) FROM STUDENTS;
The output shows the values in binary format −
ID | NAME | BIN(AGE) |
---|---|---|
1 | Varun | 101 |
2 | Akash | 11 |
3 | NULL | 110 |
In the above output, we can see that the leading zeros are removed. If we want to display them, we can use the LPAD function as shown below −
SELECT ID, NAME, LPAD(BIN(AGE), 5, "0") FROM STUDENTS;
Following is the output obtained −
ID | NAME | LPAD(BIN(AGE), 5, “0”) |
---|---|---|
1 | Varun | 00101 |
2 | Akash | 00011 |
3 | NULL | 00110 |
BIT Datatype Using a Client Program
We can also create column of the BIT datatype using the client program.
Syntax
To create a column of BIT datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −
$sql = ''CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )''; $mysqli->query($sql);
To create a column of BIT datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −
sql = "CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )"; con.query(sql);
To create a column of BIT datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −
String sql = "CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID))"; statement.execute(sql);
To create a column of BIT datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −
sql = ''CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )'' cursorObj.execute(sql)
Example
Following are the programs −
$dbhost = ''localhost''; $dbuser = ''root''; $dbpass = ''password''; $dbname = ''TUTORIALS''; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } //printf(''Connected successfully.
''); //creating a table student where age is bit data types $sql = ''CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )''; $result = $mysqli->query($sql); if ($result) { printf("Table created successfully...!n"); } // insert data into created table $q = " INSERT INTO students (NAME, AGE) VALUES (''Varun'', 5), (''Akash'', 3)"; if ($res = $mysqli->query($q)) { printf("Data inserted successfully...!n"); } //now display the table records $s = "SELECT * FROM students"; if ($r = $mysqli->query($s)) { printf("Table Records: n"); while ($row = $r->fetch_assoc()) { printf(" ID: %d, Age: %d", $row["ID"], $row["AGE"]); printf("n"); } } else { printf(''Failed''); } $mysqli->close();
Output
The output obtained is as follows −
Table created successfully...! Data inserted successfully...! Table Records: ID: 1, Age: 5 ID: 2, Age: 3
var mysql = require("mysql2"); var con = mysql.createConnection({ host: "localhost", user: "root", password: "password", }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; // console.log("Connected successfully...!"); // console.log("--------------------------"); sql = "USE TUTORIALS"; con.query(sql); //create a students table, that accepts one column of bit type. sql = "CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )"; con.query(sql); //insert data into created table sql = "INSERT INTO students (NAME, AGE) VALUES (''Varun'', 5), (''Akash'', 3)"; con.query(sql); //select datatypes of salary sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''students'' AND COLUMN_NAME = ''AGE''`; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); });
Output
The output produced is as follows −
[ { DATA_TYPE: ''bit'' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Bit { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Bit data types...!; String sql = "CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID))"; statement.execute(sql); System.out.println("column of a BIT type created successfully...!"); ResultSet resultSet = statement.executeQuery("DESCRIBE students"); while (resultSet.next()){ System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)); } connection.close(); } catch (Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! column of a BIT type created successfully...! ID int NAME varchar(40) AGE bit(3)
import mysql.connector # Establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) # Creating a cursor object cursorObj = connection.cursor() # Create table with BIT column sql = '''''' CREATE TABLE students( ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) ) '''''' cursorObj.execute(sql) print("The table is created successfully!") # Data to be inserted data_to_insert = [ (''Varun'', 5), (''Akash'', 3) ] # Insert data into the created table insert_query = "INSERT INTO students (NAME, AGE) VALUES (%s, %s)" cursorObj.executemany(insert_query, data_to_insert) # Commit the changes after the insert operation connection.commit() print("Rows inserted successfully.") # Now display the table records select_query = "SELECT * FROM students" cursorObj.execute(select_query) result = cursorObj.fetchall() print("Table Data:") for row in result: print(row) cursorObj.close() connection.close()
Output
Following is the output of the above code −
The table is created successfully! Rows inserted successfully. Table Data: (1, ''Varun'', 5) (2, ''Akash'', 3)
”;