Capítulo 13. Sintaxis de sentencias SQL

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification [, alter_specification] ...

alter_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

ALTER DATABASE le permite cambiar las características globales de una base de datos. Estas características se almacenan en el fichero db.opt en el directorio de la base de datos. Para usar ALTER DATABASE, necesita el permiso ALTER en la base de datos.

La cláusula CHARACTER SET cambia el conjunto de carácteres por defecto de la base de datos. La cláusula COLLATE cambia la colación por defecto de la base de datos. El conjunto de carácteres y la colación se discuten en Capítulo 10, Soporte de conjuntos de caracteres.

En MySQL 5.0, el nombre de base de datos puede omitirse. El comando se aplica a la base de datos por defecto. ALTER SCHEMA puede usarse desde MySQL 5.0.2.

13.1.2. Sintaxis de `ALTER TABLE`

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

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [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 column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | table_options

ALTER TABLE le permite cambiar la estructura de una tabla existente. Por ejemplo, puede añadir o borrar columnas, crear o destruir índices, cambiar el tipo de columnas existentes, o renombrar columnas o la misma tabla. Puede cambiar el comentario de la tabla y su tipo.

La sintaxis para varias de las alteraciones permitidas es similar a cláusulas del comando CREATE TABLE. Esto incluye modificaciones table_options , para opciones tales como ENGINE, AUTO_INCREMENT, y AVG_ROW_LENGTH. Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE”.

Algunas operaciones pueden producir advertencias si se intentan en una tabla para que el motor de almacenamiento no soporte la operación. Estas advertencias pueden mostrarse con SHOW WARNINGS. Consulte Sección 13.5.4.22, “Sintaxis de SHOW WARNINGS”.

Si usa ALTER TABLE para cambiar la especificación de una columan pero DESCRIBEtbl_name indica que la columna no ha cambiado, es posible que MySQL haya ignorado las modificaciones por alguna de las razones descritas en Sección 13.1.5.1, “Cambios tácitos en la especificación de columnas”. Por ejemplo, si intenta cambiar una columna VARCHAR a CHAR, MySQL usa VARCHAR si la tabla contiene otras columnas de longitud variable.

ALTER TABLE funciona creando una copia temporal de la tabla original. La alteración se realiza en la copia, luego la tabla original se borra y se renombra la nueva. Mientras se ejecuta ALTER TABLE la tabla original es legible por otros clientes. Las actualizaciones y escrituras en la tabla se esperan hasta que la nueva tabla esté lista, luego se redirigen automáticamente a la nueva tabla sin ninguna actualización fallida.

Tenga en cuenta que si usa cualquier otra opción en ALTER TABLE distinta a RENAME, MySQL siempre crea una tabla temporal, incluso si los datos no necesitan ser copiados (tales como cuando cambia el nombre de una columna). Planeamos arreglar esto en el futuro, pero debido a que ALTER TABLE no es un comando que se use frecuentemente, no es un tema demasiado urgente. Para tablas MyISAM puede incrementar la velocidad de la operación de recrear índices (que es la parte más lenta del proceso de alteración) mediante la variable de sistema myisam_sort_buffer_size poniendo un valor alto.

Para usar ALTER TABLE, necesita ALTER, INSERT, y permisos CREATE para la tabla.

IGNORE es una extensión MySQL a SQL estándar. Controla cómo funciona ALTER TABLE si hay duplicados en las claves primarias en la nueva tabla o si ocuren advertencias cuando está activo el modo STRICT. Si no se especifica IGNORE la copia se aborta y no se ejecuta si hay errores de clave duplicada. Si se especifica IGNORE , entonces para duplicados con clave única, sólo se usa el primer registro. El resto de registros conflicitivos se borran. Los valores erróneos se truncan al valor más cercano aceptable.

Puede ejecutar múltiples cláusulas ADD, ALTER, DROP, y CHANGE en un único comando ALTER TABLE . Esta es una extensión MySQL al estándar SQL, que permite sólo una de cada cláusula por comando ALTER TABLE . Por ejemplo, para borrar múltiples columnas en un único comando:

mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

CHANGEcol_name, DROPcol_name, y DROP INDEX son extensiones MySQL al estándar SQL.

MODIFY es una extensión de Oracle a ALTER TABLE.

La palabra COLUMN es opcional y puede omitirse.

Si usa ALTER TABLEtbl_name RENAME TO new_tbl_name sin ninguna otra opción, MySQL símplemente renombra cualquier fichero que se corresponda a la tabla tbl_name. No es necesario crear una tabla temporal. (Puede usar el comando RENAME TABLE para renombrar tablas. Consulte Sección 13.1.9, “Sintaxis de RENAME TABLE”.)

Las cláusulas column_definition usan la misma sintaxis para ADD y CHANGE así como CREATE TABLE. Tenga en cuenta que esta sintaxis incluye el nombre de la columna, no sólo el tipo. Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE”.

Puede renombrar una columna usando CHANGEold_col_name column_definition. Para ello, especifique el nombre de columna viejo y nuevo y el tipo de la columna actual. Por ejemplo, para renombrar una columna INTEGER de a a b, puede hacer:

mysql> ALTER TABLE t1 CHANGE a b INTEGER;

Si quiere cambiar el tipo de una columna pero no el nombre, la sintaxis CHANGE necesita un nombre viejo y nuevo de columna, incluso si son iguales. Por ejemplo:

mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

Puede usar MODIFY para cambiar el tipo de una columna sin renombrarla:

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

Si usa CHANGE o MODIFY para acortar una columna para la que existe un índice en la columna, y la longitud de la columna resultante es menor que la del índice, MySQL reduce el índice automáticamente.

Cuando cambia un tipo de columna usando CHANGE o MODIFY, MySQL intenta convertir valores de columna existentes al nuevo tipo lo mejor posible.

En MySQL 5.0, puede usar FIRST o AFTER col_name para añadir una columna a una posición específica sin un registro de tabla. Por defecto se añade al final. Puede usar FIRST y AFTER en operaciones CHANGE o MODIFY en MySQL 5.0.

ALTER COLUMN especifica un nuevo valor por defecto para una columna o borra el antiguo valor por defecto. Si el antiguo valor por defecto se borra y la columna puede ser NULL, el nuevo valor por defecto es NULL. Si la columna no puede ser NULL, MySQL asigna un valor por defecto, como se describe en Sección 13.1.5, “Sintaxis de CREATE TABLE”.

DROP INDEX borra un índice. Es una extensión MySQL al estándar SQL. Consulte Sección 13.1.7, “Sintaxis de DROP INDEX”.

Si las columnas se borran de una tabla, las columnas también se borran de cualquier índice del que formaran parte. Si todas las columnas que crean un índice se borran, también se borra el índice.

Si una tabla contiene sólo una columna, la columna no puede borrarse. Si lo que quiere es borrar la tabla, use DROP TABLE .

DROP PRIMARY KEY borra el índice primario. Nota: En versiones anteriores de MySQL, si no existe clave primaria, entonces DROP PRIMARY KEY borraría el primer índice UNIQUE de la tabla. Esto ya no es así en MySQL 5.0, cuando trata de usar DROP PRIMARY KEY en una tabla sin clave primaria daría lugar a un error.

Si añade UNIQUE INDEX o PRIMARY KEY a una tabla, se almacena antes que cualquier índice no único para que MySQL pueda detactar claves duplicadas tan rápido como sea posible.

ORDER BY le permite crear la nueva tabla con los registros en un orden específico. Tenga en cuenta que la tabla no queda en este orden tras las inserciones y borrados. Esta opción es útil cuando sabe que normalmente consultará los registros en el mismo orden; usando esta opción tras grandes cambios en la tabla, puede ser capaz de obtener un mejor rendimiento. En algunos casos, puede hacer la ordenación más fácil para MySQL si la tabla está en el orden de la columna por la que quiere ordenar posteriormente.

Si usa ALTER TABLE en una tabla MyISAM , todos los índices no únicos se crean en un batch separado (como para REPAIR TABLE). Esto debe hacer ALTER TABLE mucho más rápido cuando tiene muchos índices.

En MySQL 5.0, esta característica puede activarse explícitamente ALTER TABLE ... DISABLE KEYS le dice a MySQL que pare de actualizar índices no únicos para una tabla MyISAM . ALTER TABLE ... ENABLE KEYS debe usarse para recrear índices perdidos. MySQL lo hace con un algoritmo especial que es mucho más rápido que insertar claves una a una, así que deshabilitar claves antes de realizar operaciones de inserción masivas debería dar una mejora de velocidad. Usar ALTER TABLE ... DISABLE KEYS requiere del permiso INDEX además de los permisos mencionados anteriormente.

Las cláusulas FOREIGN KEY y REFERENCES son soportadas por el motor InnoDB, que implementa ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). Consulte Sección 15.6.4, “Restricciones (constraints) FOREIGN KEY”. Para otros motores de almacenamiento, las cláusulas se parsean pero se ignoran. La cláusula CHECK se parsea pero se ignora por todos los motores de almacenamiento. Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE”. La razón para aceptar pero ignorar las cláusulas es para compatibilidad, para hacer más fácil portar código de otros servidores SQL, y para ejecutar aplicaciones que crean tablas con referencias. Consulte Sección 1.7.5, “Diferencias en MySQL del estándar SQL”.

