mysql.h


ALTER TABLE

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification ...]

Sintaxis para alter_specification:

    ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  | ADD [COLUMN] (create_definition, create_definition,...)
  | ADD INDEX [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...)
  | ADD [CONSTRAINT [symbol]] UNIQUE [index_name] (index_col_name,...)
  | ADD FULLTEXT [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
           [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name create_definition
           [FIRST | AFTER column_name]
  | MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col
  | CHARACTER SET character_set_name [COLLATE collation_name]
  | table_options

ALTER TABLE permite modificar la estructura de una tabla existente. Por ejemplo, se pueden añadir o eliminar columnas, crear y destruir índices, cambiar el tipo de una columna existente o renombrar columnas o la propia tabla. También es posible modificar el comentario y el tipo de la tabla.

Si se usa ALTER TABLE para cambiar la especificación de una columna pero DESCRIBE tbl_name indica que la columna no ha cambiado, es posible que MySQL haya ignorado la modificación por alguna razón. Por ejemplo, si se ha intentado cambiar una columna VARCHAR a CHAR, MySQL seguirá usando VARCHAR si la tabla contiene otras columnas de longitud variable.

ALTER TABLE trabaja haciendo una copia temporal de la tabla original. La modificación se realiza durante la copia, a continuación la tabla original se borra y la nueva se renombra. Esto se hace para realizar que todas las actualizaciones se dirijan a la nueva tabla sin ningún fallo de actualización. Mientras ALTER TABLE se ejecuta, la tabla original permanece accesible en lectura para otros clientes. Las actualizaciones y escrituras en la tabla se retrasan hasta que la nueva tabla esté preparada.

Hay que tener en cuenta que si se usa otra opción para ALTER TABLE como RENAME, MySQL siempre creará una tabla temporal, aunque no sea estrictamente necesario copiarla (como cuando se cambia el nombre de una columna). Está previsto corregir esto en el futuro, pero como no es corriente usar ALTER TABLE para hacer esto, no es algo urgente de hacer. Para tablas MyISAM, se puede aumentar la velocidad de la recreación de índices (que es la parte más lenta del proceso) asignando un valor alto a la variable myisam_sort_buffer_size.

  • Para usar ALTER TABLE, es necesario tener los privilegios ALTER, INSERT y CREATE en la tabla.
  • IGNORE es una extensión MySQL a SQL-92. Controla el modo de trabajar de ALTER TABLE si hay claves duplicadas o únicas en la nueva tabla. Si no se especifica IGNORE, la copia se aborta y se deshacen los cambios. Si se especifica IGNORE, en las filas duplicadas en una clave única sólo se copia la primera fila; el resto se eliminan.
  • Se pueden usar múltiples cláusulas ADD, ALTER, DROP y CHANGE en una sentencia sencilla ALTER TABLE. Esto es una extensión MySQL a SQL-92, que permite sólo una aparición de cada cláusula en una sentencia ALTER TABLE.
  • CHANGE col_name, DROP col_name y DROP INDEX también son extensiones MySQL a SQL-92.
  • MODIFY es una extensión Oracle a ALTER TABLE.
  • La palabra opcional COLUMN es una palabra ruidosa y puede ser omitida.
  • Si se usa ALTER TABLE tbl_name RENAME TO new_name sin ninguna otra opción, MySQL sencillamente renombra los ficheros correspondientes a la tabla tbl_name. No hay necesidad de crear una tabla temporal.
  • Las cláusulas create_definition usan la misma sintaxis para ADD y CHANGE que CREATE TABLE. Esta sintaxis incluye sólo el nombre de columna, no el tipo.
  • Se puede renombrar una columna usando una cláusula CHANGE old_col_name create_definition. Para hacerlo, hay que especificar los nombres antiguo y nuevo de la columna y el tipo que la columna tiene actualmente. Por ejemplo, para renombrar una columna INTEGER desde a a b, se puede hacer esto: 
    mysql> ALTER TABLE t1 CHANGE a b INTEGER;
  • Si se quiere cambiar el tipo de una columna, pero no su nombre, la sintaxis de CHANGE sigue necesitando un nombre de columna antiguo y nuevo, aunque mantenga en mismo nombre. Por ejemplo: 
    mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
  • Sin embargo, desde la versión 3.22.16a de MySQL, se puede usar también MODIFY para modificar el tipo de una columna sin renombrarla: 
    mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
  • Si se usa CHANGE o MODIFY para acortar una columna para la cual existe un índice en parte de la columna (por ejemplo, si se tiene un índice en los primeros 10 caracteres de una columna VARCHAR), no será posible acortar la columna a un número de caracteres menos que los indexados.
  • Cuando se cambia un tipo de columna usando CHANGE o MODIFY, MySQL intenta convertir datos al nuevo tipo lo mejor posible.
  • En versiones 3.22 o siguientes de MySQL, se puede usar FIRST o ADD ... AFTER col_name para añadir una columna en una posición específica dentro de una fila de la tabla. Por defecto se añade la columna al final. Desde MySQL 4.0.1, se pueden usar las palabras FIRST y AFTER en un CHANGE o MODIFY.
  • ALTER COLUMN especifica un nuevo valor por defecto para una columna o elimina el valor por defecto anterior. Si se elimina el anterior valor por defecto y la columna puede ser NULL, el nuevo valor por defecto es NULL. Si la columna no puede ser NULL, MySQL asigna un nuevo valor por defecto, como se describe en CREATE TABLE.
  • DROP INDEX elimina un índice. Esto es una extensión MySQL para SQL-92. Ver la sintaxis de DROP INDEX.
  • Si se quitan columnas de una tabla, también se eliminan de cualquier índice de las que formen parte. Si todas las columnas que forman parte de un índice se eliminan, el índice se elimina también.
  • Si la tabla contiene sólo una columna, ésta no puede ser eliminada. Si lo que se pretende es eliminar la tabla, se debe usar DROP TABLE.
  • DROP PRIMARY KEY elimina el índice primario. Si no existiera ese índice, se eliminará el primer índice UNIQUE de la tabla. (MySQL marca la primera clave UNIQUE como la PRIMARY KEY si no se ha especificado una PRIMARY KEY explícitamente.) Si se añade una UNIQUE INDEX o una PRIMARY KEY a la tabla, se almacena antes de cualquier índice UNIQUE de modo que MySQL pueda detectar claves duplicadas lo más pronto posible.
  • ORDER BY permite crear una nueva tabla con un orden específico para las filas. La tabla no permanecerá en ese orden después de nuevas inserciones o borrados. En algunos casos, es más fácil hacer que MySQL ordenar si la tabla está indexada por la columna por la que se desea ordenarla más tarde. Esta opción es corriente principalmente cuando se sabe que se va a consultar la tabla principalmente en un orden determinado; usando esta opción después de grandes campos en la tabla, es posible obtener un mejor rendimiento.
  • Si se usa ALTER TABLE en una tabla MyISAM, todos los índices no únicos serán creados en un proceso separado (como en REPAIR). Esto hace ALTER TABLE mucho más rápido cuando existe muchos índices.
  • Desde MySQL 4.0 la característica anterior puede ser activada explícitamente. ALTER TABLE ... DISABLE KEYS hace que MySQL detenga la actualización de índices no únicos para tablas MyISAM. ALTER TABLE ... ENABLE KEYS debe ser usado para recrear índices perdidos. Como MySQL hace esto con un algoritmo especial que es mucho más rápido cuando se insertan claves una a una, desactivar las claves puede proporcionar una considerable mejora de tiempo cuando se inserta gran cantidad de filas.
  • Con la función del API de C mysql_info, se puede obtener cuantos registros han sido copiados, y (si se ha usado IGNORE) cuantos registros fueron borrados por la duplicación de valores de clave.
  • Las cláusulas FOREIGN KEY, CHECK y REFERENCES actualmente no hacen nada, excepto para tablas del tipo InnoDB que soportant ... ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...) y ... DROP FOREIGN KEY ....
  • Ver restricciones FOREIGN KEY. La sintaxis para otros tipos de tabla se proporciona sólo por compatibilidad, para hacer más fácil portar código desde otros servidores SQL y para ejecutar aplicaciones que crean tablas con referencias.
  • ALTER TABLE ignora las opciones de tabla DATA DIRECTORY y INDEX DIRECTORY.
  • Si se quieren cambiar todas las columnas CHAR/VARCHAR/TEXT a un nuevo juego de caracteres (por ejemplo después de actualizar desde MySQL 4.0.x a 4.1.1) se puede hacer: 
    ALTER TABLE table_name CHARACTER SET character_set_name;
  • El comando siguiente sólo cambia el juego de caracteres por defecto para la tabla: 
    ALTER TABLE table_name DEFAULT CHARACTER SET character_set_name;
  • El juego de caracteres por defecto es el que se usa si no se especifica un juego de caracteres para una nueva columna que se añada a la tabla (por ejemplo con ALTER TABLE ... ADD column).

Seguidamente se muestra un ejemplo que demuestra como usar ALTER TABLE. Empezamos creando una tabla t1 como: 

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

Para renombrar la tabla de t1 a t2:

mysql> ALTER TABLE t1 RENAME t2;

Para cambiar la columna a de INTEGER a TINYINT NOT NULL (dejando el mismo nombre), y cambiar la columna b de CHAR(10) a CHAR(20) y además renombrando de b a c:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

Para añadir una columna TIMESTAMP llamada d:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Para añadir un índice en la columna d, y hacer la columna a la clave primaria:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

Para eliminar la columna c:

mysql> ALTER TABLE t2 DROP COLUMN c;

Para añadir una nueva columna entera AUTO_INCREMENT llamada c:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

Anotar que debemos indexar en c, porque las columnas AUTO_INCREMENT deben estar indexadas, y también que hemos declarado c como NOT NULL, proque las columnas indexadas no pueden ser NULL.

Cuandos e añade una columna AUTO_INCREMENT, los calores de columna se llenan con una secuencia numérica automáticamente. Se puede elegir el primer número de la secuencia ejecutando SET INSERT_ID=value antes de ALTER TABLE o usando la opción de tabla AUTO_INCREMENT=value.

Con tablas MyISAM, si no se modifica la columna AUTO_INCREMENT, la secuencia de numérica no resultará afectada. Si se elimina una columna AUTO_INCREMENT y después se añade otra columna AUTO_INCREMENT, los números empezarám en 1 otra vez.