不用那么麻烦,直接拼出来delete语句 mysql -s -L -e "select concat('delete from ',table_schema,'.',table_name,' where type in (''et'',''md'',''pd'');') from information_schema.tables where table_schema in ('db1','db2','db3'); "
这个代码只能在SQL Server 中运行,我问MYSQL中怎么写? MySQL不大了解,但最近在用,谢谢!
这个代码只能在SQL Server 中运行,我问MYSQL中怎么写? MySQL不大了解,但最近在用,谢谢!
MYSQL 游标示例: mysql> delimiter // mysql> drop PROCEDURE p_bluelive// Query OK, 0 rows affected (0.00 sec)mysql> CREATE PROCEDURE p_bluelive() -> BEGIN -> DECLARE done INT DEFAULT 0; -> DECLARE v_a INT; -> DECLARE cur1 CURSOR FOR SELECT distinct a FROM t_bluelive; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -> -> OPEN cur1; -> FETCH cur1 INTO v_a; -> WHILE done=0 DO -> set @sqlstmt=concat('SELECT a,count(*) as N from t_bluelive wher e a=',v_a,' INTO OUTFILE \'',v_a,'.txt\''); -> PREPARE stmt1 FROM @sqlstmt; -> EXECUTE stmt1 ; -> DEALLOCATE PREPARE stmt1; -> FETCH cur1 INTO v_a; -> END WHILE; -> END; -> // Query OK, 0 rows affected (0.00 sec)mysql> mysql> delimiter ;
mysql -s -L -e "select concat('delete from ',table_schema,'.',table_name,' where type in (''et'',''md'',''pd'');') from information_schema.tables where table_schema in ('db1','db2','db3'); "
mysql> delimiter //
mysql> drop PROCEDURE p_bluelive//
Query OK, 0 rows affected (0.00 sec)mysql> CREATE PROCEDURE p_bluelive()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE v_a INT;
-> DECLARE cur1 CURSOR FOR SELECT distinct a FROM t_bluelive;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
-> FETCH cur1 INTO v_a;
-> WHILE done=0 DO
-> set @sqlstmt=concat('SELECT a,count(*) as N from t_bluelive wher
e a=',v_a,' INTO OUTFILE \'',v_a,'.txt\'');
-> PREPARE stmt1 FROM @sqlstmt;
-> EXECUTE stmt1 ;
-> DEALLOCATE PREPARE stmt1;
-> FETCH cur1 INTO v_a;
-> END WHILE;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;