mysql计算两点之间距离st_distance_sphere函数

mysql5.6提供了st_distance函数，
mysql5.7提供了st_distance_sphere函数。

st_distance_sphere函数是mysql5.7提供的，可以直接查询两个经纬度之间相距多少米，并且该函数的计算结果要比st_distance转换为米的结果更精确。
而st_distance则需要自己进行计算转换为单位米。

表结构

CREATE TABLE `st_distance_data` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `created_time` datetime NOT NULL,  `updated_time` datetime NOT NULL,  `name` varchar(50) NOT NULL,  `lng` decimal(15,12) NOT NULL,  `lat` decimal(15,12) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

测试数据

INSERT INTO `st_distance_data` (`id`, `created_time`, `updated_time`, `name`, `lng`, `lat`) VALUES ('1', '2023-06-19 10:09:00', '2023-06-19 10:09:03', '泉州市客运中心站', '118.611379106110', '24.885678000544');INSERT INTO `st_distance_data` (`id`, `created_time`, `updated_time`, `name`, `lng`, `lat`) VALUES ('2', '2023-06-19 10:09:18', '2023-06-19 10:09:21', '泉州市城东万达麦当劳', '118.656780339160', '24.915820013248');INSERT INTO `st_distance_data` (`id`, `created_time`, `updated_time`, `name`, `lng`, `lat`) VALUES ('3', '2023-06-19 10:09:34', '2023-06-19 10:09:36', '泉州市中骏世界城永辉超市', '118.659432662340', '24.920755765732');INSERT INTO `st_distance_data` (`id`, `created_time`, `updated_time`, `name`, `lng`, `lat`) VALUES ('4', '2023-06-19 10:09:48', '2023-06-19 10:09:50', '泉州市洛江大润发', '118.666656315130', '24.933292370776');INSERT INTO `st_distance_data` (`id`, `created_time`, `updated_time`, `name`, `lng`, `lat`) VALUES ('5', '2023-06-19 14:33:23', '2023-06-19 14:33:21', '泉州市华创园', '118.652468906910', '24.927268718491');

距离计算并从近到远排序

SELECT name as 名称, ST_Distance_Sphere(    POINT(118.652468906910, 24.927268718491),    POINT(lng, lat)) AS `距离(米)`FROM st_distance_data order by `距离(米)`;

计算结果（单位：米）

来源地址：https://blog.csdn.net/weixin_42683476/article/details/131308870