Изменение типа данных столбцов в MySQL без потери других метаданных (DEFAULT, NOTNULL...)

Если я это сделаю:

ALTER TABLE testtable MODIFY mycolumn NEWDATATYPE;

Я теряю другие определения, такие как NOT NULL, COMMENTS, DEFAULT значения... Есть ли способ сделать это?

В PostgreSQL я использовал:

ALTER TABLE testtable ALTER COLUMN mycolumn NEWDATATYPE;

И он делает то, что должно: изменить тип данных столбца, не касаясь какого-либо другого определения, только давая ошибку, если типы данных несовместимы и так далее (но вы можете указать ИСПОЛЬЗОВАНИЕ).

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

Ответ 1

Как указано в странице руководства, ALTER TABLE требуется, чтобы все атрибуты нового типа были определены.

Однако есть способ преодолеть это. Вы можете использовать INFORMATION_SCHEMA метаданные для восстановления желаемого запроса ALTER. например, если у нас есть простая таблица:

mysql> DESCRIBE t;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| value | varchar(255)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

то мы можем воспроизвести нашу инструкцию alter:

SELECT 
  CONCAT(
    COLUMN_NAME, 
    ' @new_type', 
    IF(IS_NULLABLE='NO', ' NOT NULL ', ' '), 
    EXTRA
  ) AS s
FROM 
  INFORMATION_SCHEMA.COLUMNS 
WHERE 
  TABLE_SCHEMA='test' 
  AND 
  TABLE_NAME='t'

результатом будет:

+--------------------------------------+
| s                                    |
+--------------------------------------+
| id @new_type NOT NULL auto_increment |
| value @new_type NOT NULL             |
+--------------------------------------+

Здесь я оставил @new_type, чтобы указать, что мы можем использовать переменную для этого (или даже заменить наш новый тип непосредственно на запрос). С переменной, которая будет:

Задайте наши переменные.

mysql> SET @new_type := 'VARCHAR(10)', @column_name := 'value';
Query OK, 0 rows affected (0.00 sec)

Подготовьте переменную для подготовленного оператора (это длинный запрос, но я объяснил выше):

SET @sql = (SELECT CONCAT('ALTER TABLE t CHANGE `',COLUMN_NAME, '` `', COLUMN_NAME, '` ', @new_type, IF(IS_NULLABLE='NO', ' NOT NULL ', ' '), EXTRA) AS s FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t' AND [email protected]_name);

Подготовить оператор:

mysql> prepare stmt from @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Наконец, выполните его:

mysql> execute stmt;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

Затем мы изменим тип данных на VARCHAR(10) с сохранением всех остальных спецификаций:

mysql> DESCRIBE t;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| value | varchar(10)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Ответ 2

ALTER TABLE tableName
MODIFY COLUMN columnName datatype

Ответ 3

Когда вы используете CHANGE или MODIFY в ALTER table_name, column_definition должен включать тип данных и все атрибуты, которые должны применяться к новому столбцу, за исключением атрибутов индекса, таких как PRIMARY KEY или UNIQUE. Атрибуты, присутствующие в исходном определении , но не указанные для нового определения, не переносятся вперед.

Предположим, что столбец col1 определяется как INT UNSIGNED DEFAULT 1 COMMENT 'my column', и вы изменяете столбец следующим образом:

ALTER TABLE t1 MODIFY col1 BIGINT;

Полученный столбец будет определен как BIGINT, но не будет включать атрибуты UNSIGNED DEFAULT 1 COMMENT 'my column'. Чтобы сохранить их, утверждение должно быть:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

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

См. документацию .