blocks|key|1463825|text|http://dev.mysql.com/doc/refman/5.1/en/alter-table.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1463826|ALTER+TABLE+tablename+MODIFY+columnname+INTEGER;|code-block|syntax|javascript|1463827|这将更改给定列的数据类型|1463828|根据您希望修改的列数，最好生成一个脚本或使用某种类型的mysql客户端GUI|1463829|entityMap|0|LINK|mutability|MUTABLE|url^0|0|1J|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|D|3|E|5|F|7|X|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Y|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|Z|8|@]|9|@]|C|$]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|4]]]]

<a href="http://dev.mysql.com/doc/refman/5.1/en/alter-table.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.1/en/alter-table.html</a>
<pre><code>ALTER TABLE tablename MODIFY columnname INTEGER;
</code></pre>
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

blocks|key|1249122|text|您可以使用alter+table+...+change+...方法，例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1249123|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;
%2B-------%2B--------------%2B------%2B-----%2B---------%2B-------%2B
%7C+Field+%7C+Type+++++++++%7C+Null+%7C+Key+%7C+Default+%7C+Extra+%7C
%2B-------%2B--------------%2B------%2B-----%2B---------%2B-------%2B
%7C+id++++%7C+varchar(255)+%7C+YES++%7C+++++%7C+NULL++++%7C+++++++%7C
%2B-------%2B--------------%2B------%2B-----%2B---------%2B-------%2B
1+row+in+set+(0.00+sec)|code-block|syntax|javascript|1249124|entityMap^0|5|Q|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You use the <code>alter table ... change ...</code> method, for example:

<pre><code>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)
</code></pre>

blocks|key|1249134|text|alter+table+table_name+modify+column_name+int(5)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1249135|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>alter table table_name modify column_name int(5)
</code></pre>

blocks|key|1463904|text|您还可以使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1463905|ALTER+TABLE+[tablename]+CHANGE+[columnName]+[columnName]+DECIMAL+(10,2)|code-block|syntax|javascript|1463906|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can also use this:

<pre><code>ALTER TABLE [tablename] CHANGE [columnName] [columnName] DECIMAL (10,2)
</code></pre>

blocks|key|1463960|text|如果要将某一类型的所有列更改为另一种类型，可以使用如下查询生成查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1463961|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>';|code-block|syntax|javascript|1463962|例如，如果要将列从tinyint(4)更改为bit(1)，请按如下方式运行：|offset|length|style|CODE|1463963|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)';|1463964|并得到如下输出：|1463965|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;|1463966|!！不保留唯一约束，但应使用concat的另一个if-parameter轻松修复。如果需要，我将把它留给读者来实现。|1463967|entityMap^0|0|0|9|A|M|6|0|0|0|0|E|6|O|2|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|16|8|@$I|17|J|18|K|L]|$I|19|J|1A|K|L]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|V|$]]

If you want to change all columns of a certain type to another type, you can generate queries using a query like this:

<pre><code>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;';
</code></pre>

For instance, if you want to change columns from <code>tinyint(4)</code> to <code>bit(1)</code>, run it like this:

<pre><code>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)';
</code></pre>

and get an output like this:

<pre><code>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;
</code></pre>

!! Does not keep unique constraints, but should be easily fixed with another <code>if</code>-parameter to <code>concat</code>. I'll leave it up to the reader to implement that if needed..

blocks|key|1249270|text|https://dev.mysql.com/doc/refman/8.0/en/alter-table.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1249271|您还可以为列设置默认值，只需在值后面添加default关键字即可。|1249272|ALTER+TABLE+[table_name]+MODIFY+[column_name]+[NEW+DATA+TYPE]+DEFAULT+[VALUE];|code-block|syntax|javascript|1249273|这也适用于MariaDB+(测试版本10.2)|1249274|entityMap|0|LINK|mutability|MUTABLE|url^0|0|1K|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|Y|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|Z|8|@]|9|@]|C|$]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|4]]]]

<a href="https://dev.mysql.com/doc/refman/8.0/en/alter-table.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/8.0/en/alter-table.html</a>

You can also set a default value for the column just add the DEFAULT keyword followed by the value.

<pre><code>ALTER TABLE [table_name] MODIFY [column_name] [NEW DATA TYPE] DEFAULT [VALUE];
</code></pre>

This is also working for MariaDB (tested version 10.2)

blocks|key|1464075|text|如果要更改列详细信息、设置默认值并添加注释，请使用以下命令|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1464076|ALTER+TABLE+[table_name]+MODIFY+[column_name]+[new+data+type]+
DEFAULT+[VALUE]+COMMENT+'[column+comment]'+|code-block|syntax|javascript|1464077|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you want to alter the column details, set default value and add a comment, use this
<pre><code>ALTER TABLE [table_name] MODIFY [column_name] [new data type] 
DEFAULT [VALUE] COMMENT '[column comment]' 
</code></pre>

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.

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

MariaDB

我希望将多个列的数据类型从float更改为int。执行此操作的最简单方法是什么？目前还没有要担心的数据。

问如何在MySQL中更改列的数据类型？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中更改列的数据类型？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中更改列的数据类型？
EN