在MySQL中更改列数据类型而不会丢失其他元数据（DEFAULT，NOTNULL'）

一尘不染

在MySQL中更改列数据类型而不会丢失其他元数据（DEFAULT，NOTNULL'）

sql

如果我做：

ALTER TABLE testtable MODIFY mycolumn NEWDATATYPE;

我松开了其他定义，例如NOT NULL，COMMENTS，DEFAULT值…有没有办法做到这一点？

在PostgreSQL中，我使用了：

ALTER TABLE testtable ALTER COLUMN mycolumn NEWDATATYPE;

它可以执行以下操作：更改列数据类型，而无需更改任何其他定义，仅在数据类型不兼容时给出错误，等等（但您可以指定USING）。

我将尝试一种解决方法，但我进行了一次查询以识别不同表中的几列以更新数据类型，现在我已经确定该数据已丢失，因此我也必须考虑这些信息来重做它。

阅读 157

2021-05-16

共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 COLUMN_NAME=@column_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)

2021-05-16