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

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.

Solution 1:

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

Solution 2:

alter table table_name modify column_name int(5)

Solution 3:

You can also use this:

ALTER TABLE [tablename] CHANGE [columnName] [columnName] DECIMAL (10,2)

Solution 4:

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,
                       ' <new datatype> ',
                       if(is_nullable = 'NO', ' NOT ', ''),
                       ' NULL;')
  from information_schema.columns
  where table_schema = '<your database>' 
    and column_type = '<old datatype>';

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

Solution 5:

Alter TABLE `tableName` MODIFY COLUMN `ColumnName` datatype(length);

Ex :

Alter TABLE `tbl_users` MODIFY COLUMN `dup` VARCHAR(120);