mysql批量将数据库表名以及字段全部一键转化为大写或者小写

实现原理：通过函数查询拼接的修改语句

备注：库名：my_test01

表名

批量修改表名为小写、大写语句

SELECT concat('alter table ', TABLE_NAME, ' rename to ', LOWER(TABLE_NAME),';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='my_test01';SELECT concat('alter table ', TABLE_NAME, ' rename to ', upper(TABLE_NAME),';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='my_test01';

alter table test01 rename to TEST01;alter table test02 rename to TEST02;alter table test03 rename to TEST03;

列名转小写：

SELECT concat('ALTER TABLE ', TABLE_NAME , ' CHANGE COLUMN ', COLUMN_NAME, ' ', LOWER( COLUMN_NAME ), ' ', COLUMN_TYPE, IF(ISNULL(CHARACTER_SET_NAME),'',' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci '), IF(IS_NULLABLE='NO',' NOT NULL ',' '),IF(ISNULL(COLUMN_DEFAULT),'',concat('DEFAULT \'', COLUMN_DEFAULT,'\'')), ' comment \'', COLUMN_COMMENT, '\';' ) AS 'ddl_sql'FROM information_schema.COLUMNSWHERE TABLE_SCHEMA='my_test01'

列名转大写：

SELECT concat('ALTER TABLE ', TABLE_NAME , ' CHANGE COLUMN ', COLUMN_NAME, ' ', UPPER( COLUMN_NAME ), ' ', COLUMN_TYPE, IF(ISNULL(CHARACTER_SET_NAME),'',' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci '), IF(IS_NULLABLE='NO',' NOT NULL ',' '),IF(ISNULL(COLUMN_DEFAULT),'',concat('DEFAULT \'', COLUMN_DEFAULT,'\'')), ' comment \'', COLUMN_COMMENT, '\';' ) AS 'ddl_sql'FROM information_schema.COLUMNSWHERE TABLE_SCHEMA='my_test01'

执行结果：直接执行即可

ALTER TABLE TEST01 CHANGE COLUMN ID id int NOT NULL  comment 'id';ALTER TABLE TEST01 CHANGE COLUMN NAME name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci   comment '名称';ALTER TABLE TEST01 CHANGE COLUMN AGE age int  comment '性别';ALTER TABLE TEST02 CHANGE COLUMN ID id int NOT NULL  comment 'id';ALTER TABLE TEST02 CHANGE COLUMN NAME name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci   comment '名称';ALTER TABLE TEST02 CHANGE COLUMN AGE age int  comment '性别';ALTER TABLE TEST03 CHANGE COLUMN ID id int NOT NULL  comment 'id';ALTER TABLE TEST03 CHANGE COLUMN NAME name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci   comment '名称';ALTER TABLE TEST03 CHANGE COLUMN AGE age int  comment '性别';