如何更改 MySQL 中列的数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1356866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I change the data type for a column in MySQL?
提问by Eric Wilson
I want to change the data type of multiple columns from float to int. What is the simplest way to do this?
我想将多列的数据类型从 float 更改为 int。什么是最简单的方法来做到这一点？
There is no data to worry about, yet.
尚无数据可担心。
回答by Yannick Motton
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
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
回答by php
alter table table_name modify column_name int(5)
回答by Richard
You can also use this:
你也可以使用这个：
ALTER TABLE [tablename] CHANGE [columnName] [columnName] DECIMAL (10,2)
回答by Tobb
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-parameter to轻松修复concat。如果需要，我会将其留给读者来实现。
回答by Mahrukh Mehmood
Alter TABLE `tableName` MODIFY COLUMN `ColumnName` datatype(length);
Ex :
前任 ：
Alter TABLE `tbl_users` MODIFY COLUMN `dup` VARCHAR(120);
回答by ólafur Waage
You use the alter table ... change ...method, for example:
您使用该alter table ... change ...方法，例如：
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)
回答by Hasib Kamal
To change column data type there are changemethod and modifymethod
要更改列数据类型，有更改方法和修改方法
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 changemethod
要更改字段名称也使用更改方法
ALTER TABLE student_info CHANGE roll_no identity_no VARCHAR(255);
回答by michael01angelo
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
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.
您还可以为列设置默认值，只需添加 DEFAULT 关键字后跟该值即可。
ALTER TABLE [table_name] MODIFY [column_name] [NEW DATA TYPE] DEFAULT [VALUE];
This is also working for MariaDB (tested version 10.2)
这也适用于 MariaDB（测试版 10.2）
回答by Joe_Tz
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]'