En MySQL 5.0, InnoDB soporta el uso de ALTER TABLE para borrar claves foranas:

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

Para más información, consulte Sección 15.6.4, “Restricciones (constraints) FOREIGN KEY”.

ALTER TABLE ignora las opciones DATA DIRECTORY y INDEX DIRECTORY.

Si quiere cambiar el conjunto de carácteres por defecto de la tabla y todas las columnas de carácteres (CHAR, VARCHAR, TEXT) a un nuevo conjunto de carácteres, use un comando como:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Atención: La operación precedente convierte los valores de columnas entre conjuntos de carácteres. Esto no es lo que quiere hacer si tiene una columna en un conjunto de carácteres (como latin1) pero los valores almacenados realmente usan otro conjunto de carácteres incompatible (como utf8). En este caso, tiene que hacer lo siguiente para cada una de tales columnas:

ALTER TABLE t1 CHANGE c1 c1 BLOB; ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

La razón de que esto funcione es que no hay conversión cuando convierte desde o hacia columnas BLOB .

Si especifica CONVERT TO CHARACTER SET binary, las columnas CHAR, VARCHAR, y TEXT se converten a sus cadenas de carácteres binarias (BINARY, VARBINARY, BLOB). Esto significa que las columnas no tendrán un conjunto de carácters y que siguientes operaciones CONVERT TO no se les aplicarán.

Para sólo cambiar el conjunto de carácteres por defecto de una tabla, use este comando:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

La palabra DEFAULT es opcional. El conjunto de carácteres por defecto es el que se usa si no especifica uno para una nueva columna que añada a la tabla (por ejemplo, con ALTER TABLE ... ADD column).

Atención: En MySQL 5.0, ALTER TABLE ... DEFAULT CHARACTER SET y ALTER TABLE ... CHARACTER SET son equivalentes y cambian sólo el conjunto de carácteres por defecto de la tabla.

Para una tabla InnoDB creada con su propio espacio de tablas en un fichero .ibd, este fichero puede descartarse e importarse. Para descatar el fichero .ibd , use este comando:

ALTER TABLE tbl_name DISCARD TABLESPACE;

Esto borra el fichero .ibd actual, así que asegúrese que tiene primero una copia de seguridad. Tratar de acceder a la tabla mientras se descarta el fichero provoca un error.

Para importar el fichero .ibd de la copia de seguridad de nuevo a la tabla, cópielo en el directorio de la base de datos, luego realice el comando:

ALTER TABLE tbl_name IMPORT TABLESPACE;

Consulte Sección 15.6.6, “Usar un espacio de tablas para cada tabla”.

Con la función mysql_info() de la API de C, puede consultar el número de registros copiados, y (cuando se usa IGNORE ) cuántos registros se borraron debido a duplicación de valores de claves única. Consulte Sección 24.3.3.32, “mysql_info()”.

Hay algunos ejemplos que muestran usos de ALTER TABLE. Comienza con una tabla t1 que se crea como se muestra:

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 desde INTEGER a TINYINT NOT NULL (dejando el mismo nombre), y para cambiar la columna b desde CHAR(10) a CHAR(20) así como dejarla de b a c:

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

Para añadir una nueva columna TIMESTAMP llamada d:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Para añadir índices en las columnas d y a:

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

Para borrar 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 PRIMARY KEY (c);

Tenga en cuenta que indexamos c (como PRIMARY KEY), ya que las columnas AUTO_INCREMENT deben indexarse, y también que declaramos c como NOT NULL, ya que las columnas de clave primara no pueden ser NULL.

Cuando añade una columna AUTO_INCREMENT los valores se rellenan con números secuenciales automáticamente. Para tablas MyISAM puede asignar el primer número de secuencia ejecutando SET INSERT_ID=value antes de ALTER TABLE o usando la opción de tabla AUTO_INCREMENT=value. Consulte Sección 13.5.3, “Sintaxis de SET”.

Desde MySQL 5.0.3, puede usar la opción de tabla ALTER TABLE ... AUTO_INCREMENT=value para InnoDB para asignar el número de secuencia de nuevos registros si el valor es mayor que el máximo valor en la columna AUTO_INCREMENT . Si el valor es menor que el máximo actual en la columna, no se da ningún mensaje de error y el valor de secuencia actual no se cambia.

Con tablas MyISAM , si no cambia la columna AUTO_INCREMENT , el número de secuencia no se ve afectado. Si elimina una columna AUTO_INCREMENT y luego añade otra columna AUTO_INCREMENT los números se resecuencian comenzando en 1.

Consulte Sección A.7.1, “Problemas con ALTER TABLE”.

13.1.3. Sintaxis de CREATE DATABASE

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

CREATE DATABASE crea una base de datos con el nombre dado. Para usar CREATE DATABASE, necesita el permiso CREATE en la base de datos.

Las reglas para nombres de bases de datos permitidos se dan en Sección 9.2, “Nombres de bases de datos, tablas, índices, columnas y alias”. Ocurre un error si la base de datos existe y no especifica IF NOT EXISTS.

