Sql как изменить порядок столбцов

I think what everyone here is missing is that although not everyone has to deal with 10’s, 20’s, or 1000’s instances of the same software system installed throughout the country and world, those of us that design commercially sold software do so. As a result, we expand systems over time, expand tables by adding fields as new capability is needed, and as those fields are identified do belong in an existing table, and as such, over a decade of expanding, growing, adding fields, etc to tables, and then having to work with those tables from design, to support, to sometimes digging into raw data/troubleshooting to debug new functionality bugs, it is incredibly aggravating to not have the primary information you want to see within the first handful of fields, when you may have tables with 30, 40, 50, or even 90 fields, and yes, in a strictly normalized database.

I’ve often wished I could do this, for this exact reason. But short of doing exactly what SQL does, building a Create Script for a new Table the way I want it, writing the Insert to it, then dropping all existing constraints, relationships, keys, index, etc etc from the existing table and renaming the «new» table back to the old name, and then reading all those keys, relationships, index, etc etc ….

It’s not only tedious, time-consuming, but … in five more years, it will need to happen again.

It’s so close to worth that massive amount of work, however the point is, it won’t be the last time we need this ability, since our systems will continue to grow, expand, and get fields in a wacked ordered driven by need/design additions.

A majority of developers think from a single system standpoint that serves a single company or very specific hard box market.

The «off-the-shelf» but significantly progressive designers and leaders of development in their market space will always have to deal with this problem, over and over, and would love a creative solution if anyone has one. This could easily save my company a dozen hours a week, just not having to scroll over, or remember where «that» field is in the source data table.

I think what everyone here is missing is that although not everyone has to deal with 10’s, 20’s, or 1000’s instances of the same software system installed throughout the country and world, those of us that design commercially sold software do so. As a result, we expand systems over time, expand tables by adding fields as new capability is needed, and as those fields are identified do belong in an existing table, and as such, over a decade of expanding, growing, adding fields, etc to tables, and then having to work with those tables from design, to support, to sometimes digging into raw data/troubleshooting to debug new functionality bugs, it is incredibly aggravating to not have the primary information you want to see within the first handful of fields, when you may have tables with 30, 40, 50, or even 90 fields, and yes, in a strictly normalized database.

I’ve often wished I could do this, for this exact reason. But short of doing exactly what SQL does, building a Create Script for a new Table the way I want it, writing the Insert to it, then dropping all existing constraints, relationships, keys, index, etc etc from the existing table and renaming the «new» table back to the old name, and then reading all those keys, relationships, index, etc etc ….

It’s not only tedious, time-consuming, but … in five more years, it will need to happen again.

It’s so close to worth that massive amount of work, however the point is, it won’t be the last time we need this ability, since our systems will continue to grow, expand, and get fields in a wacked ordered driven by need/design additions.

A majority of developers think from a single system standpoint that serves a single company or very specific hard box market.

The «off-the-shelf» but significantly progressive designers and leaders of development in their market space will always have to deal with this problem, over and over, and would love a creative solution if anyone has one. This could easily save my company a dozen hours a week, just not having to scroll over, or remember where «that» field is in the source data table.

MySQL-инструкция ALTER TABLE изменяет структуру таблицы. Например, можно добавлять или удалять столбцы, создавать или уничтожать индексы, изменять тип существующих столбцов или переименовывать столбцы или саму таблицу. Также можно изменить такие характеристики, как механизм хранения, используемый для таблицы, или комментарий к таблице.

Содержание:

Синтаксис многих допустимых изменений подобен инструкциям CREATE TABLE. По этому, в материале будут рассмотрены только особенности и различие синтаксиса ALTER TABLE.

• Синтаксис инструкции ALTER TABLE;
  • Описание параметров, используемых ALTER TABLE;
  • Комментарии к синтаксису ALTER TABLE;
• Добавление/изменение/удаление столбцов таблицы;
• Добавление/изменение/удаление PRIMARY KEY и INDEX;
• Добавление/изменение/удаление внешнего FOREIGN KEY;
• Добавление/изменение/удаление проверки CHECK;
• Изменение набора символов (кодировки);
• Примеры практического использования ALTER TABLE.

Синтаксис инструкции ALTER TABLE.

