Mysql 简明教程
MySQL - Repair Tables
MySQL Repair Table Statement
在数据库中，表可能会因硬件故障、软件错误或意外服务器崩溃等各种原因而损坏。当这种情况发生时，我们无法访问或处理这些表中的数据，因为数据不一致或有错误。
There can be scenarios where tables in databases can become corrupted due to various reasons such as hardware failures, software bugs, or unexpected server crashes. When this situation happens, we cannot be able to access or manipulate the data in those tables because of data inconsistencies or errors.
在这样的情况下，为了修复损坏的表，我们使用 MySQL REPAIR TABLE 语句。此语句仅适用于 MyISAM 等特定引擎。
In such situations, to repair those currupted tables, we use the MySQL REPAIR TABLE statement. This statement works for only certain engines such as MyISAM, etc.
Syntax
以下是 MySQL REPAIR TABLE 语句的语法 −
Following is the syntax of MySQL REPAIR TABLE Statement −
REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM]Example
让我们首先使用以下查询创建一个名为 CUSTOMERS 的表 −
Let us start by creating a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);在此，我们使用以下 INSERT 语句将 7 条记录插入到上面创建的表中 −
Here, we are inserting 7 records into the above created table using the below 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 );假设上面创建的表已损坏，我们使用 REPAIR TABLE 语句来修复它。
Assume the above created table is corrupted and we are using the REPAIR TABLE statement to repair it.
REPAIR TABLE CUSTOMERS;以上查询显示错误为： "The storage engine for the table doesn’t support repair" 因为 REPAIR TABLE 语句不适用于默认 InnoDB 引擎。
The above query displays an error as: "The storage engine for the table doesn’t support repair" because the REPAIR TABLE statement won’t work with the default InnoDB engine.
要修复此表，我们需要将表的引擎更改为 MyISAM ，因为它支持 REPAIR TABLE 语句。
To repair the table, We need to change the table’s engine to MyISAM because it supports the REPAIR TABLE statement.
ALTER TABLE CUSTOMERS ENGINE = MyISAM;现在，要修复 CUSTOMERS 表，执行以下查询 -
Now, to repair the CUSTOMERS table, execute the following query −
REPAIR TABLE CUSTOMERS;Repairing multiple tables
在 MySQL 中，我们还可以修复多张表，并使用 REPAIR TABLE 语句获取结果。为此，我们只需要列出我们想要修复的表的名称，并用逗号分隔它们。
In MySQL, we can also repair multiple tables and get the results using the REPAIR TABLE Statement. To do this, we just need to list the names of the tables we want to repair, separating them with commas.
Example
让我们使用以下 CREATE TABLE 语句创建三个具有名称 Test1 、 Test2 和 Test3 的表 -
Let us create three different tables with the names Test1, Test2, and Test3 using the following CREATE TABLE statements −
CREATE TABLE Test1(ID INT, Name VARCHAR(255));
CREATE TABLE Test2(ID INT, Name VARCHAR(255));
CREATE TABLE Test3(ID INT, Name VARCHAR(255));假设上述三张表已损坏。将这些表的引擎更改为 MyISAM ，以使用 REPAIR TABLE 语句修复它们 -
Assume the above three tables are corrupted. Change the engine of these tables to MyISAM to repair them with REPAIR TABLE statement −
ALTER TABLE Test1 ENGINE = MyISAM;
ALTER TABLE Test2 ENGINE = MyISAM;
ALTER TABLE Test3 ENGINE = MyISAM;现在，要修复这些表，执行以下查询 -
Now, to repair these tables, execute the following query −
REPAIR TABLE Test1, Test2, Test3;如我们从下面的输出中看到，所有三张表均处于良好状态，并且没有问题或损坏。
As we can see in the output below, all three tables are in good condition, and there are no issues or corruption.
Repair Table Options
我们有各种可选子句可与 REPAIR TABLE 一起使用，例如 QUICK 、 EXTENDED 和 USE_FRM 子句。让我们通过适当的示例一一讨论它们。
We have various optional clauses to use with REPAIR TABLE such as QUICK, EXTENDED, and, USE_FRM clause. Let us discuss them one by one with suitable examples.
QUICK Clause
QUICK 子句是默认子句，并且最常与 REPAIR TABLE 一起使用。如果你指定 QUICK 子句，MySQL 将修复表格而不会重新创建它。 -
The QUICK clause is the is the default and it is most commonly used with REPAIR TABLE. If you specify the QUICK clause, MySQL will repair the table without re-creating it. −
在以下示例中，我们对 REPAIR TABLE 语句使用 QUICK 子句来修复 CUSTOMERS 表。
In the following example, we are using the QUICK clause with the REPAIR TABLE statement to repair the CUSTOMERS table.
REPAIR TABLE CUSTOMERS QUICK;执行上述查询将产生以下输出：
Executing the query above will produce the following output −
EXTENDED Clause
如果我们指定 EXTENDED clause ，MySQL 不仅修复表，还重建索引并优化表结构。
If we specify the EXTENDED clause, MySQL not only repairs the table but also rebuilds the index and optimizes the table structure.
Note: 与 QUICK 子句相比，EXTENDED 子句更耗时。
Note: The EXTENDED clause is a more time-consuming compared to QUICK clause.
在以下示例中，我们对 REPAIR TABLE 语句使用 EXTENDED 子句来修复 CUSTOMERS 表。
In the following example, we are using the EXTENDED clause with the REPAIR TABLE statement to repair the CUSTOMERS table.
REPAIR TABLE CUSTOMERS EXTENDED;执行上述查询将产生以下输出：
Executing the query above will produce the following output −
USE_FRM clause
如果 MYI 索引文件丢失，我们可以使用 USE_FRM clause 。如果你提供此子句，将使用数据字典中的信息重新创建 .NYI 文件 -
We can use the USE_FRM clause, in case the MYI index file is missing. If you provide this clause the .NYI file will be recreated using information from the data dictionary −
此处，我们对 REPAIR TABLE 语句使用 USE_FRM 子句来修复 CUSTOMERS 表。
Here, we are using the USE_FRM clause with the REPAIR TABLE statement to repair the CUSTOMERS table.
REPAIR TABLE CUSTOMERS USE_FRM;执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Repairing table Using a Client Program
除了使用 MySQL 查询修复 MySQL 数据库中的表外，我们还可以使用客户端程序执行 REPAIR TABLE 操作。
Besides repairing a table in a MySQL database with a MySQL query, we can also use a client program to perform the REPAIR TABLE operation.