En MySQL 5.0, las opciones create_specification pueden darse para especificar característica de la base de datos. Las características se almacenan en el fichero db.opt en el directorio de la base de datos. La cláusula CHARACTER SET especifica el conjunto de carácteres por defecto de la base de datos. La cláusula COLLATE especifica la colación por defecto de la base de datos. Los nombres de colación y de conjunto de carácteres se discuten en Capítulo 10, Soporte de conjuntos de caracteres.

Las bases de datos en MySQL se implementan como directorios que contienen ficheros que se corresponden a tablas en la base de datos. Como no hay tablas en la base de datos cuando se crean inicialmente, el comando CREATE DATABASE en MySQL 5.0 crea sólo un directorio bajo el directorio de datos de MySQL y el fichero db.opt file.

Si crea manualmente un directorio bajo el directorio de datos (por ejemplo, con mkdir), el servidor lo considera como un directorio de base de datos y muestra la salida de SHOW DATABASES.

CREATE SCHEMA puede usarse desde MySQL 5.0.2.

También puede usar el programa mysqladmin para crear bases de datos. Consulte Sección 8.4, “Administrar un servidor MySQL con mysqladmin”.

13.1.4. Sintaxis de CREATE INDEX

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) index_col_name: col_name [(length)] [ASC | DESC]

En MySQL 5.0, CREATE INDEX se mapea a un comando ALTER TABLE para crear índices. Consulte Sección 13.1.2, “Sintaxis de ALTER TABLE”.

Normalmente, crea todos los índices en una tabla cuando se crea la propia tabla con CREATE TABLE. Consulte Sección 13.1.5, “Sintaxis de CREATE TABLE”. CREATE INDEX le permite añadir índices a tablas existentes.

Una lista de columnas de la forma (col1,col2,...) crea un índice de múltiples columnas. Los valores de índice se forman al concatenar los valores de las columnas dadas.

Para columnas CHAR y VARCHAR, los índices pueden crearse para que usen sólo parte de una columna, usando col_name(length) para indexar un prefijo consistente en los primeros length carácteres de cada valor de la columna. BLOB t TEXT pueden indexarse, pero se debe dar una longitud de prefijo.

El comando mostrado aquí crea un índice usando los primeros 10 carácteres de la columna name :

CREATE INDEX part_of_name ON customer (name(10));

Como la mayoría de nombres usualmente difieren en los primeros 10 carácteres, este índice no debería ser mucho más lento que un índice creado con la columna name entera. Además, usar columnas parcialmente para índices puede hacer un fichero índice mucho menor, que puede ahorrar mucho espacio de disco y además acelarar las operaciones INSERT .

Los prefijos pueden tener una longitud de hasta 255 bytes. Para tablas MyISAM y InnoDB en MySQL 5.0, pueden tener una longitud de hasta 1000 bytes . Tenga en cuenta que los límites de los prefijos se miden en bytes, mientras que la longitud de prefijo en comandos CREATE INDEX se interpreta como el número de carácteres. Tenga esto en cuenta cuando especifique una longitud de prefijo para una columna que use un conjunto de carácteres de múltiples bytes.

En MySQL 5.0:

Puede añadir un índice en una columna que puede tener valores NULL sólo si está usando MyISAM, InnoDB, o BDB .

Puede añadir un índice en una columna BLOB o TEXT sólo si está usando el tipo de tabla MyISAM, BDB, o InnoDB .

Una especificación index_col_name puede acabar con ASC o DESC. Estas palabras se permiten para extensiones futuras para especificar almacenamiento de índice ascendente o descendente. Actualmente se parsean pero se ignoran; los valores de índice siempre se almacenan en orden ascendente.

En MySQL 5.0, algunos motores le permiten especificar un tipo de índice cuando se crea un índice. La sintaxis para el especificador index_type es USING type_name. Los valores type_name posibles soportados por distintos motores se muestran en la siguiente tabla. Donde se muestran múltiples tipos de índice , el primero es el tipo por defecto cuando no se especifica index_type .

Motor de almacenamiento Tipos de índice permitidos

MyISAM BTREE

InnoDB BTREE

MEMORY/HEAP HASH, BTREE

Ejemplo:

CREATE TABLE lookup (id INT) ENGINE = MEMORY; CREATE INDEX id_index USING BTREE ON lookup (id);

TYPE type_name puede usarse como sinónimo de USINGtype_name para especificar un tipo de índice. Sin embargo, USING es la forma preferida. Además, el nombre de índice que precede el tipo de índice en la especificación de la sintaxis de índice no es opcional con TYPE. Esto es debido a que, en contra de USING, TYPE no es una palabra reservada y se interpreta como nombre de índice.

Si especifica un tipo de índice que no es legal para un motor de almacenamiento, pero hay otro tipo de índice disponible que puede usar el motor sin afectar los resultados de la consulta, el motor usa el tipo disponible.

Para más información sobre cómo MySQL usa índices, consulte Sección 7.4.5, “Cómo utiliza MySQL los índices”.

Índices FULLTEXT en MySQL 5.0 puede indexar sólo columnas CHAR, VARCHAR, y TEXT , y sólo en tablas MyISAM . Consulte Sección 12.7, “Funciones de búsqueda de texto completo (Full-Text)”.

Índices SPATIAL en MySQL 5.0 puede indexar sólo columnas espaciales, y sólo en tablas MyISAM . Los tipo de columna espaciales se describen en Capítulo 18, Extensiones espaciales de MySQL.

13.1.5. Sintaxis de CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement]

O:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)]; create_definition: column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | KEY [index_name] [index_type] (index_col_name,...) | INDEX [index_name] [index_type] (index_col_name,...) | [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...) | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...) | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | CHECK (expr) column_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] type: TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | CHAR(length) [BINARY | ASCII | UNICODE] | VARCHAR(length) [BINARY] | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] | TEXT [BINARY] | MEDIUMTEXT [BINARY] | LONGTEXT [BINARY] | ENUM(value1,value2,value3,...) | SET(value1,value2,value3,...) | spatial_type index_col_name: col_name [(length)] [ASC | DESC] reference_definition: REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION table_options: table_option [table_option] ... table_option: {ENGINE|TYPE} = engine_name | AUTO_INCREMENT = value | AVG_ROW_LENGTH = value | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] | CHECKSUM = {0 | 1} | COMMENT = 'string' | MAX_ROWS = value | MIN_ROWS = value | PACK_KEYS = {0 | 1 | DEFAULT} | PASSWORD = 'string' | DELAY_KEY_WRITE = {0 | 1} | ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS = value RAID_CHUNKSIZE = value | UNION = (tbl_name[,tbl_name]...) | INSERT_METHOD = { NO | FIRST | LAST } | DATA DIRECTORY = 'absolute path to directory' | INDEX DIRECTORY = 'absolute path to directory' select_statement: [IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)

CREATE TABLE crea una tabla con el nombre dado. Debe tener el permiso CREATE para la tabla.

Las reglas para nombres de tabla permitidos se dan en Sección 9.2, “Nombres de bases de datos, tablas, índices, columnas y alias”. Por defecto, la tabla se crea en la base de datos actual. Ocurre un error si la tabla existe, si no hay base de datos actual o si la base de datos no existe.

