查询并获取所有数据库的所有数据库名称和子查询特定表 [英] Query and get all database names and subquery especific tables from all databases

问题描述

我有不同的数据库。我在每个数据库中都有表。
我想知道我是否可以询问多少个数据库，但不包括诸如模式， mysql之类的数据库，我曾经知道如何执行由第一个问题产生的所有数据库的特定表所询问的子查询。

I have different databases. I have tables within each database. I would like to know if I can ask how many databases excluding some such as 'schema' 'mysql' I have once know how to perform a subquery asked by a particular table of all the databases resulting from the first question.

example.
the structure would be
db1 -> user-> id,name,imei,telephone,etc..
db2 -> user-> id,nameuser,imei,telephone,etc..
db3 -> user-> id,nameuser,imei,telephone,etc..
....
db1000 -> user-> id,nameuser,imei,telephone,etc..

the query are how this, but this get error

    SELECT CONCAT('SELECT * FROM ' schema_name 'where imei.'schema_name =     nameimai)
    FROM information_schema.schemata
    WHERE schema_name NOT IN ('information_schema','mysql','performance_schema','sys','performance_schema','phpmyadmin');

    Results

    name db    id          name       imei        phone
    ---------- ---------- ---------- ---------- ----------
    db1         1            John     76876876  xxx
    db2         2300         John     76876876   xxxx  
    ...
    db1000       45          John     76876876   xxx

在一个查询中可能出现

谢谢。

推荐答案

这是使用存储过程的一种方法。

Here's one way you could do it with a stored procedure.

如果我正确理解，您的多个数据库具有相同的表（用户），并且您希望针对所有这些表运行查询以获取特定值。

If I understand correctly, you have multiple databases with identical tables (user) and you want to run a query against all these tables for a specific value.

我已经做了相当笼统的介绍，以便您可以传入表名以及where子句。您的示例似乎正在使用imei ='76876876'查找用户记录，因此，如果我们使用该示例。

I've made this fairly general so that you can pass in the table name and also the where clause. Your example seemed to be looking for user records with imei = '76876876', so if we use that example.

USE test;

DELIMITER //
DROP PROCEDURE IF EXISTS multidb_select //
-- escape any quotes in the query string
-- call multidb_select ('usertest','WHERE imei = \'76876876\'')
CREATE PROCEDURE multidb_select(IN tname VARCHAR(64), IN qwhere VARCHAR(1024))
  READS SQL DATA
BEGIN
  DECLARE vtable_schema VARCHAR(64);
  DECLARE vtable_name VARCHAR(64);

  DECLARE done BOOLEAN DEFAULT FALSE;
  -- exclude views and system tables
  DECLARE cur1 CURSOR FOR 
    SELECT `table_schema`, `table_name`
    FROM `information_schema`.`tables`
    WHERE `table_name` = tname
    AND `table_type` = 'BASE TABLE'
    AND `table_schema` NOT IN 
      ('information_schema','mysql','performance_schema',
       'sys','performance_schema','phpmyadmin')
    ORDER BY `table_schema` ASC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur1;
  SET @unionall := '';

  read_loop: LOOP
    FETCH cur1 INTO vtable_schema, vtable_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- UNION ALL in case the id is the same  
    IF CHAR_LENGTH(@unionall) = 0 THEN
      SET @unionall = 
        CONCAT("SELECT \'", vtable_schema , "\' AS 'Db', t.* FROM `", 
                vtable_schema, "`.`" , vtable_name, "` t ", qwhere);
    ELSE
      SET @unionall = 
        CONCAT(@unionall, " UNION ALL SELECT \'", vtable_schema , 
                "\' AS 'Db', t.* FROM `", vtable_schema, 
                "`.`", vtable_name, "` t ", qwhere);
    END IF;  

  END LOOP;
  CLOSE cur1;

  PREPARE stmt FROM @unionall;
  EXECUTE stmt;   
  DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

使用

call test.multidb_select('user','WHERE imei = \'76876876\'')

这篇关于查询并获取所有数据库的所有数据库名称和子查询特定表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！