将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屋！