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

最新推荐文章于 2024-08-03 21:43:54 发布

jerry___

最新推荐文章于 2024-08-03 21:43:54 发布

阅读量1.7k

点赞数 7

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/jerry___/article/details/136261737

版权

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

备注：库名：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;

以上是执行结果，将执行结果的sql语句执行即可

列名转小写：

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.COLUMNS
WHERE 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.COLUMNS
WHERE 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 '性别';