mysql批量将列名改成小写

上个星期周五，有个朋友问我，能不能把MySQL中所有表的列名，通过一个程序全部由大写改成小写。

因为之前有过plsql编程的经历，所以一听这个朋友的问题，就觉得有门，不算很难实现。可能很多用过oracl的朋友都知道，在oracle里可以用 “select * from user_tables”这条sql语句查看当前用户下的所有表的详细信息。

正是基于这个。我构想如果可以动态地获取当前用户下所有表的表名，再根据表名去数据库里查询这个表所有的列，构造修改表字段的语句，那就不是可以完美地解决这个问题了嘛。

 

正是基于这个想法我开始了摸索。首先我找到了两条sql语句。

--查询某个数据库中所有的表名
--table_schema 数据库名
--table_type 表类型

select table_name from information_schema.tables where table_schema='databaseName' and table_type='base table'

--根据表名查询这个表所有的列名
--table_schema 数据库名
--table_name 表名

select colum_name,column_type  from information_schema.columns where table_schema='databaseName' and table_name='tableName'

可见。以上两条sql语句的第一条语句，就相当于在oracle中的select table_name from user_tables。

资料已查毕。那么剩下的就数码代码了。

首先，我在已有的数据库tom_test下新建了两张表。T_USER，T_GROUP。以下是这两张表的结构。

CREATE TABLE T_USER (
   USER_ID int(11) DEFAULT NULL,
   USER_NAME varchar(200) DEFAULT NULL,
   U_FK_G int(11) DEFAULT NULL,
   KEY  U_FK_G (U_FK_G),
  CONSTRAINT  t_user_ibfk_1 FOREIGN KEY (U_FK_G) REFERENCES T_GROUP (GROUP_ID)
)

 

CREATE TABLE T_GROUP (
     GROUP_ID int(11) DEFAULT NULL,
     GROUP_NAME varchar(200) DEFAULT NULL,
     GROUP_TYPE varchar(100) DEFAULT NULL
)

创建完两个表后，就开始了存储过程的编写。

1,

drop procedure if exists columnNameUpcaseToLowercase;
 delimiter //
 CREATE PROCEDURE columnNameUpcaseToLowercase()
   BEGIN
     --定义一个变量接收表名
     DECLARE tableName varchar(40) default '';
     --定义一个游标接收当前数据库下所有表的表名
     DECLARE outUrl CURSOR FOR select table_name from information_schema.tables where table_schema='tom_test' and table_type='base table';
     --如果结果集是空的则给tableName赋值为null
     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tableName = null;
     --打开游标
     OPEN outUrl;
     --将游标中的数据装入tableName 注意因为上面的sql语句查询的是一个字段，所以可以直接这样装入数据，如果是多个数据，那就要定义多个变量来接收
     FETCH outUrl INTO tableName;
     --循环
     WHILE(tableName is not null) DO
  --在这里本想根据tableName直接拼个动态游标，直接获取这个表下所有的列的，可怎么写都不行，上网查了查，网上说mysql不支持动态游标，所以就弄了个动态视图
  --它的意义在于，每循环一次拿到一个新的表名的时候，就把原来的视图删了，重建一个新的视图，装新的表下的所有列的数据
  call randCreateView(tableName);
  --这个存储过程去访问randCreateView创建的视图，完美地完成了动态游标的目的。
  call findColumnName(tableName);
       --再次装入数据，注意是一条记录一条记录地装入的
       FETCH outUrl into tableName;
     --结束循环
     END WHILE;
     --关闭游标
     CLOSE outurl;
   END;//
 --调用存储过程
 call columnNameUpcaseToLowercase();

2,

drop procedure if exists findColumnName ;
 delimiter //
 CREATE PROCEDURE findColumnName(IN tableName VARCHAR(10))
 BEGIN   
  --声明一个变量接收字段名
  DECLARE columnName varchar(200) default '';
  --声明一个字段接收字段类型
  DECLARE columnType varchar(200) default '';
  --声明一个游标查询randCreateView创建的视图
  DECLARE innerUrl CURSOR FOR SELECT * from columnName_view;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET columnName = null; 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET columnType = null; 

  OPEN innerUrl;
  FETCH innerUrl INTO columnName,columnType;
  WHILE(columnName is not null) DO
        --查了下mysql没有像oracle那样的打印语句，所以就用这个代替了
        SELECT columnName,columnType;
        --这个存储过程用于动态拼接alter table...change....修改表字段的语句
        call alterTable(tableName,columnName,columnType);
        FETCH innerUrl INTO columnName,columnType;
  END WHILE;
  CLOSE innerUrl;
 END;//

 

3,

DROP PROCEDURE IF EXISTS randCreateView;

 CREATE PROCEDURE randCreateView (IN tableName VARCHAR(10))
 BEGIN
  --如果视图已存在就删除创建
  DROP VIEW IF EXISTS columnName_view;
  --以下两句正是动态创建视图的关键
  SET @sqlstr = "CREATE VIEW columnName_view as ";
  SET @sqlstr = CONCAT(
   @sqlstr,
   "select COLUMN_NAME,COLUMN_TYPE  from information_schema.columns where table_schema='tom_test' and table_name=","'",
   tableName,"'"
  );
  --为拼接的这条sql语句创建语句对象
  PREPARE stmt FROM @sqlstr;
  --执行这条sql
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
 END;

 

4,

DROP PROCEDURE IF EXISTS alterTable;

 CREATE PROCEDURE alterTable(IN tableName VARCHAR(10),IN columnName VARCHAR(1000),IN columnType VARCHAR(1000))
 BEGIN
  --拼接修改列名的sql语句
  SET @sqlstr = "ALTER TABLE ";
  SET @sqlstr = CONCAT(@sqlstr,tableName," CHANGE ",columnName," ",LCASE(columnName)," ",columnType);
  --输出sql语句方便调试的时候查看
  select @sqlstr;
  PREPARE stmt FROM @sqlstr;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

 END;

 

执行结果如下图：