代码之家 › 专栏 › 技术社区 › J86

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

sql mysql

-1

J86 · 技术社区 · 7 年前

我有一个 VARCHAR(100) 列已调用 SerialNumber 在我身上 Devices 具有如下值的表:

SN10001
SN10002
SN10003
SN10004
...etc

如何选择 SN15000 和 SN20000 在mysql中?

mysql版本是 8.0.12 我们不能假设序列号在 SN 。

3 回复 | 直到 7 年前

Madhur Bhaiya 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%'

Thorsten Kettner 7 年前

如果您希望所有序列号介于'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;

Blue 7 年前

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