En MySQL 5.0, el nombre de tabla puede especificarse como db_name.tbl_name para crear la tabla en la base de datos específica. Esto funciona haya una base de datos actual o no. Si usa identificadores entre comillas, entrecomille el nombre de base de datos y de tabla por separado. Por ejemplo, `mydb`.`mytbl` es legal, pero `mydb.mytbl` no.

Puede usar la palabra TEMPORARY al crear una tabla. Una tabla TEMPORARY es visible sólo para la conexión actual, y se borra automáticamente cuando la conexión se cierra. Esto significa que dos conexiones distintas pueden usar el mismo nombre de tabla temporal sin entrar en conflicto entre ellas ni con tablas no TEMPORARY con el mismo nombre. (La tabla existente se oculta hasta que se borra la tabla temporal.) En MySQL 5.0, debe tener el permiso CREATE TEMPORARY TABLES para crear tablas temporales.

MySQL 5.0 soporta las palabras IF NOT EXISTS para que no ocurra un error si la tabla existe. Tenga en cuenta que no hay verificación que la tabla existente tenga una estructura idéntica a la indicada por el comando CREATE TABLE . Nota: Si usa IF NOT EXISTS en un comando CREATE TABLE ... SELECT ,cualquier registro seleccionado por la parte SELECT se inserta si la tabla existe o no.

MySQL representa cada tabla mediante un fichero .frm de formato de tabla (definición) en el directorio de base de datos. El motor para la tabla puede crear otros ficheros también. En el caso de tablas MyISAM , el motor crea ficheros índice y de datos. Por lo tanto, para cada tabla MyISAM tbl_name, hay tres ficheros de disco:

Fichero Propósito

tbl_name.frm Fichero de formato de tabla (definición)

tbl_name.MYD Fichero de datos

tbl_name.MYI Fichero índice

Los ficheros creados por cada motor de almacenamiento para representar tablas se describen en Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.

Para información general de las propiedades de los diversos tipos de columna, consulte Capítulo 11, Tipos de columna. Para información acerca de tipos de columna espaciales, consulte Capítulo 18, Extensiones espaciales de MySQL.

Si no se especifica NULL ni NOT NULL, la columna se trata como si se especificara NULL .

Una columna entera puede tener el atributo adicional AUTO_INCREMENT. Cuando inserta un valor de NULL (recomendado) o 0 en una columna AUTO_INCREMENT autoindexada, la columna se asigna al siguiente valor de secuencia. Típicamente esto es value+1, donde value es el mayor valor posible para la columna en la tabla. Secuencias AUTO_INCREMENT comienzan con 1. Tales columnas deben definirse como uno de los tipos enteros como se describe en Sección 11.1.1, “Panorámica de tipos numéricos”. (El valor 1.0 no es un entero.) Consulte Sección 24.3.3.34, “mysql_insert_id()”.

En MySQL 5.0, especificar NO_AUTO_VALUE_ON_ZERO para la opción de servidor --sql-mode o la variable de sistema sql_mode le permite almacenar 0 en columnas AUTO_INCREMENT como 0 sin generar un nuevo valor de secuencia. Consulte Sección 5.3.1, “Opciones del comando mysqld”.

Nota: Sólo puede haber una columna AUTO_INCREMENT por tabla, debe estar indexada, y no puede tener un valor DEFAULT . Una columna AUTO_INCREMENT funciona correctamente sólo si contiene sólo valores positivos. Insertar un número negativo se trata como insertar un número positivo muy grande. Esto se hace para evitar problemas de precisión cuando los números “cambian” de positivos a negativos y asegura que no obtiene accidentalmente una columna AUTO_INCREMENT que contenga 0.

Para tablas MyISAM y BDB , puede especificar una columna AUTO_INCREMENT secundaria en una clave de múltiples columnas. Consulte Sección 3.6.9, “Utilización de AUTO_INCREMENT”.

Para hacer MySQL compatible con otras aplicaciones ODBC , puede encontrar el valor AUTO_INCREMENT para el último registro insertado con la siguiente consulta:

SELECT * FROM tbl_name WHERE auto_col IS NULL

En MySQL 5.0, las definiciones de columnas de carácteres puede incluir un atributo CHARACTER SET para especificar el conjunto de carácteres y, opcionalmente, una colación para la columna. Para detalles, consulte Capítulo 10, Soporte de conjuntos de caracteres. CHARSET es sinónimo de CHARACTER SET.

CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);

MySQL 5.0 interpreta las especificaciones de longitud en definiciones de columna en carácteres. (Algunas versiones anteriores los interpretan en bytes.)

La cláusula DEFAULT especifica el valor por defecto para una columna. Con una excepción, el valor por defecto debe ser constante; no puede ser una función o una expresión. Esto significa , por ejemplo, que no puede poner como valor por defecto de una columna el valor de una función como NOW() o CURRENT_DATE. La excepción es que pude especificar CURRENT_TIMESTAMP como defecto para columnas TIMESTAMP . Consulte Sección 11.3.1.1, “Propiedades de TIMESTAMP desde MySQL 4.1”.

Antes de MySQL 5.0.2, si una definición de columna no incluye valor DEFAULT explícito, MySQL determina el valor por defecto como sigue:

Si la columna puede tener valores NULL , la columna se define como una cláusula DEFAULT NULL explícita.

Si la columna no puede tener valores NULL , MySQL define la columna con una cláusula DEFAULT explícita, usando el valor por defecto implícito para el tipo de datos de la columna . Los valores por defecto implícitos se definen como sigue:

Para tipos numéricos distintos a los declarados con el atributo AUTO_INCREMENT , por defecto es 0. Para una columna AUTO_INCREMENT , el valor por defecto es el siguiente valor de la secuencia.

Para tipos de fecha y hora distintos a TIMESTAMP, el valor por defecto es el valor “cero” apropiado para el tipo. Para la primera columna TIMESTAMP en una tabla, el valor por defecto es la fecha actual y la hora. Consulte Sección 11.3, “Tipos de fecha y hora”.

Para tipos de cadenas distintos a ENUM, el valor por defecto es la cadena vacía. Para ENUM, el valor por defecto es el primer valor de la enumeración.

Las columnas BLOB y TEXT no pueden tener un valor por defecto.

Desde MySQL 5.0.2, si una definición de columna no incluye valor DEFAULT explícito , MySQL determina el valor por defecto como sigue:

Si la columna puede tener NULL como valor, la columna se define con una cláusula DEFAULT NULL explícita. Esto es lo mismo que antes de 5.0.2.

Si la columna no puede tener valores NULL , MySQL define la columna sin cláusula DEFAULT explícita. Para entradas de datos, si un comando INSERT o REPLACE no incluye valor para la columna, MySQL trata la columna según el modo SQL activo en ese momento:

Si el modo estricto no está activado, MySQL pone en la columna el valor por defecto implícito para el tipo de datos de la columna.

Si está activo el modo estricto, ocurre un error para tablas transaccionales y el comando se deshace. Para tablas no transaccionales, ocurre un error, pero si esto ocurre para el segundo registro o siguientes de un comando de múltiples registros, los registros precedentes se insertarán.

