ALTER TABLE changes a table to the current
character set. If you get a duplicate-key error during
ALTER TABLE, the cause is either that the new
character sets maps two keys to the same value or that the table
is corrupted. In the latter case, you should run REPAIR
TABLE on the table.
If ALTER TABLE dies with the following error,
the problem may be that MySQL crashed during an earlier
ALTER TABLE operation and there is an old
table named
A-xxx or
B-xxx lying
around:
Error on rename of './database/name.frm'
to './database/B-xxx.frm' (Errcode: 17)
In this case, go to the MySQL data directory and delete all
files that have names starting with A- or
B-. (You may want to move them elsewhere
instead of deleting them.)
ALTER TABLE works in the following way:
-
Create a new table named
A-xxxwith the requested structural changes. -
Copy all rows from the original table to
A-xxx. -
Rename the original table to
B-xxx. -
Rename
A-xxxto your original table name. -
Delete
B-xxx.
If something goes wrong with the renaming operation, MySQL tries
to undo the changes. If something goes seriously wrong (although
this shouldn't happen), MySQL may leave the old table as
B-xxx. A simple
rename of the table files at the system level should get your
data back.
If you use ALTER TABLE on a transactional
table or if you are using Windows or OS/2, ALTER
TABLE unlocks the table if you had done a
LOCK TABLE on it. This is done because
InnoDB and these operating systems cannot
drop a table that is in use.
First, consider whether you really need to change the column
order in a table. The whole point of SQL is to abstract the
application from the data storage format. You should always
specify the order in which you wish to retrieve your data. The
first of the following statements returns columns in the order
col_name1,
col_name2,
col_name3, whereas the second returns
them in the order col_name1,
col_name3,
col_name2:
mysql>SELECTcol_name1,col_name2,col_name3FROMtbl_name; mysql>SELECTcol_name1,col_name3,col_name2FROMtbl_name;
If you decide to change the order of table columns anyway, you can do so as follows:
-
Create a new table with the columns in the new order.
-
Execute this statement:
mysql>
INSERT INTO new_table->SELECT columns-in-new-order FROM old_table; -
Drop or rename
old_table. -
Rename the new table to the original name:
mysql>
ALTER TABLE new_table RENAME old_table;
SELECT * is quite suitable for testing
queries. However, in an application, you should
never rely on using SELECT
* and retrieving the columns based on their position.
The order and position in which columns are returned does not
remain the same if you add, move, or delete columns. A simple
change to your table structure could cause your application to
fail.
The following list indicates limitations on the use of
TEMPORARY tables:
-
A
TEMPORARYtable can only be of typeHEAP,ISAM,MyISAM,MERGE, orInnoDB. -
You cannot refer to a
TEMPORARYtable more than once in the same query. For example, the following does not work:mysql>
SELECT * FROM temp_table, temp_table AS t2;ERROR 1137: Can't reopen table: 'temp_table' -
The
SHOW TABLESstatement does not listTEMPORARYtables. -
You cannot use
RENAMEto rename aTEMPORARYtable. However, you can useALTER TABLEinstead:mysql>
ALTER TABLE orig_name RENAME new_name; -
There are known issues in using temporary tables with replication. See Section 6.7, “Replication Features and Known Problems”, for more information.