MySql如何将varchar(纬度，经度)转换为Decimal字段？

Question

在 mysql 中，我有一个包含谷歌地图提供的纬度和经度的 varchar。我需要能够基于边界框值进行查询，但不需要现在可用的地理特征。我试图用在 varchar 中找到的 Decimal 值填充 2 个新的 Decimal 字段。这是我尝试使用的查询，但结果是新字段中的所有舍入值。

示例数据:

'45.390746926938185, -122.75535710155964',
'45.416444621636415, -122.63058006763458'

创建表:

CREATE TABLE IF NOT EXISTS `cameras` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(75) NOT NULL,
  `position` varchar(75) NOT NULL,
  `latitude` decimal(17,15) default NULL,
  `longitude` decimal(18,15) default NULL,
  `address` varchar(75) NOT NULL,<br />
  `date` varchar(11) NOT NULL,<br />
  `status` int(1) NOT NULL default '1',
  `duplicate_report` int(11) NOT NULL default '0',
  `missing_report` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1050 ;

SQL:

UPDATE cameras
SET latitude = CAST( (substring(position,1,locate(',',position))) AS DECIMAL(17,15) ),
    longitude = CAST( (substring(position,locate(',',position)+1)) AS DECIMAL(18,15) )

SQL 替代尝试:

UPDATE cameras
SET latitude = CONVERT( (substring(position,1,locate(',',position))), DECIMAL(17,15) ),
    longitude = CONVERT( (substring(position,locate(', ',position)+1)), DECIMAL(18,15) )

结果字段值适用于两种情况:

45.000000000000000 and -122.000000000000000
AND
45.000000000000000 and -122.000000000000000

谁能看出我做错了什么？谢谢。

Answer 1

CAST 和 CONVERT 形式似乎都是正确的。

SELECT CAST((SUBSTRING(t.position,1,LOCATE(',',t.position))) AS DECIMAL(17,15)) AS lat_
     , CONVERT(SUBSTRING(t.position,LOCATE(', ',t.position)+1),DECIMAL(18,15)) AS long_
  FROM (SELECT '45.390746926938185, -122.75535710155964' AS `position`) t

              lat_                   long_
------------------  ----------------------
45.390746926938185    -122.755357101559640

我认为 position 是一个保留字，但我认为在这种情况下这并不重要。但是分配表别名并限定所有列引用不会有什么坏处

UPDATE cameras c
   SET c.latitude = CAST((SUBSTRING(c.position,1,LOCATE(',',c.position))) AS DECIMAL(17,15))
     , c.longitude = CAST((SUBSTRING(c.position,LOCATE(',',c.position)+1)) AS DECIMAL(18,15))

但我怀疑这不会解决问题。

要检查的一件事是在表上定义的更新触发器之前或之后，它正在舍入/修改分配给纬度和经度列的值？

我建议您尝试只运行一个查询。

SELECT CAST((SUBSTRING(c.position,1,LOCATE(',',c.position))) AS DECIMAL(17,15)) AS lat_
     , CAST((SUBSTRING(c.position,LOCATE(',',c.position)+1)) AS DECIMAL(18,15)) AS lon_
  FROM cameras c

并验证生成了您期望的十进制值。

点字符应被识别为小数点。位置列是否包含其他一些特殊字符，如空格或其他内容？

从您发布的内容来看，CAST 和 CONVERT 似乎处理整数部分直至小数点。 (那里不应该隐式转换为有符号整数，因此不清楚为什么不包括小数点后的字符。)

---

如果您能弄清楚使用什么字符来表示小数点，那么您可以使用 MySQL REPLACE() 函数将这些字符替换为简单的点字符。