Khóa học miễn phí MySQL – BIT nhận dự án làm có lương

MySQL – BIT

Table of content


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)
    

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc