在mysql中选择一个varchar值范围[closed]

因为您使用的是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;
   

如果您希望所有序列号介于'sn15000'和'sn20000'之间,包括这两个,您可以使用 BETWEEN (否则你会用 < 和 > ):

select *
from mytable
where serial_number  between 'SN15000' and 'SN20000';

如果允许序列号越来越短,请添加一个长度条件:

select *
from mytable
where serial_number  between 'SN15000' and 'SN20000'
and char_length(serial_number) = 7;

• 你可以用 Replace() 要替换的函数 SN 中包含空字符串的子字符串 SerialNumber 列。
• 那么,我们可以 Cast() 将字符串修改为无符号整数值。
• 最终,使用 Where 根据需要匹配范围的条件。

您可以尝试以下操作:

SELECT * 
FROM Devices 
WHERE CAST(REPLACE(SerialNumber, 'SN', '') AS UNSIGNED) >= 15000 AND 
      CAST(REPLACE(SerialNumber, 'SN', '') AS UNSIGNED) <= 20000 AND 
      SerialNumber LIKE 'SN%'