mysql字段转大写，生成SQL再拷贝执行

mysql字段转大写，生成SQL再拷贝执行

1 2 3 4 5 6 7 8 9

      
      

       
       SELECT 

      
      
      
      


      
      
      
      

       
       CONCAT('ALTER table ',t2.table_schema,'.',t2.table_name,' CHANGE ',' \`',t2.column_name,'\` \`',UPPER(t2.column_name),
       
       '\` ',  

      
      
      
      

       
       t2
       
       .column_type,
       
       ' ',CASE WHEN t2.is_nullable=
       
       'NO' THEN 
       
       'not null' ELSE 
       
       'null' END,
       
       ' COMMENT '
       
       '',t2
       
       .column_comment,
       
       ''
       
       ';') AS c  

      
      
      
      

       
         FROM information_schema
       
       .tables t1, information_schema
       
       .columns t2  

      
      
      
      

       
        WHERE t1
       
       .table_schema = 
       
       'cms'  

      
      
      
      

       
          AND t1
       
       .table_type = 
       
       'base table'  

      
      
      
      

       
          AND t1
       
       .table_schema = t2
       
       .table_schema  

      
      
      
      

       
          AND t1
       
       .table_name = t2
       
       .table_name  

      
      

mysql表名批量转大写的存储过程

原始数据 复制代码

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

       
       

        
        #
        
        call uppercase('库名')

       
       
       
       

        
        DROP PROCEDURE IF EXISTS uppercase; 

       
       
       
       

        
        CREATE PROCEDURE uppercase(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 = UPPER(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