我正在考虑SQL中4个新的比较运算符的建议.这些类似于>,> =和< =运算符,但是只有当满足不等式的所有值中的每个操作数的值是最接近另一个操作数的值的值时才为真.由于一个值几乎处于另一个值,所以我得出结论(意识到没有第一个关键字,丢弃唯一关键字之后),一个很好的选择是定义这4个新的运算符：
> @ @> b：如果一个> b和no a’ a满足’> b和no b’> b满足> b”
> @ @ b：如果b @>一个
> a @> = b：如果a≥b且no a’< a满足'≥b和no b'> b满足≥b’
> a @< = b：如果b @> = a则为true
问题是：为什么像这样的操作符不存在？
(2014-03-20)我重新提出了这个问题,因为上述表述显然还不够清楚：
是否有理由这样的操作符不应该存在？
以下示例旨在作为找出@ …运算符可能出现的问题的起点.我将使用3个MySQL表：
create table ta (id int auto_increment, ca char, primary key(id), unique index(ca));
create table tb (id int auto_increment, cb char, primary key(id), index(cb));
create table tc (id int auto_increment, cc char, primary key(id));
insert into ta (ca) values ('A'),('E'),('I'),('O'),('U');
insert into tb (cb) values ('C'),('D'),('E'),('F'),('F'),('M'),('N'),('O'),('Z');
insert into tc (cc) values ('C'),('D'),('E'),('F'),('F'),('M'),('N'),('O'),('Z');示例#1
当列具有唯一的值时,可以通过将查询或子查询的输出限制为1行来获取@ …运算符的效果,尽管有一些更笨拙的语法：
?> select * from ta where ca @> 'B'; -- currently not valid, equivalent to:
!> select * from ta where ca > 'B' order by ca limit 1;
+----+------+
| id | ca |
+----+------+
| 2 | E |
+----+------+(限制1是特定于MySQL,MariaDB,PostgreSQL等,其他RDBMS选择前1,其中rownum = 1等)
在表ta中,我们在列ca上有一个唯一的索引.可以利用该索引以与ca =’E’相同的速度获取所选值.优化器可以实现这一点,但是如果没有,则可以根据所选择的值设置不需要的扫描的数据结构(MySQL的解释说这是一个范围类型查询).
示例#2
当列具有非唯一值时,限制输出行是无用的,语法变得更加笨拙：
?> select * from tb where cb @> 'E'; -- currently not valid, equivalent to:
!> select * from tb where cb = (select min(cb) from tb where cb > 'E');
+----+------+
| id | cb |
+----+------+
| 4 | F |
| 5 | F |
+----+------+幸运的是,如果我正确地读取了解释的输出,MySQL是足够聪明的,以优化子查询,但如果不是,索引将被使用两次而不是一次.
对于在表cc中没有索引的表tc的情况,MySQL进行两次表扫描.这是可以理解的,因为单个表扫描将意味着为临时结果使用未知量的存储空间.
示例#3
假设您需要所有由值和其后继值组成的对：
?> select t1.ca as c1, t2.ca as c2
from ta t1
join ta t2 on t1.ca @< t2.ca; -- currently not valid, equivalent to:
!> select t1.ca as c1, t2.ca as c2
from ta t1
join ta t2 on t2.ca = (select min(ca) from ta where ca > t1.ca);
+------+------+
| c1 | c2 |
+------+------+
| A | E |
| E | I |
| I | O |
| O | U |
+------+------+如果我正确地阅读了解释的输出,MySQL优化器就不能在没有相关的子查询的情况下进行,而我们人类会更好地了解.也许借助于有特殊处理的@ …操作符,优化器会做单次扫描？
示例#4
这是相似的,但跨两个表,其中一个具有非唯一索引：
?> select * from ta join tb on ca @< cb; -- currently not valid, equivalent to:
!> select * from ta join tb on cb = (select min(cb) from tb where cb > ca);
+----+------+----+------+
| id | ca | id | cb |
+----+------+----+------+
| 1 | A | 1 | C |
| 2 | E | 4 | F |
| 2 | E | 5 | F |
| 3 | I | 6 | M |
| 4 | O | 9 | Z |
| 5 | U | 9 | Z |
+----+------+----+------+这也是MySQL优化器没有优化掉子查询,尽管(可能有@
?> select * from ta join tb
where round((ascii(ca)+ascii(cb))/2) @> ascii('E');
-- currently not valid, equivalent to:
!> select * from ta join tb
where round((ascii(ca)+ascii(cb))/2) = (
select min(round((ascii(ca)+ascii(cb))/2)) from ta, tb
where round((ascii(ca)+ascii(cb))/2) > ascii('E')
);
+----+------+----+------+
| id | ca | id | cb |
+----+------+----+------+
| 3 | I | 1 | C |
| 2 | E | 4 | F |
| 2 | E | 5 | F |
+----+------+----+------+示例#6
…这是另一个例子,这次是一个select表达式：
?> select *, cb @< ca
from tb, ta; -- currently not valid, equivalent to:
!> select *, ifnull(cb = (select max(cb) from tb where cb < ca), 0) as 'cb @< ca'
from tb, ta;
+----+------+----+------+----------+
| id | cb | id | ca | cb @< ca |
+----+------+----+------+----------+
| 1 | C | 1 | A | 0 |
| 1 | C | 2 | E | 0 |
| 1 | C | 3 | I | 0 |
| 1 | C | 4 | O | 0 |
| 1 | C | 5 | U | 0 |
| 2 | D | 1 | A | 0 |
| 2 | D | 2 | E | 1 |
| -- (omitting rows with cb @< ca equal to 0 from here on)
| 4 | F | 3 | I | 1 |
| 5 | F | 3 | I | 1 |
| 7 | N | 4 | O | 1 |
| 8 | O | 5 | U | 1 |我知道以下注意事项：
警告#1
@运算符是“非本地”的,因为它们需要了解其操作数的所有可能值.这在上述示例中显示的所有条件似乎并不成问题,但在其他地方可能是一个问题(尽管我还没有找到一个不能被额外的子查询解决的例子).
警告#2
@ …运算符,不像他们的@less对手,不是传递的.他们与<>运算符,但.
警告#3
充分利用@ …运算符可能意味着引入新的索引和表访问类型(如示例中所述).
请注意,这个问题不是作为讨论的起点.我正在寻找为什么像@ …操作符之类的东西不符合标准以及我所知道的任何SQL方言的原因 – 我希望这些原因与这些操作符的定义和/或实现的一些问题有关我忽略了
我知道有一个原因是“奥卡姆剃须刀”(pluralitas nonest ponenda sine required),但是,正如我已经试图显示的,这里也有一些优点(简洁易用的优化).我正在寻找更强的原因
(2014-03-31)@> @< @> =和@< =可以成为|> |< |> =和|< =或类似(read：first greater / less [equal]),以便不与@ identifier前缀的已建立的使用相冲突.
