JSON_EXRACT 與原生 SQL 查詢
我本來打算在 SO 上問這個，但想得更好，所以我在這裡嘗試一下。
我已經對此進行了搜尋，但找不到任何確定的數據。我有一個 MySQL 表，如下所示：
| id | type | value |該
value欄位包含一個 JSON 字元串，列類型為 JSON。我的 JSON 包含我希望能夠在 IE 上呼叫的標識資訊WHERE json_extract(value, '$.contractor_id')='12345'這樣做而不是僅僅創建一個單獨的
contractor_id列有什麼性能影響？這個特定的表有大約 500,000 行。也不是一個可contractor_id鍵或可索引的欄位……那麼它真的是 6 單向半打嗎？還是出於性能考慮，我需要創建一個單獨的列是否有特定原因？
取決於您的 MySQL 版本。
在 MySQL 5.7 中，可以基於 json_extract() 表達式創建一個虛擬列，並在該虛擬列上創建一個索引。但是您必須使用該虛擬列進行搜尋才能使用索引。
mysql> create table mytable (id serial primary key, type text, value json); Query OK, 0 rows affected (0.01 sec) mysql> insert into mytable set value = '{"contractor_id": "12345"}'; Query OK, 1 row affected (0.00 sec) mysql> alter table mytable add column contractor_id int as (json_extract(value, '$.contactor_id')), add index (contractor_id); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from mytable where json_extract(value, '$.contractor_id')='12345'; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select * from mytable where contractor_id='12345'; +----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | mytable | NULL | ref | contractor_id | contractor_id | 5 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)在 MySQL 8.0 中，您可以在表達式上創建虛擬索引，而無需先創建虛擬列。但是對 json 表達式的索引是有限制的。
mysql> alter table mytable add index ((json_unquote(json_extract(value, '$.contractor_id')))); ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.所以我必須將它轉換為一個整數：
mysql> alter table mytable add index ((cast(json_unquote(json_extract(value, '$.contractor_id')) as signed))); Query OK, 0 rows affected (0.01 sec)優化器似乎能夠解決這個問題，我什至可以根據部分錶達式來使用索引。
mysql> explain select * from mytable where json_extract(`value`,'$.contractor_id')=12345; +----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | mytable | NULL | ref | functional_index | functional_index | 9 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+------------------+------------------+---------+-------+------+----------+-------+但是，RDBMS 中關於 JSON 的所有這些特性都是一個麻煩。您最終會被迫採用複雜且需要深入了解高級功能的解決方案。
為什麼不直接創建
contractor_id或任何其他要作為普通列索引的屬性？那要簡單得多。我看到人們在 MySQL 中使用 JSON 的次數越多，我就越覺得它是添加到產品中的最糟糕和最不必要的特性之一。
在某些情況下，當您必須儲存具有可變欄位的數據時，您可能確實需要一個“半結構化”列。JSON 對此很有用，或者 XML、YAML 或 protobufs 等。但是讓它們像普通列一樣支持 SQL 操作並不是一個好策略。
對要搜尋或排序的屬性使用普通列。如果必須，請僅使用 JSON 作為“有效負載”列來儲存可變數據。
您可能還喜歡我的演講How to Use JSON in MySQL Wrong。我在 MySQL 8.0 之前開發了該展示文稿，因此它不包括表達式索引，但其他點仍然是正確的，例如 JSON 需要更多空間來儲存相同的數據。