MySQL - CHECKSUM TABLE Statement

---

---

In case something wrong happens to the database server like if the server was shutdown unexpectedly or, an error occurred while writing data to the hard disk, etc. These situations could make the database operate incorrectly and in the worst case, it can be crashed. Sometimes specific tables will be crushed.

CHECKSUM TABLE Statement

A checksum is a mechanism to detect issues in the table space. The CHECKSUM TABLE Statement is used to report checksum on the specified table. To execute this statement, you need SELECT privilege.

Syntax

Following is the syntax of the MySQL CHECK TABLE Statement −

CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option: {
   FOR UPGRADE
   | QUICK
   | FAST
   | MEDIUM
   | EXTENDED
   | CHANGED
}

Example

Assume we have created a table named sales as shown below −

CREATE TABLE sales(
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255)
);

Now, we will insert 5 records in Sales table using INSERT statements −

Insert into sales values
(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 
'Hyderabad');
(2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 
'Vishakhapatnam');
(3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 
'Vijayawada');
(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 
'Chennai');
(5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 
'Goa');

Following table retrieves the checksum of the above table −

CHECKSUM TABLE Sales;

Output

Following is the output of the above query −

Table	Checksum
mydb.sales	1059835375

Verifying multiple tables

You can also retrieve the ckecksum of multiple tables at once using the CHECKSUM TABLE Statement.

Example

Assume we have created three new tables as shown below −

CREATE TABLE Test1(ID INT, Name VARCHAR(255));
CREATE TABLE Test2(ID INT, Name VARCHAR(255));
CREATE TABLE Test3(ID INT, Name VARCHAR(255));

Following query gets the checksum of all these tables and displays the result −

CHECKSUM TABLE Test1, Test2, Test3;

Output

The above query will generate the Following output −

Table	Checksum
mydb.test1	0
mydb.test2	0
mydb.test3	0

The CHECKSUM TABLE Options

The CHECKSUM TABLE statement provides various optional clauses −

If you specify the QUICK clause in the CHECKSUM TABLE statement, it just checks the incorrect links −

CHECKSUM TABLE sales QUICK;

Output

The above mysql query produces the following output −

Table	Checksum
mydb.sales	NULL

If you specify the EXTENDED clause in the CHECKSUM TABLE statement, it performs a full check for all keys in each row −

CHECKSUM TABLE sales EXTENDED;

Output

Following is the output of the above query −

Table	Checksum
mydb.sales	1059835375