https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html
SHOW CREATE TABLE tablename // and then full text -> go
CREATE TABLE `json3` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`login` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`info` json NOT NULL,
`td` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
INSERT INTO json3 (login,password,info)
VALUES ('gk','123456','{"course":"IT"}')
SELECT * FROM json3
id login password info td
1 gk 123456 {"course": "IT"} 2020-02-02 16:39:08
SELECT id, login, password, JSON_EXTRACT(info,'$.course') as course
from json3
where JSON_EXTRACT(info,'$.course') = 'IT'
SELECT id, login, password, info->'$.course' as course
from json3
where info->'$.course' = 'IT'
UPDATE json3
set info = JSON_SET(info,'$.login','Gideon Koch')
where id=1
// or where info->'$.course' = 'IT'
// add a new object {'admin:'no'} to the info (JSON data)
UPDATE json3
SET info= JSON_SET(info,'$.admin','no')
WHERE id = 1
// result {"admin": "no", "login": "Gideon Koch", "course": "IT"}
// or under type as free
UPDATE json3
SET info= JSON_INSERT(info,'$.type','free')
WHERE id = 1
// result {"type": "free", "admin": "no", "login": "Gideon Koch", "course": "IT"}
// Remove an item
UPDATE json3
SET info= JSON_REMOVE(info,'$.type')
WHERE id = 1
// result {"admin": "no", "login": "Gideon Koch", "course": "IT"}
JSON_ARRAY_INSERT