FrankerZ

因为您使用的是mysql 8,所以可以利用regex函数。可以使用以下查询获取结果:

SELECT *
FROM Devices
WHERE SerialNumber REGEXP '^SN\\d+$'
AND REGEXP_REPLACE(SerialNumber, '[^\\d]+', '') BETWEEN 15000 and 20000;

首先,我们匹配以确保 SN 后面跟着一些数字。其次,我们用零替换任何不是数字的内容,然后检查它是否在15000到20000之间。可以看到一个例子 here 。

虽然上面的查询解决了您的答案,但这将导致 particularly nasty row-scans 在桌子上,如果桌子越大,可能会导致一些非常糟糕的性能。

我建议您将结构重新格式化一点(特别是如果此表将变大,并且此类搜索将经常发生),以便您可以使用索引进行搜索:

1. 添加2列: string_part 和 int_part 在设备上。在两个字段上添加索引 (`string_part`, `int_part`) . 插入/更新数据时,可以创建触发器,也可以通过编程将数据拆分,然后将分离的数据插入各自的字段中。(3向您展示了如何使用虚拟列执行此操作。)然后您可以高效地搜索数据库:

   SELECT * 
   FROM Devices 
   WHERE string_part = 'SN' AND int_part >= 15000 AND int_part <= 20000
   

2. 确保所有数据的长度一致,即正好是5个整数部分:(sn00001,sn10000)。然后简单地用字符串进行比较。在这种情况下 SN1500 不会出现在你的数据中 SN01500 )不会有以下情况:

   SELECT * 
   FROM Devices 
   WHERE SerialNumber >= 'SN15000' AND SerialNumber <= 'SN20000'
   

3. 使用虚拟列,并在该列上添加索引。可以找到一个例子 here . 注意查询次数(100ms到1ms):

   CREATE TABLE Devices (
     `SerialNumber` VARCHAR(20),
     `SerialNumberString` VARCHAR(20) GENERATED ALWAYS AS (REGEXP_SUBSTR(SerialNumber, '^[A-Za-z]+')) VIRTUAL,
     `SerialNumberInteger` INT(11) UNSIGNED GENERATED ALWAYS AS (REGEXP_SUBSTR(SerialNumber, '\\d+$')) VIRTUAL,
     INDEX `SerialNumberIndex` (`SerialNumberString`, `SerialNumberInteger`)
   ) DEFAULT charset=utf8 COLLATE=utf8_unicode_ci;
   

   你的问题是:

   SELECT SerialNumber
   FROM Devices
   WHERE SerialNumberString = 'SN' AND SerialNumberInteger BETWEEN 15000 AND 20000;