一次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导出是直接可以的 这个问题还要多测试
未来肯定有更好的方法 待补充