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.
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
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 的表 −
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)
);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 );Assume the above created table is corrupted and we are using the REPAIR TABLE statement to repair it.
REPAIR TABLE CUSTOMERS;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.
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 表，执行以下查询 -
REPAIR TABLE CUSTOMERS;Repairing multiple tables
在 MySQL 中，我们还可以修复多张表，并使用 REPAIR TABLE 语句获取结果。为此，我们只需要列出我们想要修复的表的名称，并用逗号分隔它们。
Example
让我们使用以下 CREATE TABLE 语句创建三个具有名称 Test1 、 Test2 和 Test3 的表 -
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 语句修复它们 -
ALTER TABLE Test1 ENGINE = MyISAM;
ALTER TABLE Test2 ENGINE = MyISAM;
ALTER TABLE Test3 ENGINE = MyISAM;现在，要修复这些表，执行以下查询 -
REPAIR TABLE Test1, Test2, Test3;如我们从下面的输出中看到，所有三张表均处于良好状态，并且没有问题或损坏。
Repair Table Options
我们有各种可选子句可与 REPAIR TABLE 一起使用，例如 QUICK 、 EXTENDED 和 USE_FRM 子句。让我们通过适当的示例一一讨论它们。
QUICK Clause
QUICK 子句是默认子句，并且最常与 REPAIR TABLE 一起使用。如果你指定 QUICK 子句，MySQL 将修复表格而不会重新创建它。 -
在以下示例中，我们对 REPAIR TABLE 语句使用 QUICK 子句来修复 CUSTOMERS 表。
REPAIR TABLE CUSTOMERS QUICK;执行上述查询将产生以下输出：