MySQL CHECK TABLE Statement

Summary: in this tutorial, you will learn how to use the MySQL CHECK TABLE statement to check one or more tables or views for errors.

Introduction to MySQL CHECK TABLE statement

The CHECK TABLE statement allows you to check one or more tables for errors.

The following shows the syntax of the CHECK TABLE statement:

CHECK TABLE tbl_name, [,table_name]...
[option]Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify one or more names of the tables you want to check for errors.

Second, specify one or more options that can be:

• FOR UPGRADE
• QUICK
• FAST
• MEDIUM
• EXTENDED
• CHANGED

The CHECK TABLE statement works for InnoDB, MyISAM, ARCHIVE, and CSV tables.

The storage engine may accept or ignore the option of the CHECK TABLE statement.

The CHECK TABLE statement also checks views for problems. For example, it can check if a view references tables that do not exist.

The CHECK TABLE returns a result set that includes four columns:

The CHECK TABLE statement might generate multiple rows of information for each checked table or view. The last row should have a Msg_type value of status and the Msg_text should typically be OK

MySQL CHECK TABLE statement examples

Let’s take some examples of using the CHECK TABLE statement.

1) Using the CHECK TABLE to check for errors in tables

First, log in to the MySQL database server using the root account:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

It’ll prompt you to enter the password. Enter the valid password to log in:

Enter password: ********Code language: SQL (Structured Query Language) (sql)

Second, switch the current database to classicmodels sample database:

use classicmodels;Code language: SQL (Structured Query Language) (sql)

Third, check the tables customers and products for errors:

CHECK TABLE customers, products;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| classicmodels.customers | check | status   | OK       |
| classicmodels.products  | check | status   | OK       |
+-------------------------+-------+----------+----------+
2 rows in set (0.03 sec)Code language: SQL (Structured Query Language) (sql)

The output shows that both tables have no errors.

2) Using the MySQL CHECK TABLE to check for errors in a view

First, create a database called test:

CREATE DATABASE IF NOT EXISTS test;Code language: SQL (Structured Query Language) (sql)

Second, switch to the test database:

USE test;Code language: PHP (php)

Third, create a table called employees in the test database:

CREATE TABLE employees(
  id INT AUTO_INCREMENT PRIMARY KEY, 
  first_name VARCHAR(255) NOT NULL, 
  last_name VARCHAR(255) NOT NULL, 
  email VARCHAR(255) NOT NULL, 
  phone VARCHAR(255) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Fourth, create a view called contacts based on the employees table:

CREATE VIEW contacts AS 
SELECT 
  concat_ws(' ', first_name, last_name) as name, 
  email, 
  phone 
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

Fifth, drop the table employees:

DROP TABLE employees;Code language: SQL (Structured Query Language) (sql)

Finally, check the view contacts for the error:

CHECK TABLE contacts\G;Code language: SQL (Structured Query Language) (sql)

Output:

*************************** 1. row ***************************
   Table: test.contacts
      Op: check
Msg_type: Error
Msg_text: View 'test.contacts' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 2. row ***************************
   Table: test.contacts
      Op: check
Msg_type: error
Msg_text: Corrupt
2 rows in set (0.00 sec)

ERROR:
No query specifiedCode language: SQL (Structured Query Language) (sql)

Note that the \G instructs mysql to display the result in a more readable, vertical format rather than a traditional horizontal, table-based layout.

The first row indicates that the view references an invalid table and the second row specifies that the view is corrupt.

Summary

• Use the MySQL CHECK TABLE statement to check one or more tables or views for errors.