巧用SQL拼接语句( 二 )

11.查找大写表及转为小写表# 若lower_case_table_names=0可能导致表名既有大写又有小写,# 想将lower_case_table_names设为1的话 需要先将大写的表和视图名称改为小写的 。# 查找出名称为大写的表和视图SELECTTABLE_SCHEMA,TABLE_NAME,TABLE_TYPE FROMinformation_schema.`TABLES` WHERETABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )AND TABLE_NAME REGEXP BINARY '[A-Z]';# 拼接出大写表名改为小写的SQLSELECTCONCAT( 'rename table ', TABLE_SCHEMA, '.', TABLE_NAME, ' to ', TABLE_SCHEMA, '.', LOWER( TABLE_NAME ), ';' ) FROMinformation_schema.`TABLES` WHERETABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )AND TABLE_TYPE = 'BASE TABLE'AND TABLE_NAME REGEXP BINARY '[A-Z]'; 

巧用SQL拼接语句

文章插图
 




推荐阅读