Why does mariadb regex give contrary result?

Question

I have a column that contains following content:

+------------+
| name       |
+------------+
| 你好世界    |
| HelloWorld　|
| Hello世界 　|
+------------+

and I hope

SELECT `name` FROM `table`  WHERE `name` REGEXP '[u4e00-u9fa5]';

gives me only Chinese contained row like this:

+------------+
| name       |
+------------+
| 你好世界    |
+------------+

but it actually gives me contrary result:

+------------+
| name       |
+------------+
| HelloWorld　|
| Hello世界 　|
+------------+

I knew that:

SELECT `name` FROM `table`  WHERE `name` NOT REGEXP '[u4e00-u9fa5]';

can work as expected,but I want to know why mysql regexp gives contrary result?Is this the default setting?Or I made a mistake.Thanks in advance.

Answer 1

If you are checking to see if a utf8 string has CJK characters in it:

WHERE HEX(name) REGEXP '^(..)*E[456789]'

That will not include the Chinese characters that are not in the "BMP" plane.