mysql - 架构中以 cm 开头的每个表的行数(用户)

Question

我想要所有表的列表以及“用户”列中相应的 NULL 条目数。我已经使用

打印了以 cm 开头的所有表格名称

SELECT
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES WHERE (TABLE_NAME LIKE 'cm%') ;

但是对于我想要运行的每个表

SELECT COUNT(1) FROM <TABLENAME> WHERE `create_user` IS NULL
 OR `create_time` IS NULL 

并打印

Answer 1

创建下表，并将其替换为正确的数据库名称:

CREATE TABLE `db`.`tbl_count_null` (
  `tableschema` varchar(64) NOT NULL DEFAULT '',
  `tablename` varchar(64) NOT NULL DEFAULT '',
  `qtd` char(0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

看看以下过程是否适合您:

CREATE DEFINER=`user`@`%` PROCEDURE `nameprocedure`()
BEGIN
  DECLARE x, y LONGTEXT;
  DECLARE done INT DEFAULT 0;
  DECLARE databasesCursor CURSOR FOR 
    SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'cm%';
  DECLARE CONTINUE HANDLER 
                    FOR SQLSTATE '02000' SET done = 1;

        OPEN databasesCursor;
            myLoop: LOOP
                FETCH databasesCursor INTO x, y;

                IF NOT done THEN
                    SET @query = CONCAT("INSERT INTO`db`.`tbl_count_null` (tableschema, tablename, qtd) SELECT '",x,"' AS `schema`,'",y,"' AS `table`,COUNT(1) FROM `",x,"`.`",y,"` WHERE `create_user` IS NULL OR `create_time` IS NULL;");
                    PREPARE stmt FROM @query;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;

                END IF;
            END LOOP myLoop;

        CLOSE databasesCursor;
END;

创建上述过程后，使用命令调用:

call db.nameprocedure;

验证记录是否已插入表db。tbl_count_null