matigo

不需要包装用于 INSERT 括号内。见鬼,整个查询可以简化如下:

INSERT INTO analyze_checks(dbname,table_name,frag_ratio, days,needs_optimization,needs_analyzing ) 
SELECT 'test' AS dbname,
       tmp.table_name, 
       CAST(tmp.frag_ratio AS DECIMAL(5,2)) AS frag_ratio, 
       tmp.days,
       CASE WHEN tmp.frag_ratio > 1 THEN 'Yes' ELSE 'No' END AS needs_optimization,    
       CASE WHEN tmp.days > -1 THEN 'Yes' ELSE 'No' END as needs_analyzing 
  FROM (SELECT CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) AS table_name,    
               ROUND(t.DATA_FREE/1024/1024, 2) AS data_free,   
               (t.data_free / (t.index_length + t.data_length)) AS frag_ratio,  
               DATEDIFF(NOW(), last_update) AS days 
          FROM information_schema.tables t LEFT JOIN mysql.innodb_table_stats s ON t.table_name = s.table_name 
         WHERE DATA_FREE > 0) tmp
 WHERE 'Yes' = CASE WHEN tmp.frag_ratio > 1 THEN 'Yes' 
                    WHEN tmp.days > -1 THEN 'Yes'
                    ELSE 'No' END
 ORDER BY frag_ratio DESC;

适当使用括号和衍生表格即可