临时表与动态SQL

临时表

MySQL里面的数据是以表为数据基础的，因此对象，数组等变量的处理方式其实不太适合用传统编程语言的方式去使用。但如果有时候处理逻辑太复杂，需要用到变量来缓存一下中间处理的结果怎么办呢，因地制宜，使用临时表。

MySQL临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。使用临时表需要MySQL版本在 3.23以上

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

临时表的创建方式与普通表一样，只不过在前面多出来一个temporary代表这是一个临时表。而且临时表是可选Engine的，默认创建表的时候ENGINE=InnoDB，临时表为了加快速度可以使用ENGINE=memory

使用的时候通常会在前面加一句删除表的操作以免在同一个连接中已经使用过相同名称的表导致意外的错误

drop temporary table if exists temp_table;

临时表创建后可以像普通的表一样进行insert，update等操作，合理使用临时表能简化代码逻辑。

假设我们现在有一个数组["S0002", "S0003"]作为入参，数组里面是学生的学号，我们需要根据学号取出学生姓名的数组：["Tony", "Bob"]，直接写SQL语句的话大概会这么写

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

如果用in的话，可以一句话就写出来，但是写是可以这么写，怎么对着一个传入的数组或者说字符串使用in呢

我们知道in是可以用select出来的表的结果的，如果使用循环和临时表的话，可以创建个临时表，使用循环把值加到临时表，然后就可以用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

不要忘记使用前先删掉临时表，小心临时表里还有以前的数据

动态SQL

MySQL5.0以后支持了动态SQL，类似于部分解释性语言中的eval语句，是一种把字符串变成可执行的代码的方法。所以上面的例子如果直接使用拼字符串的方式来解代码量就少多了

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;
END

使用substring将数组掐头去尾，然后concat拼接成一个完成的select语句，再通过动态SQL执行。其中stmt是一个变量，大家都习惯这么写，这一段基本每次都粘贴一样的即可。

08.临时表与动态SQL

临时表与动态SQL

临时表

动态SQL