Perch and MySQL 8

If you are using Perch v3.1.6 (or older) with MySQL 8, some database queries may produce an error. This is due to a change in how MySQL handles regular expressions:

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer’s implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe. For information about ways in which applications that use regular expressions may be affected by the implementation change, see Regular Expression Compatibility Considerations.)

There are differences between how International Components for Unicode (ICU) and Henry Spencer library handle regular expression operations. One of the differences is word boundary markers:

The Spencer library supports word-beginning and word-end boundary markers ([[:<:]] and [[:>:]] notation). ICU does not. For ICU, you can use \b to match word boundaries; double the backslash because MySQL interprets it as the escape character within strings.

Perch uses [[:<:]] and [[:>:]] in some of its database queries:

The contains filters

The contains filter uses the [[:<:]] and [[:>:]] notation.

perch_collection('Team', [
    'filter' => 'name',
    'match' => 'contains',
    'value' => 'Joe',
]);

As a workaround, you can write your own regex expression with \b instead:

perch_collection('Team', [
    'filter' => 'name',
    'match' => 'regex',
    'value' => '\bJoe\b',
]);

Note that Perch will escape the backslash for you so you don’t have to.

Search handlers

The content search handler that searches pages, regions and collections also uses the [[:<:]] and [[:>:]]. Some first-party apps like the Perch Blog app also uses them for its search handler.

So unfortunately, if you are using Perch v3.1.6 (or older) with MySQL 8, you probably cannot safely use the perch_content_search() function unless you write your own custom search handlers.

link