ALTER TABLE tbl_name
  -- изменение свойств таблицы 
    RENAME [TO | AS] new_tbl_name
    CHARACTER SET=charset_name [COLLATE=collation_name]
    CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
    -- добавление/изменение/удаление столбцов 
    -- изменяемой таблицы  
    ADD COLUMN col_name column_definition [FIRST | AFTER col_name]
    CHANGE COLUMN old_col_name new_col_name column_definition 
          [FIRST | AFTER col_name]
    MODIFY COLUMN col_name column_definition
          [FIRST | AFTER col_name]
    ALTER COLUMN col_name SET DEFAULT literal | (expr) 
    ALTER COLUMN col_name DROP DEFAULT
    RENAME COLUMN old_col_name TO new_col_name
    DROP COLUMN col_name
    -- добавление/изменение/удаление индексированных
    -- столбцов изменяемой таблицы  
    ADD INDEX [index_name] (col_name1[, col_name2, ... ]) [USING BTREE | HASH]
    ADD UNIQUE KEY [index_name] [USING BTREE | HASH]
    ADD FULLTEXT | SPATIAL [INDEX] [index_name]
    RENAME INDEX old_index_name TO new_index_name
    DROP INDEX index_name
    ADD PRIMARY KEY reference_definition
    DROP PRIMARY KEY
    ADD [CONSTRAINT index_name] FOREIGN KEY reference_definition
    DROP FOREIGN KEY index_name
    -- добавление/изменение/удаление индексированных
    -- столбцов изменяемой таблицы  
    ADD [CONSTRAINT check_name] CHECK (expr) [[NOT] ENFORCED]
    ALTER CHECK check_name [NOT] ENFORCED
    DROP CHECK check_name

Описание параметров, используемых ALTER TABLE.

• tbl_name: имя изменяемой таблицы
• new_tbl_name: новое уникальное имя изменяемой таблицы
• charset_name: имя кодировки, в которой будут храниться символьные и текстовые типы данных столбцов таблицы. Список доступных кодировок можно посмотреть командой SHOW CHARACTER SET;. Подробнее в материале «Преобразование типов и кодировки в БД MySQL».
• collation_name: имя кодировки, которая будет использоваться при поиске/сравнении/сортировке символьных и текстовых типов данных. Список доступных кодировок можно посмотреть командой SHOW COLLATION;. Подробнее в материале «Преобразование типов и кодировки в БД MySQL».
• col_name: имя столбца изменяемой таблицы
• column_definition: определение столбца изменяемой таблицы подобно синтаксису CREATE TABLE. Подробнее в материале «Создание таблиц CREATE TABLE в БД MySQL».
• old_col_name: имя изменяемого столбца
• new_col_name: новое уникальное имя изменяемого столбца
• literal: значение по умолчанию, которое будет принимать столбец, если его значение не указано в инструкции INSERT при добавлении записи в таблицу. Тип этого значения должен соответствовать значению data_type.
• (expr) — выражение заключенное в скобки. Это выражение, значение которого будет использоваться соответствующим оператором. Обычно выражение содержит ограниченные конструкции. Если выражение будет содержать запрещенные конструкции, то возникает ошибка.
• col_name1/col_name2: строка, уникальное имя столбца/колонки в создаваемой таблице.
• old_index_name: имя изменяемого индекса
• index_name: уникальное имя индекса
• new_index_name: новое уникальное имя изменяемого индекса
• check_name необязательное уникальное имя ограничения, которое используется для проверки вставляемых значений в колонки таблицы. Если оно не задано, то MySQL генерирует его самостоятельно.

Комментарии к синтаксису ALTER TABLE:

1. Чтобы использовать инструкцию ALTER TABLE, необходимы привилегии пользователя ALTER, CREATE и INSERT для таблицы. Переименование таблицы требует привилегий ALTER и DROP.

2. После имени таблицы tbl_name необходимо указать изменения, которые необходимо внести. Если ничего не указано, то инструкция ALTER TABLE ничего не делает.

3. Для операторов ADD и CHANGE в параметре column_definition используется тот же синтаксис, что и для инструкции CREATE TABLE.

4. Слово COLUMN является необязательным и может быть опущено, за исключением RENAME COLUMN (чтобы отличить операцию переименования столбца от операции переименования таблицы RENAME).

