mysql常用sql-JZTXT

#获取添加所有索引的语句
SELECT
    CONCAT('ALTER TABLE `',
            TABLE_NAME,
            '` ',
            'ADD ',
            IF(NON_UNIQUE = 1,
                CASE UPPER(INDEX_TYPE)
                    WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
                    WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
                    ELSE CONCAT('INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE)
                END,
                IF(UPPER(INDEX_NAME) = 'PRIMARY',
                    CONCAT('PRIMARY KEY USING ', INDEX_TYPE),
                    CONCAT('UNIQUE INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE))),
            '(',
            GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')
                ORDER BY SEQ_IN_INDEX ASC
                SEPARATOR ', '),
            ');') AS 'Show_Add_Indexes'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'svmain' and UPPER(INDEX_NAME) <> 'PRIMARY'
GROUP BY INDEX_TYPE , NON_UNIQUE , TABLE_NAME , INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

#获取删除所有索引的语句
SELECT
    CONCAT('ALTER TABLE `', TABLE_NAME, '` DROP INDEX ',INDEX_NAME,';') AS 'SQL'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'svmain' and UPPER(INDEX_NAME) <> 'PRIMARY'
GROUP BY INDEX_TYPE , NON_UNIQUE , TABLE_NAME , INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

#获取添加所有外键的语句
select concat('ALTER TABLE ', TABLE_NAME, ' add constraint ', CONSTRAINT_NAME, ' FOREIGN KEY (',GROUP_CONCAT(COLUMN_NAME),') references ',referenced_table_name,'(',referenced_column_name,');') as `SQL`
from information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA = 'svmain'
AND referenced_table_name IS NOT NULL
GROUP BY TABLE_NAME , CONSTRAINT_NAME,referenced_table_name,referenced_column_name
ORDER BY TABLE_NAME ASC , CONSTRAINT_NAME ASC;

#获取删除所有外键的sql语句
SELECT concat('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';') AS 'SQL'
FROM information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA = 'svmain'
AND referenced_table_name IS NOT NULL;