MySQL - 搜索特定值的多个模式

Question

我的服务器有多个模式。我需要找到特定的电子邮件地址。每个模式看起来几乎相同，它们具有相同的结构。我想做的是编写一个脚本来显示与给定电子邮件相关的 dbname 和 specyfic 数据。这是我的脚本，但它不起作用:(

DELIMITER $$

#Drop procedure if exists SearchAllDb $$
Create procedure SearchAllDb()

BEGIN
DECLARE DB_NAME Varchar(50);
DECLARE done INT default FALSE;
DECLARE CURSOR_ALL_DB_NAMES CURSOR FOR
SELECT schema_name from information_schema.schemata
WHERE schema_name like 'itools_%';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN CURSOR_ALL_DB_NAMES;

myloop: LOOP
FETCH CURSOR_ALL_DB_NAMES INTO DB_NAME; 

IF done THEN LEAVE myloop;
END IF; 

WHILE done != TRUE DO

SET @SQL = CONCAT('select DB_NAME, id, sn, name, email 
from',DB_NAME,'.`open_cases` where email like 'xxx@yahoo.com'');
prepare stmt from @SQL;
execute stmt;
deallocate prepare stmt;

END WHILE; 

CLOSE CURSOR_ALL_DB_NAMES; 
END;

DELIMITER ;

我有一些用 t-sql 编写的类似的东西，它可以工作......

DECLARE @DB_NAME VARCHAR(50);
DECLARE CURSOR_ALL_DB_NAMES CURSOR FOR
SELECT name FROM master.sys.databases
WHERE name like 'pickup-%' and name!= 'pickup-stored';

OPEN CURSOR_ALL_DB_NAMES;
FETCH NEXT FROM CURSOR_ALL_DB_NAMES INTO @DB_NAME 

WHILE @@Fetch_Status = 0
BEGIN
Print @DB_NAME;
exec ('select '''+ @DB_NAME + ''' as db, id, sn, email from ['+ 
@DB_NAME+'].dbo.requests where email in (''xxx@live.com'')');

FETCH NEXT FROM CURSOR_ALL_DB_NAMES INTO @DB_NAME 
END

CLOSE CURSOR_ALL_DB_NAMES;
DEALLOCATE CURSOR_ALL_DB_NAMES;

Answer 1

在引入 WHILE 之前，您看起来一切都很好。

1. WHILE 是多余的，因为您使用的是 LOOP，您应该删除这些行。
2. LOOP 没有 END。
3. CONCAT 语句将第一次使用 DB_NAME 视为您的列名之一，并且 LIKE 存在一些引用问题。
4. ';'该过程结束时应该是您指定为分隔符的“$$”。

我对您的变量名称进行了一些改动，但这个经过整理的版本应该可以说明这些要点。

DELIMITER $$

DROP PROCEDURE IF EXISTS SearchAllDb $$
CREATE PROCEDURE SearchAllDb()
BEGIN
  DECLARE db_name VARCHAR(64);
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR
    SELECT schema_name 
    FROM information_schema.schemata
    WHERE schema_name LIKE 'itools_%';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  myloop: LOOP
    FETCH cur INTO db_name; 

    IF done THEN 
      LEAVE myloop;
    END IF; 

    SET @SQL = CONCAT('SELECT ''', db_name, ''', id, sn, name, email 
      FROM `', db_name,'`.`open_cases` 
      WHERE email LIKE ''xxx@yahoo.com''');

    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END LOOP; 

  CLOSE cur; 
END $$

DELIMITER ;

https://dev.mysql.com/doc/refman/5.7/en/cursors.html 的文档中有一个非常好的 CURSOR 示例这将帮助您了解程序的结构。