MySQL - CHECK 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.

MySQL CHECK TABLE Statement

The MySQL CHECK TABLE Statement is used to check the integrity of database tables, if there’re are any errors in the specified table this statement lists them out.

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 verifies the status of the above table −

CHECK TABLE sales;

Output

Following is the output of the above query −

Table	Op	Msg_type	Msg_text
mydb.sales	check	status	OK

Verifying multiple tables

You can also verify the status of multiple tables at once using the CHECK 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 verifies all these tables and displays the result −

CHECK TABLE Test1, Test2, Test3;

Output

The above query generates the following output −

Table	Op	Msg_type	Msg_text
mydb.test1	check	status	OK
mydb.test2	check	status	OK
mydb.test3	check	status	OK

The CHECK TABLE Options

The CHECK TABLE statement provides various optional clauses −

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

CHECK TABLE sales QUICK;

Output

The above mysql query will produce the output shown below −

Table	Op	Msg_type	Msg_text
mydb.sales	check	status	OK

If you specify the FAST clause in the CHECK TABLE statement, it checks the tables that are closed properly −

CHECK TABLE sales FAST;

Output

Following is the output of the above mysql query −

Table	Op	Msg_type	Msg_text
mydb.sales	check	status	Table is already up to date

If you specify the CHANGED clause in the CHECK TABLE statement, it just checks the that have been modified −

CHECK TABLE sales CHANGED;

Output

Following is the output of the above query −

Table	Op	Msg_type	Msg_text
mydb.sales	check	status	Table is already up to date

If you specify the MEDIUM clause in the CHECK TABLE statement, it verifies the links, calculates the key checksum for the rows −

CHECK TABLE sales MEDIUM;

Output

The above mysql query generates the following output −

Table	Op	Msg_type	Msg_text
mydb.sales	check	status	OK

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

CHECK TABLE sales EXTENDED;

Output

After executing the above query, it will produce the following output −

Table	Op	Msg_type	Msg_text
mydb.sales	check	status	OK