Переименование таблицы mysql через консоль
Часто возникает необходимость изменения информации, хранящейся в базе данных. С помощью команды UPDATE вы сможете обновлять данные одной или нескольких колонок в каждой записи таблицы.
Синтаксис запроса на обновление данных.
Для примера заполним таблицу books для хранения книг из условной базы данных Bookstore, которую мы создали в одном из предыдущех постов.
Сначала выберем базу данных, для которой будем выполнять запросы.
Посмотрим какие данные сейчас хранятся в колонках id, title, author, price, discount, amount таблицы books для первых 5 записей.
mysql> SELECT id, title, author, price, discount, amount FROM books LIMIT 5;
+----+--------------------------+-------------------+--------+----------+--------+
| id | title | author | price | discount | amount |
+----+--------------------------+-------------------+--------+----------+--------+
| 1 | Дубровский | Александр Пушкин | 230.00 | 0 | 4 |
| 2 | Нос | Николай Гоголь | 255.20 | 0 | 7 |
| 3 | Мастер и Маргарита | Михаил Булгаков | 240.50 | 0 | 10 |
| 4 | Мёртвые души | Николай Гоголь | 173.00 | 0 | 3 |
| 5 | Преступление и наказание | Фёдор Достоевский | 245.00 | 0 | 3 |
+----+--------------------------+-------------------+--------+----------+--------+
5 rows in set (0.00 sec)
Обновим цену, процент скидки и уменьшим количество на 2 для книги с идентификатором 3.
Теперь проверим как обновились данные.
mysql> UPDATE books
-> SET price=263.00, discount=10, amount=amount-2
-> WHERE id=3;
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT id, title, author, price, discount, amount FROM books LIMIT 5;
+----+--------------------------+-------------------+--------+----------+--------+
| id | title | author | price | discount | amount |
+----+--------------------------+-------------------+--------+----------+--------+
| 1 | Дубровский | Александр Пушкин | 230.00 | 0 | 4 |
| 2 | Нос | Николай Гоголь | 255.20 | 0 | 7 |
| 3 | Мастер и Маргарита | Михаил Булгаков | 263.00 | 10 | 8 |
| 4 | Мёртвые души | Николай Гоголь | 173.00 | 0 | 3 |
| 5 | Преступление и наказание | Фёдор Достоевский | 245.00 | 0 | 3 |
+----+--------------------------+-------------------+--------+----------+--------+
5 rows in set (0.00 sec)
Как переставить колонки в таблице MySQL?
Чтобы переставить колонку используйте команду AFTER, также понадобится повторно определить тип данных.
Переместим колонку с ценой Price на место после колонки Author:
Как добавить колонку к таблице MySQL?
Синтаксис добавления колонки в таблицу:
Для примера добавим к таблице books колонку с именем discount, в которой будет хранится процент скидки на книги и amount для хранения количества книг.
Можно добавлять, удалять и модифицировать сразу несколько колонок за раз.
Каждая новая колонка добавляется в конец таблицы. Если вы хотите добавить новую колонку после определенной колонки, то используйте команду AFTER .
Добавим колонку shelf_position сразу после колонки price.
Обновление данных для нескольких записей в MySQL
Используя команду WHERE можно задавать сразу несколько записей для обновления данных.
Попробуем добавить скидку в 5% для всех книг под авторством Николая Гоголя, а также в название книги добавим слово "(Акция)".
Запрос на обновление данных, в этом случае, будет выглядеть так:
В запросе используется функция CONCAT() , которая прибавляет к текущему значению поля title строку "(Акция)".
В результате получим следующие изменения.
mysql> UPDATE books
-> SET discount=5, title=CONCAT(title, " (Акция)" )
-> WHERE author= "Николай Гоголь" ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT id, title, author, price, discount, amount FROM books LIMIT 5;
+----+--------------------------+--------------------+--------+----------+--------+
| id | title | author | price | discount | amount |
+----+--------------------------+--------------------+--------+----------+--------+
| 1 | Дубровский | Александр Пушкин | 230.00 | 0 | 4 |
| 2 | Нос (Акция) | Николай Гоголь | 255.20 | 5 | 7 |
| 3 | Мастер и Маргарита | Михаил Булгаков | 263.00 | 10 | 8 |
| 4 | Мёртвые души (Акция) | Николай Гоголь | 173.00 | 5 | 3 |
| 5 | Преступление и наказание | Фёдор Достоевский | 245.00 | 0 | 3 |
+---------+--------------------------+--------------------+--------+----------+--------+
5 rows in set (0.00 sec)
Будьте предельно внимательны при обновлении данных в таблицах, так как если вы вдруг забудете задать условие обновления и не напишите команду WHERE , то указанное в запросе обновление будет применено ко всем строкам в таблице!
Пример того, что может получится если не указать команду WHERE .
В результате запроса выше получим одинаковую цену и название для всех книг в таблице.
mysql> UPDATE books SET price=103.90, title= "Барышня-крестьянка" ;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> SELECT id, title, author, price, discount FROM books LIMIT 5;
+----+------------------------+--------------------+--------+----------+
| id | title | author | price | discount |
+----+------------------------+--------------------+--------+----------+
| 1 | Барышня-крестьянка | Александр Пушкин | 103.90 | 0 |
| 2 | Барышня-крестьянка | Николай Гоголь | 103.90 | 5 |
| 3 | Барышня-крестьянка | Михаил Булгаков | 103.90 | 10 |
| 4 | Барышня-крестьянка | Николай Гоголь | 103.90 | 5 |
| 5 | Барышня-крестьянка | Фёдор Достоевский | 103.90 | 0 |
+----+------------------------+--------------------+--------+----------+
5 rows in set (0.00 sec)
Как изменить метаданные о таблице в MySQL?
С помощью команды ALTER можно также изменить некоторые метаданные о таблице.
Для начала давайте их выведем.
Вывод для таблицы books будет выглядеть следующим образом.
mysql> SHOW TABLE STATUS LIKE 'books'\G;
*************************** 1. row ***************************
Name: books
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 24
Avg_row_length: 682
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 5242880
Auto_increment: 25
Create_time: 2019-01-18 20:00:32
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Как удалить колонку из таблицы MySQL?
Синтаксис удаления колонки из таблицы:
Если в таблице осталась только одна колонка, то ее удалить нельзя.
Удалим колонку edition с номером издания книги.
Как изменить значение по умолчанию в колонке таблицы MySQL?
С помощью команды ALTER также можно изменить значение по умолчанию для любой колонки, даже если оно не было установлено при создании таблицы.
Синтаксис установки значения по умолчанию для колонки:
Установим для колонки discount значение по умолчанию:
Чтобы удалить значение по умолчанию, используйте синтаксис:
Удалим созданное значение по умолчанию для колонки discount:
Как изменить имя и тип данных у колонки в таблице MySQL?
Синтаксис смены имени и типа данных у колонки:
Сменим имя у колонки сreation_date на entry_date и тип данных c TIMESTAMP на DATE .
Если вы хотите сменить только тип данных, оставив имя колонки прежним, то воспользуйтесь следующим синтаксисом:
Как изменить имя таблицы MySQL?
Синтаксис переименования таблицы:
Изменим имя таблицы books на books_collection;
Как обновить записи в таблице базы данных MySQL с помощью PHP (PDO)
Для обновления большого количества данных, гораздо удобнее будет воспользоваться HTML-формой, поля которой будут соответствовать полям таблицы из базы данных. Форму разместим в файле index.php .
Перед созданием формы мы обратимся к нашей таблице books и выведем данные, которые в ней хранятся, просто для того чтобы видеть, что содержит таблица и не редактировать ее вслепую.
RENAME TABLE renames one or more tables. You must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.
For example, to rename a table named old_table to new_table , use this statement:
That statement is equivalent to the following ALTER TABLE statement:
RENAME TABLE , unlike ALTER TABLE , can rename multiple tables within a single statement:
Renaming operations are performed left to right. Thus, to swap two table names, do this (assuming that a table with the intermediary name tmp_table does not already exist):
Metadata locks on tables are acquired in name order, which in some cases can make a difference in operation outcome when multiple transactions execute concurrently. See Section 8.11.4, “Metadata Locking”.
As of MySQL 8.0.13, you can rename tables locked with a LOCK TABLES statement, provided that they are locked with a WRITE lock or are the product of renaming WRITE -locked tables from earlier steps in a multiple-table rename operation. For example, this is permitted:
This is not permitted:
Prior to MySQL 8.0.13, to execute RENAME TABLE , there must be no tables locked with LOCK TABLES .
With the transaction table locking conditions satisfied, the rename operation is done atomically; no other session can access any of the tables while the rename is in progress.
If any errors occur during a RENAME TABLE , the statement fails and no changes are made.
You can use RENAME TABLE to move a table from one database to another:
Using this method to move all tables from one database to a different one in effect renames the database (an operation for which MySQL has no single statement), except that the original database continues to exist, albeit with no tables.
Like RENAME TABLE , ALTER TABLE . RENAME can also be used to move a table to a different database. Regardless of the statement used, if the rename operation would move the table to a database located on a different file system, the success of the outcome is platform specific and depends on the underlying operating system calls used to move table files.
If a table has triggers, attempts to rename the table into a different database fail with a Trigger in wrong schema ( ER_TRG_IN_WRONG_SCHEMA ) error.
An unencrypted table can be moved to an encryption-enabled database and vice versa. However, if the table_encryption_privilege_check variable is enabled, the TABLE_ENCRYPTION_ADMIN privilege is required if the table encryption setting differs from the default database encryption.
To rename TEMPORARY tables, RENAME TABLE does not work. Use ALTER TABLE instead.
RENAME TABLE works for views, except that views cannot be renamed into a different database.
Any privileges granted specifically for a renamed table or view are not migrated to the new name. They must be changed manually.
RENAME TABLE tbl_name TO new_tbl_name changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string “ tbl_name _ibfk_ ” to reflect the new table name. InnoDB interprets foreign key constraint names that begin with the string “ tbl_name _ibfk_ ” as internally generated names.
Foreign key constraint names that point to the renamed table are automatically updated unless there is a conflict, in which case the statement fails with an error. A conflict occurs if the renamed constraint name already exists. In such cases, you must drop and re-create the foreign keys for them to function properly.
RENAME TABLE tbl_name TO new_tbl_name changes internally generated and user-defined CHECK constraint names that begin with the string “ tbl_name _chk_ ” to reflect the new table name. MySQL interprets CHECK constraint names that begin with the string “ tbl_name _chk_ ” as internally generated names. Example:
RENAME TABLE renames one or more tables. You must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.
For example, to rename a table named old_table to new_table , use this statement:
That statement is equivalent to the following ALTER TABLE statement:
RENAME TABLE , unlike ALTER TABLE , can rename multiple tables within a single statement:
Renaming operations are performed left to right. Thus, to swap two table names, do this (assuming that a table with the intermediary name tmp_table does not already exist):
Metadata locks on tables are acquired in name order, which in some cases can make a difference in operation outcome when multiple transactions execute concurrently. See Section 8.11.4, “Metadata Locking”.
To execute RENAME TABLE , there must be no active transactions or tables locked with LOCK TABLES . With the transaction table locking conditions satisfied, the rename operation is done atomically; no other session can access any of the tables while the rename is in progress.
If any errors occur during a RENAME TABLE , the statement fails and no changes are made.
You can use RENAME TABLE to move a table from one database to another:
Using this method to move all tables from one database to a different one in effect renames the database (an operation for which MySQL has no single statement), except that the original database continues to exist, albeit with no tables.
Like RENAME TABLE , ALTER TABLE . RENAME can also be used to move a table to a different database. Regardless of the statement used, if the rename operation would move the table to a database located on a different file system, the success of the outcome is platform specific and depends on the underlying operating system calls used to move table files.
If a table has triggers, attempts to rename the table into a different database fail with a Trigger in wrong schema ( ER_TRG_IN_WRONG_SCHEMA ) error.
To rename TEMPORARY tables, RENAME TABLE does not work. Use ALTER TABLE instead.
RENAME TABLE works for views, except that views cannot be renamed into a different database.
Any privileges granted specifically for a renamed table or view are not migrated to the new name. They must be changed manually.
RENAME TABLE tbl_name TO new_tbl_name changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string “ tbl_name _ibfk_ ” to reflect the new table name. InnoDB interprets foreign key constraint names that begin with the string “ tbl_name _ibfk_ ” as internally generated names.
Foreign key constraint names that point to the renamed table are automatically updated unless there is a conflict, in which case the statement fails with an error. A conflict occurs if the renamed constraint name already exists. In such cases, you must drop and re-create the foreign keys for them to function properly.
Процесс развития проекта в некоторых случает требует модификации имен некоторых таблиц, иногда и самих баз данных. Эта заметка о том, как можно посредством консоли MySQL переименовать таблицу в базе данных. Для этих задач в синтаксисе MySQL предусмотрена команда RENAME TABLE. Давайте для наглядного примера использования рассмотрим простую задачу. Предположим, что у нас есть база данных, которая называется project_db, одна из таблиц в базе данных называется t_goods. Необходимо переименовать таблицу t_goods в t_prod.
И самое первое что нужно сделать, это убедиться, что учётная запись, под которой вы работаете позволяет переименовывать таблицы. Бывает, что прав не хватает, и здесь уже следует позаботиться о наличии прав для выполнения данных манипуляций в базе данных.
Для начала рассмотрим синтаксис команды RENAME TABLE, он прост для понимания:
Способ первый
Однако перед его использованием, нам следует указать базу данных, с которой мы хотим работать в консоли MySQL, делается это посредством команды USE.
Указываем базу данных:
После этого можно убедиться, что вы используете нужную базу данных, элементарным образом просмотрев список таблиц в текущей базе данных.
Список таблиц в базе данных
После выполнения команды должен появиться весь список таблиц, которые содержит база project_db. Визуально убеждаемся, что нужная нам таблица в списке, затем переименовываем её уже известной нам командой.
Переименование таблицы
После выполнения команды можно убедиться, что операция прошла успешно, просмотрев список посредством команды SHOW TABLES, о которой уже говорилось выше.
Ну вот, пожалуй, и всё, таблицы таким могут переименованы в угодные вам названия, без потери данных. Кстати хотелось отметить команду SHOW, на самом деле она довольно универсальная и может быть использована не только для просмотра списка таблиц. Так к примеру, можно просмотреть список баз данных, посредством похожей команды.
Просмотр списка баз данных
После выполнения команды, в окне консоли будет показан список всех баз данных на сервере MySQL. Это так же бывает полезно при администрировании сервера.
Способ второй, для MySQL 5.5 (InnoDB)
Первый способ предусматривал предварительное использование команды USE database. Однако, в работе не всегда бывает удобно использовать данный способ, т.к. следует вводить последовательность комманд.
В MySQL версии 5.5 и выше если у вас используется в качестве базы данных InnoDB, то переименование таблицы, а также её перемещение между базами данных выполняется довольно просто.
Переименование таблицы
Таким образом, в команде RENAME TABLE до точки указывается название базы данных, а после точки – имя таблицы. Аналогичным образом можно перемещать таблицы в другую базу данных, с возможностью переименования.
Перемещение таблицы в другую базу данных
Команда переместить таблицу t_goods из базы project_db в базу catalog_db с новым именем t_prod. Согласитесь, в работе может быть это так же удобно. Все данные таблицы при этом остаются в сохранности.
Команда ALTER TABLE используется для осуществления изменений таблицы:
- Добавление колонок
- Удаление колонок
- Модификация колонок
- Изменения имени таблицы
- Изменения кодировки таблицы
- Добавление и удаление ограничений
Для дальнейших примеров будем использовать таблицу books из базы данных Bookstore, которую создали в одном из предыдущих постов.
Чтобы просмотреть изменения колонок в таблице, воспользуйтесь командой:
SHOW COLUMNS FROM table_name;
Перед началом работ выберем базу данных, с которой будем работать.
Читайте также: