如何在mysql列中搜索json编码的值？

Question

我在MySQL列（extraData）中存储了一些额外的表单数据，格式为JSON。

示例数据：

{"1":{"name":"sadsdaf ","surname":"sdafa","occupation":"sdaas","email":"[e-mail]","barcode":"052355491"},"2":{"name":"sadafd","surname":"sdafa","occupation":"sddaf","email":"[e-mail]","barcode":"052355492"}}

我想进行一个搜索查询，也搜索存储在json编码字段中的条形码？而不仅仅是这样。

$sql = "SELECT name FROM visitors WHERE barcode = '".$barcode."'";

但同时也

 $sql = "SELECT name FROM visitors WHERE barcode = '".$barcode."' OR extraData = '".$barcode."'";

我需要如何修改extraData变量，才能检查条形码并获得相应的姓名、姓氏等信息？

- alex

Answer 1

SELECT TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(V.extraData,'"name":', -1),',',1),'}',1) 
 FROM visitors V
 WHERE  V.extraData LIKE '%"barcode"%'
   AND :barcode = TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(V.extraData,'"barcode":', -1),',',1),'}',1);

LIKE '%"barcode"%' 过滤 extraData 字段中包含 barcode 值的行。 :barcode 表示需要匹配的值，TRIM 用于去除额外的双引号，嵌套的 SUBSTRING_INDEX 搜索：

从 '"barcode":' 的第一个匹配项开始到下一个 ','（如果有更多键/值）的下一个匹配项，一直到下一级别的 '}'（如果是该级别的最后一个键/值）

1. keep in mind that if you find that a certain key/val is always present in every row and is accessed everywhere you should consider adding an extra field to the table/data model and create an index out of it (ie. name, surname).

2. the values retrieved must be decoded "always".

   "Einstürzende Neubauten" is stored as "Einst\u00fcrzende Neubauten"
   

3. doing search and compare numbers are "ok", like in the example (the trim double quotes sometimes would not be necesary as if you json_encode an integer var it will be stored as "number":99 ). but fractional numbers would be stored depending on the locale "1,5" / "1.5"

4. search and compare strings are more tricky: you have to "jsonencode" the value to match

   "https://www.google.com" is stored as "https:\/\/www.google.com"
   

5. check your data normalization skills.

6. check your optimization skills

7. use an online tool to see how your data will be encoded

- alo Malbarez

---

仅仅因为一个帖子包含“Einstürzende Neubauten”作为例子，就点赞是否被认为是不好的行为？ - lysdexia

@lysdexia 是的，它是这样的。 - Andrew

@Andrew 很高兴我控制住了自己。 - lysdexia