5. В одном операторе ALTER TABLE допускается использование нескольких операторов ADD, ALTER, DROP и CHANGE, разделенных запятыми. Это расширение MySQL для стандартного SQL, которое допускает только одно из каждого предложения для оператора ALTER TABLE. Например, чтобы удалить несколько столбцов в одном запросе, можно сделать следующее:

   ALTER TABLE t2 
     DROP COLUMN c, 
     DROP COLUMN d;
   

6. Если при использовании ALTER TABLE для изменения CHARACTER SET или COLLATE появляется ошибка повторяющегося ключа, то причина либо в том, что новое COLLATE сопоставляет два ключа с одним и тем же значением, либо в том, что таблица повреждена. В последнем случае необходимо запустить команду REPAIR TABLE для таблицы. REPAIR TABLE работает с таблицами MyISAM, ARCHIVE и CSV.

7. Если ALTER TABLE используется для движка InnoDB или MySQL работает под Windows, то ALTER TABLE разблокирует таблицу, если она заблокирована LOCK TABLE. Это происходит потому, что InnoDB и Windows не могут удалить таблицу, которая используется.

Добавление/изменение/удаление столбцов таблицы.

Инструкции ALTER ... SET DEFAULT ... или ALTER ... DROP DEFAULT указывает новое значение по умолчанию для столбца или удаляют старое значение по умолчанию соответственно. Если старое значение по умолчанию удалено, то новое значение по умолчанию будет равно NULL. Если столбец не может быть NULL, то MySQL будет пытаться присвоить значение по умолчанию которое возможно для установленного в столбце типа.

Операторы CHANGE, MODIFY, RENAME COLUMN и ALTER позволяют изменять имена и определения существующих столбцов. Они имеют следующие сравнительные характеристики:

• CHANGE:

  • Можно переименовать столбец и изменить его определение или и то, и другое.
  • Имеет больше возможностей, чем MODIFY или RENAME COLUMN за счет удобства некоторых операций.
    • если нужно только переопределить параметры хранения данных, то необходимо дважды указать имя столбца.
    • если нужно только переименовать столбец, то необходимо повторно переопределить параметры хранения данных.
  • С атрибутами FIRST или AFTER col_name можно изменить порядок столбцов.

• MODIFY:

  • Можно изменить определение столбца, но не его имя.
  • Более удобно, чем CHANGE, чтобы изменить определение столбца, не переименовывая его.
  • С атрибутами FIRST или AFTER col_name можно изменить порядок столбцов.

• RENAME COLUMN:

  • Можно изменить имя столбца, но не его определение.
  • Более удобно, чем CHANGE, для переименования столбца без изменения его определения.

• ALTER:

  • используется только для изменения значения столбца по умолчанию DEFAULT.

Инструкция CHANGE — это расширение MySQL для стандартного SQL. MODIFY и RENAME COLUMN — это расширения MySQL для совместимости с Oracle.

Для изменения имени И определения столбца, лучше использовать оператор CHANGE, который позволяет менять все за один запрос. Например, чтобы переименовать столбец с a на b и изменить его определение INT NOT NULL для использования типа данных BIGINT, сохранив при этом атрибут NOT NULL, нужно сделать следующее:

ALTER TABLE t1 
  CHANGE a b BIGINT NOT NULL;

Чтобы изменить ТОЛЬКО определение столбца, можно использовать CHANGE или MODIFY. Синтаксис CHANGE требует дважды указывать одно и то же имя столбца, чтобы оставить его без изменений. Например, чтобы изменить определение столбца b, нужно сделать следующее:

ALTER TABLE t1 
  CHANGE b b INT NOT NULL;

Оператор MODIFY более удобен для изменения определения без изменения имени:

ALTER TABLE t1 
  MODIFY b INT NOT NULL;

Чтобы ТОЛЬКО переименовать столбец, а определение оставить прежним, можно использовать CHANGE или RENAME COLUMN. С оператором CHANGE синтаксис требует повторного указания определения столбца (которое столбец имеет в настоящее время). Например, чтобы переименовать столбец b, который имеет определение INT NOT NULL на a с тем же определением, нужно сделать следующее:

ALTER TABLE t1 
  CHANGE b a INT NOT NULL;

С оператором RENAME COLUMN удобнее изменять имя столбца без изменения его определения:

ALTER TABLE t1 RENAME COLUMN b TO a;

