Mysql – Alter table with casting

alter-tablecastMySQL

Is there a way of run an alter table command with cast on MySQL databases?

I know that is possible to do it with Postgresql and Sql servers, but I didn't find anything about it when searching the documentation..

My database is very large and I want to convert Decimal to BigInt, so I'm trying to do it with a single command and avoid workarounds..

Best Answer

You can do it using a single statement.

CREATE TABLE T (val decimal(18,3));
INSERT INTO T VALUES (1.25);
INSERT INTO T VALUES (12.526);
INSERT INTO T VALUES (1000.25);
SELECT val FROM T;
|      val |
| -------: |
|    1.250 |
|   12.526 |
| 1000.250 |
ALTER TABLE T MODIFY COLUMN val bigint;
SELECT * FROM T;
|  val |
| ---: |
|    1 |
|   13 |
| 1000 |

dbfiddle here