Suponga que una tabla t se define como sigue:

CREATE TABLE t (i INT NOT NULL);

En este caso, i no tiene valor explícito, así que en modo estricto todos los siguientes comandos producen un error en modo estricto y no se inserta ningún registro. Para modo no estricto, sólo el tercer comando produce un error; el valor implícito por defecto se inserta para las dos primeras, pero la tercera falla ya que DEFAULT(i) no puede producir un valor:

INSERT INTO t VALUES(); INSERT INTO t VALUES(DEFAULT); INSERT INTO t VALUES(DEFAULT(i));

Consulte Sección 5.3.2, “El modo SQL del servidor”.

Para una tabla dada, puede usar el comando SHOW CREATE TABLE para ver qué columnas puede tener una cláusula explícita DEFAULT.

Un comentario para una columna puede especificarse en MySQL 5.0 con la opción COMMENT . El comentario se muestra con los comandos SHOW CREATE TABLE y SHOW FULL COLUMNS .

En MySQL 5.0, el atributo SERIAL puede usarse como un alias para BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

KEY normalemente es sinónimo para INDEX. En MySQL 5.0, el atributo clave PRIMARY KEY puede especificarse como KEY cuando se da en una definición de columna. Esto se implementó por compatibilidad con otros sistemas de bases de datos.

En MySQL, un índice UNIQUE es uno en que todos los valores en el índice deben ser distintos. Ocurre un error si intenta añadir un nuevo registro con una clave que coincida con un registro existente. La excepción es que una columna en el índice puede contener valores NULL , puede contener valores NULL múltiples. Esta excepción no se aplica a tablas BDB , en las que una columna indexada le permita un único NULL.

Una PRIMARY KEY es una KEY única donde todas las columnas de la clave deben definirse como NOT NULL. Si no se declaran explícitamente como NOT NULL, MySQL las declara implícitamente ( y sin decirlo ) . Una tabla puede tener sólo una PRIMARY KEY. Si no tiene una PRIMARY KEY y una aplicación pide una PRIMARY KEY en sus tablas, MySQL retorna el primer índice UNIQUE que no tenga columnas NULL como la PRIMARY KEY.

En la tabla creada, una PRIMARY KEY se guarda en primer lugar, seguida por todos los índices UNIQUE, y luego los índices no únicos. Esto ayuda al optimizador MySQL a priorizar qué indice usar y también detectar más rápido claves UNIQUE duplicadas.

Una PRIMARY KEY puede ser un índice de múltiples columnas. Sin embargo, no puede crear un índice de múltiples columnas usando el atributo de clave PRIMARY KEY en una especificación de columna. Hacerlo sólo marca la columna como primaria. Debe usar una cláusula PRIMARY KEY(index_col_name, ...) separada.

Si un índice PRIMARY KEY o UNIQUE consite sólo de una columna que tenga un tipo entero, puede referirse a la columna como _rowid en comandos SELECT.

En MySQL, el nombre de una PRIMARY KEY es PRIMARY. Para otros índices, si no asigna un nombre, el índice tieen el mismo nombre que la primera columna indexada, con un sufijo opcional (_2, _3, ...) para hacerlo único. Puede ver los nombres de índice para una tabla usando SHOW INDEX FROMtbl_name. Consulte Sección 13.5.4.11, “Sintaxis de SHOW INDEX”.

A partir de MySQL 5.0, algunos motores de almacenamiento le permiten especificar un tipo de índice al crear el índice. Consulte Sección 13.1.4, “Sintaxis de CREATE INDEX”.

Para más información acerca de cómo usa los índices MySQL, consulte Sección 7.4.5, “Cómo utiliza MySQL los índices”.

En MySQL 5.0, sólo los motores MyISAM, InnoDB, BDB, y MEMORY soporta índices en columnas que pueden tener valores NULL . En otros casos, debe declarar columnas indexadas como NOT NULL u ocurre un error.

Con sintaxis col_name(length) en una especificación de índice, puede crear un índice que use sólo los primeros length carácteres de una columna CHAR o VARCHAR . Indexar sólo un prefijo de valores de columna como este puede hacer el fichero de índice mucho más pequeño. Consulte Sección 7.4.3, “Índices de columna”.

En MySQL 5.0, los motores MyISAM y InnoDB soportan indexación en columnas BLOB y TEXT . Al indexar columnas BLOB o TEXT debe especificar una longitud de prefijo para el índice. Por ejemplo:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

En MySQL 5.0, los prefijos pueden tener hasta 1000 bytes de longitud para tablas MyISAM y InnoDB y 255 bytes para otros tipos de tabla. Tenga en cuenta que los límites de prefijo se miden en bytes, mientras que la longitud de prefijo en comandos CREATE TABLE se interpretan como el número de carácteres. Asegúrese de tener esto en cuenta al especificar una longitud de prefijo para una columna que use un conjunto de carácteres multi-byte .

Una especificación index_col_name puede acabar con ASC o DESC. Estas palabras clave se permiten para extensiones futuras para especificar almacenamiento de índices ascendente o descendentemente. Actualmente se parsean pero se ignoran; los valores de índice siempre se almacenan en orden ascendente.

Cuando usa ORDER BY o GROUP BY en una columna TEXT o BLOB en un SELECT, el servidor ordena los valores usando sólo el número inicial de bytes indicados por la variable de sistema max_sort_length . Consulte Sección 11.4.3, “Los tipos BLOB y TEXT”.

En MySQL 5.0, puede crear índices especiales FULLTEXT , que se usan para índices full-text . Sólo las tablas MyISAM soportan índices FULLTEXT . Pueden crearse sólo desde columnas CHAR, VARCHAR, y TEXT. La indexación siempre se hace sobre la columna entera; la indexación parcial no se soporta y cualquier longitud de prefijo se ignora. Consulte Sección 12.7, “Funciones de búsqueda de texto completo (Full-Text)” para más detalles.

En MySQL 5.0, puede crear índices SPATIAL en tipos de columna espaciales. Los tipos espaciales se soportan sólo para tablas MyISAM y las columnas indexadas deben declararase como NOT NULL. Consulte Capítulo 18, Extensiones espaciales de MySQL.

En MySQL 5.0, las tablas InnoDB soportan el chequeo de restricciones de claves foráneas . Consulte Capítulo 15, El motor de almacenamiento InnoDB. Tenga en cuenta que la sintaxis FOREIGN KEY en InnoDB es más restrictiva que la sintaxis presentada para el comando CREATE TABLE al inicio de esta sección: las columnas en la tabla referenciada debe siempre nombrarse explícitamente. InnoDB soporta tanto acciones ON DELETE como ON UPDATE en MySQL 5.0. Para la sintaxis precisa, consulte Sección 15.6.4, “Restricciones (constraints) FOREIGN KEY”.

Para otros motores de almacenamiento, MySQL Server parsea la sintaxis FOREIGN KEY y REFERENCES en comandos CREATE TABLE , pero no hace nada. La cláusula CHECK se parsea paro se ignora en todos los motores de almacenamiento. Consulte Sección 1.7.5.5, “Claves foráneas (foreign keys)”.