Для изменений определения столбца с помощью CHANGE или MODIFY определение должно включать тип данных и все атрибуты, которые должны применяться к новому столбцу, кроме атрибутов индекса, таких как PRIMARY KEY или UNIQUE. Атрибуты, присутствующие в исходном определении, но не указанные для нового определения, не переносятся. Предположим, что столбец col1 определен как INT UNSIGNED DEFAULT 1 COMMENT 'столбец'. Пробуем изменить столбец, намереваясь изменить только INT на BIGINT:

ALTER TABLE t1 
  MODIFY col1 BIGINT;

Этот запрос изменяет тип данных с INT на BIGINT, но также удаляет атрибуты UNSIGNED, DEFAULT и COMMENT. Чтобы сохранить их, инструкция должна включать их явно:

ALTER TABLE t1 
  MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'столбец';

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

Предупреждение. Это преобразование может привести к изменению данных. Например, если сократить строковый столбец, то значения могут быть усечены. Чтобы предотвратить потерю информации, если преобразование в новый тип приведет к изменению данных, необходимо включить строгий режим SQL.

Если использовать CHANGE или MODIFY для уменьшения размера хранимых данных в столбце, для которого существует индекс, а результирующая длина столбца меньше, чем длина индекса, то MySQL автоматически укорачивает индекс.

Для столбцов, переименованных CHANGE или RENAME COLUMN, MySQL не переименовывает автоматически ссылки на эти столбцы, которые используются в представлениях и/или сохраненных функциях и процедурах. Все эти ссылки необходимо изменить вручную.

Чтобы изменить порядок столбцов в таблице, необходимо в операциях CHANGE или MODIFY использовать атрибуты FIRST и AFTER col.

Добавление/изменение/удаление PRIMARY KEY и INDEX.

Инструкция DROP PRIMARY KEY удаляет первичный ключ. Если первичного ключа нет, то возникает ошибка.

Если включена системная переменная sql_require_primary_key, попытка удалить первичный ключ приведет к ошибке.

Если в таблицу добавлен UNIQUE INDEX или PRIMARY KEY, то MySQL сохранит его перед любым неуникальным индексом, чтобы разрешить обнаружение повторяющихся ключей как можно раньше.

Инструкция DROP INDEX удаляет индекс. Это расширение MySQL для стандартного SQL. Чтобы посмотреть имена индексов таблицы, необходимо использовать команду SHOW INDEX FROM tbl_name.

Некоторые механизмы хранения позволяют указать тип индекса при их создании. Синтаксис USING BTREE | HASH.

Инструкция RENAME INDEX old_index_name TO new_index_name переименовывает индекс. Это расширение MySQL для стандартного SQL. Содержимое таблицы остается неизменным. Параметр old_index_name должен быть именем существующего индекса в таблице, который не удаляется тем же оператором ALTER TABLE. Параметр new_index_name это новое имя индекса, которое не может дублировать имя индекса в результирующей таблице после применения изменений. Ни одно из имен индексов не может иметь имя PRIMARY.

Если вы используете ALTER TABLE для таблицы MyISAM, все неуникальные индексы создаются в отдельном пакете (как для REPAIR TABLE). Это должно сделать ALTER TABLE намного быстрее, когда у вас много индексов.

После операций по изменению индексов может потребоваться запустить команду ANALYZE TABLE для обновления информации о количестве элементов индекса.

Добавление/изменение/удаление внешнего FOREIGN KEY.

Для ALTER TABLE, в отличие от CREATE TABLE, инструкция ADD FOREIGN KEY игнорирует index_name, если оно задано, и использует автоматически сгенерированное имя внешнего ключа. В качестве обходного пути, чтобы указать имя внешнего ключа, необходимо дополнительно указать оператор CONSTRAINT index_name:

ALTER TABLE t1 
  ADD CONSTRAINT index_name FOREIGN KEY (....) ...

Важно. MySQL молча игнорирует встроенные спецификации REFERENCES, где ссылки определены как часть спецификации столбца. MySQL принимает только предложения REFERENCES, определенные как часть отдельной спецификации FOREIGN KEY.

Добавление/изменение/удаление проверки CHECK.

До версии MySQL 8.0.16 (узнать версию SELECT VERSION();) оператор ALTER TABLE допускал только ограниченную версию синтаксиса: ADD CHECK (expr), которая анализировалась и игнорировалась.

Начиная с MySQL 8.0.16, оператор ALTER TABLE позволяет добавлять, удалять или изменять ограничения CHECK.

