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.
9 Answers
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
4 Comments
Despertar
Friendly reminder - The default for columns is NULLABLE, so if you have a NOT NULL column, don't forget to use "MODIFY columnname INTEGER NOT NULL" or else you will change your column from NOT NULL to NULL.
EndermanAPM
Will alter table delete the data on the table or fail on the execution if something is not complaint with the new column type?
mircealungu
ALTER TABLE tablename MODIFY columnname INTEGER unsigned; <-- if you care about the new column being unsigned. Was my case.
Halvor Holsten Strand
I think @Despertars warning might also be relevant for keeping any specifications of CHARSET or COLLATE.
alter table table_name modify column_name int(5)
Comments
You can also use this:
ALTER TABLE [tablename] CHANGE [columnName] [columnName] DECIMAL (10,2)
Comments
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..
Comments
Alter TABLE `tableName` MODIFY COLUMN `ColumnName` datatype(length);
Ex :
Alter TABLE `tbl_users` MODIFY COLUMN `dup` VARCHAR(120);
Comments
If you want to alter the column details, set default value and add a comment, use this
ALTER TABLE [table_name] MODIFY [column_name] [new data type]
DEFAULT [VALUE] COMMENT '[column comment]'
Comments
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);
Comments
You use the alter table ... change ... method, for example:
mysql> create table yar (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into yar values(5);
Query OK, 1 row affected (0.01 sec)
mysql> alter table yar change id id varchar(255);
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc yar;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
1 Comment
Zsolt Szilagyi
I believe it's MODIFY rather than change, but maybe both works.
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)
ALTER TABLE) will, in fact, work even if the column already contains data. However, converting a float column into an integer column will cause any non-integer values in it to be rounded to the nearest integer.