How do I change the data type for a column in MySQL

Question

I want to change the data type of multiple columns from float to int   What is the simplest way to do this   There is no data to worry about  yet

User · Answer

Alter TABLE  tableName  MODIFY COLUMN  ColumnName  datatype length     Ex    Alter TABLE  tbl users  MODIFY COLUMN  dup  VARCHAR 120

User · Answer

alter table table name modify column name int 5

User · Answer

If you want to alter the column details add a comment  use this  ALTER TABLE  table name  MODIFY  column name   new data type  DEFAULT  VALUE  COMMENT   column comment

User · Answer

You use the alter table     change     method  for example   mysql gt  create table yar  id int   Query OK  0 rows affected  0 01 sec   mysql gt  insert into yar values 5   Query OK  1 row affected  0 01 sec   mysql gt  alter table yar change id id varchar 255   Query OK  1 row affected  0 03 sec  Records  1  Duplicates  0  Warnings  0  mysql gt  desc yar   ------- -------------- ------ ----- --------- -------    Field   Type           Null   Key   Default   Extra    ------- -------------- ------ ----- --------- -------    id      varchar 255    YES          NULL               ------- -------------- ------ ----- --------- -------  1 row in set  0 00 sec

User · Answer

To change column data type there are change   method and modify method   ALTER TABLE student info CHANGE roll no roll no VARCHAR 255    ALTER TABLE student info MODIFY roll no VARCHAR 255        To change the field name also use the change method   ALTER TABLE student info CHANGE roll no identity no VARCHAR 255

User · Answer

If you want to change all columns of a certain type to another type  you can generate queries using a query like this   select distinct concat  alter table                           table name                           modify                           column name                            lt new datatype gt                            if is nullable    NO     NOT                                  NULL      from information schema columns   where table schema     lt your database gt        and column type     lt old datatype gt      For instance  if you want to change columns from tinyint 4  to bit 1   run it like this   select distinct concat  alter table                           table name                           modify                           column name                           bit 1                            if is nullable    NO     NOT                                  NULL      from information schema columns   where table schema    MyDatabase       and column type    tinyint 4      and get an output like this   alter table table1 modify finished bit 1   NOT  NULL  alter table table2 modify canItBeTrue bit 1   NOT  NULL  alter table table3 modify canBeNull bit 1   NULL       Does not keep unique constraints  but should be easily fixed with another if-parameter to concat  I ll leave it up to the reader to implement that if needed

User · Answer

http   dev mysql com doc refman 5 1 en alter-table html ALTER TABLE tablename MODIFY columnname INTEGER   This will change the datatype of given column Depending on how many columns you wish to modify it might be best to generate a script  or use some kind of mysql client GUI

User · Answer

You can also use this   ALTER TABLE  tablename  CHANGE  columnName   columnName  DECIMAL  10 2

User · Answer

https   dev mysql com doc refman 8 0 en alter-table html  You can also set a default value for the column just add the DEFAULT keyword followed by the value   ALTER TABLE  table name  MODIFY  column name   NEW DATA TYPE  DEFAULT  VALUE     This is also working for MariaDB  tested version 10 2

[mysql] How do I change the data type for a column in MySQL?

Examples related to mysql