MySQL Stored Procedure for CRUD Dynamic Queries
The new MySQL 5.7 version has some great features.
One of them is the ‘JSON data’ type. We can use it for column of table like ‘Virtual Colunm’ with Indexing.
But, ‘JSON data’ is also useful for stored procedure. Because we can compact parameters and the procedure could be more flexible.I made the procedures for table insert/update via JSON parameter.
When I apply them to my project, data-handling codes in the application decreased dramatically.
(These procedures impliment ‘MySQL Procedure Pattern for Nested Transaction‘)
|
The code below is an old version. The latest source code is in Github. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
DELIMITER $ CREATE PROCEDURE `sp_do_insert_table_by_json`( IN `i_columns` JSON, IN `i_table` VARCHAR(64), IN `i_schema` VARCHAR(64), OUT `o_result` JSON ) body:BEGIN /** General Procedure for Table Inserting from JSON 2016-06 Aiden Kihyun Kim Parameters IN `i_columns` JSON : Columns {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_table` VARCHAR(64) : Table name IN `i_schema` VARCHAR(64) : Database name OUT `o_result` JSON : {"return":return_code, "row_count":row_count, "last_insert_id":last_insert_id} **/ DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT; DECLARE v_schema VARCHAR(64) DEFAULT NULL; DECLARE c_done BIT DEFAULT FALSE; DECLARE v_column VARCHAR(64) DEFAULT NULL; DECLARE v_column_key VARCHAR(64) DEFAULT NULL; DECLARE v_extra VARCHAR(64) DEFAULT NULL; DECLARE v_type VARCHAR(64) DEFAULT NULL; DECLARE v_query1 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE v_query2 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE require_rollback CONDITION FOR SQLSTATE '45000'; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET o_result = JSON_SET(o_result, '$.return', -999); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1062 BEGIN SET o_result = JSON_SET(o_result, '$.return', -901); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1216,1452 BEGIN SET o_result = JSON_SET(o_result, '$.return', -902); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1217,1451 BEGIN SET o_result = JSON_SET(o_result, '$.return', -903); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR require_rollback BEGIN IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; SET o_result = JSON_OBJECT('return', -1, 'row_count', -1); SET v_schema = IF(i_schema IS NULL, database(), v_schema); BEGIN DECLARE c_column CURSOR FOR SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=v_schema AND TABLE_NAME=i_table AND EXTRA<>'VIRTUAL GENERATED' ORDER BY ORDINAL_POSITION; DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE; OPEN c_column; cursor_loop: LOOP FETCH c_column INTO v_column, v_column_key, v_extra; IF c_done THEN LEAVE cursor_loop; END IF; IF (v_column_key='PRI' AND v_extra='auto_increment') THEN SET o_result = JSON_SET(o_result, '$.last_insert_id', NULL); ELSEIF JSON_CONTAINS_PATH(i_columns,'one',CONCAT('$.',v_column)) THEN SET v_query1 = CONCAT(IF(v_query1 IS NULL,'',CONCAT(v_query1,', ')), '`', v_column, '`'); SET v_query2 = IF(v_query2 IS NULL,'',CONCAT(v_query2,', ')); SET v_type = JSON_TYPE(JSON_EXTRACT(i_columns,CONCAT('$.',v_column))); IF v_type='NULL' THEN SET v_query2 = CONCAT(v_query2, 'NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query2 = CONCAT(v_query2, JSON_EXTRACT(i_columns, CONCAT('$.',v_column))); ELSE SET v_query2 = CONCAT(v_query2, '''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_columns, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; END IF; END LOOP; CLOSE c_column; END; IF (v_query1 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -101); SIGNAL require_rollback; END IF; IF (v_query2 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -102); SIGNAL require_rollback; END IF; IF (v_autocommit = 1) THEN SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION autocommit = 0; START TRANSACTION; END IF; SET @query = CONCAT('INSERT INTO `', v_schema, '`.`', i_table, '` (', v_query1, ') VALUES (', v_query2, ')'); PREPARE stmt FROM @query; EXECUTE stmt; SELECT ROW_COUNT(), LAST_INSERT_ID() INTO @row_count, @last_insert_id; DEALLOCATE PREPARE stmt; IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN COMMIT; SET SESSION autocommit = 1; END IF; IF JSON_CONTAINS_PATH(o_result,'one','$.last_insert_id') THEN SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count, '$.last_insert_id', @last_insert_id); ELSE SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count); END IF; END$ DELIMITER ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
DELIMITER $ CREATE PROCEDURE `sp_do_update_table_by_json`( IN `i_columns` JSON, IN `i_conditions` JSON, IN `i_table` VARCHAR(64), IN `i_schema` VARCHAR(64), OUT `o_result` JSON ) body:BEGIN /** General Procedure for Table Updating from JSON 2016-06 Aiden Kihyun Kim Parameters IN `i_columns` JSON : Columns {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_conditions` JSON : Search conditions {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_table` VARCHAR(64) : Table name IN `i_schema` VARCHAR(64) : Database name OUT `o_result` JSON : {"return":return_code, "row_count":row_count} **/ DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT; DECLARE v_schema VARCHAR(64) DEFAULT NULL; DECLARE c_done BIT DEFAULT FALSE; DECLARE v_column VARCHAR(64) DEFAULT NULL; DECLARE v_column_key VARCHAR(64) DEFAULT NULL; DECLARE v_extra VARCHAR(64) DEFAULT NULL; DECLARE v_type VARCHAR(64) DEFAULT NULL; DECLARE v_array JSON DEFAULT NULL; DECLARE v_query1 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE v_query2 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE require_rollback CONDITION FOR SQLSTATE '45000'; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET o_result = JSON_SET(o_result, '$.return', -999); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1062 BEGIN SET o_result = JSON_SET(o_result, '$.return', -901); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1216,1452 BEGIN SET o_result = JSON_SET(o_result, '$.return', -902); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1217,1451 BEGIN SET o_result = JSON_SET(o_result, '$.return', -903); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR require_rollback BEGIN IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; SET o_result = JSON_OBJECT('return', -1, 'row_count', -1); SET v_schema = IF(i_schema IS NULL, database(), v_schema); BEGIN DECLARE c_column CURSOR FOR SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=v_schema AND TABLE_NAME=i_table AND EXTRA<>'VIRTUAL GENERATED' ORDER BY ORDINAL_POSITION; DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE; OPEN c_column; cursor_loop: LOOP FETCH c_column INTO v_column, v_column_key, v_extra; IF c_done THEN LEAVE cursor_loop; END IF; IF JSON_CONTAINS_PATH(i_columns,'one',CONCAT('$.',v_column)) THEN SET v_type = JSON_TYPE(JSON_EXTRACT(i_columns,CONCAT('$.',v_column))); SET v_query1 = CONCAT(IF(v_query1 IS NULL,'',CONCAT(v_query1,', ')), '`', v_column, '`='); IF v_type='NULL' THEN SET v_query1 = CONCAT(v_query1, 'NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query1 = CONCAT(v_query1, JSON_EXTRACT(i_columns, CONCAT('$.',v_column))); ELSE SET v_query1 = CONCAT(v_query1, '''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_columns, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; END IF; IF JSON_CONTAINS_PATH(i_conditions,'one',CONCAT('$.',v_column)) THEN SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,CONCAT('$.',v_column))); SET v_query2 = CONCAT(IF(v_query2 IS NULL,'',CONCAT(v_query2,' AND ')), '`', v_column, '`'); IF v_type='NULL' THEN SET v_query2 = CONCAT(v_query2, '=NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query2 = CONCAT(v_query2, '=', JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))); ELSEIF v_type IN ('ARRAY','OBJECT') THEN SET v_array = JSON_EXTRACT(i_conditions,CONCAT('$.',v_column)); IF v_type = 'OBJECT' THEN IF JSON_CONTAINS_PATH(v_array,'one',CONCAT('$."2"')) THEN SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'),JSON_EXTRACT(v_array,'$."2"')); ELSE SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"')); END IF; END IF; IF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'BETWEEN' THEN SET v_query2 = CONCAT(v_query2, ' BETWEEN ', JSON_EXTRACT(v_array,'$[1]'), ' AND ', JSON_EXTRACT(v_array,'$[2]')); ELSEIF JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')) IN ('<','<=','>','>=') THEN SET v_query2 = CONCAT(v_query2, JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')), JSON_EXTRACT(v_array,'$[1]')); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'LIKE' THEN SET v_query2 = CONCAT(v_query2, ' LIKE ''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')),'''',''''''), ''''); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IN' THEN SET v_query2 = CONCAT(v_query2, ' IN (', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), ')'); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IS' THEN SET v_query2 = CONCAT(v_query2, ' IS ', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]'))); END IF; ELSE SET v_query2 = CONCAT(v_query2, '=''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; END IF; END LOOP; CLOSE c_column; END; IF (v_query1 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -101); SIGNAL require_rollback; END IF; IF (v_query2 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -102); SIGNAL require_rollback; END IF; IF (v_autocommit = 1) THEN SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION autocommit = 0; START TRANSACTION; END IF; SET @query = CONCAT('UPDATE `', v_schema, '`.`', i_table, '` SET ', v_query1, ' WHERE ', v_query2); PREPARE stmt FROM @query; EXECUTE stmt; SELECT ROW_COUNT() INTO @row_count; DEALLOCATE PREPARE stmt; IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN COMMIT; SET SESSION autocommit = 1; END IF; SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count); END$ DELIMITER ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 |
DELIMITER $ CREATE PROCEDURE `sp_do_delete_table_by_json`( IN `i_conditions` JSON, IN `i_table` VARCHAR(64), IN `i_schema` VARCHAR(64), OUT `o_result` JSON ) body:BEGIN /** General Procedure for Table Row Deleting from JSON 2016-06 Aiden Kihyun Kim Parameters IN `i_conditions` JSON : Search conditions {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_table` VARCHAR(64) : Table name IN `i_schema` VARCHAR(64) : Database name OUT `o_result` JSON : {"return":return_code, "row_count":row_count} **/ DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT; DECLARE v_schema VARCHAR(64) DEFAULT NULL; DECLARE c_done BIT DEFAULT FALSE; DECLARE v_column VARCHAR(64) DEFAULT NULL; DECLARE v_column_key VARCHAR(64) DEFAULT NULL; DECLARE v_extra VARCHAR(64) DEFAULT NULL; DECLARE v_type VARCHAR(64) DEFAULT NULL; DECLARE v_array JSON DEFAULT NULL; DECLARE v_query TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE require_rollback CONDITION FOR SQLSTATE '45000'; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET o_result = JSON_SET(o_result, '$.return', -999); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1062 BEGIN SET o_result = JSON_SET(o_result, '$.return', -901); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1216,1452 BEGIN SET o_result = JSON_SET(o_result, '$.return', -902); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1217,1451 BEGIN SET o_result = JSON_SET(o_result, '$.return', -903); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR require_rollback BEGIN IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; SET o_result = JSON_OBJECT('return', -1, 'row_count', -1); SET v_schema = IF(i_schema IS NULL, database(), v_schema); BEGIN DECLARE c_column CURSOR FOR SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=v_schema AND TABLE_NAME=i_table AND EXTRA<>'VIRTUAL GENERATED' ORDER BY ORDINAL_POSITION; DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE; OPEN c_column; cursor_loop: LOOP FETCH c_column INTO v_column, v_column_key, v_extra; IF c_done THEN LEAVE cursor_loop; END IF; IF JSON_CONTAINS_PATH(i_conditions,'one',CONCAT('$.',v_column)) THEN SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,CONCAT('$.',v_column))); SET v_query = CONCAT(IF(v_query IS NULL,'',CONCAT(v_query,' AND ')), '`', v_column, '`'); IF v_type='NULL' THEN SET v_query = CONCAT(v_query, '=NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query = CONCAT(v_query, '=', JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))); ELSEIF v_type IN ('ARRAY','OBJECT') THEN SET v_array = JSON_EXTRACT(i_conditions,CONCAT('$.',v_column)); IF v_type = 'OBJECT' THEN IF JSON_CONTAINS_PATH(v_array,'one',CONCAT('$."2"')) THEN SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'),JSON_EXTRACT(v_array,'$."2"')); ELSE SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"')); END IF; END IF; IF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'BETWEEN' THEN SET v_query = CONCAT(v_query, ' BETWEEN ', JSON_EXTRACT(v_array,'$[1]'), ' AND ', JSON_EXTRACT(v_array,'$[2]')); ELSEIF JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')) IN ('<','<=','>','>=') THEN SET v_query = CONCAT(v_query, JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')), JSON_EXTRACT(v_array,'$[1]')); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'LIKE' THEN SET v_query = CONCAT(v_query, ' LIKE ''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')),'''',''''''), ''''); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IN' THEN SET v_query = CONCAT(v_query, ' IN (', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), ')'); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IS' THEN SET v_query = CONCAT(v_query, ' IS ', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]'))); END IF; ELSE SET v_query = CONCAT(v_query, '=''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; END IF; END LOOP; CLOSE c_column; END; IF (v_query IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -101); SIGNAL require_rollback; END IF; IF (v_autocommit = 1) THEN SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION autocommit = 0; START TRANSACTION; END IF; SET @query = CONCAT('DELETE FROM `', v_schema, '`.`', i_table, '` WHERE ', v_query); PREPARE stmt FROM @query; EXECUTE stmt; SELECT ROW_COUNT() INTO @row_count; DEALLOCATE PREPARE stmt; IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN COMMIT; SET SESSION autocommit = 1; END IF; SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count); END$ DELIMITER ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 |
DELIMITER $ CREATE PROCEDURE `sp_do_search_table_by_json`( IN `i_conditions` JSON, IN `i_table` VARCHAR(64), IN `i_schema` VARCHAR(64), OUT `o_result` JSON ) body:BEGIN /** General Procedure for Table Searching from JSON 2016-06 Aiden Kihyun Kim Parameters IN `i_conditions` JSON : Search conditions {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_table` VARCHAR(64) : Table name IN `i_schema` VARCHAR(64) : Database name OUT `o_result` JSON : {"return":return_code, "row_count":row_count} **/ DECLARE v_schema VARCHAR(64) DEFAULT NULL; DECLARE c_done BIT DEFAULT FALSE; DECLARE v_column VARCHAR(64) DEFAULT NULL; DECLARE v_column_key VARCHAR(64) DEFAULT NULL; DECLARE v_extra VARCHAR(64) DEFAULT NULL; DECLARE v_type VARCHAR(64) DEFAULT NULL; DECLARE v_array JSON DEFAULT NULL; DECLARE v_query1 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE v_query2 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE v_query3 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET o_result = JSON_SET(o_result, '$.return', -999); SET o_result = JSON_OBJECT('return', -1, 'row_count', -1); SET v_schema = IF(i_schema IS NULL, database(), v_schema); BEGIN DECLARE c_column CURSOR FOR SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = v_schema AND TABLE_NAME = i_table ORDER BY ORDINAL_POSITION; DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE; OPEN c_column; cursor_loop: LOOP FETCH c_column INTO v_column, v_column_key, v_extra; IF c_done THEN LEAVE cursor_loop; END IF; SET v_query1 = IF(v_query1 IS NULL,'',CONCAT(v_query1,', ')); IF (v_data_type = 'json') THEN SET v_query1 = CONCAT(v_query1, 'CAST(`', v_column, '` AS CHAR) AS ', v_column); ELSEIF (INSTR(v_data_type,'binary')>0) THEN SET v_query1 = CONCAT(v_query1, 'HEX(`', v_column, '`) AS ', v_column); ELSEIF (INSTR(v_data_type,'point')>0 OR INSTR(v_data_type,'linestring')>0 OR INSTR(v_data_type,'polygon')>0 OR INSTR(v_data_type,'geometry')>0) THEN SET v_query1 = CONCAT(v_query1, 'ST_AsText(`', v_column, '`) AS ', v_column); ELSE SET v_query1 = CONCAT(v_query1, '`', v_column, '`'); END IF; IF JSON_CONTAINS_PATH(i_conditions,'one',CONCAT('$.',v_column)) THEN SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,CONCAT('$.',v_column))); IF v_type='NULL' THEN SET v_query3 = CONCAT('`', v_column, '`=NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query3 = CONCAT('`', v_column, '`=', JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))); ELSEIF v_type IN ('ARRAY','OBJECT') THEN SET v_array = JSON_EXTRACT(i_conditions,CONCAT('$.',v_column)); IF v_type = 'OBJECT' THEN IF JSON_CONTAINS_PATH(v_array,'one',CONCAT('$."2"')) THEN SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'),JSON_EXTRACT(v_array,'$."2"')); ELSE SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"')); END IF; END IF; IF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'BETWEEN' THEN SET v_query3 = CONCAT('`', v_column, '` BETWEEN ', JSON_EXTRACT(v_array,'$[1]'), ' AND ', JSON_EXTRACT(v_array,'$[2]')); ELSEIF JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')) IN ('<','<=','>','>=') THEN SET v_query3 = CONCAT('`', v_column, '`', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')), JSON_EXTRACT(v_array,'$[1]')); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'LIKE' THEN SET v_query3 = CONCAT('`', v_column, '` LIKE ''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')),'''',''''''), ''''); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IN' THEN SET v_query3 = CONCAT('`', v_column, '` IN (', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), ')'); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IS' THEN SET v_query3 = CONCAT('`', v_column, '` IS ', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]'))); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'MATCH' THEN SET v_query3 = CONCAT('MATCH (', v_column, ') AGAINST (''"', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), '"'' IN BOOLEAN MODE)' ); END IF; ELSE SET v_query3 = CONCAT('`', v_column, '`=''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; SET v_query2 = CONCAT(IF(v_query2 IS NULL,' ',CONCAT(v_query2,' AND ')), v_query3); END IF; END LOOP; CLOSE c_column; END; IF (v_query1 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -101); LEAVE body; END IF; IF (v_query2 IS NULL) THEN SET v_query2 = '1=1'; END IF; SET @query = CONCAT('SELECT SQL_CALC_FOUND_ROWS ', v_query1, ' FROM `', v_schema, '`.`', i_table, '` WHERE ', v_query2); IF JSON_CONTAINS_PATH(i_conditions,'one','$.sort_field') AND JSON_TYPE(JSON_EXTRACT(i_conditions,'$.sort_field'))<>'NULL' THEN SET @query = CONCAT(@query, ' ORDER BY ', JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.sort_field'))); IF JSON_CONTAINS_PATH(i_conditions,'one','$.sort_order') AND JSON_TYPE(JSON_EXTRACT(i_conditions,'$.sort_order'))<>'NULL' THEN SET @query = CONCAT(@query, ' ', JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.sort_order'))); END IF; END IF; IF JSON_CONTAINS_PATH(i_conditions,'one','$.page') AND (JSON_TYPE(JSON_EXTRACT(i_conditions,'$.page'))='INTEGER' OR JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.page')) REGEXP '[0-9]+') AND JSON_CONTAINS_PATH(i_conditions,'one','$.page_size') AND (JSON_TYPE(JSON_EXTRACT(i_conditions,'$.page_size'))='INTEGER' OR JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.page_size')) REGEXP '[0-9]+') THEN SET @query = CONCAT(@query, ' LIMIT ', ((CAST(JSON_EXTRACT(i_conditions,'$.page') AS UNSIGNED)-1)*CAST(JSON_EXTRACT(i_conditions,'$.page_size') AS UNSIGNED)), ',', JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.page_size'))); END IF; PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT FOUND_ROWS() INTO @row_count; SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count); END$ DELIMITER ; |
Print Friendly
