MySQL数据库在众多表中对表名的查询及预处理存储过程(变量做表名)

以下的文章主要介绍的是MySQL数据库在众多表中进行表名与字段名的查询的实际操作步骤，以及对实现其查询所要用到的SQL 语句的介绍，还有两个实际解决方案的描述，以下就是文章的主要内容描述。

在MySQL 众多表中查找一个表名或者字段名的 SQL 语句：

1. SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE 'Name'; 
2. SELECT TABLE_NAME  FROM information_schema.`TABLES` WHERE TABLE_NAME LIKE '%_click' OR TABLE_NAME LIKE '%_open' OR TABLE_NAME LIKE '%_reg' AND table_schema = 'ad_flow';

MySQL数据库在众多表中查询表名和字段名，下面两种方法也可以查到：

1. SELECT column_name from information_schema.columns WHERE column_name LIKE ’
   %searchTerm%’ AND table_schema = ‘yourDB’  
2. SELECT column_name from information_schema.columns WHERE column_name LIKE ’
   %searchTerm%’ AND table_schema = ‘yourDB’ AND table_name = ‘yourDBTable’  

这样，我们在面多突然出现的那么多表时，就不会茫然失措了。

 

变量做表名 执行sql存储过程中会吧变量当做表名 所以得用预处理做

BEGIN
#Routine body goes here...
DECLARE ad_id INT;
DECLARE _done INT DEFAULT 0;
DECLARE ad_click INT DEFAULT 0;
DECLARE ad_open INT DEFAULT 0;#ip
DECLARE ad_reg INT DEFAULT 0;
DECLARE today INT;


DECLARE _cur CURSOR FOR
SELECT id FROM 027game.games_ad;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;

OPEN _cur;
REPEAT
FETCH _cur INTO ad_id;
IF NOT _done THEN
#查询id_click
SET @tmp_tablename = CONCAT('ad_flow.',ad_id,'_click');
SET @tmp_sql = CONCAT("SELECT COUNT(id) INTO @tmp_count1 FROM ",@tmp_tablename);
PREPARE stmt FROM @tmp_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET ad_click = @tmp_count1;

#查询id_open
SET @tmp_tablename = CONCAT('ad_flow.',ad_id,'_open');
SET @tmp_sql = CONCAT("SELECT COUNT(DISTINCT(ip)) INTO @tmp_count2 FROM ",@tmp_tablename);
PREPARE stmt FROM @tmp_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET ad_open = @tmp_count2;

#查询id_reg
SET @tmp_tablename = CONCAT('ad_flow.',ad_id,'_reg');
SET @tmp_sql = CONCAT("SELECT COUNT(id) INTO @tmp_count3 FROM ",@tmp_tablename);
PREPARE stmt FROM @tmp_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET ad_reg = @tmp_count3;

SET today = UNIX_TIMESTAMP(CURDATE());
#执行检查 看是更新还是插入 如果有数据 就进行更新 如果没有则插入

SET @tmp_sql = CONCAT("SELECT COUNT(id) INTO @ishave FROM 027game.games_ad_stat"," WHERE ad_id = ",ad_id," AND date =",today);
PREPARE stmt FROM @tmp_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


#执行插入或更新
IF NOT @ishave THEN
SET @tmp_sql = "INSERT INTO 027game.games_ad_stat (date,ip,click,reg,ad_id) VALUES (?,?,?,?,?)";
SET @a = today;
SET @b = ad_open;
SET @c = ad_click;
SET @d = ad_reg;
SET @e = ad_id;
PREPARE stmt FROM @tmp_sql;
EXECUTE stmt USING @a,@b,@c,@d,@e;
DEALLOCATE PREPARE stmt;

ELSE
SET @tmp_sql = "UPDATE 027game.games_ad_stat SET ip=?,click=?,reg=? WHERE ad_id = ? AND date = ?";
SET @a = ad_open;
SET @b = ad_click;
SET @c = ad_reg;
SET @d = ad_id;
SET @e = today;
PREPARE stmt FROM @tmp_sql;
EXECUTE stmt USING @a,@b,@c,@d,@e;
DEALLOCATE PREPARE stmt;

END IF;
END IF;
UNTIL _done END REPEAT;

CLOSE _cur;

END

 

 

BEGIN
  DECLARE tablename VARCHAR(50);
  DECLARE _done INT DEFAULT 0;
    #Routine body goes here...

    #定义循环光标
  DECLARE _cur CURSOR FOR
  SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE (TABLE_NAME LIKE '%_click' OR TABLE_NAME LIKE '%_open' OR TABLE_NAME LIKE '%_reg') AND table_schema = 'ad_flow';

  #定义循环结束标记
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;

  OPEN _cur;
  
  #开始循环
  REPEAT
                FETCH _cur INTO tablename ;
                IF NOT _done THEN
                  SET @clearsql = CONCAT('TRUNCATE ',tablename);
                  PREPARE stmt FROM @clearsql;
                  EXECUTE stmt;
                END IF;
  UNTIL _done END REPEAT;
 
  CLOSE _cur;
END