CREATE DEFINER=`myDatabase`@`%` PROCEDURE `ChangeTableColumnName`(
in in_table_name varchar(255)
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tableName VARCHAR(255);
DECLARE columnName VARCHAR(255);
DECLARE newColumnName VARCHAR(255);
DECLARE newColumnName2 VARCHAR(255);
DECLARE columnType VARCHAR(255);
DECLARE columnComment VARCHAR(255);
DECLARE cur CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE,COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
and table_name = in_table_name
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO tableName, columnName, columnType,columnComment;
IF done THEN
LEAVE read_loop;
END IF;
#先看首字母要不要改
IF ASCII(SUBSTRING(columnName,1,1)) BETWEEN 65 AND 90 THEN
#首字母转小写
call LowercaseFirstLetter(columnName,newColumnName);
select 1;
SET @sql = CONCAT('ALTER TABLE `', tableName, '` CHANGE `', columnName, '` `', newColumnName, '` ', columnType);
if columnComment is not null && columnComment != '' then
set @sql = concat(@sql,' COMMENT \'',columnComment,'\'');
end if;
-- 执行ALTER TABLE语句
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
-- 再将驼峰命名转换为下划线命名
call ToLowercaseWithUnderscore(newColumnName,newColumnName2);
#select newColumnName,newColumnName2,newColumnName != newColumnName2;
-- 检查是否需要重命名
IF newColumnName != newColumnName2 THEN
SET @sql = CONCAT('ALTER TABLE `', tableName, '` CHANGE `', newColumnName, '` `', newColumnName2, '` ', columnType);
if columnComment is not null && columnComment != '' then
set @sql = concat(@sql,' COMMENT \'',columnComment,'\'');
end if;
-- 执行ALTER TABLE语句
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END LOOP;
CLOSE cur;
END
MySQL数据库将表字段为驼峰命名的字段更改为小写加下划线格式的字段的SQL脚本
于 2024-09-10 10:08:10 首次发布