Как изменить таблицу в mysql через консоль
Ниже предоставлен список наиболее полезных и часто используемых команд MySQL с примерами.
Это перевод статьи отсюда>>>, с некоторыми незначительными изменениями и дополнениями.
mysql> в начале строки означает, что команда выполняется из MySQL-клиента.
Общие команды
Что бы проверить статус сервера MYSQL выполните:
Что бы подключиться к серверу MySQL из консоли, если сервер MySQL находится на том же хосте:
Работа с базами и таблицами
Работа с базами
Создать базу данных на MySQL сервере:
Показать список всех баз данных на сервере MySQL:
Переключиться для работы с определенной базой данных:
Работа с таблицами
Отобразить все таблицы в базе данных:
Просмотреть формат таблицы в базе:
Показать все содержимое таблицы:
Отобразить количество строк в таблице:
Подсчитать количество колонок в таблице:
Удаление строки в таблице:
mysql> DELETE from [table name] where [field name] = 'whatever';Удаление столбца из таблицы:
mysql> alter table [table name] DROP INDEX [column name];Удалить таблицу из базы:
Работа с колонками
Добавить колонку в таблицу:
mysql> ALTER TABLE [table name] ADD COLUMN [new column name] varchar (20);Изменение имени колонки:
mysql> ALTER TABLE [table name] CHANGE [old column name] [new column name] varchar (50);Создать колонку с уникальным именем, что бы избежать дубликатов в названиях:
mysql> ALTER TABLE [table name] ADD UNIQUE ([column name]);Изменение размера колонки:
mysql> ALTER TABLE [table name] MODIFY [column name] VARCHAR(3);Выборка данных
Показать все содержимое таблицы:
Отобразить колонки и их содержимое в выбранной таблице:
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever"; mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444'; mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number; mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444'; mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;Показать все уникальные записи:
Отобразить выбранные записи, отсортированные по возрастанию ( asc ) или убыванию ( desc ):
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;Регулярные выражения
Импорт и экспорт данных в/из файла
Загрузка файла CSV в таблицу:
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (field1,field2,field3);Пользователи, пароли сервера MySQL:добавление, изменение пользователей и паролей
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username', PASSWORD('password')); mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');Восстановление/изменение пароля root сервера MySQL — остановка MySQL, запуск без таблиц привилегий, подключение под root , установка нового пароля, выход и перезапуск MySQL.
Подробнее о восстановлении пароля root для MySQL написано тут>>>.
Команда ALTER TABLE используется для осуществления изменений таблицы:
- Добавление колонок
- Удаление колонок
- Модификация колонок
- Изменения имени таблицы
- Изменения кодировки таблицы
- Добавление и удаление ограничений
Для дальнейших примеров будем использовать таблицу books из базы данных Bookstore, которую создали в одном из предыдущих постов.
Чтобы просмотреть изменения колонок в таблице, воспользуйтесь командой:
SHOW COLUMNS FROM table_name;
Перед началом работ выберем базу данных, с которой будем работать.
Как добавить колонку к таблице MySQL?
Синтаксис добавления колонки в таблицу:
ALTER TABLE table_name ADD column_name data_type attributes;Для примера добавим к таблице books колонку с именем discount, в которой будет хранится процент скидки на книги и amount для хранения количества книг.
Можно добавлять, удалять и модифицировать сразу несколько колонок за раз.
ALTER TABLE books ADD discount TINYINT UNSIGNED , ADD amount SMALLINT UNSIGNED ;Каждая новая колонка добавляется в конец таблицы. Если вы хотите добавить новую колонку после определенной колонки, то используйте команду AFTER .
Добавим колонку shelf_position сразу после колонки price.
ALTER TABLE books ADD shelf_position VARCHAR (20) AFTER Price;Как удалить колонку из таблицы MySQL?
Синтаксис удаления колонки из таблицы:
Если в таблице осталась только одна колонка, то ее удалить нельзя.
Удалим колонку edition с номером издания книги.
Как переставить колонки в таблице MySQL?
Чтобы переставить колонку используйте команду AFTER, также понадобится повторно определить тип данных.
ALTER TABLE table_name MODIFY COLUMN column_name data_type AFTER column_after_name;Переместим колонку с ценой Price на место после колонки Author:
ALTER TABLE books MODIFY COLUMN Price DECIMAL (15,2) AFTER Author;Как изменить имя и тип данных у колонки в таблице MySQL?
Синтаксис смены имени и типа данных у колонки:
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name new_data_type;Сменим имя у колонки сreation_date на entry_date и тип данных c TIMESTAMP на DATE .
ALTER TABLE books CHANGE COLUMN creation_date entry_date DATE ;Если вы хотите сменить только тип данных, оставив имя колонки прежним, то воспользуйтесь следующим синтаксисом:
ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;Как изменить значение по умолчанию в колонке таблицы MySQL?
С помощью команды ALTER также можно изменить значение по умолчанию для любой колонки, даже если оно не было установлено при создании таблицы.
Синтаксис установки значения по умолчанию для колонки:
ALTER TABLE table_name ALTER column_name SET DEFAULT value ;Установим для колонки discount значение по умолчанию:
Чтобы удалить значение по умолчанию, используйте синтаксис:
ALTER TABLE table_name ALTER column_name DROP DEFAULT ;Удалим созданное значение по умолчанию для колонки discount:
Как изменить имя таблицы MySQL?
Синтаксис переименования таблицы:
Изменим имя таблицы books на books_collection;
Как изменить метаданные о таблице в 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 » Команда ALTER TABLE в MySQL: как добавить, удалить и изменить столбцы
(2 оценок, среднее: 5,00 из 5)
Использование и примеры ALTER TABLE
Во-первых, давайте создадим базу данных и таблицу, которые мы будем называть соответственно «mytest» и «andreyex». Вот как вы это сделаете:
Переименовать таблицу
Синтаксис для переименования таблицы:
Итак, чтобы изменить таблицу andreyex на «myusers», выполните следующие действия:
ALTER TABLE сам по себе ничего не делает. Начните с имени команды и таблицы, а затем укажите изменения, которые необходимо внести.Добавление столбца и изменение свойств столбца
Синтаксис добавления столбца в таблицу:
Чтобы удалить столбец:
Здесь мы добавим строковый столбец «address», набрав:
Добавьте столбец TIMESTAMP с именем «date», введя следующую команду:
Добавьте индекс в столбец с именем «id», выполнив:
Также возможно сделать несколько дополнений одновременно:
Теперь наша таблица должна выглядеть так:
Изменить тип столбца
Синтаксис модификации столбцов:
Чтобы изменить поле адреса, чтобы разрешить более крупные строки:
Мы также можем объединить сразу несколько модификаций:
С помощью этой команды мы также изменили столбец с именем «name» на «lastname» и указали ему значения NULL.
Изменение значения столбца по умолчанию
Чтобы изменить значение по умолчанию для столбца, используйте синтаксис SET DEFAULT:
Теперь наша финальная таблица выглядит так:
Почему вы должны использовать ALTER TABLE?
Внесение изменений в структуру базы данных MySQL через командную строку и ALTER TABLE иногда может быть более быстрым и гибким, чем использование PHPMyAdmin или аналогичных инструментов графического интерфейса.
Команда предлагает еще больше возможностей, которые здесь мы не рассматривали. Для получения дополнительной информации ознакомьтесь с документацией по MySQL или задайте вопрос в разделе комментариев.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
Если в Вашем проекте есть таблицы размер которых исчисляется гигабайтами, а для того чтобы поменять структуру такой таблицы вам на несколько часов приходится останавливать все сервисы — эта статья будет для Вас.
Дано: таблица размером в несколько десятков гигабайт данных. Задача — изменить структуру таблицы.
Сразу забегу в перед, работать метод будет только на транзакционных таблицах. Если у вас MyISAM-таблица на десятки гигабайт, то тут как в том анекдоте — «разбирайтесь сами со своими проблемами». Пример будет приведен для InnoDB таблицы.
Предположим что структура нашей таблицы такая:
Мы хотим добавить в эту таблицу поле last_login.
Какие у нас есть варианты.
В лоб
Вариант прекрасно работет на мелких проектах где размер таблиц редко превышает 50 000 записей. Нам вариант не подходит т.к. ALTER будет выполнятся слишком долго и все это время таблица будет заблокирована как на запись так и на чтение. Соответственно сервис нужно будет останавливать на это время.
Включаем мозг
Можно вообще не трогать таблицу раз уж на то пошло, а сделать отдельную `users_lastvisits`:
Теперь можно во всех запросах где нужен last_login делать JOIN с таблицей last_login. Работать будет, конечно, медленнее, да и в запросах дописывать JOIN тоже лишнее время, но в целом этого иногда бывает достаточно и на этом пункте можно и остановится.
И все таки — нужно добавить поле
Можно поднять master-slave репликацию, сделать ALTER на slave-сервере а потом поменять их местами. Если честно я таким никогда не занимался, может это и проще следующего способа, но не всегда есть возможность поднять репликацию.
Мой способ заключается в следующем
Создаем новую таблицу с конечной структурой, делаем на первой таблицы триггеры, которые будут логировать все изменения, одновременно с этим начинаем переливать данные из первой таблицы во вторую, а по окончании «вливаем» изменившиеся данные и переименовываем таблицы.
Итак, подготавливаем 2 таблицы — первая с нужной структурой, вторая для логирования изменений.
Теперь начинаем переливку. Для этого надо открыть 2 соединения с базой. В одном будет идти собственно переливка, в другом нужно будет ненадолго заблокировать таблицу на запись.
Все, теперь пока таблица переливается у нас есть время подумать как будем вливать изменившиеся с момента начала переливки данные. Тут вообщем то ничего сложного — скрипт приводить я не буду, нужно просто брать по одной записи из таблицы users_updated_rows в том порядке, в котором они добавлялись (сортировать по первичному ключу) и обновлять или удалять её в таблице _users;
Итак, переливка таблицы уже закончилась, нужно вливать остальные данные. Запускаем скрипт. Скрипт должен работать постоянно и обновлять все записи которые добавляются в лог, когда он перельет все данные нужно переименовать таблицы:
Стоит заметит что в этот момент возможна небольшая потеря данных т.к. запросы выполняются не атомарно. Если это критично, лучше выключить сервис на некоторое время чтобы запросов на изменение не было. Можно например забрать права на запись у пользователя а выполнять команды под другим пользователем.
Если все сделать правильно данные не будут потеряны и перерывов в работе сервиса практически не будет. Что нам и требовалось. Таким же способом можно перелить данные на другой сервер, поменяется только способ переливки. Вместо
нужно перелить через mysqldump:
Таким способом мне удалось перелить без остановки работы сервисов на другой сервер таблицу размером в 60Gb и 400 млн строк где то за 12 часов.
Кстати, велосипед уже изобретен Facebook и называется Online Schema Change for MySQL.
В этом учебном пособии вы узнаете, как использовать MySQL оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с синтаксисом и примерами).
Описание
MySQL оператор ALTER TABLE используется для добавления, изменения или удаления столбцов в таблице. Оператор MySQL ALTER TABLE также используется для переименования таблицы.
Добавить столбец в таблицу
Синтаксис
Синтаксис добавления столбца в таблицу MySQL (с использованием оператора ALTER TABLE):
ALTER TABLE table_nameADD new_column_name column_definition
[ FIRST | AFTER column_name ];
table_name - имя таблицы для изменения.
new_column_name - имя нового столбца для добавления в таблицу.
column_definition - тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name - необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, то новый столбец будет добавлен в конец таблицы.
Пример
Рассмотрим пример, который показывает, как добавить столбец в таблицу MySQL, используя оператор ALTER TABLE.
Например:
Этот MySQL пример ALTER TABLE добавит столбец с именем last_name в таблицу contacts . Он будет создан как столбец NOT NULL и появится в таблице после поля contact_id .
Добавить несколько столбцов в таблицу
Синтаксис
Синтаксис добавления нескольких столбцов в таблицу MySQL (с использованием оператора ALTER TABLE):
ALTER TABLE table_nameADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
.
;
table_name - имя таблицы для изменения.
new_column_name - имя нового столбца для добавления в таблицу.
column_definition - тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name - необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, новый столбец будет добавлен в конец таблицы.
Пример
Рассмотрим пример, который показывает, как добавить несколько столбцов в таблицу MySQL, используя оператор ALTER TABLE.
Например:
Этот пример ALTER TABLE добавит в таблицу contacts два столбца - last_name и first_name .
Поле last_name будет создано как столбец varchar (40) NOT NULL и появится в таблице contacts после столбца contact_id . Столбец first_name будет создан как столбец NULL varchar (35) и появится в таблице после столбца last_name .
Изменить столбец в таблице
Синтаксис
Синтаксис для изменения столбца в таблице MySQL (с использованием оператора ALTER TABLE):
ALTER TABLE table_nameMODIFY column_name column_definition
[ FIRST | AFTER column_name ];
table_name - имя таблицы для изменения.
column_name - имя столбца для изменения в таблице.
column_definition - измененный тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name - необязательный. Он сообщает MySQL, где в таблице помещается столбец, если вы хотите изменить его позицию.
Пример
Рассмотрим пример, который показывает, как изменить столбец в таблице MySQL с помощью оператора ALTER TABLE.
Например:
Этот пример ALTER TABLE изменит столбец с именем last_name как тип данных varchar (50) и установит для столбца значения NULL.
Изменить несколько столбцов в таблице
Синтаксис
Синтаксис для изменения нескольких столбцов в таблице MySQL (с использованием оператора ALTER TABLE):
ALTER TABLE table_nameMODIFY column_name column_definition
[ FIRST | AFTER column_name ],
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
.
;
table_name - имя таблицы для изменения.
column_name - имя столбца для изменения в таблице.
column_definition - измененный тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name - необязательный. Он сообщает MySQL, где в таблице помещается столбец, если вы хотите изменить его позицию.
Пример
Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице MySQL, используя оператор ALTER TABLE.
Этот пример ALTER TABLE будет изменять в таблице contacts два столбца - last_name и first_name .
Поле last_name будет изменено на столбец NULL varchar (55) и появится в таблице после столбца contact_type . Столбец first_name будет изменен на столбец varchar (30) NOT NULL (и не изменит позицию в определении таблицы contacts , так как не указано FIRST | AFTER).
Удаление столбца из таблицы
Синтаксис
Синтаксис для удаления столбца из таблицы в MySQL (с использованием оператора ALTER TABLE):
Например:
table_name - имя таблицы для изменения.
column_name - имя столбца для удаления из таблицы.
Пример
Рассмотрим пример, который показывает, как удалить столбец из таблицы в MySQL с помощью оператора ALTER TABLE.
Например:
Этот пример ALTER TABLE удаляет столбец с именем contact_type из таблицы contacts .
Переименование столбца в таблице
Синтаксис
Синтаксис для переименования столбца в таблице MySQL (с использованием оператора ALTER TABLE):
ALTER TABLE table_nameCHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ];
table_name - имя таблицы для изменения.
old_name - столбец для переименования.
new_name - новое имя столбца.
column_definition - тип данных и определение столбца (NULL или NOT NULL и т. д.). Вы должны указать определение столбца при переименовании столбца, даже если оно не изменится.
FIRST | AFTER column_name - необязательный. Он сообщает MySQL, где в таблице помещается столбец, если вы хотите изменить его позицию.
Пример
Рассмотрим пример, который показывает, как переименовать столбец в таблице MySQL с помощью оператора ALTER TABLE.
Например:
Читайте также: