MySQL:检查范围间隔中是否包含值 [英] MySQL: check if a value is contained in a range interval

问题描述

我想知道是否可以检查列中是否包含字符串值，因为该列包含开始值和结束值.

I was wondering if it is possible to check if a string value is contained in a column given that the column contains the start and end values.

例如:如果表的NR列包含以下行:

For example: if the table has a column NR with the following rows:

400-500
45-76,23-25
12,14-19,21

400-500
45-76,23-25
12,14-19,21

我想找到其中包含值421的行.因此答案应该在第一行.

I want to find the row which has the value 421 in it. So the answer should be the first row.

在mysql中有可能吗?

Is this possible in mysql?

推荐答案

您应该有两个表:一个用于列，一个用于列范围.这样，一个简单的查询将检索您需要的内容.

You should have two tables: one for columns, one for column ranges. With that, a simple query will retrieve what you need.

CREATE TABLE foo (
    foo_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (foo_id)
)
ENGINE=InnoDB;

CREATE TABLE foo_range (
    foo_id INT(10) UNSIGNED NOT NULL,
    column_from INT(10) UNSIGNED NOT NULL,
    column_to INT(10) UNSIGNED NOT NULL,
    INDEX foo_range_fk1 (foo_id),
    CONSTRAINT foo_range_fk1 FOREIGN KEY (foo_id) REFERENCES foo (foo_id)
)
ENGINE=InnoDB;

INSERT INTO foo(foo_id)
VALUES (1), (2), (3);

INSERT INTO foo_range(foo_id, column_from, column_to)
VALUES (1, 400, 500), (2, 45, 74), (2, 23, 25), (3, 12, 14), (3, 19, 21);

SELECT foo_id
FROM foo_range
WHERE 421 BETWEEN column_from AND column_to;

实际上，除非您要存储其他数据，否则甚至不需要主表:)

And, actually, the main table is not even necessary unless you want to store additional data :)

如果您不喜欢其他数据库设计，则可能必须检索所有行并使用客户端语言进行匹配.

If you are stuck with the other DB design, you'll probably have to retrieve all rows and use your client side language to do the matching.

这篇关于MySQL:检查范围间隔中是否包含值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！