I'm working on the multi-tenant application and each user has its database.
Assume we have more tenants and each tenant has schema (schema1, schema2, schema3,....)
and each schema has the same structure of tables.
Each schema has a customer table with the same structure(id, name, age,...).
I have a table called tenants in the shared schema (shared_schema) that has all schema names in a column called tenant_id.
So how can get all schema from tenants table and then get customer count for each schema
in single (query, procedure, view,... any workaround).

tenants table:-

schema1, schema2,....
customer table

Expected result

procedure workaround

DROP PROCEDURE IF EXISTS `tradeTypeStats`;

DELIMITER $$

CREATE PROCEDURE `tradeTypeStats`()
BEGIN
    declare scName varchar(250);
    declare q varchar(2000);

    DROP TABLE IF EXISTS ResultSet;
    create temporary table ResultSet (
     id bigint
    );

    DROP TABLE IF EXISTS MySchemaNames;
    create temporary table MySchemaNames (
        schemaName varchar(250)
    );

    insert into MySchemaNames(schemaName)
    SELECT distinct
        ms.tenant_id as SchemaName FROM shared_schema.tenants ms;

label1:
    LOOP
        set scName = (select schemaName from MySchemaNames limit 1);
        set @q = concat('select id from ', scName, '.customer');
        PREPARE stmt1 FROM @q;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;

        delete from MySchemaNames where schemaName = scName;
        IF ((select count(*) from MySchemaNames) > 0) THEN
            ITERATE label1;
        END IF;
        LEAVE label1;

    END LOOP label1;

    SELECT * FROM ResultSet;

    DROP TABLE IF EXISTS MySchemaNames;
    DROP TABLE IF EXISTS ResultSet;
END
$$

DELIMITER ;

CALL tradeTypeStats();

But it gives me an error

Error
Static analysis:

1 errors were found during analysis.

Missing expression. (near "ON" at position 25)
SQL query: Copy Edit Edit

SET FOREIGN_KEY_CHECKS = ON;

MySQL said: Documentation

#2014 - Commands out of sync; you can't run this command now

CodePudding user response：

I could not figure out what the point of the intermediate MySchemaNames table is. I would just use a cursor -

DELIMITER $$
CREATE PROCEDURE `tradeTypeStats`()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _schema_name VARCHAR(250);
  DECLARE _q VARCHAR(2000);

  DECLARE `cur` CURSOR FOR
    SELECT DISTINCT tenant_id AS SchemaName FROM shared_schema.tenants;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  DROP TABLE IF EXISTS ResultSet;
  CREATE TEMPORARY TABLE ResultSet (
    tenant_id VARCHAR(250) NOT NULL,
    customer_counts INT NOT NULL
  );

  OPEN cur;

  read_loop: LOOP

    FETCH cur INTO _schema_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    
    SET @q = CONCAT('INSERT INTO ResultSet SELECT \'', _schema_name,'\', COUNT(*) FROM ', _schema_name, '.customer');
    PREPARE stmt1 FROM @q;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

  END LOOP;
  CLOSE cur;
  
  SELECT * FROM ResultSet;
  DROP TABLE ResultSet;

END$$
DELIMITER ;