MySQL trig数学计算距离结果为空

Question

我在计算经纬度距离时遇到了一个奇怪的问题。如果我将这个查询输入到 mysql 中

SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(-90.496706)) + sin(radians(30.50492800)) * sin(radians(30.50492800))))

结果为空。然而，进入

SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(-90.496706)) + sin(radians(30.50492800)) * sin(radians(30.50492700))))

或

SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(-90.49670)) + sin(radians(30.50492800)) * sin(radians(30.50492800))))

给出结果。

有什么原因会导致这种情况吗？

Answer 1

一点都不奇怪，

3 个表达式中没有一个计算相同的东西。

---

差异

SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(-90.496706)) + sin(radians(30.50492800)) * sin(radians(30.50492800))))

SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians(-90.496706)) + sin(radians(30.50492800)) * sin(radians( <强> 30.50492700 ))))

SELECT (3959 * acos(cos(radians(30.50492800)) * cos(radians(30.50492800)) * cos(radians(-90.49670600) - radians( <强> -90.49670 )) + sin(radians(30.50492800)) * sin(radians(30.50492800))))

---

评估

• 第一个表达式
  1. cos(radians(30.50492800))等于0.861585503968495
  2. cos(radians(30.50492800))等于0.861585503968495
  3. cos(radians(-90.49670600) - radians(-90.496706))等于1
  4. sin(radians(30.50492800)) * sin(radians(30.50492800))等于0.257670419351355
  5. 所以我们评估0.861585503968495 * 0.861585503968495 * 1 + 0.257670419351355这给 1.000000000000000513393952565025
  6. acos(1.000000000000000513393952565025)返回空值。
• 第二个表达
  1. cos(radians(30.50492800))等于0.861585503968495
  2. cos(radians(30.50492800))等于0.861585503968495
  3. cos(radians(-90.49670600) - radians(-90.496706))等于1
  4. sin(radians(30.50492800)) * sin(radians(30.50492700))等于0.257670411718131
  5. 所以我们评估0.861585503968495 * 0.861585503968495 * 1 + 0.257670411718131等于 0.999999992366776513393952565025
  6. acos(0.999999992366776513393952565025) = 0.000123557464423953
  7. 3959 * 0.000123557464423953 = 0.489164001654431
• 第三个表达
  1. cos(radians(30.50492800))等于0.861585503968495
  2. cos(radians(30.50492800))等于0.861585503968495
  3. cos(radians(-90.49670600) - radians(-90.49670))等于0.999999999999995
  4. sin(radians(30.50492800)) * sin(radians(30.50492800))等于0.257670419351355
  5. 所以我们评估0.861585503968495 * 0.861585503968495 * 0.999999999999995 + 0.257670419351355等于 0.999999999999996801746049321797
  6. acos(0.999999999999996801746049321797)等于 8.16170211889098e-08
  7. 乘以 3959我们得到 0.000323121786886894

---

为什么 acos(1.000000000000000513393952565025) 返回 null ？

来自documentation ,

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1.

如您所见，1.000000000000000513393952565025高于 1。

对于另外两个不给null的表达式，给定参数给acos是

• 0.999999992366776513393952565025
• 0.000323121786886894

它们都在 [-1, 1] 范围内所以我们没有空值。