MySQL
ALTERAR MESA

Sintaxis

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

Observaciones

    alter_specification: table_options
      | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
      | ADD [COLUMN] (col_name column_definition,...)
      | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD SPATIAL [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
      | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
      | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
      | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
      | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
      | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
      | DROP [COLUMN] col_name
      | DROP PRIMARY KEY
      | DROP {INDEX|KEY} index_name
      | DROP FOREIGN KEY fk_symbol
      | DISABLE KEYS
      | ENABLE KEYS
      | RENAME [TO|AS] new_tbl_name
      | RENAME {INDEX|KEY} old_index_name TO new_index_name
      | ORDER BY col_name [, col_name] ...
      | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
      | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
      | DISCARD TABLESPACE
      | IMPORT TABLESPACE
      | FORCE
      | {WITHOUT|WITH} VALIDATION
      | ADD PARTITION (partition_definition)
      | DROP PARTITION partition_names
      | DISCARD PARTITION {partition_names | ALL} TABLESPACE
      | IMPORT PARTITION {partition_names | ALL} TABLESPACE
      | TRUNCATE PARTITION {partition_names | ALL}
      | COALESCE PARTITION number
      | REORGANIZE PARTITION partition_names INTO (partition_definitions)
      | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
      | ANALYZE PARTITION {partition_names | ALL}
      | CHECK PARTITION {partition_names | ALL}
      | OPTIMIZE PARTITION {partition_names | ALL}
      | REBUILD PARTITION {partition_names | ALL}
      | REPAIR PARTITION {partition_names | ALL}
      | REMOVE PARTITIONING
      | UPGRADE PARTITIONING
   index_col_name: col_name [(length)] [ASC | DESC]
   index_type: USING {BTREE | HASH}
   index_option: KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'

Cambio de motor de almacenamiento; tabla de reconstrucción cambiar file_per_table

Por ejemplo, si t1 es actualmente una tabla InnoDB, esta declaración cambia su motor de almacenamiento a InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

Si la tabla ya es InnoDB, esto reconstruirá la tabla y sus índices y tendrá un efecto similar a OPTIMIZE TABLE . Puede ganar algo de mejora de espacio en disco.

Si el valor de innodb_file_per_table es actualmente diferente del valor en vigencia cuando se construyó t1 , esto se convertirá a (o desde) file_per_table.

ALTER COLUMNA DE MESA

CREATE DATABASE stackoverflow;

USE stackoverflow;

Create table stack(
    id_user int NOT NULL,
    username varchar(30) NOT NULL,
    password varchar(30) NOT NULL
);

ALTER TABLE stack ADD COLUMN submit date NOT NULL; -- add new column
ALTER TABLE stack DROP COLUMN submit; -- drop column
ALTER TABLE stack MODIFY submit DATETIME NOT NULL; -- modify type column
ALTER TABLE stack CHANGE submit submit_date DATETIME NOT NULL; -- change type and name of column
ALTER TABLE stack ADD COLUMN mod_id INT NOT NULL AFTER id_user; -- add new column after existing column

Tabla ALTER añadir INDEX

Para mejorar el rendimiento, es posible que desee agregar índices a las columnas

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`column_name`)

Alterar para agregar índices compuestos (múltiples columnas)

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`col1`,`col2`)

Cambiar el valor de incremento automático

Cambiar un valor de incremento automático es útil cuando no desea un espacio en una columna AUTO_INCREMENT después de una eliminación masiva.

Por ejemplo, ha publicado muchas filas (publicitarias) no deseadas en su tabla, las eliminó y desea corregir la brecha en los valores de incremento automático. Suponga que el valor MAX de la columna AUTO_INCREMENT es 100 ahora. Puede usar lo siguiente para arreglar el valor de incremento automático.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

Cambiar el tipo de una columna de clave primaria

ALTER TABLE fish_data.fish DROP PRIMARY KEY;
ALTER TABLE fish_data.fish MODIFY COLUMN fish_id DECIMAL(20,0) NOT NULL PRIMARY KEY;

Un intento de modificar el tipo de esta columna sin eliminar primero la clave principal daría como resultado un error.

Cambiar definición de columna

El cambio de la definición de una columna db, la consulta siguiente se puede usar, por ejemplo, si tenemos este esquema db

users (
    firstname varchar(20),
    lastname varchar(20),
    age char(2)
)

Para cambiar el tipo de columna de age de char a int , usamos la siguiente consulta:

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

El formato general es:

ALTER TABLE table_name CHANGE column_name new_column_definition

Renombrando una base de datos MySQL

No hay un solo comando para cambiar el nombre de una base de datos MySQL, pero se puede usar una solución alternativa simple para lograr esto haciendo una copia de seguridad y restaurando:

