MYSQL中的REGEXP加入工作不正常

I have a next mysql query:

我有一个下一个mysql查询:

SELECT 
    shop_sections.title, 
    shop_sections.id, 
    shop_sections.parent_id, 
    count(shop_items.id) AS items
FROM 
    shop_sections 
LEFT JOIN 
    shop_items 
ON 
    shop_items.section_id 
        REGEXP "^" + shop_sections.parent_id + "([[.full-stop.]].*|$)" 
GROUP BY 
    shop_sections.id

REGEXP perform not properly. For row shop_items.section_id = 1 it gets all rows with field shop_items.section_id that are started with '1' (i.e. 13.14 or 13.15, but it must get whether 1 or 1.* (1.1, 1.2, 1.2.3 and the like)).

REGEXP执行不正常。对于row shop_items.section_id = 1,它获取所有带有字段shop_items.section_id的行,以“1”开头(即13.14或13.15,但必须得到1或1. *(1.1,1.2,1.2.3等) ))。

I've noticed that if change

我注意到如果改变了

REGEXP "^" + shop_sections.parent_id + "([[.full-stop.]].*|$)"

REGEXP“^”+ shop_sections.parent_id +“([[.full-stop。]]。* | $)”

REGEXP "^1([[.full-stop.]].*|$)"

than it works properly for 1 (and also for any value if insert it to the query manually). But I want to fetch all items using one query.

比它适用于1(以及任何值,如果手动插入查询)。但我想使用一个查询获取所有项目。

What may be the problem?

可能是什么问题?

UPD.

shop_items.section_id contains values in the next form: parent_section.child_section_1.child_section_2.child_section_3 etc. I need this for pagination on item's pages.

shop_items.section_id包含下一个表单中的值:parent_section.child_section_1.child_section_2.child_section_3等。我需要这个用于项目页面上的分页。

Values in shop_sections.parent_id have the same form.

shop_sections.parent_id中的值具有相同的形式。

All I want to do is to show a tree of sections and a number of items for every section.

我想要做的就是为每个部分显示一个部分树和一些项目。

2 个解决方案

#1

+ can't be used for concatenation in MySQL. Your test should be :

+不能用于MySQL中的连接。你的测试应该是:

ON 
  shop_items.section_id 
    REGEXP CONCAT('^', shop_sections.parent_id, '([[.full-stop.]].*|$)')

The strange results you get are due to the broken concatenation performing an addition instead. Suppose shop_sections.parent_id is 1, then :

您获得的奇怪结果是由于断开的串联执行添加。假设shop_sections.parent_id为1,则:

'^' + shop_sections.parent_id + '([[.full-stop.]].*|$)'

... is really evaluated 0 + 1 + 0 (strings cast as integers are evaluated as 0 unless they begin with a sequence of numbers), which used as a regular expression is equivalent to a useless '1'.

...实际上被评估为0 + 1 + 0(作为整数的字符串被评估为0,除非它们以数字序列开头),其用作正则表达式相当于无用的“1”。

#2

Why do you need the full-stop instead of just escaping the .? as in:

为什么你需要全程而不是只是逃避。?如:

REGEXP "^" + shop_sections.parent_id + "(\\.|$)"

#1