最近在弄一个宠物项目，需要使用根据距离对信息进行排序，直接在数据库中存储经纬度然后SQL中写计算公式并排序的查询效率极其低下
好在MYSQL5.7以后新增了geometry和相应的计算，还可以走索引，亲测十倍左右的性能提升，果断走起
但是在迁移的过程中遇到了设置表和数据同步的问题，多放尝试后找到如下解决方案，不多说，直接上代码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | // pet_miniprogram_test 数据库名，lailu_adopt_info表名，这句先添加字段，不设置默认值 ALTER TABLE `pet_miniprogram_test`.`lailu_adopt_info` ADD COLUMN `geo_metry` geometry COMMENT '位置信息序列化存储 ' AFTER `clockin_count`; // 将原有信息的geo_metry字段全部更新为默认值，我这里默认为0和0，目的是把NULL变成正常数据，后续才能设置默认值 UPDATE lailu_adopt_info SET geo_metry = ST_GeomFromText('POINT(0.0 0.0)'); // 修改表，设置默认值，如果没有上面一步的话不能成功 ALTER TABLE `pet_miniprogram_test`.`lailu_adopt_info` MODIFY COLUMN `geo_metry` geometry NOT NULL DEFAULT (point(0.0,0.0)) COMMENT '位置信息序列化存储 '; // 添加索引，这里需要注意索引类型为SPATIAL空间索引 ALTER TABLE `pet_miniprogram_test`.`lailu_adopt_info` ADD SPATIAL INDEX `geo_metry_index`(`geo_metry`) COMMENT 'GEO索引'; // 从用户表把经纬度查过来，写入对应的数据，这里我加了一个RAND()做0-1的偏移，避免出现多条经纬度一致的情况导致接口分页的时候由于距离一样输出数据的时候丢数据 UPDATE lailu_adopt_info adopt, lailu_user users SET adopt.geo_metry = (POINT(RAND()+users.longitude, users.latitude-RAND())) WHERE adopt.user_id = users.user_id; |
完成上面的操作以后就可以用ST_Distance_Sphere愉快的计算距离并排序了
尊重原创，转载请注明出处