Добавить в таблицу mysql столбец: Как добавить столбец в таблицу MySQL

Пара слов про Alter Table, или как делать не надо / Хабр


Это скорее не статья, а небольшая заметка о некоторых особенностях работы с большими таблицами в MySQL.

Причиной написания стало вроде бы будничное добавление новой колонки в таблицу. Но все оказалось не так просто, как предполагалось.


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

Чтобы было понятнее, характеристики таблицы и базы:

  • размер таблицы 110Gb

  • число строк: 7.5 млн

  • storage engine: InnoDB

  • есть два sql-сервера, соединенных по схеме master-slave, при этом master — на SSD, а slave — на HDD


Вроде бы очевидное решение для добавления колонки — Alter Table.

alter table table_name add source varchar(32)


Им мы и воспользовались (да, мы понимали, что это плохо, но в данном конкретном случае риски были минимальны).

Результаты оказались довольно неприятными:

  • на мастере процесс добавления колонки шел около часа (!)

  • на слейве он начался после окончания процесса на мастере и продолжался около 8 часов (!!)

  • во время выполнения alter table на слейве полностью остановилась репликация данных (!!!)


Но нет худа без добра: небольшой бонус оказался в том, что после добавления колонки размер таблицы уменьшился на 10%.

На графиках ниже это наглядно видно.

График загрузки CPU на мастере.

График загрузки CPU на слейве.

Отставание репликации.

Какие неприятности ждут тех, кто делает это на боевых таблицах?

Во-первых, на время выполнения Alter Table нельзя писать данные в таблицу (но можно читать). На самом деле это зависит от версии MySQL, в последних это не так, но тем не менее надо понимать, на что способна именно Ваша версия, дабы избежать неприятностей.

Соответственно, если таблица большая, то время недоступности будет значительным (как у нас, при использовании SSD это заняло час, а на обычном диске — 8 часов), что вряд ли ожидают Ваши заказчики.

Во-вторых, как в нашем случае, на время выполнения Alter Table на слейве полностью остановилась синхронизация всех таблиц, а не только той, которую мы изменяли. Поэтому в случае, если у Вас данные на втором сервере критичны и должны быть свежими — Вы рискуете остаться без обновлений со всеми вытекающими последствиями.

Еще один неочевидный момент, с которым мы столкнулись во время добавления колонки (но это было в другой раз) — на диске нужно дополнительное место.

Дело в том, что некоторые изменения таблиц пересоздают таблицу с нуля, поэтому места нужно не меньше, чем уже существующая таблица. Для больших таблиц, соответственно, места нужно, мягко говоря, немало. Согласно документации, временная таблица создается в том же каталоге, что и оригинальная.

Кроме того, во время выполнения всяких Alter Table все изменения записываются в лог-файл, чтобы после изменений накатить данные за то время, в течение которого проводилась операция. И тут тоже может ждать неприятный сюрприз: если таблица изменяется долго, а объем операций большой, то может закончится не только место на диске, но и превыситься лимит на размер файла, указанный в настройках SQL. В любом случае Вас ожидает «the online DDL operation fails, and uncommitted concurrent DML operations are rolled back».

Мы столкнулись с тем, что каталог для временных файлов был маловат, в результате пришлось переопределить innodb_tmpdir.

Посмотреть, куда указывает переменная в данный момент, можно так:

select @@GLOBAL.innodb_tmpdir;


Имейте ввиду, что размер временного каталога также может быть нужен размером с таблицу + индексы. В общем, запасайтесь местом.

Дабы не повторять документацию, более детально читайте по ссылке https://dev. mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.html

А как же делать надо? На самом деле нет единого рецепта на все случаи жизни.

Один из возможных вариантов, как делаем мы для таблиц, которые не критичны на обновление:

  • Создаем новую таблицу с нужной структурой

  • Заполняем поля из старой таблицы

  • Удаляем или переименовываем старую таблицу

  • Переименовываем новую


Повторюсь, что это работает для не критичных к обновлению таблиц. И при этом позволяет избежать блокировки репликации. При этом надо учитывать, что заполнение новой таблицы надо делать так, чтобы давать возможность продолжать репликацию, а поскольку она проходит последовательно, то нельзя обойтись одним sql-выражением, надо разбивать на несколько маленьких запросов, между выполнениями которых будет проходить репликация других данных. В других случаях возможны другие варианты, может быть кто-нибудь поделится в комментариях.

UPD. Пользователь syavadee посоветовал использовать percona online schema change. По сути она реализует описанный выше алгоритм с дополнительными плюшками.

UPD. Пользователь arheops рекомендует включить parallel replication/gtid для решения проблем с репликацией.

Ну и попутно, иногда, чтобы понять, насколько большая таблица и сколько в ней строк, нужно, как учат, сделать

select count(*) from table_name


