php - 如何在多语言json中执行相似搜索？

Question

我们已归档以下数据:

{
  "en":"Nokia-130",
  "ar":"\u0646\u0648\u0643\u064a\u0627 130 (2017)",
  "ch":"\u8bfa\u57fa\u4e9a130\uff082017\uff09"
}

我们正在努力实现:

SELECT json_extract(NAME, "$.ar")
FROM products
WHERE json_extract(NAME, "$.ar") LIKE '%\u0646%';

它工作正常并给出结果，但是当我们点击时

SELECT json_extract(NAME, "$.ar")
FROM products
WHERE json_extract(NAME, "$.ar") LIKE '%\u0646\u0648%';

这不起作用。

Answer 1

我无法重现该问题:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `products`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `products` (
    ->   `NAME` JSON
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `products`
    ->   (`NAME`)
    -> VALUES
    ->   ('{
    '>       "en":"Nokia-130",
    '>       "ar":"\u0646\u0648\u0643\u064a\u0627 130 (2017)",
    '>       "ch":"\u8bfa\u57fa\u4e9a130\uff082017\uff09"
    '>     }');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT `NAME` -> "$.ar"
    -> FROM `products`
    -> WHERE `NAME` -> "$.ar" LIKE '%\u0646%';
+----------------------------------------+
| `NAME` -> "$.ar"                       |
+----------------------------------------+
| "u0646u0648u0643u064au0627 130 (2017)" |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT `NAME` -> "$.ar"
    -> FROM `products`
    -> WHERE `NAME` -> "$.ar" LIKE '%\u0646\u0648%';
+----------------------------------------+
| `NAME` -> "$.ar"                       |
+----------------------------------------+
| "u0646u0648u0643u064au0627 130 (2017)" |
+----------------------------------------+
1 row in set (0.00 sec)

参见db-fiddle .