Para tablas MyISAM cada columna NULL ocupa un bit extra, redondeado al byte más próximo. La máxima longitud de registro en bytes puede calcularse como sigue:

row length = 1 + (sum of column lengths) + (number of NULL columns + delete_flag + 7)/8 + (number of variable-length columns)

delete_flag es 1 para tables con formato de registro estático. Las tablas estáticas usan un bit en el registro para un flag que indica si el registro se ha borrado. delete_flag es 0 para tablas dinámicas ya que el flag se almacena en una cabecera de registro dinámica.

Estos cálculos no se aplican en tablas InnoDB , en las que el tamaño de almacenamiento no es distinto para columnas NULL y NOT NULL .

La parte table_options de la sintaxis CREATE TABLE puede usarse desde MySQL 3.23.

Las opciones ENGINE y TYPE especifican el motor de almacenamiento para la tabla. ENGINE es el nombre preferido para la opción en MySQL 5.0, y TYPE está obsoleto. El soporte para la palabra TYPE usada en este contexto desaparecerá en MySQL 5.1.

Las opciones ENGINE y TYPE pueden tener los siguientes valores:

Motor de almacenamiento Descripción

ARCHIVE El motor de almacenamiento para archivar. Consulte Sección 14.7, “El motor de almacenamiento ARCHIVE”.

BDB Tablas transaccionales con bloqueo de página. Conocidas como BerkeleyDB. Consulte Sección 14.4, “El motor de almacenamiento BDB (BerkeleyDB)”.

CSV Tablas que almacenan registros en valores separados por comas. Consulte Sección 14.8, “El motor de almacenamiento CSV”.

EXAMPLE Motor de ejemplo. Consulte Sección 14.5, “El motor de almacenamiento EXAMPLE”.

FEDERATED Motor que accede a tablas remotas. Consulte Sección 14.6, “El motor de almacenamiento FEDERATED”.

HEAP Consulte Sección 14.3, “El motor de almacenamiento MEMORY (HEAP)”.

(OBSOLETE) ISAM No disponible en MySQL 5.0. Si está actualizando a MySQL 5.0 desde una versión prévia, debe convertir cualquier tabla ISAM existente a MyISAM antes de la actualización. Consulte Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.

InnoDB Tablas transaccionales con bloqueo de registro y claves foráneas. Consulte Capítulo 15, El motor de almacenamiento InnoDB.

MEMORY Los datos de este tipo de tabla se almacenan sólo en memoria. (Conocido anteriormente como HEAP.)

MERGE Colección de tablas MyISAM usadas como una sola tabla. También conocido como MRG_MyISAM. Consulte Sección 14.2, “El motor de almacenamiento MERGE”.

MyISAM Motor binario portable que es el motor por defecto usado en MySQL. Consulte Sección 14.1, “El motor de almacenamiento MyISAM”.

NDBCLUSTER Clusterizado, tolerante a errores, tablas en memoria. También conocido como NDB. Consulte Capítulo 16, MySQL Cluster.

Para más información acerca de motores MySQL, consulte Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.

Si un motor no está disponible, MySQL usa en su lugar MyISAM . Por ejemplo, si una definición de tabla incluye la opción ENGINE=BDB pero el servidor MySQL no soporta tablas BDB , la tabla se crea como MyISAM . Esto hace posible tener un entorno de replicación donde tiene tablas transaccionales en el maestro pero tablas no transaccionales en el esclavo (para tener más velocidad). En MySQL 5.0, aparece una advertencia si la especificación del motor no es correcta.

Las otras opciones de tabla se usan para optimizar el comportamiento de la tabla. En la mayoría de casos, no tiene que especificar ninguna de ellas. La opción funciona para todos los motores a no ser que se indique lo contrario:

AUTO_INCREMENT

El valor inicial para AUTO_INCREMENT para la tabla. En MySQL 5.0, sólo funciona para tablas MyISAM y MEMORY. También se soporta para InnoDB desde MySQL 5.0.3. Para inicializar el primer valor de auto incremento para motores que no soporten esta opción, inserte un registro de prueba con un valor que sea uno menor al deseado tras crear la tabla, y luego borre este registro.

Para motores que soportan la opción de tabla AUTO_INCREMENT en comandos CREATE TABLE puede usar ALTER TABLEtbl_name AUTO_INCREMENT = n para resetear el valor AUTO_INCREMENT .

AVG_ROW_LENGTH

Una aproximación de la longitud media de registro para su tabla. Necesita inicializarla sólo para tablas grandes con registros de longitud variable.

Cuando crea una tabla MyISAM , MySQL usa el producto de las opciones MAX_ROWS y AVG_ROW_LENGTH para decidir el tamaño de la tabla resultante. Si no las especifica, el tamaño máximo para la tabla es 65,536TB de datos (4GB antes de MySQL 5.0.6). (Si su sistema operativo no soporta ficheros de este tamaño, los tamaños de fichero se restringen al límite del sistema operativo.) Si quiere mantener bajos los tamaños de los punteros para que el índice sea pequeño y rápido y no necesita realmente ficheros grandes, puede decrementar el tamaño de puntero por defecto mediante la variable de sistema myisam_data_pointer_size que se añadió en MySQL 4.1.2. (Consulte Sección 5.3.3, “Variables de sistema del servidor”.) Si quiere que todas sus tablas sean capaces de crecer por encima del límite por defecto y quiere mantener sus tablas ligeramente más lentas y más grandes de lo necesario, puede incrementar el tamaño de punter por defecto cambiando esta variable.

[DEFAULT] CHARACTER SET

Especifica el conjunto de carácteres para la tabla. CHARSET es un sinónimo.

para CHARACTER SET.

COLLATE

Especifica la colación por defecto de la tabla.

CHECKSUM

Póngalo a 1 si quiere que MySQL mantenga un checksum para todos los registros (un checksum que MySQL actualiza automáticamente según cambia la tabla). Esto hace que la tabla tenga actualizaciones más lentas, pero hace más fácil encontrar tablas corruptas. El comando CHECKSUM TABLE muestra el checksum (sólo para MyISAM).

COMMENT

Un comentario para su tabla, hasta 60 carácteres.

MAX_ROWS

Máximo número de registros que planea almacenar en la tabla. No es un límite absoluto, sino un indicador que la tabla debe ser capaz de almacenar al menos estos registros.

MIN_ROWS

Mínimo número de registros que planea almacenar en la tabla.

PACK_KEYS

Ponga esta opción a 1 si quiere tener índices más pequeños. Esto hace normalmente que las actualizaciones sean más lentas y las lecturas más rápidas. Poner esta opción a 0 deshabilita la compresión de claves. Ponerla a DEFAULT le dice al motor que comprima sólo columnas CHAR/VARCHAR largas (MyISAM y ISAM sólo).

Si no usa PACK_KEYS, por defecto se comprimen sólo cadenas, no números. Si usa PACK_KEYS=1, también se empaquetan números.

Al comprimir claves de números binarios, MySQL usa compresión de prefijo:

Cada clave necesita un byte extra para indicar cuántos bytes de la clave previa son los mismos para la siguiente clave.

