MySQL 如何在mysql中将字符串转换为浮点数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7368163/
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 can I convert a string to a float in mysql?
提问by JYelton
I have a table containing latitude and longitude values stored as strings (VARCHAR) which I'd like to convert to FLOAT (10,6).
我有一个表，其中包含存储为字符串 ( VARCHAR) 的纬度和经度值，我想将其转换为FLOAT (10,6).
However there doesn't appear to be a straightforward way to do this using CAST()or CONVERT().
但是，似乎没有一种直接的方法可以使用CAST()or来做到这一点CONVERT()。
How can I convert these columns easily? This is a one-time conversion.
如何轻松转换这些列？这是一次转换。
回答by JYelton
It turns out I was just missing DECIMALon the CAST()description:
事实证明我只是DECIMAL在CAST()描述中遗漏了：
DECIMAL[(M[,D])]Converts a value to DECIMAL data type. The optional arguments M and D specify the precision (M specifies the total number of digits) and the scale (D specifies the number of digits after the decimal point) of the decimal value. The default precision is two digits after the decimal point.
DECIMAL[(M[,D])]将值转换为 DECIMAL 数据类型。可选参数 M 和 D 指定十进制值的精度（M 指定总位数）和小数位数（D 指定小数点后的位数）。默认精度为小数点后两位。
Thus, the following query worked:
因此，以下查询有效：
UPDATE table SET
latitude = CAST(old_latitude AS DECIMAL(10,6)),
longitude = CAST(old_longitude AS DECIMAL(10,6));
回答by zloctb
mysql> SELECT CAST(4 AS DECIMAL(4,3));
+-------------------------+
| CAST(4 AS DECIMAL(4,3)) |
+-------------------------+
| 4.000 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST('4.5s' AS DECIMAL(4,3));
+------------------------------+
| CAST('4.5s' AS DECIMAL(4,3)) |
+------------------------------+
| 4.500 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST('a4.5s' AS DECIMAL(4,3));
+-------------------------------+
| CAST('a4.5s' AS DECIMAL(4,3)) |
+-------------------------------+
| 0.000 |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)
回答by Justas
This will convert to a numeric value without the need to cast or specify length or digits:
这将转换为数字值，而无需强制转换或指定长度或数字：
STRING_COL+0.0
STRING_COL+0.0
If your column is an INT, can leave off the .0to avoid decimals:
如果您的列是INT，可以.0省略 以避免小数：
STRING_COL+0
STRING_COL+0
