Temporary tables and dynamic SQL

A temporary table

MySQL data is based on tables, so objects, arrays and other variables are not suitable for traditional programming languages. But what if sometimes the processing logic is too complicated, and you need variables to cache the results of intermediate processing? Use temporary tables.

MySQL temporary tables are very useful when we need to save some temporary data. Temporary tables are only visible in the current connection. When a connection is closed, Mysql automatically drops the table and frees all space. To use temporary tables, MySQL version 3.23 or older is required

create temporary table temp_table(
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) null,
  PRIMARY KEY (id)
);
Copy the code

A temporary table is created in the same way as a normal table, except that a temporary table is added to indicate that it is a temporary table. Temporary tables are created with Engine =InnoDB by default. Temporary tables are created with Engine =memory for speed

Delete table delete table delete table delete table delete table delete

drop temporary table if exists temp_table;
Copy the code

After creating a temporary table, you can perform insert and update operations like a normal table. Proper use of temporary tables can simplify code logic.

Suppose we now have an array [“S0002”, “S0003”] as an input parameter, and the array contains the student number. We need to retrieve the student name array: [“Tony”, “Bob”]

select CONCAT('[', GROUP_CONCAT(JSON_QUOTE(name)), ']') from student_table
where number in ("S0002", "S0003");
Copy the code

If you use in, you can write it in one sentence, but you can write it this way, how do you use in on an array or a string that’s passed in

We know that in is the result of a select table, so if you’re using loops and temporary tables, you can create a temporary table, add values to the temporary table using loops, and then you can use in

CREATE PROCEDURE `get_name`(in p_input json)
BEGIN
  declare v_i int;
  declare v_length int;

  declare v_number varchar(255);

  set v_length = json_length(p_input);
  set v_i = 0;
  
  drop temporary table if exists temp_table;
  create temporary table temp_table(
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `number` varchar(255) null,
  PRIMARY KEY (id)
) ENGINE = memory;

  while v_i < v_length do
    set v_number = json_unquote(json_extract(p_input,concat('$[',v_i,']')));
    insert into temp_table(number) values (v_number);
    set v_i = v_i + 1;
  end while;
  
  select CONCAT('[', GROUP_CONCAT(JSON_QUOTE(name)), ']') from student_table
  where number in (select number from temp_table);
END
Copy the code

Don’t forget to delete the temporary table before using it. Be careful that the temporary table still contains previous data

Dynamic SQL

Since MySQL5.0, dynamic SQL has been supported, similar to the eval statement in partially interpreted languages, which is a way to turn strings into executable code. So the above example would have been much less complex to solve if you had just used a string

CREATE PROCEDURE `get_name`(in p_input json) BEGIN set @v_sql = concat("select CONCAT('[', GROUP_CONCAT(JSON_QUOTE(name)), ']') from student_table where number in (", substring(p_input, 2, LENGTH(p_input)-2), ") "); PREPARE stmt FROM @v_sql; EXECUTE stmt; deallocate prepare stmt; ENDCopy the code

The array is pinched using subString, and concat is concatenated into a completed SELECT statement, which is executed using dynamic SQL. So STMT is a variable, and that’s how you write it, so I’m just going to paste the same thing every time.