Добавление нового ограничения CHECK. Значение элементов синтаксиса такое же, как и для CREATE TABLE:

ALTER TABLE tbl_name
    ADD [CONSTRAINT check_name] CHECK (expr) [[NOT] ENFORCED];

Удаление существующего ограничения CHECK с именем check_name:

ALTER TABLE tbl_name
    DROP CHECK check_name;

Включение ENFORCED или выключение NOT ENFORCED проверки ограничения CHECK с именем check_name:

ALTER TABLE tbl_name
    ALTER CHECK check_name [NOT] ENFORCED;

Предложения DROP CHECK и ALTER CHECK являются расширениями MySQL для стандартного SQL.

Начиная с MySQL 8.0.19 (узнать версию SELECT VERSION();), ALTER TABLE допускает более общий (и стандартный для SQL) синтаксис для удаления и изменения существующих ограничений любого типа, где тип ограничения определяется по имени ограничения:

Удаление существующего ограничения с именем check_name:

ALTER TABLE tbl_name
    DROP CONSTRAINT check_name;

Если включена системная переменная sql_require_primary_key, то попытка удалить первичный ключ приведет к ошибке.

Включение/выключение проверки ограничения CHECK с именем check_name:

ALTER TABLE tbl_name
    ALTER CONSTRAINT symbol [NOT] ENFORCED;

Если изменение таблицы приводит к нарушению принудительного ограничения CHECK, то возникает ошибка, и таблица не изменяется. Примеры операций, при которых возникает ошибка:

• Попытки добавить атрибут AUTO_INCREMENT к столбцу, используемому в ограничении CHECK.
• Попытки добавить принудительное ограничение CHECK или применить непринудительное ограничение CHECK, для которых существующие строки нарушают условие ограничения.
• Попытки изменить, переименовать или удалить столбец, который используется в ограничении CHECK, если это ограничение также не удалено в том же операторе. Исключение: если ограничение CHECK относится только к одному столбцу, то удаление столбца автоматически удаляет ограничение.

Изменение набора символов (кодировки).

Чтобы изменить набор символов (кодировку) таблицы по умолчанию и все столбцы символов CHAR, VARCHAR, TEXT на новый набор символов (кодировку), нужно использовать следующий запрос:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Запрос также изменяет сопоставление COLLATE всех столбцов символов. Если не указать предложение COLLATE, то оператор CHARACTER SET использует сопоставление по умолчанию для указанного набора символов (кодировки). Если это сопоставление не подходит для предполагаемого использования таблицы (например, если оно изменится с сопоставления с учетом регистра на сопоставление без учета регистра), то необходимо указать сопоставление COLLATE явно.

Для столбца с типом данных VARCHAR или одним из типов TEXT команда CONVERT TO CHARACTER SET изменяет тип данных по мере необходимости. Такое изменение необходимо, чтобы новый столбец был достаточно длинным для хранения такого же количества символов, как исходный столбец. Например, столбец TEXT имеет два байта длины, которые хранят длину значений в байтах в столбце, максимум до 65 535. Для столбца latin1 TEXT каждому символу требуется один байт, поэтому столбец может хранить до 65 535 символов. Если столбец преобразован в utf8, то для каждого символа может потребоваться до трех байтов, при максимально возможной длине 3 × 65535 = 196605 байт. Эта длина не умещается в байтах длины столбца TEXT, поэтому MySQL преобразует тип данных в MDIUMTEXT, который является наименьшим строковым типом, для которого байты длины могут записывать значение 196 605. Точно так же столбец VARCHAR может быть преобразован в MEDIOMTEXT.

Чтобы избежать только что описанных изменений типа данных, не используйте CONVERT TO CHARACTER SET. Вместо этого, для изменения отдельных столбцов нужно использовать оператор MODIFY. Например:

ALTER TABLE t 
  MODIFY colText_latin1 TEXT CHARACTER SET utf8;
ALTER TABLE t 
  MODIFY colVarchar_latin1 VARCHAR(M) CHARACTER SET utf8;

Предупреждение. Операция CONVERT TO преобразует значения столбца между исходным и именованным наборами символов. Это не то, что обычно нужно. Например, если есть столбец с одной кодировкой (например, latin1), но сохраненные значения фактически используют другую, несовместимую кодировку (например, utf8), то в этом случае необходимо сделать следующее для каждого такого столбца:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

