MySQL
ALTER TABLE

Синтаксис

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

замечания

    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'

Изменение механизма хранения; перестроить таблицу; изменить file_per_table

Например, если t1 в настоящее время не является таблицей InnoDB, это утверждение изменяет механизм хранения на InnoDB:

ALTER TABLE t1 ENGINE = InnoDB;

Если таблица уже InnoDB, это приведет к восстановлению таблицы и ее индексов и эффекта, аналогичного OPTIMIZE TABLE . Вы можете немного улучшить дисковое пространство.

Если значение innodb_file_per_table в настоящее время отличается от значения, действующего при построении t1 , оно преобразуется в (или из) file_per_table.

ALTER COLUMN OF TABLE

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

ALTER table добавить INDEX

Чтобы повысить производительность, вы можете добавить индексы в столбцы

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

изменение для добавления составных (нескольких столбцов) индексов

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

Изменить значение автоинкремента

Изменение значения автоматического прироста полезно, когда вы не хотите, чтобы пробел в столбце AUTO_INCREMENT после массивного удаления.

Например, у вас появилось много нежелательных (рекламных) строк, размещенных в вашей таблице, вы удалили их, и вы хотите исправить пробел в значениях автоматического увеличения. Предположим, что значение MAX столбца AUTO_INCREMENT равно 100. Вы можете использовать следующее, чтобы исправить значение автоинкремента.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

Изменение типа столбца первичного ключа

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

Попытка изменить тип этого столбца без первоначального удаления первичного ключа приведет к ошибке.

Изменить определение столбца

Изменение определения столбца db может быть использовано, например, для запроса, если у нас есть эта схема db

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

Чтобы изменить тип столбца age от char до int , мы используем следующий запрос:

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

Общий формат:

ALTER TABLE table_name CHANGE column_name new_column_definition

Переименование базы данных MySQL

Нет единой команды для переименования базы данных MySQL, но для ее достижения можно использовать простой способ обхода путем резервного копирования и восстановления:

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>

шаги:

1. Скопируйте строки выше в текстовый редактор.
2. Замените все ссылки на <old name> , <new name> и <password> (+ необязательно root для использования другого пользователя) с соответствующими значениями.
3. Выполняйте один за другим в командной строке (при условии, что папка «bin» MySQL находится в пути и вводит «y» при появлении запроса).

Альтернативные шаги:

Переименуйте (переместите) каждую таблицу с одной базы данных на другую. Сделайте это для каждой таблицы:

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

Вы можете создавать эти заявления, делая что-то вроде

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

Предупреждение. Не пытайтесь делать какие-либо таблицы или базы данных, просто перемещая файлы в файловой системе. Это хорошо работало в старые времена только MyISAM, но в новые дни InnoDB и табличных пространств это не сработает. Особенно, когда «Словарь данных» перемещается из файловой системы в системные таблицы InnoDB, возможно, в следующем крупном выпуске. Перемещение (в отличие от просто DROPping ) PARTITION таблицы InnoDB требует использования «переносных табличных пространств». В ближайшем будущем даже не будет файла.

Обмен именами двух баз данных MySQL

Следующие команды могут использоваться для замены имен двух баз данных MySQL ( <db1> и <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

шаги:

1. Скопируйте строки выше в текстовый редактор.
2. Замените все ссылки на <db1> , <db2> и <password> (+ необязательно root для использования другого пользователя) с соответствующими значениями.
3. Выполняйте один за другим в командной строке (при условии, что папка «bin» MySQL находится в пути и вводит «y» при появлении запроса).

Переименование таблицы MySQL

Переименование таблицы может выполняться одной командой:

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

Следующий синтаксис делает то же самое:

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

Если переименовать временную таблицу, следует использовать версию синтаксиса ALTER TABLE .

шаги:

1. Замените <old name> и <new name> в строке выше соответствующими значениями. Примечание. Если таблица перемещается в другую базу данных, имя dbname . Синтаксис tablename можно использовать для <old name> и / или <new name> .
2. Выполните его в соответствующей базе данных в командной строке MySQL или в клиенте, таком как MySQL Workbench. Примечание. Пользователь должен иметь привилегии ALTER и DROP на старой таблице и CREATE и INSERT на новом.

Переименование столбца в таблице MySQL

Переименование столбца может быть выполнено в одном заявлении, но также как и новое имя, также должно быть указано «определение столбца» (то есть его тип данных и другие необязательные свойства, такие как обнуление, автоматическое увеличение и т. Д.).

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

шаги:

1. Откройте командную строку MySQL или клиент, например MySQL Workbench.
2. Выполните следующий оператор: SHOW CREATE TABLE <table name>; (заменив <table name> на соответствующее значение).
3. Запишите полное определение столбца для столбца, который нужно переименовать (т.е. все, что появляется после имени столбца, но перед запятой, отделяющей его от следующего имени столбца) .
4. Замените <old name> , <new name> и <column definition> в строке выше соответствующими значениями и затем выполните их.