Mysql
Mysql JSON 按鍵值更新
我有一張像這樣的桌子：
CREATE TABLE `campus_tb` ( `campus_id` int(11) NOT NULL AUTO_INCREMENT, `campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`campus_dataJSON`)), PRIMARY KEY (`campus_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci INSERT INTO `campus_tb`( `campus_dataJSON`) VALUES ( '[ {"id":"100","u_email": "dr@kol.vop","name":"James","age":"17","course":"IT"}, {"id":"101","u_email": "meg@gmail.com","name":"Eric","age":"19","course":"CS"}, {"id":"102","u_email": "kitt@joko.com","name":"Julie","age":"21"}]') +--------------------+-----------------------------------------------------------+ | campus_id | campus_dataJSON | +--------------------+-----------------------------------------------------------+ | 1 | [ | {"id":"100","u_email": "dr@kol.vop","name":"James","age":"17","course":"IT"}, | {"id":"101","u_email": "meg@gmail.com","name":"Eric","age":"19","course":"CS"}, | {"id":"102","u_email": "kitt@joko.com","name":"Julie","age":"21"} | | ] ---------------------------------------------------------------------------------- | 2 | [ | {"id":"12","u_email": "dr2@kol.vop","name":"Fomu","age":"17","course":"IT"}, | {"id":"13","u_email": "meg2@gmail.com","name":"Jenga","age":"19","course":"CS"}, | {"id":"18","u_email": "kitt2@joko.com","name":"Billie","age":"21"} | | ] ----------------------------------------------------------------------------------我正在使用 10.4.15-MariaDB
((1)) MySql 查詢以更新學生的詳細資訊，
"email"WHERE campus_id = 1例如我想添加"admitted":"YES"where'meg@gmail.com'ANDcampus_id=1`{"id":"101","u_email": "meg@gmail.com","name":"Eric","age":"19","course":"CS", "admitted":"YES" }`((2)) Mysql Query to UPDATE from
"age":"21"to"age":"25"where'kitt@joko.com'ANDcampus_id=1這是我迄今為止對 ((1)) 和 ((2)) 所做的嘗試：
UPDATE `campus_tb` set `campus_dataJSON` = JSON_SET( `campus_dataJSON` , json_unquote(json_search( `campus_dataJSON` , 'one', 'dr@kol.vop')), JSON_MERGE(`campus_dataJSON`,'$.admitted','YES') ) where `campus_id` = 1 //Strangely, this clears out all data in the column. UPDATE `campus_tb` set `campus_dataJSON` = JSON_MERGE( `campus_dataJSON` , json_unquote(json_search(`campus_dataJSON` , 'one', 'meg@gmail.com')), JSON_OBJECT('$.admitted','YES')) where `campus_id` =1; UPDATE `campus_tb` set `campus_dataJSON` = = JSON_INSERT(`campus_dataJSON` , '$.admitted', "YES") WHERE `campus_dataJSON`->'$.u_email' = 'dr@kol.vop'; // this returns ERROR near '>u_email' UPDATE `campus_tb` set `campus_dataJSON` = = JSON_SET(`campus_dataJSON` , '$.age', "25") WHERE `campus_dataJSON`->'$.u_email' = 'kitt@joko.com'; // this returns same ERROR near '>email'來自不同網站的範例
我看到了這個
UPDATE players SET player_and_games = JSON_INSERT(player_and_games, '$.games_played.Puzzler', JSON_OBJECT('time', 20)) WHERE player_and_games->'$.name' = 'Henry';從這個網站：https ://www.compose.com/articles/mysql-for-your-json/
但是使用相同的方法會引發錯誤：
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$.email' = '
Json 並沒有那麼複雜，但是更新一個條目很複雜，尤其是當你想要靈活的時候
第一個更新查詢是查詢，您將使用它來查找正確的數組索引並添加您承認的欄位
第二個僅在較短的版本中向您顯示相同的內容，以便您了解概念。
正如我在評論中所說，當您已經知道需要更新、更改、使用規範化資料結構時，這會讓您的生活更輕鬆
CREATE TABLE `campus_tb` ( `campus_id` int(11) NOT NULL AUTO_INCREMENT, `campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`campus_dataJSON`)), PRIMARY KEY (`campus_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `campus_tb`( `campus_dataJSON`) VALUES ( '[ {"id":"100","u_email": "dr@kol.vop","name":"James","age":"17","course":"IT"}, {"id":"101","u_email": "meg@gmail.com","name":"Eric","age":"19","course":"CS"}, {"id":"102","u_email": "kitt@joko.com","name":"Julie","age":"21"}]'); INSERT INTO `campus_tb`( `campus_dataJSON`) VALUES ( '[ {"id":"100","u_email": "dr@kol.vop","name":"James","age":"17","course":"IT"}, {"id":"101","u_email": "meg@gmail.com","name":"Eric","age":"19","course":"CS"}, {"id":"102","u_email": "kitt@joko.com","name":"Julie","age":"21"}]');✓ ✓ ✓SELECT CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', 'meg@gmail.com'),'.',1),'"',''),'.admitted') FROM campus_tb;| CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', 'meg@gmail.com'),'.',1),'"',''),'.admitted') | | :---------------------------------------------------------------------------------------------------------------- | | $[1].承認 | | $[1].承認 |UPDATE campus_tb SET `campus_dataJSON` = JSON_INSERT(`campus_dataJSON`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', 'meg@gmail.com'),'.',1),'"',''),'.admitted'), 'YES') WHERE campus_id = 3;✓UPDATE campus_tb SET `campus_dataJSON` = JSON_INSERT(`campus_dataJSON`, '$[1].admitted', 'YES') WHERE campus_id = 2;✓SELECT * FROM `campus_tb`校園號 | 校園數據JSON --------: | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 | [{“id”：“100”，“u_email”：“dr@kol.vop”，“name”：“James”，“age”：“17”，“course”：“IT”}，{“id “：”101，“u_email”：“meg@gmail.com”，“姓名”：“埃里克”，“年齡”：“19”，“課程”：“CS”，“錄取”：“是”} ，{“id”：“102”，“u_email”：“kitt@joko.com”，“姓名”：“朱莉”，“年齡”：“21”}] 3 | [{“id”：“100”，“u_email”：“dr@kol.vop”，“name”：“James”，“age”：“17”，“course”：“IT”}，{“id “：”101，“u_email”：“meg@gmail.com”，“姓名”：“埃里克”，“年齡”：“19”，“課程”：“CS”，“錄取”：“是”} ，{“id”：“102”，“u_email”：“kitt@joko.com”，“姓名”：“朱莉”，“年齡”：“21”}]UPDATE campus_tb SET `campus_dataJSON` = JSON_REPLACE(`campus_dataJSON`, '$[2].age', 25) WHERE campus_id = 2;✓UPDATE campus_tb SET `campus_dataJSON` = JSON_REPLACE(`campus_dataJSON`, CONCAT(REPLACE(SUBSTRING_INDEX(JSON_SEARCH(`campus_dataJSON`, 'one', 'kitt@joko.com'),'.',1),'"',''),'.age'), 25) WHERE campus_id = 3;✓SELECT * FROM `campus_tb`校園號 | 校園數據JSON --------: | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 | [{“id”：“100”，“u_email”：“dr@kol.vop”，“name”：“James”，“age”：“17”，“course”：“IT”}，{“id “：”101，“u_email”：“meg@gmail.com”，“姓名”：“埃里克”，“年齡”：“19”，“課程”：“CS”，“錄取”：“是”} ，{“id”：“102”，“u_email”：“kitt@joko.com”，“姓名”：“朱莉”，“年齡”：25}] 3 | [{“id”：“100”，“u_email”：“dr@kol.vop”，“name”：“James”，“age”：“17”，“course”：“IT”}，{“id “：”101，“u_email”：“meg@gmail.com”，“姓名”：“埃里克”，“年齡”：“19”，“課程”：“CS”，“錄取”：“是”} ，{“id”：“102”，“u_email”：“kitt@joko.com”，“姓名”：“朱莉”，“年齡”：25}]db<>在這裡擺弄