mysql - 在不丢失其他元数据的情况下更改 MySQL 中的列数据类型(DEFAULT、NOTNULL...)

Question

如果我这样做:

ALTER TABLE testtable MODIFY mycolumn NEWDATATYPE;

我放弃了其他定义，例如 NOT NULL、COMMENTS、DEFAULT 值...有没有办法做到这一点？

In PostgreSQL I used :

ALTER TABLE testtable ALTER COLUMN mycolumn NEWDATATYPE;

它做了应该做的事情:更改列数据类型，不触及任何其他定义，仅在数据类型不兼容时才给出错误等等(但您可以指定 USING)。

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

Answer 1

正如 manual page 中所述, ALTER TABLE 要求定义所有新类型属性。

但是，有一种方法可以克服这一点。您可以使用 INFORMATION_SCHEMA meta-data重建所需的 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)

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             |+--------------------------------------+

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)
  

• 为 prepared statement 准备变量(这是很长的查询，但我在上面留下了解释):

  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)