Но на больших и нагруженных таблицах это тоже не самая быстрая операция, особенно когда у вас с пол миллиона строк и больше.

Поэтому для примерной оценки объема можно воспользоваться следующим способом:

SHOW TABLE STATUS FROM express where name='table_name'


К сожалению, на движке InnoDB полученный размер может отличаться процентов на 50 (в нашем случае с таблицей выше реальное число записей порядка 7.5 млн, а указанный способ показал только 5 млн), но для ориентировочной оценки это вполне подходит.

На этом все, надеюсь, заметка кому-то поможет избежать больших неприятностей с якобы безобидными командами SQL.

mysql — добавление нового столбца NOT NULL в существующую таблицу с данными

Задать вопрос

спросил

Изменено
1 год, 1 месяц назад

Просмотрено
2к раз

Я хочу добавить новый NOT NULL в существующую таблицу с данными в MySQL 5.7. Я видел этот вопрос, и я использую предложенное там решение.

Я добавляю новый столбец как NULL , заполняю данные для нового столбца и затем изменяю столбец с NOT NULL на NULL .

 -- 1. добавить новый столбец как нулевой
ALTER TABLE `mytable` ADD COLUMN newCol BIT NULL AFTER curCol;
-- 2.  заполнить данные по умолчанию для нового столбца
УСТАНОВИТЬ sql_safe_updates = 0;
ОБНОВЛЕНИЕ `mytable` SET newCol = 0;
УСТАНОВИТЬ sql_safe_updates = 1;
-- 3. изменить столбец на NOT NULL
ALTER TABLE `mytable` ALTER COLUMN newCol BIT NOT NULL;

Но я получаю следующую ошибку в последней команде:

Код ошибки: 1064. Ошибка в синтаксисе SQL; проверить
руководство, соответствующее вашей версии сервера MySQL, на право
синтаксис для использования рядом с «BIT NOT NULL:» в строке 1

  • MySQL
  • SQL

6

Этот фрагмент SQL недействителен в MySQL:

 ALTER TABLE `mytable` ALTER COLUMN newCol BIT NOT NULL;

Вместо этого рассмотрите:

 ALTER TABLE `mytable` MODIFY newCol BIT NOT NULL;

Ссылка: MySQL ALTER TABLE синтаксис

1

Вы можете сделать это в три шага:

  1. Добавьте новый столбец (изначально пусть он будет иметь NULL значений) 

     ALTER TABLE my_table ADD COLUMN new_col тип данных NULL AFTER cur_col;

  2. Обновить таблицу, чтобы не было NULL в нашем новом столбце

     UPDATE my_table SET new_col = 0, ГДЕ new_col IS NULL;

  3. Измените наш новый столбец на NOT NULL 

     ALTER TABLE my_table MODIFY COLUMN тип данных new_col NOT NULL;

Ссылка: StackOverflow — изменение столбца: от нуля до не нуля

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Обязательно, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

MySQL — создать новую таблицу, добавить новый столбец, изменить и удалить существующий столбец — Dev Bay — советы по интерфейсу

Как разработчики интерфейса, мы не можем оставаться только в области HTML/CSS/JS. Нам нужно время от времени выполнять операции в других областях ИТ, например, в бэкэнде, на стороне сервера или в базах данных. Вероятно, самой популярной базой данных, используемой в веб-разработке, является MySQL. В этих примерах я буду использовать PhpMyAdmin, который позволяет обслуживать базу данных с помощью графического интерфейса или SQL-запросов. Я кратко объясню здесь несколько основных операций с базой данных MySQL, а именно:

Содержание

Создание новой таблицы в MySQL

Чтобы создать новую таблицу в MySQL, нам нужно выполнить следующий код: 

 CREATE TABLE new_table_name (
    имя_столбца ТИП(ДЛИНА[НЕОБЯЗАТЕЛЬНЫЙ]) ДОПОЛНИТЕЛЬНЫЕ_АТРИБУТЫ,
    имя_столбца ТИП(ДЛИНА[НЕОБЯЗАТЕЛЬНЫЙ]) ДОПОЛНИТЕЛЬНЫЕ_АТРИБУТЫ,
    имя_столбца ТИП(ДЛИНА[НЕОБЯЗАТЕЛЬНЫЙ]) ДОПОЛНИТЕЛЬНЫЕ_АТРИБУТЫ,
    ...
)

Но это может выглядеть довольно загадочно, так что посмотрите ниже на реальном примере. Мы создаем новую таблицу в MySQL, которая будет содержать данные клиентов. Нам нужны в качестве столбцов: уникальный ID для каждого клиента, его/ее имя, возраст и некоторое описание. Код выглядит так, как показано ниже, я объясню его дальше.

 СОЗДАТЬ ТАБЛИЦУ клиентов (
id INT(6) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
имя VARCHAR(30) НЕ NULL,
возраст INT(3) НЕ NULL,
описание VARCHAR(255)
)

