知識庫

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.

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 DESCRIBE
tbl_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.

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

mysql> ALTER TABLE t1 CHANGE a b INTEGER;

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

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

ALTER TABLE t1 CHANGE c1 c1 BLOB;

ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

ALTER TABLE tbl_name DISCARD TABLESPACE;

ALTER TABLE tbl_name IMPORT TABLESPACE;

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?.


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?.

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:

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 .

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
USING type_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.

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:

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.

SELECT * FROM tbl_name WHERE auto_col IS NULL

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