使用SELECT * from information_schema.columns where TABLE_SCHEMA='DPCDB' and COLUMN_NAME='Station_No'已查出所有包涵的表了,游标循环怎么用,没用过
通过Navicat for MySQL远程怎么弄
没学过mysql啊,现在是做测试,查看删除Station后数据库中是否删干净了
试试这个CREATE PROCEDURE `getTable`(IN table_schemas VARCHAR(100),IN column_names VARCHAR(100),IN column_values VARCHAR(100)) BEGIN DECLARE done INT DEFAULT 0; DECLARE tabname VARCHAR(100); DECLARE column_counts INT DEFAULT 0; DECLARE cur_tmp CURSOR FOR SELECT TABLE_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA=table_schemas AND COLUMN_NAME=column_names; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; OPEN cur_tmp; FETCH cur_tmp INTO tabname; WHILE done=0 DO SET @mysql:=CONCAT('SELECT COUNT(*) INTO @counts FROM ',tabname,' WHERE ',column_names,'=\'',column_values,'\''); PREPARE stmt FROM @mysql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET column_counts=column_counts+@counts; FETCH cur_tmp INTO tabname; END WHILE; CLOSE cur_tmp; IF column_counts>0 THEN SELECT '存在符合条件的数据!' AS result; ELSE SELECT '不存在符合条件的数据!' AS result; END IF; END; 调用CALL getTable('DPCDB','Station_No','10');
如果仅是一次性工作,则非常简单, 你先用 SELECT table_name from information_schema.columns where TABLE_SCHEMA='DPCDB' and COLUMN_NAME='Station_No' 得到所有表名,然后复制到EXCEL中,利用EXCEL 公式生成 select 'table1' as tb,count(*) from table1 where Station_No=10 union all select 'table2' as tb,count(*) from table2 where Station_No=10 union all select 'table3' as tb,count(*) from table3 where Station_No=10 union all .. select 'tableN' as tb,count(*) from tableN where Station_No=10 在到MYSQL中执行即可。
SELECT table_name from information_schema.columns where TABLE_SCHEMA='DPCDB' and COLUMN_NAME='Station_No'
如果有, 得到相应表名。判断是否有Station_No=10的记录,得到 结果
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tabname VARCHAR(100);
DECLARE column_counts INT DEFAULT 0;
DECLARE cur_tmp CURSOR FOR SELECT TABLE_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA=table_schemas AND COLUMN_NAME=column_names;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN cur_tmp;
FETCH cur_tmp INTO tabname;
WHILE done=0 DO
SET @mysql:=CONCAT('SELECT COUNT(*) INTO @counts FROM ',tabname,' WHERE ',column_names,'=\'',column_values,'\'');
PREPARE stmt FROM @mysql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET column_counts=column_counts+@counts;
FETCH cur_tmp INTO tabname;
END WHILE;
CLOSE cur_tmp;
IF column_counts>0 THEN
SELECT '存在符合条件的数据!' AS result;
ELSE
SELECT '不存在符合条件的数据!' AS result;
END IF;
END;
调用CALL getTable('DPCDB','Station_No','10');
select 'table1' as tb,count(*) from table1 where Station_No=10 union all
select 'table2' as tb,count(*) from table2 where Station_No=10 union all
select 'table3' as tb,count(*) from table3 where Station_No=10 union all
..
select 'tableN' as tb,count(*) from tableN where Station_No=10
在到MYSQL中执行即可。