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
      Truncate Tables

      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.

      Truncate Table

      MySQL TRUNCATE TABLE Statement

      The MySQL TRUNCATE TABLE statement is used to delete only the data of an existing table, but not the table.
      This command helps to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and hefty process.
      You can delete a table using the DROP TABLE command, but be careful because it completely erases both data and the table's structure from the database. If you want to store some data again, you would need to re-create this table once again.

      Syntax

      Following is the basic syntax of the TRUNCATE TABLE statement 
      TRUNCATE TABLE table_name
      Where, table_name is the name of the table you need to delete all the records from.

      Example

      First of all, let us create a table with name CUSTOMERS using the following query 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      Now, we are inserting 7 records into the above-created table using the following INSERT statement 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 ),
      (3, 'Kaushik', 23, 'Kota', 2000.00 ),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
      (7, 'Muffy', 24, 'Indore', 10000.00 );
      Using the following query, we are displaying the records of CUSTOMERS table 
      SELECT * FROM CUSTOMERS;
      Following are the records of CUSTOMERS table
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00
      In the following query, we are using the TRUNCATE TABLE command to remove all the records in the CUSTOMERS table 
      TRUNCATE TABLE CUSTOMERS;

      Output

      The records have been truncated from the CUSTOMERS table without any error.
      Query OK, 0 rows affected (0.02 sec)

      Verification

      To verify whether the records have been truncated, let us retrieve the records using the following query 
      SELECT * FROM CUSTOMERS;
      As we can see the output below, there are no records present in the CUSTOMERS table. Thus, the records have been truncated.
      Empty set (0.00 sec)

      TRUNCATE vs DELETE

      Following are some major differences between the TRUNCATE and DELETE commands, even though they work similar logically:
      DELETE
      TRUNCATE
      The DELETE command in SQL removes one or more rows from a table based on the conditions specified in a WHERE Clause.
      The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met.
      It is a DML(Data Manipulation Language) command.
      It is a DDL(Data Definition Language) command.
      There is a need to make a manual COMMIT after making changes to the DELETE command, for the modifications to be committed.
      When you use the TRUNCATE command, the modifications made to the table are committed automatically.
      It deletes rows one at a time and applies some criteria to each deletion.
      It removes all of the information in one go.
      The WHERE clause serves as the condition in this case.
      There is no necessity of using a WHERE Clause.
      All rows are locked after deletion.
      TRUNCATE utilizes a table lock, which locks the pages so they cannot be deleted.
      It makes a record of each and every transaction in the log file.
      The only activity recorded is the deallocation of the pages on which the data is stored.
      It consumes a greater amount of transaction space compared to TRUNCATE command.
      It takes comparatively less amount of transaction space.
      If there is an identity column, the table identity is not reset to the value it had when the table was created.
      It returns the table identity to a value it was given as a seed.
      It requires authorization to delete.
      It requires table alter permission.
      When it comes to large databases, it is much slower.
      It is faster.

      TRUNCATE vs DROP

      The TRUNCATE and DROP are two different commands. TRUNCATE just deletes the table's records, whereas DROP command deletes the table entirely from the database.
      However, there are still some differences between these commands, which are summarized in the following table
      DROP
      TRUNCATE
      The DROP command in SQL removes an entire table from a database including its definition, indexes, constraints, data etc.
      The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met and resets the table definition.
      It is a DDL(Data Definition Language) command.
      It is also a DDL(Data Definition Language) command.
      The table space is completely freed from the memory.
      The table still exists in the memory.
      All the integrity constraints are removed.
      The integrity constraints still exist in the table.
      Requires ALTER and CONTROL permissions on the table schema and table respectively, to be able to perform this command.
      Only requires the ALTER permissions to truncate the table.
      DROP command is much slower than TRUNCATE but faster than DELETE.
      It is faster than both DROP and DELETE commands.

      Truncating Table Using a Client Program

      Besides truncating a table in a MySQL database with a MySQL query, we can also use a client program to perform the TRUNCATE TABLE operation.

      Syntax

      Following are the syntaxes to truncate a table from MySQL Database in various programming languages −
      PHPNodeJSJavaPython
      To truncate a table from MySQL database through a PHP program, we need to execute the Truncate Table statement using the mysqli function query() as 
      $sql = "TRUNCATE TABLE table_name";
      $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.');
      
      $sql = " TRUNCATE TABLE clone_table ";
      if ($mysqli->query($sql)) {
          printf("table truncated successfully.");
      }
      if ($mysqli->errno) {
          printf("table could not be truncated: %s", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      table truncated successfully.