因为您使用的是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
在桌子上,如果桌子越大,可能会导致一些非常糟糕的性能。
我建议您将结构重新格式化一点(特别是如果此表将变大,并且此类搜索将经常发生),以便您可以使用索引进行搜索:
-
添加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
-
确保所有数据的长度一致,即正好是5个整数部分:(sn00001,sn10000)。然后简单地用字符串进行比较。在这种情况下
SN1500
不会出现在你的数据中
SN01500
)不会有以下情况:
SELECT *
FROM Devices
WHERE SerialNumber >= 'SN15000' AND SerialNumber <= 'SN20000'
-
使用虚拟列,并在该列上添加索引。可以找到一个例子
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;