mysql 常用脚本【库表操作】

mysql查同个实例两个数据库中的表名差异：

SELECT
    TABLE_NAME 
FROM
    (
    SELECT
        TABLE_NAME,
        count( 1 ) AS cnt 
    FROM
        information_schema.TABLES 
    WHERE
        TABLE_SCHEMA IN ( 'db_a', 'db_b' ) 
        AND ( table_name NOT LIKE '%2018%' AND table_name NOT LIKE '%2019%' ) 
    GROUP BY
        TABLE_NAME 
    ) t1 
WHERE
    cnt <> 2 
    LIMIT 50;

查询有哪些表的字段长度不正确的sql：

SELECT
	* 
FROM
	information_schema.`COLUMNS` 
WHERE
	TABLE_SCHEMA = 'zgs_emigrated' 
	AND COLUMN_NAME = 'school_code' 
	AND CHARACTER_MAXIMUM_LENGTH <> 12;

SQL语句,查询数据库里是否存在某个表:

SELECT
	COUNT( `TABLE_NAME` ) 
FROM
	`INFORMATION_SCHEMA`.`TABLES` 
WHERE
	`TABLE_SCHEMA` = 'listen-dev-ver2-0' 
	AND `TABLE_NAME` = 'log_login_202004'