Change column data type in MySQL without losing other metadata (DEFAULT, NOTNULL...)

Question

If I do:

ALTER TABLE testtable MODIFY mycolumn NEWDATATYPE;

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

In PostgreSQL I used:

ALTER TABLE testtable ALTER COLUMN mycolumn NEWDATATYPE;

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.

Answer 1

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

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)

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'

the result would be:

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

• 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
  

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)

Answer 2

ALTER TABLE tableName
MODIFY COLUMN columnName datatype