mysql - 替代 MySql 中的 DROP 或 CREATE

Question

我有以下查询，直到昨天为止都运行良好...但是由于 xyz 原因 DROP 和 CREATE 访问已从 ID 撤销...所以有什么方法可以在 MySql 中执行以下查询:

DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp
(
call_start_date_time DATE,
cust_ph_no INT(11),
actual_repeat_calls INT(11),
total_calls INT(11),
INDEX(call_start_date_time));

INSERT IGNORE INTO temp(call_start_date_time,cust_ph_no,actual_repeat_calls,total_calls) 
(SELECT DATE(call_start_date_time),cust_ph_no,count(*)-1 as actual_repeat_calls,count(*) as total_calls 
FROM call_data 
WHERE DATE(call_start_date_time) = '2016-04-30' 
AND campaign_name = 'VT' 
AND report_id = '-2' 
AND call_status_disposition NOT LIKE 'ST' 
AND campaign_type = 'ID' 
GROUP BY DATE(call_start_date_time),cust_ph_no);

SELECT call_start_date_time,(SUM(actual_repeat_calls)/SUM(total_calls)) 
as 
repeat_ratio 
FROM temp 
GROUP BY call_start_date_time;

Answer 1

要创建临时表，您必须具有 CREATE TEMPORARY TABLES 权限。

一个简单的替代方法是简单地删除表中的所有行:

delete from temp;

或使用截断:

truncate table temp;

但并不完全相同(临时表是每个 session 的，多个 session 可以使用相同的临时表名称而不会发生冲突)。