FREE AI-Enhanced Online MariaDB Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE `test1` (
  `game_id` bigint(20) unsigned NOT NULL DEFAULT 0,
  `user_id` bigint(20) unsigned NOT NULL,
  `challenge_id` bigint(20) unsigned DEFAULT NULL,
  `week` int(11) NOT NULL,
  `question_id` bigint(20) unsigned NOT NULL DEFAULT 0,
  `question_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `selected_answer` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `correct_answer` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `score` bigint(20) NOT NULL,
  `sub_score` bigint(20) NOT NULL,
  `reputation` double(10,2) NOT NULL,
  `sub_reputation` double(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

LOCK TABLES `test1` WRITE;

INSERT INTO `test1` VALUES
(80616,1032,62,24,304,'one-choice','[\"2\"]','[\"2\"]',500,300,1.00,1.00),
(80616,1032,62,24,305,'one-choice','[\"4\"]','[\"1\"]',600,200,1.00,1.00),
(80616,1032,62,24,306,'this-that','[\"4\"]','[\"2\"]',600,400,1.00,1.00),
(80616,1032,62,24,307,'multiple-choice','[\"3\"]','[\"2\",\"3\"]',800,500,1.00,1.00),
(380609,14559,22,4,108,'one-choice','[\"1\"]','[\"1\"]',100,50,1.00,1.00),
(380609,14559,22,4,402,'connect-dots','[[\"1\", \"7\"], [\"2\", \"6\"], [\"4\", \"5\"], [\"3\", \"8\"]]','[[\"2\",\"6\"],[\"3\",\"8\"],[\"4\",\"5\"],[\"1\",\"7\"]]',400,30,1.00,1.00),
(380609,14559,22,4,403,'this-that','[\"2\"]','[\"2\"]',200,30,1.00,1.00),
(380609,14559,22,4,1080,'multiple-choice','[\"2\", \"4\"]','[\"2\",\"4\"]',100,100,1.00,1.00);
UNLOCK TABLES;

select question_id,  selected_answer, correct_answer, json_array_intersect(selected_answer, correct_answer) from test1 where question_id = 402;

select question_id, selected_answer, correct_answer, json_array_intersect(selected_answer, correct_answer) from test1;

select json_array_intersect('[["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]]', '[["2","6"],["3","8"],["4","5"],["1","7"]]');
select json_array_intersect('[["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]]', '[["2","6"],["3","8"],["4","5"],["1","7"]]') from test1;

question_id	selected_answer	correct_answer	json_array_intersect(selected_answer, correct_answer)
402	[["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]]	[["2","6"],["3","8"],["4","5"],["1","7"]]	[["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]]

question_id	selected_answer	correct_answer	json_array_intersect(selected_answer, correct_answer)
304	["2"]	["2"]	["2"]
305	["4"]	["1"]	NULL
306	["4"]	["2"]	NULL
307	["3"]	["2","3"]	NULL
108	["1"]	["1"]	NULL
402	[["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]]	[["2","6"],["3","8"],["4","5"],["1","7"]]	NULL
403	["2"]	["2"]	NULL
1080	["2", "4"]	["2","4"]	NULL

json_array_intersect('[["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]]', '[["2","6"],["3","8"],["4","5"],["1","7"]]')
[["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]]