一、. 表名批量转为小写
1.1.添加存储过程
create definer=`ltkjtest`@`%` procedure `lowercasetablenames`(in dbname varchar(200))
begin
declare done int default 0;
declare oldname varchar(200);
declare cur cursor for select table_name from information_schema.tables where table_schema = dbname;
declare continue handler for not found set done = 1;
open cur;
repeat
fetch cur into oldname;
set @newname = lower(oldname);
set @isnotsame = @newname <> binary oldname;
if not done && @isnotsame then
set @sql = concat('rename table `',oldname,'` to `', lower(@newname), '_tmp` ');
prepare tmpstmt from @sql;
execute tmpstmt;
set @sql = concat('rename table `',lower(@newname),'_tmp` to `',@newname, '`');
prepare tmpstmt from @sql;
execute tmpstmt;
deallocate prepare tmpstmt;
end if;
until done end repeat;
close cur;
end
1.2.执行存储过程
call lowercaseTablenames('数据库名');
二、. 表字段名批量转为小写
需求场景:
最近在开发项目时,需要创建一张表,发现别的库,已经有人创建了,于是便把建表语言拿过来,直接建表,但是有一件不爽的事,原来创建表时,字段全部是大写,作为强迫症患者的,非得把字段转为小写,才看着顺眼。
如下:
1.1 解决方法:
首先使用下面的sql,生成将字段名称改为小写的sql。
SELECT concat('ALTER TABLE ', '表名'
, ' CHANGE COLUMN '
, COLUMN_NAME, ' '
, LOWER(COLUMN_NAME)
, ' '
, COLUMN_TYPE
, ' COMMENT '''
, COLUMN_COMMENT
, ''';') AS '修改脚本'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '数据库名'
and TABLE_NAME = '表名';
我这边的数据库名称是 ltkj-project-manage,表名是 act_ge_bytearray,生成将字段名称改为小写的sql如下
SELECT concat('ALTER TABLE ', 'act_ge_bytearray'
, ' CHANGE COLUMN '
, COLUMN_NAME, ' '
, LOWER(COLUMN_NAME)
, ' '
, COLUMN_TYPE
, ' COMMENT '''
, COLUMN_COMMENT
, ''';') AS 'she'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'ltkj-project-manager'
and TABLE_NAME = 'act_ge_bytearray';
执行完,结果如下
将全部修改字段名称大小的sql复制到出来,然后执行一下,既可以将该表的全部字段改为小写了