A.7. 与表定义有关的事宜

A.7. 与表定义有关的事宜

A.7.1. 与ALTER TABLE有关的问题

A.7.2. 如何更改表中的列顺序

A.7.3. TEMPORARY TABLE问题

A.7.1. 与ALTER TABLE有关的问题

ALTER TABLE将表更改为当前字符集。如果在执行ALTER TABLE操作期间遇到重复键错误，原因在于新的字符集将2个键映射到了相同值，或是表已损坏。在后一种情况下，应在表上运行REPAIR TABLE。

如果ALTER TABLE失败并给出下述错误，问题可能是因为在ALTER TABLE操作的早期阶段出现MySQL崩溃，没有名为A-xxx或B-xxx的旧表：

Error on rename of './database/name.frm'

to './database/B-xxx.frm' (Errcode: 17)

在该情况下，进入MySQL数据目录，并删除其名称为以A-或B-开始的所有文件（或许你希望将它们移动到其他地方而不是删除它们）。

ALTER TABLE的工作方式如下：

• 用请求的结构变化创建名为A-xxx的新表。
• 将所有行从原始表拷贝到A-xxx。
• 将原始表重命名为B-xxx。
• 将A-xxx重命名为原始表的名称。
• 删除B-xxx。

如果在重命名操作中出错，MySQL将尝试撤销更改。如果错误很严重（尽管这不应出现），MySQL会将旧表保留为B-xxx。简单地在系统级别上重命名表文件，应能使数据复原。

如果在事务性表上使用ALTER TABLE，或正在使用Windows或OS/2操作系统，如果已在表上执行了LOCK TABLE操作，ALTER TABLE将对表执行解锁操作。这是因为InnoDB和这类操作系统不能撤销正在使用的表。

A.7.2. 如何更改表中的列顺序

首先，请考虑是否的确需要更改表中的列顺序。SQL的核心要点是从数据存储格式获取应用。总应指定检索数据的顺序。在下面的第1条语句中，以col_name1、col_name2、col_name3顺序返回列；在第2条语句中，以col_name1、col_name3、col_name2顺序返回列：

mysql> SELECT col_name1, col_name2, col_name3 FROM tbl_name;

mysql> SELECT col_name1, col_name3, col_name2 FROM tbl_name;

如果决定更改表列的顺序，可执行下述操作：

1. 用具有新顺序的列创建新表。
2. 执行该语句：

mysql> INSERT INTO new_table

    -> SELECT columns-in-new-order FROM old_table;

3. 撤销或重命名old_table。
4. 将新表重命名为原始名称：

mysql> ALTER TABLE new_table RENAME old_table;

SELECT *十分适合于测试查询。但是，在应用程序中，永远不要依赖SELECT *的使用，不要依赖根据其位置检索列。如果添加、移动或删除了列，所返回的列的顺序和位置不会保持相同。对表结构的简单更改也会导致应用程序失败。

A.7.3. TEMPORARY TABLE问题

下面介绍了对使用TEMPORARY表的限制：

• TEMPORARY表只能是HEAP、ISAM、MyISAM、MERGE、或InnoDB类型。
• 在相同的查询中，不能引用TEMPORARY表1次以上。例如，下例不能正常工作：

mysql> SELECT * FROM temp_table, temp_table AS t2;

错误1137：不能再次打开表：'temp_table'

• SHOW TABLES语句不会列出TEMPORARY表。
• 不能使用RENAME重命名TEMPORARY表。但能使用ALTER TABLE取而代之：

mysql> ALTER TABLE orig_name RENAME new_name;