使用MySQL，请用好 JSON 这张牌！

{ "Image": {   "Width": 800,   "Height": 600,   "Title": "View from 15th Floor",   "Thumbnail": {     "Url": "http://www.example.com/image/xx9943",     "Height": 125,     "Width": 100   }, "IDs": [116, 943, 234, 38793] }}

[   {     "precision": "zip",     "Latitude": 37.7668,     "Longitude": -122.3959,     "Address": "",     "City": "SAN FRANCISCO",     "State": "CA",     "Zip": "94107",     "Country": "US"   },   {     "precision": "zip",     "Latitude": 37.371991,     "Longitude": -122.026020,     "Address": "",     "City": "SUNNYVALE",     "State": "CA",     "Zip": "94085",     "Country": "US"   } ]

DROP TABLE IF EXISTS UserLogin;
CREATE TABLE UserLogin (    userId BIGINT NOT NULL,    loginInfo JSON,    PRIMARY KEY(userId));

SET @a = '{   "cellphone" : "1",   "wxchat" : "码农",   "77" : "1"}';

INSERT INTO UserLogin VALUES (1,@a);

SET @b = '{    "cellphone" : "1188"}';
INSERT INTO UserLogin VALUES (2,@b);

SELECT    userId,    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,    JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchatFROM UserLogin;+--------+-------------+--------------+| userId | cellphone   | wxchat       |+--------+-------------+--------------+|      1 | 11| 码农     ||      2 | 11| NULL         |+--------+-------------+--------------+2 rows in set (0.01 sec)

SELECT     userId,    loginInfo->>"$.cellphone" cellphone,    loginInfo->>"$.wxchat" wxchatFROM UserLogin;

ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");
ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);

EXPLAIN SELECT  *  FROM UserLogin WHERE cellphone = '11'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: UserLogin   partitions: NULL         type: constpossible_keys: idx_cellphone          key: idx_cellphone      key_len: 1023          ref: const         rows: 1     filtered: 100.00        Extra: NULL1 row in set, 1 warning (0.00 sec)

CREATE TABLE UserLogin (    userId BIGINT,    loginInfo JSON,    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),    PRIMARY KEY(userId),    UNIQUE KEY uk_idx_cellphone(cellphone));

CREATE TABLE Tags (    tagId bigint auto_increment,    tagName varchar(255) NOT NULL,    primary key(tagId));

SELECT * FROM Tags;+-------+--------------+| tagId | tagName      |+-------+--------------+|     1 | 70后         ||     2 | 80后         ||     3 | 90后         ||     4 | 00后         ||     5 | 爱运动       ||     6 | 高学历       ||     7 | 小资         ||     8 | 有房         ||     9 | 有车         ||    10 | 常看电影     ||    11 | 爱网购       ||    12 | 爱外卖       |+-------+--------------+

+-------+---------------------------------------+|用户    |标签                                   |+-------+---------------------------------------+|David  |80后 ；高学历 ；小资 ；有房 ；常看电影   ||Tom    |90后 ；常看电影 ；爱外卖                 |+-------+---------------------------------------

DROP TABLE IF EXISTS UserTag;CREATE TABLE UserTag (    userId bigint NOT NULL,    userTags JSON,    PRIMARY KEY (userId));

INSERT INTO UserTag VALUES (1,'[2,6,8,10]');INSERT INTO UserTag VALUES (2,'[3,10,12]');

ALTER TABLE UserTagADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));

EXPLAIN SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$")\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: UserTag   partitions: NULL         type: refpossible_keys: idx_user_tags          key: idx_user_tags      key_len: 9          ref: const         rows: 1     filtered: 100.00        Extra: Using where1 row in set, 1 warning (0.00 sec)

SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$");+--------+---------------+| userId | userTags      |+--------+---------------+|      1 | [2, 6, 8, 10] ||      2 | [3, 10, 12]   |+--------+---------------+2 rows in set (0.00 sec)

EXPLAIN SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]')\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: UserTag   partitions: NULL         type: rangepossible_keys: idx_user_tags          key: idx_user_tags      key_len: 9          ref: NULL         rows: 3     filtered: 100.00        Extra: Using where1 row in set, 1 warning (0.00 sec)

SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]');+--------+---------------+| userId | userTags      |+--------+---------------+|      1 | [2, 6, 8, 10] |+--------+---------------+1 row in set (0.00 sec)

EXPLAIN SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]')\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: UserTag   partitions: NULL         type: rangepossible_keys: idx_user_tags          key: idx_user_tags      key_len: 9          ref: NULL         rows: 4     filtered: 100.00        Extra: Using where1 row in set, 1 warning (0.00 sec)

SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');+--------+---------------+| userId | userTags      |+--------+---------------+|      1 | [2, 6, 8, 10] ||      2 | [3, 10, 12]   |+--------+---------------+2 rows in set (0.01 sec)