一起使用 SQL LIKE 和 IN [英] Using SQL LIKE and IN together

问题描述

有没有办法将 LIKE 和 IN 一起使用?

Is there a way to use LIKE and IN together?

我想实现这样的目标.

SELECT * FROM tablename WHERE column IN ('M510%', 'M615%', 'M515%', 'M612%');

所以基本上我希望能够将列与一堆不同的字符串相匹配.有没有另一种方法可以通过一个查询来做到这一点，或者我是否必须遍历我正在寻找的字符串数组?

So basically I want to be able to match the column with a bunch of different strings. Is there another way to do this with one query or will I have to loop over the array of strings I am looking for?

推荐答案

您可以在一个查询中通过将单个 LIKE 与 OR 串在一起来完成:

You can do it by in one query by stringing together the individual LIKEs with ORs:

SELECT * FROM tablename
WHERE column LIKE 'M510%'
OR    column LIKE 'M615%'
OR    column LIKE 'M515%'
OR    column LIKE 'M612%';

请注意，像 LIKE 和每行函数这样的东西并不总是能很好地扩展.如果您的表可能会变大，您可能需要考虑向您的表中添加另一列以独立存储该字段的前四个字符.

Just be aware that things like LIKE and per-row functions don't always scale that well. If your table is likely to grow large, you may want to consider adding another column to your table to store the first four characters of the field independently.

这会复制数据，但您可以通过使用插入和更新触发器来保证它保持一致.然后在该新列上放置索引，您的查询将变为:

This duplicates data but you can guarantee it stays consistent by using insert and update triggers. Then put an index on that new column and your queries become:

SELECT * FROM tablename WHERE newcolumn IN ('M510','M615','M515','M612');

这会将计算成本移动到必要的程度(当数据更改时)，不是每次您阅读它时.事实上，你可以更进一步，将你的新列作为一个布尔值来表示它是四种特殊类型之一(如果这组特价不会经常改变).然后查询会更快:

This moves the cost-of-calculation to the point where it's necessary (when the data changes), not every single time you read it. In fact, you could go even further and have your new column as a boolean indicating that it was one of the four special types (if that group of specials will change infrequently). Then the query would be an even faster:

SELECT * FROM tablename WHERE is_special = 1;

这种存储要求与速度的权衡对于大型数据库来说是一个有用的技巧——通常，磁盘空间便宜，CPU 消耗量很大，而且数据的读取频率远高于写入频率.通过将计算成本转移到写入阶段，您可以分摊所有读取的成本.

This tradeoff of storage requirement for speed is a useful trick for larger databases - generally, disk space is cheap, CPU grunt is precious, and data is read far more often than written. By moving the cost-of-calculation to the write stage, you amortise the cost across all the reads.

这篇关于一起使用 SQL LIKE 和 IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！