将MySQL表varchar字段更改为十进制字段 [英] Alter MySQL table varchar fields to decimal fields
问题描述
我有一个带有多个列的MySQL表
I have a MySQL-table with several colomns like
id text_1 val_1 text_2 text_3 val_2
1 'bla' 1,23 'blub' 5,67 12,34
不幸的是,所有字段都定义为varchar
.现在,我尝试更改表以使某些字段为十进制值.
Unfortunately all fields are defined as varchar
. Now I try to alter the table to make some fields decimal values.
我会尝试
UPDATE table SET
val_1 = CAST(val_1 AS DECIMAL(5,2)),
val_2 = CAST(val_2 AS DECIMAL(5,2));
这应该给我十进制值,其中包含两个小数,最大值为999,99,对吗?
This should give me decimal values with two decimals and a max value of 999,99, correct?
这是更改表结构的正确方法吗?这应该是一次转换,因为值应为da十进制类型,字符串应为varchar类型.
And is this the correct way to alter the table structure? This should be a one time conversion, as values should get da decimal type and strings should be varchar type.
我必须先将逗号"转换为点"吗?
Do I have to convert the 'comma' to 'point' first?
更新
不幸的是,我也有类似'< 0,01'的值.但是该字段应为十进制字段.我该怎么办?
Unfortunately I have also values like `<0,01'. But the field should be a decimal field. What can I do with that?
推荐答案
是的,您必须将转换为.第一的.
Yes, you have to convert the , to a . first.
UPDATE sometable SET val_1=REPLACE(val_1,',','.');
此后必须更改表:
ALTER TABLE sometable MODIFY COLUMN val_1 DECIMAL(5,2);
不需要您的更新声明.
如果值较小,则将四舍五入(以您的情况为准).为防止舍入,请使用其他DECIMAL
设置(带有更多数字).
If you have smaller values, they will get rounded (down in your case). To prevent rounding, use another DECIMAL
setup (with more digits).
这篇关于将MySQL表varchar字段更改为十进制字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!