获取批量修改列为大写SQL脚本
1 SELECT
2 concat( 'alter table ', TABLE_NAME, ' change column ', COLUMN_NAME, ' ', UCASE( COLUMN_NAME ), ' ', COLUMN_TYPE,' ',EXTRA,' comment "',COLUMN_COMMENT,'";' ) AS '修改脚本'
3 FROM
4 information_schema.COLUMNS
5 WHERE
6 TABLE_SCHEMA = '数据库名'
获取批量修改表为小写SQL脚本
1 SELECT
2 concat( 'alter table ', TABLE_NAME, ' rename to ', LCASE( TABLE_NAME ), ';' ) AS ‘修改脚本’
3 FROM
4 information_schema.TABLES
5 WHERE
6 TABLE_SCHEMA = '数据库名'
获取批量修改表列SQL脚本
1 SELECT
2 concat( 'alter table ', TABLE_NAME, ' modify ', COLUMN_NAME,' decimal(18,3);' ) AS '修改脚本'
3 FROM
4 information_schema.COLUMNS
5 WHERE
6 TABLE_SCHEMA = 'meritdata' AND (COLUMN_TYPE LIKE 'decimal%' or COLUMN_TYPE LIKE 'float%') AND COLUMN_TYPE LIKE '%,2)' AND TABLE_NAME in (
7 'I_AM_TABLE_NAME'
8 )
备份SQL查询结果
1 mysql -uroot -p -e 'select * from testdb.info'>/opt/supwang/wzz.sql
查询数据库表索引(这里我排除了主键)
1 select TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN
2 from
3 information_schema.statistics
4 where
5 table_schema='库名' and INDEX_NAME!='PRIMARY'
6 GROUP BY TABLE_NAME, INDEX_NAME;