Skip to content

Instantly share code, notes, and snippets.

@win0err

win0err/1.sql

Created February 11, 2018 21:13
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save win0err/a4f75528217e6547468551d0f942aae5 to your computer and use it in GitHub Desktop.
Save win0err/a4f75528217e6547468551d0f942aae5 to your computer and use it in GitHub Desktop.
Download ZIP
Продвинутая работа с JSON в MySQL – https://habrahabr.ru/post/348854/
Raw
1.sql
SELECT * FROM `players`;
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| id | player_and_games | names_virtual |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| 1 | {"id": 1, "name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Sergeant V", "level": 20, "weapon": "sniper rifle"}, "Crazy Tennis": {"won": 4, "lost": 1}}} | Sally |
| 2 | {"id": 2, "name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": {"rank": "Major General VIII", "level": 127, "weapon": "carbine"}, "Crazy Tennis": {"won": 10, "lost": 30}}} | Thom |
| 3 | {"id": 3, "name": "Ali", "games_played": {"Puzzler": {"time": 12}, "Battlefield": {"rank": "First Sergeant II", "level": 37, "weapon": "machine gun"}, "Crazy Tennis": {"won": 30, "lost": 21}}} | Ali |
| 4 | {"id": 4, "name": "Alfred", "games_played": {"Puzzler": {"time": 10}, "Battlefield": {"rank": "Chief Warrant Officer Five III", "level": 73, "weapon": "pistol"}, "Crazy Tennis": {"won": 47, "lost": 2}}} | Alfred |
| 5 | {"id": 5, "name": "Phil", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Lt. Colonel III", "level": 98, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 130, "lost": 75}}} | Phil |
| 6 | {"id": 6, "name": "Henry", "games_played": {"Puzzler": {"time": 17}, "Battlefield": {"rank": "Captain II", "level": 87, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 68, "lost": 149}}} | Henry |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
Raw
2.sql
+---------------------------+------------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+------------------+------+-----+---------+-------------------+
| id | int(10) unsigned | NO | PRI | NULL | |
| player_and_games | json | NO | | NULL | |
| names_virtual | varchar(20) | NO | | NULL | VIRTUAL GENERATED |
| battlefield_level_virtual | int(11) | NO | | NULL | VIRTUAL GENERATED |
| tennis_won_virtual | int(11) | NO | | NULL | VIRTUAL GENERATED |
| tennis_lost_virtual | int(11) | NO | | NULL | VIRTUAL GENERATED |
| times_virtual | int(11) | NO | | NULL | VIRTUAL GENERATED |
+---------------------------+------------------+------+-----+---------+-------------------+
Raw
3.sql
SELECT `names_virtual`, `battlefield_level_virtual`, `tennis_won_virtual`, `tennis_lost_virtual`, `times_virtual` FROM `players`;
+---------------+---------------------------+--------------------+---------------------+---------------+
| names_virtual | battlefield_level_virtual | tennis_won_virtual | tennis_lost_virtual | times_virtual |
+---------------+---------------------------+--------------------+---------------------+---------------+
| Sally | 20 | 4 | 1 | 7 |
| Thom | 127 | 10 | 30 | 25 |
| Ali | 37 | 30 | 21 | 12 |
| Alfred | 73 | 47 | 2 | 10 |
| Phil | 98 | 130 | 75 | 7 |
| Henry | 87 | 68 | 149 | 17 |
+---------------+---------------------------+--------------------+---------------------+---------------+
Raw
4.sql
SHOW INDEX ON `players`;
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| players | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | |
| players | 1 | names_idx | 1 | names_virtual | A | 6 | NULL | NULL | | BTREE | | |
| players | 1 | times_idx | 1 | times_virtual | A | 5 | NULL | NULL | | BTREE | | |
| players | 1 | won_idx | 1 | tennis_won_virtual | A | 6 | NULL | NULL | | BTREE | | |
| players | 1 | lost_idx | 1 | tennis_lost_virtual | A | 6 | NULL | NULL | | BTREE | | |
| players | 1 | level_idx | 1 | battlefield_level_virtual | A | 6 | NULL | NULL | | BTREE | | |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment