一次mysql数据关于union+concat用法的记录

    SELECT CONCAT('SELECT COUNT(*)  FROM ',table_name,' union all') 
    FROM information_schema.tables WHERE table_name LIKE 'tbl_sms_bill_1806%'
    INTO OUTFILE '/tmp/full06.sql';
    
    
    SELECT CONCAT('SELECT COUNT(*)  FROM ',table_name,' union') 
    FROM information_schema.tables WHERE table_name LIKE 'tbl_sms_bill_1808%'
    INTO OUTFILE '/tmp/full08.sql';
    
    SELECT CONCAT('SELECT COUNT(*)  FROM ',table_name,' union') 
    FROM information_schema.tables WHERE table_name LIKE 'tbl_sms_bill_1809%'
    INTO OUTFILE '/tmp/full09.sql';
    
    
    
    SELECT CONCAT('SELECT COUNT(*)  FROM ',table_name,' WHERE stat !="DELIVRD"',' union') 
    FROM information_schema.tables WHERE table_name LIKE 'tbl_sms_bill_1806%'
    INTO OUTFILE '/tmp/bak06.sql';
    
    
    SELECT CONCAT('SELECT COUNT(*)  FROM ',table_name,' WHERE stat !="DELIVRD"',' union') 
    FROM information_schema.tables WHERE table_name LIKE 'tbl_sms_bill_1808%'
    INTO OUTFILE '/tmp/bak08.sql';
    
    
    SELECT CONCAT('SELECT COUNT(*)  FROM ',table_name,' WHERE stat !="DELIVRD"',' union') 
    FROM information_schema.tables WHERE table_name LIKE 'tbl_sms_bill_1809%'
    INTO OUTFILE '/tmp/bak09.sql';

 

 

union 去除重复值 将结果拼接

union all 不去除重复值

 

关于concat的时候  如果语句当中有引号可以用 双引号代替

 

另外有的mysql版本在用concat导出是直接可以的  这个问题还要多测试

 

未来肯定有更好的方法 待补充