使用索引语义确定MySql索引列中的字段是否为数字

Question

我有一个VARCHAR表，我想在其中获得给定MySql列具有数值(可以转换为number，您知道)的行数。现在，我正在对这个字段做一个简单的REGEXP检查。因为这个表非常大，所以我使用了一系列索引来REGEXP，尽可能少的行。

但是这个VARCHAR列也被编入索引。有没有一个巧妙的MySql索引算法，我可以利用它来扫描更少的行？:-/这是一个InnoDB表。

Answer 1

您可能不喜欢这样，因为您可能已经在尝试避免它，但是当我遇到这样的情况时，我添加了一个额外的列，将varchar存储在一个数值列(使用触发器更新)中，并对其进行查询，而不是尝试做一些聪明的事情。

但是，我认为有一种方法可以做到这一点(尽管我在生产中从来没有这样做的理由)，那就是利用索引将值按顺序排列的事实，这样所有以数字开头的值都被排序在一起。

假设有这样一个表：

CREATE TABLE `test_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text_or_number` varchar(255),
  PRIMARY KEY (`id`),
  KEY `test_1_idx` (`text_or_number`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

通过利用utf8字符的顺序只能选择以数字开头的值- http://en.wikipedia.org/wiki/UTF-8#Examples

utf-8中0之前的最低值是"/"，它之后的最高值是":"，所以这应该只提取以数字开头的值：

select   cast(text_or_number as unsigned)
from     test_1
where    text_or_number < ':'
and      text_or_number > '/'
and      cast(text_or_number as unsigned) > 0;

它仍然可以包含以数字开头但不以数字结尾的值，这就是为什么我添加了cast(...) >0子句，但我认为mysql将足够智能，可以按顺序运行where子句，所以希望它只对以数字字符开头的行子集运行强制转换。