如果有人在mysql方面为我提供一些小帮助,那将非常有用.
It will be very great-full if anyone will provide me a small help in mysql.
我有一个具有10亿条记录的表,其中一列具有逗号分隔的值.
I have a table having 1 billion records in which one column having comma separated value.
我要用逗号分隔值以进行搜索.
I have a comma separated values to search.
我想选择那些在该逗号中具有任何值的行,并从该字符串值中分离出列.
I want to select those rows which having anyone value in that comma separated column from that string value.
例如 表是这样的,它具有以逗号分隔的列:-
e.g, Table is A having column comma_separated like this:-
并且我有一个字符串,其逗号分隔值为"79、62、70、107".
and i have a string having comma separated values "79, 62, 70, 107".
结果将是行号1,2,3,5,7,8,9,10(在提及图片中).
我使用正则表达式进行了此操作,但是这花费了太多时间,因此出于优化目的,我想避免这样做.
推荐答案您无法真正优化自己的工作.基本上,您可以运行如下查询:
You can't really optimize what you are doing. Basically, you can run a query like this:
where find_in_set(79, comma_separated) > 0 or find_in_set(62, comma_separated) > 0 or find_in_set(70, comma_separated) > 0 or find_in_set(107, comma_separated) > 0这需要全表扫描.而且,尽管性能可能比正则表达式稍好,但效率仍然不高.
This requires a full-table scan. And, although the performance might be slightly better than a regular expression, it still will not be efficient.
存储此数据的正确方法是作为连接表.这将乘以行数,因此数据的第一行将成为联结表中的三行(每个值一个).
The proper way to store this data is as a junction table. This multiplies the number of rows, so the first row in your data becomes three rows in the junction table (one for each value).
您不想将事物列表存储为逗号分隔的列表有许多原因.您的值看起来像另一个表中的id,这使情况变得更糟:
There are numerous reasons why you do not want to store lists of things as a comma-separated list. Your values look like ids in another table, making things even worse:
- 值应以其本机格式存储.因此,将整数存储为字符串不是一个好主意.
- SQL中列表的本机结构是表,而不是列表.
- 表上的函数更强大,并且具有字符串函数.
- SQL不能将索引(全文索引除外)用于字符串操作.
- 当您有一个引用另一个表的ID时,应该有一个外键约束.您不能使用存储在字符串中的列表来做到这一点.
更多推荐
两组具有逗号分隔值的MySQL交集
发布评论