Сначала мы объявляем имя столбца (как указано выше: идентификатор, имя, возраст, описание) и его длину в скобках, например. (6). Это то, что я назвал «ADDITION_ATTRIBUTES», является своего рода спецификацией и описывает, например. что разрешено в столбце или если у столбца есть важная задача. Ниже я объяснил используемые атрибуты в моем примере:

  • UNSIGNED — столбец может содержать только числа, равные или большие нуля,
  • NOT NULL — данные обязательны для этого поля. Вы не можете оставить это поле пустым (null = пусто),
  • PRIMARY KEY — сделать уникальный порядок строк в таблице,
  • AUTO_INCREMENT — механизм базы данных будет увеличивать значение этого поля на 1 для каждой новой добавленной строки.

Используя простые слова, мы создаем новую таблицу MySQL «клиенты» со следующими 4 полями:

  1. «id» — только целые числа максимальной длины 6 [int(6)], значения должны быть равны или больше 0 [UNSIGNED], это поле не может быть пустым [NOT NULL], это ключевое поле, которое идентифицирует строк в таблице [PRIMARY KEY] и увеличивается на 1 при добавлении новой строки [AUTO INCREMENT].
  2. «имя» — столбец может содержать строковые значения максимальной длины 30 [VARCHAR(30)]  и не может быть пустым [NOT NULL].
  3. «возраст» — только целые числа максимальной длины 3 [int(3)], это поле не может быть пустым [NOT NULL].
  4. «описание» — столбец может содержать строковые значения длиной не более 30 [VARCHAR(255)]

Посмотрите, как создание новой таблицы в MySQL выглядит в PhpMyAdmin на изображениях:

Команда MySQLКоманда MySQLНовая таблица клиентов

 

ОК, теперь создать новую таблицу должно быть легко 🙂

Добавить новый столбец в существующую таблицу MySQL

У нас есть нашу таблицу «клиенты», но мы поняли, что нам нужна еще одна колонка. Этот столбец должен содержать валюту (GBP, EUR, USD…), назначенную каждому пользователю. Это довольно простая операция. Чтобы добавить новый столбец в существующую таблицу MySQL, мы должны использовать следующий синтаксис :

 ИЗМЕНИТЬ ТАБЛИЦУ имя_таблицы
ADD COLUMN new_column_name TYPE(LENGTH[OPTIONAL]) ADDITIONAL_ATTRIBUTES [FIRST|LAST|ПОСЛЕ `other_coumn_name`];;

В нашем примере это будет выглядеть следующим образом: 

 ALTER TABLE клиентов
ДОБАВИТЬ СТОЛБЦУ валюты VARCHAR(3) NOT NULL ПОСЛЕ `name`;

Мы используем команду ALTER TABLE для изменения существующей таблицы MySQL. Затем мы пишем ADD COLUMN в том же синтаксисе, который мы использовали для создания таблицы (выше – шаг 1). Единственная новая вещь заключается в том, что в конце запроса мы должны написать, где должен быть размещен новый столбец. Мы можем написать:

  • ПЕРВЫЙ — тогда это будет первый столбец
  • ПОСЛЕ `existing_column_name`

В моем примере я хочу поместить новый столбец таблицы MySQL сразу после столбца «имя». Пожалуйста, проверьте это на изображениях, которые показывают, как добавить новый столбец в таблицу в PhpMyAdmin с помощью запросов:

MySQL добавить новый столбец в существующую таблицу

 

Добавлен новый столбец MySQL

 

ОК — надеюсь, это было легко 🙂 Перейдем к следующему пункту .

Изменить существующий столбец в MySQL

Итак, теперь мы знаем, как создать новую таблицу в MySQL, как добавить новый столбец, и теперь я покажу, как изменить существующий столбец в таблице MySQL. Это совсем легко.

Например, мы решили, что последний столбец в таблице «клиенты» должен быть длиннее. Это означает, что мы хотим изменить тип столбца с VARCHAR на «TEXT». Синтаксис этого запроса: 

 ALTER TABLE `table_name`
ИЗМЕНИТЬ `column_name` `column_name` TYPE(LENGTH) ADDITIONAL_ATTRIBUTES;

В нашем случае мы должны изменить существующий столбец «описание» в таблице MySQL, поэтому наш запрос будет выглядеть так:

 ALTER TABLE `клиенты`
ИЗМЕНИТЬ `описание` `описание` ТЕКСТ;

Проверьте на изображениях, как изменить существующую таблицу в PhpMyAdmin, используя запрос:

MySQL меняет существующий столбец в таблице

 

MySQL меняет таблицу

 

MySQL меняет таблицу

 

Удалить существующую таблицу в MySQL

Сейчас я покажу, как удалить существующая таблица в MySQL.