Команда ALTER в MySQL
Когда нам нужно изменить имя таблицы или поля таблицы, нужно использовать команду MySQLALTER.
Команда MySQLALTERиспользуется для изменения структуры таких объектов, как базы данных, таблицы и индексы.
ALTERКоманда позволяет добавлять, изменять или удалять объекты базы данных и может использоваться для таких операций, как изменение определений столбцов таблицы, добавление ограничений, а также создание и удаление индексов.
Команда ALTER очень мощная и позволяет гибко изменять и корректировать базу данных при изменении её структуры.
Ниже приведеныALTERраспространённые способы использования и примеры команды:
1. Добавить столбец
ALTER TABLE table_name ADD COLUMN new_column_name datatype;
Следующая инструкция SQL добавляет в таблицу employees столбец типа date с именем birth_date:
Пример
ADD COLUMN birth_date DATE;
2. Изменить тип данных столбца
Пример
MODIFY COLUMN column_name new_datatype;
Следующая инструкция SQL изменяет тип данных столбца salary в таблице employees на DECIMAL(10,2):
Пример
MODIFY COLUMN salary DECIMAL(10,2);
3. Изменить имя столбца
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype;
Следующая инструкция SQL изменяет имя столбца в таблице employees с old_column_name на new_column_name и также может одновременно изменить тип данных:
Пример
CHANGE COLUMN old_column_name new_column_name VARCHAR(255);
4. Удалить столбец
ALTER TABLE table_name DROP COLUMN column_name;
Следующая инструкция SQL удаляет столбец birth_date из таблицы employees:
Пример
DROP COLUMN birth_date;
5. Добавить PRIMARY KEY
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
Следующая инструкция SQL добавляет первичный ключ в таблицу employees:
Пример
ADD PRIMARY KEY (employee_id);
6. Добавить FOREIGN KEY
ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table (column_name);
Следующая инструкция SQL добавляет внешний ключ в таблицу orders, ссылающийся на столбец customer_id таблицы customers:
Пример
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);
7. Изменение имени таблицы
ALTER TABLE old_table_name RENAME TO new_table_name;
Следующая инструкция SQL изменяет имя таблицы с employees на staff:
Пример
RENAME TO staff;
Примечание:
Но при использовании командыALTERнеобходимо быть особенно осторожным, поскольку некоторые операции могут потребовать перестроения таблиц или индексов, что может повлиять на производительность базы данных и время выполнения.
При внесении важных структурных изменений рекомендуется сначала создать резервную копию данных и действовать осторожно в производственной среде.
Пример
Перед началом руководства этой главы давайте сначала создадим таблицу с именем:testalter_tbl。
Пример
Введите пароль:*******
mysql> USE EXAMPLE;
База данных изменена
mysql> CREATE TABLE testalter_tbl
-> (
-> i INT,
-> c CHAR(1)
-> );
Запрос выполнен, 0 строк затронуто (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Поле | Тип | NULL | Ключ | По умолчанию | Дополнительно |
+-------+---------+------+-----+---------+-------+
| i | INT(11) | ДА | | NULL | |
| c | CHAR(1) | ДА | | NULL | |
+-------+---------+------+-----+---------+-------+
2 строк в наборе (0.00 sec)
Удаление, добавление или изменение полей таблицы
Следующая команда использует команду ALTER и предложение DROP, чтобы удалить поле i из созданной выше таблицы:
mysql> ALTER TABLE testalter_tbl DROP i;
Если в таблице данных остаётся только одно поле, DROP нельзя использовать для удаления этого поля.
В MySQL используйте предложение ADD, чтобы добавить столбец в таблицу данных. В следующем примере в таблицу testalter_tbl добавляется поле i и определяется тип данных:
mysql> ALTER TABLE testalter_tbl ADD i INT;
После выполнения вышеуказанной команды поле i будет автоматически добавлено в конец полей таблицы данных.
mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
Если нужно указать позицию нового поля, можно использовать ключевые слова, предоставляемые MySQL: FIRST (чтобы задать его как первый столбец), AFTER имя_поля (чтобы задать его после определённого поля).
Попробуйте следующую инструкцию ALTER TABLE. После успешного выполнения используйте SHOW COLUMNS, чтобы просмотреть изменения в структуре таблицы:
ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c;
Ключевые слова FIRST и AFTER можно использовать с предложениями ADD и MODIFY, поэтому если вы хотите изменить позицию поля таблицы данных, нужно сначала использовать DROP, чтобы удалить поле, затем использовать ADD, чтобы добавить поле и задать его позицию.
Изменение типа и имени поля
Если нужно изменить тип и имя поля, можно использовать предложение MODIFY или CHANGE в команде ALTER.
Например, чтобы изменить тип поля c с CHAR(1) на CHAR(10), можно выполнить следующую команду:
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
Использование предложения CHANGE имеет довольно отличающийся синтаксис. После ключевого слова CHANGE сразу указывается имя поля, которое вы хотите изменить, затем задаются новое имя и тип поля. Попробуйте следующий пример:
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
Влияние ALTER TABLE на значения NULL и значения по умолчанию
При изменении поля можно указать, содержит ли оно значение или нужно ли задать значение по умолчанию.
В следующем примере поле j указывается как NOT NULL со значением по умолчанию 100.
mysql> ALTER TABLE testalter_tbl
-> MODIFY j BIGINT NOT NULL DEFAULT 100;
Если не задать значение по умолчанию, MySQL автоматически установит для поля значение по умолчанию NULL.
Изменение значения по умолчанию для поля
Вы можете использовать ALTER для изменения значения по умолчанию поля. Попробуйте следующий пример:
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | 1000 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
Вы также можете использовать команду ALTER с предложением DROP, чтобы удалить значение по умолчанию поля, как в следующем примере:
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) Changing a Table Type:
Чтобы изменить тип таблицы данных, можно использовать команду ALTER с предложением TYPE. Попробуйте следующий пример, в котором мы меняем тип таблицы testalter_tbl на MYISAM:
Примечание:Чтобы просмотреть тип таблицы данных, можно использовать оператор SHOW TABLE STATUS.
mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
mysql> SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
Name: testalter_tbl
Type: MyISAM
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 25769803775
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2007-06-03 08:04:36
Update_time: 2007-06-03 08:04:36
Check_time: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Изменение имени таблицы
Если вам нужно изменить имя таблицы данных, можно использовать предложение RENAME в операторе ALTER TABLE.
Попробуйте следующий пример, чтобы переименовать таблицу данных testalter_tbl в alter_tbl:
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
Команду ALTER также можно использовать для создания и удаления индексов в таблицах данных MySQL. Мы представим эту возможность в следующих главах.
Другие расширения