在MySQL中更改列数据类型而不会丢失其他元数据(DEFAULT，NOTNULL ...) [英] Change column data type in MySQL without losing other metadata (DEFAULT, NOTNULL...)

问题描述

如果我这样做:

ALTER TABLE testtable MODIFY mycolumn NEWDATATYPE;

我松开了其他定义，例如NOT NULL，COMMENTS，DEFAULT值...有办法吗?

I loose other definitions like NOT NULL, COMMENTS, DEFAULT values... Is there a way to do it?

在我使用的PostgreSQL中:

ALTER TABLE testtable ALTER COLUMN mycolumn NEWDATATYPE;

它可以执行以下操作:更改列数据类型，而无需更改任何其他定义，仅在数据类型不兼容时给出错误，依此类推(但您可以指定USING).

And it does what is supposed to: change the column datatype, without touching any other definition, only giving error if data types were not compatible and so on (but you can specify USING).

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

I'll try a workaround, but I did a query to identify several columns across different tables to update the datatype and now I've identified that this data was lost, so I'll have to redo it considering these informations too.

推荐答案

如手册页，ALTER TABLE要求定义所有新的类型属性.

As it's stated in manual page, ALTER TABLE requires all new type attributes to be defined.

但是，有一种方法可以克服这个问题.您可以使用 INFORMATION_SCHEMA元数据进行重构所需的ALTER查询.例如，如果我们有简单的表格:

However, there is a way to overcome this. You may use INFORMATION_SCHEMA meta-data to reconstruct desired ALTER query. for example, if we have simple table:

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语句:

then we can reproduce our alter statement with:

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，表明我们可以为此使用变量(甚至可以直接将新类型替换为查询).变量为:

Here I've left @new_type to indicate that we can use variable for that (or even substitute our new type directly to query). With variable that would be:

• 设置我们的变量.

• Set our variables.

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

为准备的语句准备变量(这是一个很长的查询，但是我在上面留下了解释):

Prepare variable for prepared statement (it's long query, but I've left explanations above):

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);

准备声明:

Prepare statement:

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

最后，执行它:

Finally, execute it:

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

然后我们将保存所有其余的说明符，将数据类型更改为VARCHAR(10):

Then we'll get our data type changed to VARCHAR(10) with saving all the rest specifiers:

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)

这篇关于在MySQL中更改列数据类型而不会丢失其他元数据(DEFAULT，NOTNULL ...)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！