El puntero al registro se almacena en orden de el-mayor-byte-primero directamente tras la clave, para mejorar la compresión.

Esto significa que si tiene muchas claves iguales en dos registros consecutivos, todas las “mismas” claves siguientes usualmente sólo ocupan dos bytes (incluyendo el puntero al registro). Comparar esto con el caso ordinario donde las siguente claves ocupan storage_size_for_key + pointer_size (donde el tamaño del puntero es usualmente 4). Obtiene un gran beneficio a partir de la compresión de prefijos sñolo si tiene muchos números que sean el mismo. Si todas las claves son totalmente distintas, usa un byte más por clave, si la clave no es una clave que pueda tener valores NULL . (En ese caso, el tamaño empaquetado de la clave se almacena en el mismo byte que se usa para marcar si una clave es NULL.)

PASSWORD

Encripta el fichero .frm con una contraseña. Esta opción no hace nada en la versión estándar de MySQL.

DELAY_KEY_WRITE

Póngalo a 1 si quiere retardar actualizaciones de clave para la tabla hasta que la tabla se cierra (sólo en MyISAM).

ROW_FORMAT

Define cómo deben almacenarse los registros. Actualmente esta opción sólo funciona con tablas MyISAM. El valor de la opción puede ser FIXED o DYNAMIC para formato de longitud estática o variable. myisampack cambia el tipo a COMPRESSED. Consulte Sección 14.1.3, “Formatos de almacenamiento de tablas MyISAM”.

Desde MySQL/InnoDB-5.0.3, los registros de InnoDB se almacenan de forma más compacta (ROW_FORMAT=COMPACT) por defecto. El antiguo formato puede usarse espeficicando ROW_FORMAT=REDUNDANT.

RAID_TYPE

Tenga en cuenta que el soporte para RAID se ha eliminado desde MySQL 5.0. Para información sobre RAID, consulte Manual de referencia de MySQL 4.1.

UNION

UNION se usa cuando quiere usar una colección de tablas idénticas como una. Funciona sólo con tablas MERGE . Consulte Sección 14.2, “El motor de almacenamiento MERGE”.

En MySQL 5.0, debe tener permisos SELECT, UPDATE, y DELETE para las tablas mapeadas en una tabla MERGE . (Nota: Originalmente, todas las tablas usadas tenían que estar en la misma base de datos que la tabla MERGE. Esta restricción se ha eliminado.)

INSERT_METHOD

Si quiere insertar datos en una tabla MERGE debe especificarlo con INSERT_METHOD en la tabla en que se debe insertar el registro. INSERT_METHOD es una opción útil para tablas MERGE sólo. Use un valor dee FIRST o LAST para que las inserciones vayan a la primera o última tabla, o un valor de NO para evitar inserciones. Consulte Sección 14.2, “El motor de almacenamiento MERGE”.

DATA DIRECTORY, INDEX DIRECTORY

Usando DATA DIRECTORY='directory' o INDEX DIRECTORY='directory' puede especificar dónde debe el moto MyISAM buardar un fichero de datos e índice de una tabla. Tenga en cuenta que el directorio debe ser una ruta completa al directorio (no una ruta relativa).

Estas opciones sólo funcionan cuando no usa la opción --skip-symbolic-links . Su sistema operativo debe tener una llamada realpath() que funcione bien. Consulte Sección 7.6.1.2, “Utilización de enlaces simbólicos para tablas en Unix” para más información.

En MySQL 5.0, puede crear una tabla de otra añadiendo un comando SELECT al final del comando CREATE TABLE :

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL crea nuevas columnas para todos los elementos en un SELECT. Por ejemplo:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b)) -> TYPE=MyISAM SELECT b,c FROM test2;

Esto crea una tabla MyISAM con tres columnas, a, b, y c. Tenga en cuenta que las columnas para el comando SELECT se añaden a la derecha de la tabla, no se sobreescriben en la misma. Consulte el siguiente ejemplo:

mysql> SELECT * FROM foo; +---+ | n | +---+ | 1 | +---+ mysql> CREATE TABLE bar (m INT) SELECT n FROM foo; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM bar; +------+---+ | m | n | +------+---+ | NULL | 1 | +------+---+ 1 row in set (0.00 sec)

Para cada registro en la tabla foo, se inserta un registro en bar con los valores de foo y valores por defecto para las nuevas columnas:

Si hay cualquier error al copiar los datos a la tabla, se borra automáticamente y no se crea.

CREATE TABLE ... SELECT no crea ningún índice automáticamente. Se hace a propósito para hacer el comando lo más flexible posible. Si quiere tener índices en la tabla creada, debe especificarlo antes del comando SELECT :

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

Algunas conversiones de tipos de columnas pueden ocurrir. Por ejemplo, el atributo AUTO_INCREMENT no se preserva, y las columnas VARCHAR pueden ser CHAR .

Al crear una tabla con CREATE ... SELECT, asegurése de poner un alias para cualquier llamada a función o expresión en la consulta. Si no lo hace, el comando CREATE puede fallar o crear nombres de columnas no deseados.

CREATE TABLE artists_and_works SELECT artist.name, COUNT(work.artist_id) AS number_of_works FROM artist LEFT JOIN work ON artist.id = work.artist_id GROUP BY artist.id;

Puede especificar explícitamente el tipo de una columna generada:

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

En MySQL 5.0, use LIKE para crear una tabla vacía basada en la definición de otra tabla, incluyendo cualquier atributo de columna e índice definido en la tabla original:

CREATE TABLE new_tbl LIKE orig_tbl;

CREATE TABLE ... LIKE no copia ninguna opción de tabla DATA DIRECTORY o INDEX DIRECTORY especificadas en la tabla original, ni ninguna definición de clave foránea.

Puede preceder SELECT con IGNORE o REPLACE para indicar cómo tratar registros que dupliquen claves únicas. Con IGNORE, los nuevos registros que duplican un registro único existente se descartan. Con REPLACE, los nuevos registros reemplazan a los antiguos con el mismo valor. Si ni IGNORE ni REPLACE se indican, los valores únicos duplicados dan un error.

Para asegurar que el log de update o binario puede usarse para recrear tablas originales, MySQL no permite inserciones concurrentes durante CREATE TABLE ... SELECT.

13.1.5.1. Cambios tácitos en la especificación de columnas

En algunos casos, MySQL cambia especificaciones de columnas silencioasmente de las dadas en un comando CREATE TABLE o ALTER TABLE . Pueden ser cambiso a un tipo de datos, a atributos asociados con un tipo de datos o a una especificación de índice.

Los posibles cambios de tipos de datos se dan en la siguiente lista. Ocurren antes de MySQL 5.0.3. Desde 5.0.3, ocurre un error si no se puede crear una columna usando el tipo de datos especificado.

Columnas VARCHAR con una longitudo menor que cuatro se cambian a CHAR.

Si cualquier columna en una tabla tiene una longitud variable, el registro entero pasa a tener longitud variable. Por lo tanto, si una tabla contiene cualquier columna de longitud variable (VARCHAR, TEXT, o BLOB), toda columna CHAR con más de tres carácteres se cambia a columna VARCHAR . Esto no afecta cómo usa las columnas en ningún modo; en MySQL, VARCHAR es sólo un modo distinto de almacenar carácteres. MySQL realiza esta conversión porque ahorra espacio y hacer las operaciones de tabla más rápidas. Consulte Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.

