问题描述
我想知道是否可以检查列中是否包含字符串值,因为该列包含开始值和结束值.
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:检查范围间隔中是否包含值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!