MYSQL脚本将列名转换为小写 [英] a MYSQL script to convert the column names to lowercase

问题描述

我正在寻找一个MYSQL脚本，可将数据库中的所有列名一次转换为小写...

I am looking for a single MYSQL script to convert ALL column names in a database to lowercase in one go...

我继承了一个MYSQL数据库，该数据库具有很多大小写混合的列名(150个具有奇怪命名约定的表)，我不想手动逐个表地进行操作.

I have inherited a MYSQL database that has a lot of mixed case column names (150 tables with a strange naming convention) and I don't want to go through manually each table by table to do this.

有人有这样的剧本吗?

谢谢

推荐答案

您可以通过从以下语句的输出开始构建脚本来解决此任务:

You can solve this task by building a script, starting with the output from this statement:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'dbname';
ORDER BY table_name

有关此功能的详细信息，请参见" MYSQL: :INFORMATION_SCHEMA栏目表"

Details about this feature can be found here "MYSQL::The INFORMATION_SCHEMA COLUMNS Table"

然后，您可以使用ALTER TABLE .. CHANGE功能更改列的名称

Then you can use the ALTER TABLE .. CHANGE feature to change the name of the columns

例如

ALTER TABLE mytable CHANGE old_name new_name varchar(5);

另请参见" MYSQL :: ALTER TABLE语法"

不同的数据类型有不同的要求，因此您需要UNION:

Different datatype have different requirements so you need the UNIONs:

SELECT 'ALTER TABLE '||table_name||' CHANGE '|| column_name||' '||lower(column_name)||' '||datatype||'('||CHAR(character_maximum_length)||');' AS Line
    FROM information_schema.columns
    WHERE table_schema = dbname and datatype in ( 'CHAR', 'VARCHAR' )
    ORDER BY table_name
    UNION
SELECT 'ALTER TABLE '||table_name||' CHANGE '|| column_name||' '||lower(column_name)||' '||datatype||'('||CHAR(numeric_precision)||');' AS Line
    FROM information_schema.columns
    WHERE table_schema = dbname and datatype in ( 'INTEGER' )
    ORDER BY table_name
    UNION
SELECT 'ALTER TABLE '||table_name||' CHANGE '|| column_name||' '||lower(column_name)||' '||datatype||'('||CHAR(numeric_precision)||','||CHAR(numeric_scale)|');' AS Line
    FROM information_schema.columns
    WHERE table_schema = dbname and datatype in ( 'FLOAT' )
    ORDER BY table_name
    UNION
SELECT 'ALTER TABLE '||table_name||' CHANGE '|| column_name||' '||lower(column_name)||' '||datatype||');' AS Line
    FROM information_schema.columns
    WHERE table_schema = dbname and datatype in ( 'DATE' )
    ORDER BY table_name

这篇关于MYSQL脚本将列名转换为小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！