Antes de MySQL 5.0.3, una columna CHAR o VARCHAR con una longitud mayor a 255 se convierte al tipo TEXT más pequeño que puede contener valores de la longitud dada. Por ejemplo, VARCHAR(500) se convierte en TEXT, y VARCHAR(200000) se convierte en MEDIUMTEXT. Tenga en cuenta que esta conversión resulta en un cambio de comportamiento del tratamiento de espacios finales.

Conversiones similares ocurren para BINARY y VARBINARY, excepto que se convierten en tipo BLOB.

Desde MySQL 5.0.3, una columna CHAR o BINARY con una longitud mayor a 255 no se convierte silenciosamente . En su lugar, ocurre un error. Desde MySQL 5.0.6 , la conversión silenciosa de columnas VARCHAR y VARBINARY con una longitudo mayor a 65,535 no ocurre si el modo estricto SQL está activado. En su lugar, ocurre un error.

Para una especificación de DECIMAL(M,D), si M no es mayor que D, se ajusta por encima. Por ejemplo DECIMAL(10,10) pasa a ser DECIMAL(11,10).

Otros cambios de columna incluyen cambios de atributos o especficación de índice:

Los tamaños de muestra de TIMESTAMP se descartan. Tenga en cuenta que columnas TIMESTAMP han cambiado considrablemente en versiones recientes de MySQL anteriores a 5.0; para una descripción, consulte Manual de referencia de MySQL 4.1.

Las columnas que son parte de PRIMARY KEY son NOT NULL incluso si no se declaran como tales.

Los espacios finales se borran automáticamente para ENUM y SET cuando se crea la tabla.

MySQL mapea ciertos tipos de columna usados por otras bases de datos SQL a tipos MySQL . Consulte Sección 11.7, “Usar tipos de columnas de otros motores de bases de datos”.

Si incluye una cláusula USING para especificar un tipo de índice que no sea legal para un motor de almacenamiento dado, pero hay otro tipo de índice disponible que puede usar el motor sin afectar el resultado de la consulta, el motor usa el tipo disponible.

Para ver si MySQL usa un tipo de columna distinto al especificado, realice un comando DESCRIBE o SHOW CREATE TABLE tras crear o alterar la tabla.

Otros cambios de tipo de columna pueden ocurrir si comprime una tabla usando myisampack. Consulte Sección 14.1.3.3, “Características de las tablas comprimidas”.

13.1.6. Sintaxis de DROP DATABASE

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE borrar todas las tablas en la base de datos y borrar la base de datos. Sea muy cuidadoso con este comando! Para usarDROP DATABASE, necesita el permiso DROP en la base de datos.

IF EXISTS se usa para evitar un error si la base de datos no existe.

DROP SCHEMA puede usarse desde MySQL 5.0.2.

Si usa DROP DATABASE en una base de datos enlazada simbólicamente, tanto el enlace como la base de datos se borran.

DROP DATABASE retorna el número de tablas que se eliminan. Se corresponde con el número de ficheros .frm borrados.

El comando DROP DATABASE borrar del directorio de base de datos los ficheros y directorios que MySQL puede crear durante operaciones normales:

Todos los ficheros con estas extensiones:

.BAK .DAT .HSH

.MRG .MYD .ISD

.MYI .db .frm

Todos los subdirectorios con nombres que tienen dos dígitos hexadecimales 00-ff. Son subdirectorios usados por tablas RAID . (Estos directorios no se borran desde MySQL 5.0, cuando se eliminó el soporte para tablas RAID . Debe convertir las tablas RAID y eliminar estos directorios manualmente antes de actualizar a MySQL 5.0. Consulte Sección 2.10.1, “Aumentar la versión de 4.1 a 5.0”.)

El fichero db.opt , si existe.

Si permanecen otros ficheros o directorios en el directorio de la base de datos tras que MySQL borre los ficheros listados, el directorio de base de datos no puede borrarse. En este caso, debe borrar cualquier fichero restante manualmente y realizar el comando DROP DATABASE de nuevo.

Puede borrar bases de datos con mysqladmin. Consulte Sección 8.4, “Administrar un servidor MySQL con mysqladmin”.

13.1.7. Sintaxis de DROP INDEX

DROP INDEX index_name ON tbl_name

DROP INDEX borra el índice llamado index_name de la tabla tbl_name. En MySQL 5.0, DROP INDEX se mapea a comando ALTER TABLE para borrar el índice. Consulte Sección 13.1.2, “Sintaxis de ALTER TABLE”.

13.1.8. Sintaxis de DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]

DROP TABLE borra una o más tablas. Debe tener el permiso DROP para cada tabla. Todos los datos de la definición de tabla son borrados, así que tenga cuidado con este comando!

Use IF EXISTS para evitar un error para tablas que no existan. Un NOTE se genera para cada tabla no existente cuando se usa IF EXISTS. Consulte Sección 13.5.4.22, “Sintaxis de SHOW WARNINGS”.

RESTRICT y CASCADE se permiten para hacer la portabilidad más fácil. De momento, no hacen nada.

Nota: DROP TABLE hace un commit automáticamente con la transacción activa,a no ser que use la palabra TEMPORARY.

La palabra TEMPORARY tiene el siguiente efecto:

El comando sólo borra tablas TEMPORARY.

El comando no acaba una transacción en marcha.

No se chequean derechos de acceso. (Una tabla TEMPORARY es visible sólo para el cliente que la ha creado, así que no es necesario.)

Usar TEMPORARY es una buena forma de asegurar que no borra accidentalmente una tabla no TEMPORARY.

13.1.9. Sintaxis de RENAME TABLE

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

Este comando renombra una o más tablas.

La operación de renombrar se hace automáticamente, lo que significa que ningún otro flujo puede acceder a ninguna de las tablas mientras se ejecuta el renombrado. Por ejemplo, si tiene una tabla existente old_table, puede crear otra tabla new_table con la misma estructura pero vacía, y luego reemplazar la tabla existente con la vacía como sigue:

CREATE TABLE new_table (...); RENAME TABLE old_table TO backup_table, new_table TO old_table;

Si el comando renombra más de una tabla, las operaciones de renombrado se realizan de izquierda a derecha. Si quiere intercambiar dos nombres de tablas, puede hacerlo así (asumiendo que no existe ninguna tabla llamada tmp_table):

RENAME TABLE old_table TO tmp_table, new_table TO old_table, tmp_table TO new_table;

Mientras haya dos bases de datos en el mismo sistema de ficheros puede renombrar una tabla para moverla de una base de datos a otra:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

Cuando ejecuta RENAME, no puede tener ninguna tabla bloqueada o transacciones activas. Debe tener los permisos ALTER y DROP en la tabla original, y los permisos CREATE y INSERT en la nueva tabla.

Si MySQL encuentra cualquier error en un renombrado múltiple, hace un renombrado inverso para todas las tablas renombradas para devolver todo a su estado original.

previous page start next page

Capítulo 13. Sintaxis de sentencias SQL

MySQL 5.0