带范围的 MySQL 查询字段

Question

我买了一个邮政编码数据库，它按如下顺序存储所有地址的街道号码:

1002-1004;1101-1105;1102-1104;1201-1205;1202-1204;1301-1305;1302-1304;1401-1405;1402-1404;1501-1505;1502-1504;1601-1605;1602-1604;1701-1705;1702-1704;1801-1805;1802-1804;1902-1904;1901-1905;2007;2006-2012;2011-2013;2106-2112;2107-2113;2206-2212;2207-2213;2306-2312;2307-2313;2406-2412;2407-2413;2506-2512;2507-2513;2606-2612;2607-2613;2706-2712;2707-2713;2806-2812;2807-2813;2906-2912;2907-2913;3014-3020;3015-3021;3114-3120;3115-3121;3214-3220;3215-3221;3314-3320;3315-3321;3414-3420;3415-3421

这是最长的，也可以是较短的列表，只是一个范围或只有一个数字。我正在尝试找到一种仅使用邮政编码和街道号码来查找地址的方法。

显然，我可以用最小值/最大值拆分数据库中的每个条目，但这使得更新任务变得更加艰巨。

那么有没有一种方法可以通过 MySQL 查询实现这一点？还是我必须在这里依赖 PHP(就我而言)？

热切期待...

Answer 1

没有简单的 native 函数来检查数值(例如 1017)是否在一组存储为字符串的范围内，如您所示。

简短的回答是，不，这种搜索不能在 MySQL 中完成。

较长的答案是，虽然不切实际，但可以执行拉出该列表中每个范围的各个最小和最大分量所需的那种字符串操作。

首先，从性能的角度来看，这是不切实际的(您不会希望为每次搜索都做那种繁重的工作。其次，这是不切实际的，因为执行这种字符串操作需要扭曲的 SQL。

从 MySQL 性能的角度来看，执行此类搜索的更好方法是将该范围列表表示为表中的单独行。

r_mod r_min r_max
----- ----- -----
    0  1002  1004
    1  1101  1105
    0  1102  1104
    1  1201  1205

要让 MySQL 将这些范围列表中的每一个“拆分”为单独行上的一个范围，并在每个列表上添加尾随分号，您可以执行如下查询:

SELECT n.i
     , SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(d.foo,';', n.i ),';',-1),'-', 1 ),'-',-1)%2 AS r_mod
     , SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(d.foo,';', n.i ),';',-1),'-', 1 ),'-',-1)/1 AS r_min
     , SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(d.foo,';', n.i ),';',-1),'-', 2 ),'-',-1)/1 AS r_max
  FROM (SELECT '1002-1004;1101-1105;1102-1104;1201-1205;1202-1204;1301-1305;' AS foo) d
 CROSS
  JOIN ( SELECT h.d*100+t.d*10+o.d+1 AS i
           FROM (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) o
          CROSS
           JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
                  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t
          CROSS 
           JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
                  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) h
       ) n
HAVING r_max > 0

(请注意，对于该查询，范围列表字符串必须附加一个尾随分号，并且该查询只会提取每个列表的前 1000 个条目。)鉴于这种构造，理论上可以进行“搜索” ，使用数字比较:

avalue MOD 2 = r_mod AND avalue BETWEEN r_min AND r_max

再次回到较短的答案，将范围作为范围的字符串列表存储在表中，为每次搜索执行这种类型的字符串操作是不切实际的。

有了一个预填充的表，其中每个 r_min 和 r_max 值都有单独的行，就可以在 MySQL 中进行搜索。