如何更改MySQL中列的数据类型?

I want to change the data type of multiple columns from float to int. What is the simplest way to do this?

我想要将多个列的数据类型从浮点数改为int，最简单的方法是什么?

There is no data to worry about, yet.

目前还没有数据需要担心。

6 个解决方案

#1

639

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 howmany columns you wish to modify it might be best to generate a script, or use some kind of mysql client GUI

取决于您希望修改多少列，最好是生成一个脚本，或者使用某种mysql客户机GUI。

#2

alter table table_name modify column_name int(5)

#3

You can also use this:

你也可以用这个:

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

#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:

例如，如果您想要从tinyint(4)到bit(1)中更改列，可以这样运行:

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

! !不保留唯一的约束，但是应该用另一个if参数轻松地固定。如果需要的话，我将把它留给读者来实现。

#5

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

Ex :

例:

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

#6

You use the alter table ... change ... method, for example:

使用alter table…改变……方法,例如:

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

639