|
ALTER TABLE changes
a table to the current character set. If you during
ALTER TABLE get a
duplicate key error, then the cause is either that the new character sets maps
to keys to the same value or that the table is corrupted, in which case you
should run REPAIR TABLE
on the table.
If ALTER TABLE dies
with an error like this:
Error on rename of
'./database/name.frm' to './database/B-a.frm' (Errcode:
17)
the problem may be that MySQL has crashed in a previous
ALTER TABLE and there is
an old table named
´A-something' or
´B-something' lying
around. 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
the following way:
- Create a new table named
´A-xxx' with the
requested changes.
- All rows from the old table are copied to
´A-xxx'.
- The old table is renamed
´B-xxx'.
- ´A-xxx' is
renamed to your old table name.
- ´B-xxx' is
deleted.
If something goes wrong with the renaming operation,
MySQL tries to undo the changes. If something goes seriously wrong (this
shouldn't happen, of course), MySQL may leave the old table as
´B-xxx', but a
simple rename on the system level should get your data back.
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. For example:
SELECT col_name1, col_name2,
col_name3 FROM tbl_name;
will return columns in the order
col_name1,
col_name2,
col_name3, whereas:
SELECT col_name1, col_name3,
col_name2 FROM tbl_name;
will return columns in the order
col_name1,
col_name3,
col_name2.
You should NEVER, in an application, use
SELECT * and retrieve
the columns based on their position, because the order in which columns are
returned CANNOT be guaranteed over time. A simple change to your database
may cause your application to fail rather dramatically.
If you want to change the order of columns anyway, you can do it as
follows:
- Create a new table with the columns in the right order.
- Execute INSERT INTO new_table
SELECT fields-in-new_table-order FROM old_table.
- Drop or rename
old_table.
- ALTER TABLE new_table RENAME
old_table.
The following are a list of the limitations with
TEMPORARY TABLES.
- A temporary table can only be of type
HEAP,
ISAM or
MyISAM.
- You can't use temporary tables more than once in the same query. For
example, the following doesn't work.
- select * from temporary_table,
temporary_table as
t2;
We
plan to fix the above in 4.0.
- You can't use RENAME
on a TEMPORARY table.
Note that ALTER TABLE org_name
RENAME new_name works! We plan to fix the above in 4.0.
|