Причина, по которой это работает, заключается в том, что при преобразовании в тип BLOB или из BLOB — преобразования не происходит.

Чтобы изменить только набор символов (кодировку) по умолчанию для таблицы, необходимо использовать запрос:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

Оператор DEFAULT не является обязательным. Набор символов (кодировка) по умолчанию — это набор символов, который используется для хранения данных символьных и текстовых столбцов, если столбец добавляется позже (например, с помощью ALTER TABLE ... ADD column).

Если включена системная переменная foreign_key_checks (это настройкой по умолчанию), то преобразование набора символов не разрешено для таблиц, содержащих столбец, используемый в FOREIGN KEY. Обходной путь заключается в отключении foreign_key_checks перед выполнением преобразования набора символов. Необходимо выполнить преобразование в обеих таблицах, участвующих в ограничении внешнего ключа, прежде чем включить foreign_key_checks. Если включить foreign_key_checks после преобразования только одной из таблиц, то операция ON DELETE CASCADE или ON UPDATE CASCADE может повредить данные в ссылочной таблице из-за неявного преобразования, которое происходит во время этих операций.

Примеры практического использования ALTER TABLE.

Начнем с таблицы t1, созданной, как показано здесь:

CREATE TABLE t1 (a INTEGER, b CHAR(10));

Переименуем таблицу с t1 на t2:

ALTER TABLE t1 RENAME t2;

Изменим столбец a с INTEGER на TINYINT NOT NULL (оставив имя прежним) и изменим столбец b с CHAR(10) на CHAR(20), а также переименуем его с b на c:

ALTER TABLE t2 
  MODIFY a TINYINT NOT NULL, 
  CHANGE b c CHAR(20);

Добавим новый столбец TIMESTAMP с именем d:

ALTER TABLE t2 ADD d TIMESTAMP;

Добавим индекс для столбца d и индекс UNIQUE для столбца a:

ALTER TABLE t2 
  ADD INDEX (d), 
  ADD UNIQUE (a);

Удалим столбец c:

ALTER TABLE t2 DROP COLUMN c;

Добавим новый целочисленный столбец AUTO_INCREMENT с именем c:

ALTER TABLE t2 
  ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (c);

Запрос проиндексировал столбец c (как PRIMARY KEY), т.к. столбцы AUTO_INCREMENT должны быть индексированными. Также в запросе объявлен столбец c как NOT NULL, т.к. первичные ключи не могут быть NULL.

Когда добавляется столбец AUTO_INCREMENT, значения столбцов автоматически заполняются порядковыми номерами. Для таблиц MyISAM можно установить первый порядковый номер, выполнив SET INSERT_ID=value перед ALTER TABLE или используя параметр таблицы AUTO_INCREMENT=value.

В таблицах MyISAM, если не изменить столбец AUTO_INCREMENT, то порядковый номер не изменится. Если удалить столбец AUTO_INCREMENT, а затем добавить еще один AUTO_INCREMENT, то порядок чисел изменится, начиная с 1.

При использовании репликации, добавление столбца AUTO_INCREMENT в таблицу может привести к неодинаковому порядку строк в копии и источнике. Это происходит потому, что порядок, в котором нумеруются строки, зависит от конкретного механизма хранения, используемого для таблицы, и порядка, в котором были вставлены строки. Если важно иметь одинаковый порядок в источнике и копии, то строки должны быть упорядочены до присвоения номера AUTO_INCREMENT. Предполагая, что нужно добавить столбец AUTO_INCREMENT в таблицу t1, следующие операторы создают новую таблицу t2, идентичную t1, но со столбцом AUTO_INCREMENT:

CREATE TABLE t2 (
  id INT AUTO_INCREMENT PRIMARY KEY
) SELECT * FROM t1 ORDER BY col1, col2;

Этот набор операторов предполагает, что таблица t1 имеет столбцы col1 и col2.

Следующий набор инструкций также создает новую таблицу t2, идентичную t1, с добавлением столбца AUTO_INCREMENT:

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 
  ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

Важно. Чтобы гарантировать одинаковый порядок как в источнике, так и в копии, все столбцы t1 должны быть указаны в операторе ORDER BY.

Независимо от метода, использованного для создания и заполнения копии со столбцом AUTO_INCREMENT, последний шаг — удалить исходную таблицу, а затем переименовать копию:

DROP TABLE t1;
ALTER TABLE t2 RENAME t1;