MySQL比较好用的sql脚本^_^

1.【数据字典】生成脚本

SELECT
    a.TABLE_SCHEMA "库名",
    a.TABLE_NAME "表名",
    a.COLUMN_NAME "列名",
    a.COLUMN_TYPE "类型",
    a.COLUMN_DEFAULT "默认值",
    a.IS_NULLABLE "是否为空",
    a.CHARACTER_SET_NAME "表字符集",
    a.COLLATION_NAME "校验字符集",
    CONCAT( a.COLUMN_COMMENT, a.COLUMN_KEY, a.EXTRA ) "列备注",
    b.TABLE_COMMENT "表备注",
    b.ENGINE "引擎" 
FROM
    information_schema.COLUMNS a,
    information_schema.TABLES b 
WHERE
    a.TABLE_SCHEMA = b.TABLE_SCHEMA 
    AND a.TABLE_SCHEMA IN ( 'xx', 'xxx', 'xxxx' ) 
    AND a.TABLE_NAME = b.TABLE_NAME 
ORDER BY
    a.TABLE_SCHEMA,
    a.TABLE_NAME,
    a.ORDINAL_POSITION

 

2.【查询一个库的大小】

select sum(data_length+index_length)/1024/1024/1024 GB from information_schema.tables where table_schema='xxx';

 

select sum(data_length+index_length)/1024/1024 MB from information_schema.tables where table_schema='xxx' AND table_name = 'xxx'

 

3.【去重脚本】

-- 去重，把查询出来的重复行删除。
SELECT table_to_be_deleted_id
FROM
    `table_to_be_deleted`
WHERE
    table_to_be_deleted_id IN (
        SELECT
            t.table_to_be_deleted_id
        FROM
            (
                SELECT
                    someone_id, role_name
                FROM
                    `table_to_be_deleted`
                WHERE
                    tenant_id = 'xxx'
                GROUP BY
                    someone_id, role_name
                HAVING
                    count(*) > 1
            ) a, table_to_be_deleted t
         WHERE a.someone_id = t.someone_id AND a.role_name = t.role_name
    )    
AND table_to_be_deleted_id NOT IN (
    SELECT
        *
    FROM
        (
            SELECT
                min(table_to_be_deleted_id)
            FROM
                `table_to_be_deleted`
            WHERE
                tenant_id = 'xxx'
            GROUP BY
                someone_id, role_name
            HAVING
                count(*) > 1
        ) b
);