Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      BIT

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

       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.
      • b01B11
      • 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

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

      Example

      Following are the programs −
      PHPNodeJSJavaPython
      $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