mysql 列名 小写_MYSQL脚本将列名转换为小写

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

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.

Has anyone got such a script?

Thanks

解决方案

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

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

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

e.g.

ALTER TABLE mytable CHANGE old_name new_name varchar(5);

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