A.7. Cuestiones relacionadas con definiciones de tabla

MySQL 5.0

A.7. Cuestiones relacionadas con definiciones de tabla

A.7.1. Problemas con ALTER TABLE

changes a table to the current character set. If you get a duplicate-key error during , 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 on the table.

If dies with the following error, the problem may be that MySQL crashed during an earlier operation and there is an old table named or lying around:

Error on rename of './database/name.frm'
to './database/B-.frm' (Errcode: 17)

In this case, go to the MySQL data directory and delete all files that have names starting with or . (You may want to move them elsewhere instead of deleting them.)

works in the following way:

  • Create a new table named with the requested structural changes.

  • Copy all rows from the original table to .

  • Rename the original table to .

  • Rename to your original table name.

  • Delete .

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 . A simple rename of the table files at the system level should get your data back.

If you use on a transactional table or if you are using Windows or OS/2, unlocks the table if you had done a on it. This is because and these operating systems cannot drop a table that is in use.

A.7.2. Cómo cambiar el orden de las columnas en una tabla

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 , , , whereas the second returns them in the order , , :

mysql> SELECT , ,  FROM ;
mysql> SELECT , ,  FROM ;

If you decide to change the order of table columns anyway, you can do so as follows:

  1. Create a new table with the columns in the new order.

  2. Execute this statement:

    mysql> INSERT INTO new_table
        -> SELECT columns-in-new-order FROM old_table;
    
  3. Drop or rename .

  4. Rename the new table to the original name:

    mysql> ALTER TABLE new_table RENAME old_table;
    

is quite suitable for testing queries. However, in an application, you should never rely on using 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.

A.7.3. Problemas con TEMPORARY TABLE

The following list indicates limitations on the use of tables:

  • A table can only be of type , , , , or .

  • You cannot refer to a table 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 statement does not list tables.

  • You cannot use to rename a table. However, you can use instead:

    mysql> ALTER TABLE orig_name RENAME new_name;