mysqladmin -uroot -p<password> create <new name>
mysqldump -uroot -p<password> --routines <old name> | mysql -uroot -pmypassword <new name>
mysqladmin -uroot -p<password> drop <old name>

Pasos:

1. Copia las líneas de arriba en un editor de texto.
2. Reemplace todas las referencias a <old name> , <new name> y <password> (+ opcionalmente root para usar un usuario diferente) con los valores relevantes.
3. Ejecute uno por uno en la línea de comandos (suponiendo que la carpeta "bin" de MySQL esté en la ruta e ingrese "y" cuando se le solicite).

Pasos alternativos:

Renombra (mueve) cada tabla de una db a la otra. Haga esto para cada mesa:

RENAME TABLE `<old db>`.`<name>` TO  `<new db>`.`<name>`;

Puedes crear esas declaraciones haciendo algo como

SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO ',
                           'new_db.', table_name)
    FROM information_schema.TABLES
    WHERE table_schema = 'old_db';

Advertencia. No intente hacer ningún tipo de tabla o base de datos simplemente moviendo los archivos en el sistema de archivos. Esto funcionó bien en los viejos tiempos de solo MyISAM, pero en los nuevos días de InnoDB y tablespaces, no funcionará. Especialmente cuando el "Diccionario de datos" se mueve desde el sistema de archivos a las tablas InnoDB del sistema, probablemente en la próxima versión principal. En movimiento (en lugar de sólo DROPping ) una PARTITION de una tabla InnoDB requiere el uso de tablas "transportables". En un futuro cercano, ni siquiera habrá un archivo que alcanzar.

Intercambiando los nombres de dos bases de datos MySQL

Los siguientes comandos se pueden usar para intercambiar los nombres de dos bases de datos MySQL ( <db1> y <db2> ):

mysqladmin -uroot -p<password> create swaptemp
mysqldump -uroot -p<password> --routines <db1> | mysql -uroot -p<password> swaptemp
mysqladmin -uroot -p<password> drop <db1>
mysqladmin -uroot -p<password> create <db1>
mysqldump -uroot -p<password> --routines <db2> | mysql -uroot -p<password> <db1>
mysqladmin -uroot -p<password> drop <db2>
mysqladmin -uroot -p<password> create <db2>
mysqldump -uroot -p<password> --routines swaptemp | mysql -uroot -p<password> <db2>
mysqladmin -uroot -p<password> drop swaptemp

Pasos:

1. Copia las líneas de arriba en un editor de texto.
2. Reemplace todas las referencias a <db1> , <db2> y <password> (+ opcionalmente root para usar un usuario diferente) con los valores relevantes.
3. Ejecute uno por uno en la línea de comandos (suponiendo que la carpeta "bin" de MySQL esté en la ruta e ingrese "y" cuando se le solicite).

Renombrando una tabla MySQL

El cambio de nombre de una tabla se puede hacer en un solo comando:

RENAME TABLE `<old name>` TO `<new name>`;

La siguiente sintaxis hace exactamente lo mismo:

ALTER TABLE `<old name>` RENAME TO `<new name>`;

Si se cambia el nombre de una tabla temporal, se debe usar la versión ALTER TABLE de la sintaxis.

Pasos:

1. Reemplace <old name> y <new name> en la línea de arriba con los valores relevantes. Nota: Si la tabla se está moviendo a una base de datos diferente, el dbname . tablename sintaxis de nombre de tablename se puede usar para <old name> y / o <new name> .
2. Ejecútelo en la base de datos correspondiente en la línea de comandos de MySQL o en un cliente como MySQL Workbench. Nota: El usuario debe tener privilegios ALTER y DROP en la tabla anterior y CREATE e INSERT en la nueva.

Renombrando una columna en una tabla MySQL

El cambio de nombre de una columna se puede hacer en una sola declaración, pero además del nuevo nombre, también se debe especificar la "definición de columna" (es decir, su tipo de datos y otras propiedades opcionales como nulabilidad, incremento automático, etc.).

ALTER TABLE `<table name>` CHANGE `<old name>` `<new name>` <column definition>;

Pasos:

1. Abra la línea de comandos de MySQL o un cliente como MySQL Workbench.
2. Ejecute la siguiente instrucción: SHOW CREATE TABLE <table name>; (reemplazando <table name> con el valor relevante).
3. Tome nota de la definición de la columna completa para la columna que se va a renombrar (es decir, todo lo que aparece después del nombre de la columna pero antes de la coma que lo separa del nombre de la siguiente columna) .
4. Reemplace <old name> , <new name> y <column definition> en la línea anterior con los valores relevantes y luego ejecútelo.