![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_1646196177.webp)
Rebuilding or Repairing Tables or Indexes
157
To rebuild a table by dumping and reloading it, use
mysqldump
to create a dump file and
mysql
to
reload the file:
shell>
mysqldump db_name t1 > dump.sql
shell>
mysql db_name < dump.sql
To rebuild all the tables in a single database, specify the database name without any following table
name:
shell>
mysqldump db_name > dump.sql
shell>
mysql db_name < dump.sql
To rebuild all tables in all databases, use the
--all-databases
[298]
option:
shell>
mysqldump --all-databases > dump.sql
shell>
mysql < dump.sql
To rebuild a table with
ALTER TABLE
, use a “null” alteration; that is, an
ALTER TABLE
statement that
“changes” the table to use the storage engine that it already has. For example, if
t1
is a
MyISAM
table,
use this statement:
mysql>
ALTER TABLE t1 ENGINE = MyISAM;
If you are not sure which storage engine to specify in the
ALTER TABLE
statement, use
SHOW CREATE
TABLE
to display the table definition.
If you must rebuild a table because a table checking operation indicates that the table is corrupt or
needs an upgrade, you can use
REPAIR TABLE
if that statement supports the table's storage engine.
For example, to repair a
MyISAM
table, use this statement:
mysql>
REPAIR TABLE t1;
For storage engines such as
InnoDB
that
REPAIR TABLE
does not support, use
mysqldump
to create
a dump file and
mysql
to reload the file, as described earlier.
For specifics about which storage engines
REPAIR TABLE
supports, see
Section 13.7.2.6, “
REPAIR
TABLE
Syntax”
.
mysqlcheck --repair
provides command-line access to the
REPAIR TABLE
statement. This can
be a more convenient means of repairing tables because you can use the
--databases
[291]
or
--
all-databases
[290]
option to repair all tables in specific databases or all databases, respectively:
shell>
mysqlcheck --repair --databases db_name ...
shell>
mysqlcheck --repair --all-databases
For incompatibilities introduced in MySQL 5.1.24 by the fix for Bug #27877 that corrected the
utf8_general_ci
and
ucs2_general_ci
collations, a workaround is implemented as of MySQL
5.1.62, 5.5.21, and 5.6.5. Upgrade to one of those versions, then convert each affected table using
one of the following methods. In each case, the workaround altering affected columns to use the
utf8_general_mysql500_ci
and
ucs2_general_mysql500_ci
collations, which preserve the
original pre-5.1.24 ordering of
utf8_general_ci
and
ucs2_general_ci
.
• To convert an affected table after a binary upgrade that leaves the table files in place, alter the table
to use the new collation. Suppose that the table
t1
contains one or more problematic
utf8
columns.
To convert the table at the table level, use a statement like this:
ALTER TABLE t1
CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci;
To apply the change on a column-specific basis, use a statement like this (be sure to repeat the
column definition as originally specified except for the
COLLATE
clause):
ALTER TABLE t1
MODIFY c1 CHAR(N) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci;
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...