比较 MySQL 查询中字符串的值
我在比较 VARCHAR 字段中的值时遇到一些问题。
我有一张包含产品的桌子，每种产品都有体积。我将卷存储在 VARCHAR 字段中，它通常是一个数字（30、40、200..），但有些产品具有多个卷，并且它们的数据以分号分隔存储，例如 30;60;80。
我知道不建议像这样存储多个卷，但我必须像这样使用它。
我正在尝试实现产品的按量搜索功能。我还想显示体积大于或等于搜索体积的产品。 这不是单卷产品的问题，而是多卷产品的问题。
也许一个例子会让事情变得更清楚：假设我有一个产品，其体积字段为：30;40;70;80。 如果有人搜索一卷，比如说 50 卷，我希望显示该产品。
为此，我正在考虑编写自己的自定义 MySQL 函数（我以前从未这样做过），但也许有人可以提供不同的解决方案。
我为我糟糕的英语表示歉意，但我希望我能清楚地表达我的问题。
谢谢。
I'm having some trouble comparing values found in VARCHAR fields.
I have a table with products and each product has volume. I store the volume in a VARCHAR field and it's usually a number (30, 40, 200..) but there are products that have multiple volumes and their data is stored separated by semicolons, like so 30;60;80.
I know that storing multiple volumes like that is not recommended but I have to work with it like it is.
I'm trying to implement a search by volume function for the products. I want to also display the products that have a bigger or equal volume than the one searched.
This is not a problem with the products that have a single volume, but it is a problem with the multiple volume products.
Maybe an example will make things clearer: Let's say I have a product with this in it's volume field: 30;40;70;80.
If someone searched for a volume, lets say 50, I want that product to be displayed.
To do this I was thinking of writing my own custom MySQL function (I've never this before) but maybe someone can offer a different solution.
I apologize for my poor English but I hope I made my question clear.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您只需与最大值进行比较即可。卷是否总是按从左到右递增的顺序存储？如果是，则与最后一个分号后面的值（如果存在）进行比较。您可以使用 SUBSTRING_INDEX 来选择最后的值。
You only need to compare with the largest value. Are the volumes always stored in increasing order from left to right? If so then compare with the value after the last semicolon if one is present. You can use SUBSTRING_INDEX to select the last value.