mysql - 存储过程

Question

我一直在谷歌上寻找答案，但找不到任何东西。我有一个游标语句，用于提取数据库中存在的表的名称。

目标是:具有 2 个参数的存储过程，database1 和 database2比较两个数据库并输出差异。数据库名称以制表符/空格分隔

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE db_tables VARCHAR(256);

DECLARE cursor1 CURSOR FOR 
        SELECT TABLE_NAME, TABLE_SCHEMA
        FROM information_schema.tables  
        WHERE TABLE_SCHEMA = db1
        AND TABLE_TYPE = 'BASE TABLE';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor1;
FETCH cursor1 into db_tables;

WHILE done = FALSE DO
SET query1 = SELECT * FROM db1 WHERE table1 IN(table_name);
END WHILE;
CLOSE cursor1;

END

Answer 1

这使用了INFORMATION_SCHEMA.TABLES信息

架构

create database db1;
create database db2;

create table db1.s1 (id int);
create table db1.s2 (id int);
create table db1.s3 (id int);

create table db2.s2 (id int);
create table db2.s3 (id int);
create table db2.s4 (id int);

查询

select t1.table_name, 2 as 'not in this one'
from INFORMATION_SCHEMA.TABLES t1
where t1.table_schema='db1'
and not exists (select * from INFORMATION_SCHEMA.TABLES t2 where t2.table_schema='db2' and t2.table_name=t1.table_name)
union
select t1.table_name, 1 as 'not in this one'
from INFORMATION_SCHEMA.TABLES t1
where t1.table_schema='db2'
and not exists (select * from INFORMATION_SCHEMA.TABLES t2 where t2.table_schema='db1' and t2.table_name=t1.table_name)

结果

+------------+-----------------+
| table_name | not in this one |
+------------+-----------------+
| s1         |               2 |
| s4         |               1 |
+------------+-----------------+

这意味着表 s1 在数据库 db1 中，但不在 db2 中，而表 s4在数据库 db2 中，但不在 db1 中。

存储过程

delimiter $$
create procedure showDBDiffInTableNames
( x1 varchar(40),x2 varchar(40) )
BEGIN
    -- 
    -- passed parameters, x1 is a string containing the name of a database
    -- x2 is a string containing the name of another database
    --
    select t1.table_name, 2 as 'not in this one'
    from INFORMATION_SCHEMA.TABLES t1
    where t1.table_schema=x1
    and not exists (select * from INFORMATION_SCHEMA.TABLES t2 where t2.table_schema=x2 and t2.table_name=t1.table_name)
    union
    select t1.table_name, 1 as 'not in this one'
    from INFORMATION_SCHEMA.TABLES t1
    where t1.table_schema=x2
    and not exists (select * from INFORMATION_SCHEMA.TABLES t2 where t2.table_schema=x1 and t2.table_name=t1.table_name);
END
$$
DELIMITER ;

测试一下:

调用 showDBDiffInTableNames('x1','x2');

相同的结果

t1 和 t2 只是表别名。请参阅手册页 here .从手册页:

The following list describes general factors to take into account when writing joins.

A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

....

如果提前知道我要查找两个或更多表，我几乎从不编写不带别名的查询。它减少了打字。它们在自连接(到同一个表)中尤为常见。您需要一种方法来区分您正在处理的是哪一个，以从查询中删除不明确的错误。所以这就是那个别名在那里的原因。另外，您会注意到该表在两次后消失了。

有两种写法，如上面的粉色/桃色 block 所示。