How to convert MySql column names to lowercase for better readability?

Question

I've got a db with a dozen of tables inside of it that have irregularity regarding the low- and uppercase usage. Is there a simple way to convert all column names in all mysql-tables to lowercase ? Maybe with the exception of strings ending with "_ID"?

I know that mysql isn't case sensitive. This is all about better readability. If you have column names like "Author", "AUTHOR_ID" and "AuthorName" in one table it's hard to read and I like to have it more consistent using lowercase.

MySQL does not [make column names case-sensitive](https://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html), so I'm not entirely sure what's being asked. — hd1, Feb 09 '14 at 08:07
I know but in the name of consistency I'd like to have my columns being lowercase for better readability. — Hexodus, Feb 09 '14 at 08:11
is this a one time thing where you rename all columns or done for a query — Class, Feb 09 '14 at 08:11
read my answer below. I have added links to question where you can find sql queries how to list all tables, all columns in a table and how to rename them. Also a question link is posted which will show you how to handle foreign keys. — Altaf Hussain, Feb 09 '14 at 08:14
Use [column aliasing](https://dev.mysql.com/doc/refman/5.0/en/select.html‎) to get the column to display however you want — hd1, Feb 09 '14 at 08:23

score 3 · Answer 1 · edited May 23 '17 at 10:27

3

Edit: - Open phpMyAdmin. -> Hit Export and export the file as *.sql file. -> Edit the SQL File and you will find alot of Create table and such queries -> Edit the names in your text editor and Save it. -> Open phpMyAdmin and delete all tables and import your earlier saved *.sql file and run it and it should do the trick!

Otherwise:

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

Also: a MYSQL script to convert the column names to lowercase

edited May 23 '17 at 10:27

Community

1
1

answered Feb 09 '14 at 08:16

Hassan

111
10

Thanx for your good intentions but it's about changing the table colum names not strings in php. By the way I'm not one whos giving here minus grades - it's someone else. I'm glad about every answer(attempt). – Hexodus Feb 09 '14 at 08:19
Sorry Hassan it's still about MySQL not PHP. This doesn't help. – Hexodus Feb 09 '14 at 08:23
@Hexodus I have rewritten this, hope this is what you were looking for. Run the SQL Queries. – Hassan Feb 09 '14 at 08:24
This is indeed better. But I have 200 columns+ in my tables. – Hexodus Feb 09 '14 at 08:28
There is no other way, either you have to run this queries, or make a new database which could be quicker. – Hassan Feb 09 '14 at 08:29
Another better way: Export your database as a .sql file and edit it with notepad++ and in the names of CREATE table and stuff, you change the name into a lowercase way, and once you are done, save the file, goto phpMyAdmin, hit import and select the SQL File and it should do the trick! – Hassan Feb 09 '14 at 08:30
@Hexodus I have added another way of doing it which is much faster, if you are interested, try it. – Hassan Feb 09 '14 at 08:34
Thank you for reputation, much appreciated! – Hassan Feb 09 '14 at 08:37
nice answer Hassan, keep it up like that :) . – Altaf Hussain Feb 09 '14 at 09:47
2

**Exact copy from this [answer](http://stackoverflow.com/a/1612823/1003917). When you copy an answer , specify the source of it !** – Shankar Narayana Damodaran Feb 09 '14 at 11:28

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

This:

$sql = mysqli_query($dbConn,"SHOW TABLES");

$tables=array();
while($table_row = mysqli_fetch_array($sql))
{
    $tables[]=$table_row[0];
}
foreach($tables as $table){
    $sql = mysqli_query($dbConn,"SHOW COLUMNS FROM ".$table);

    $query = "ALTER TABLE ".$table;
    while($column_row = mysqli_fetch_array($sql))
    {
        if(substr($column_row['Field'],-3)=="_ID"){
        
            $new_column = strtolower(substr($column_row['Field'],0,-3))."_ID";
        }
        else
        {
            $new_column = strtolower($column_row['Field']);
        }
            
        $query .= " CHANGE COLUMN ". " " .$column_row['Field']." ".$new_column. " " . $column_row['Type'] . " " . (($column_row['Null']=='YES')?'NULL':'NOT NULL') . " " . (($column_row['Default']=='')?'':' DEFAULT '.$column_row['Default']) . " " . $column_row['Extra'] . ",";
        
    }
    $query = rtrim($query,',');
    echo $query;
    echo "<br><br><br>";
}

Will give you a list of all the ALTER TABLE statements for each column in each table.

Warning:

I've made the above query to print all the ALTER statements on screen, however, it's upto you to decide whether to execute it or not. I'm not sure whether your database will remain the same after executing them because they may not cover all the possible data types and conditions you have selected for each column.

Do the above, it won't make the changes to your database and see if any of your columns are missing any significant datatype or conditional value.

Looks a like this could be a way but I should better be careful with this solution. Makes me somehow nervous doing it this way. — Hexodus, Feb 09 '14 at 09:19
Lol, what you can do is, use the command `SHOW CREATE TABLE ` It will return the create table code (which gives you all the specification of each column) and with it you can cross-check if all the specification of each column has been mentioned in the `ALTER` code — AyB, Feb 09 '14 at 09:43

score -1 · Answer 3 · answered Oct 04 '16 at 12:56

hello example code for you you may help you.

$c1 = mysql_connect("localhost","root","");// Connection




$db1 = mysql_select_db("INFORMATION_SCHEMA");



$get_column = mysql_query("SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='data_base_name' AND `TABLE_NAME`='table_name'");

while($row = mysql_fetch_assoc($get_column)){


$old_name = $row['COLUMN_NAME'];
$new_name = strtolower($row['COLUMN_NAME']);
$datatype= $row['DATA_TYPE'];
$size = $row['CHARACTER_MAXIMUM_LENGTH'];



if($row['DATA_TYPE'] !="varchar" && $row['DATA_TYPE'] !="text"){
$query =  "ALTER TABLE mstusers CHANGE $old_name $new_name $datatype".";<br/>";
}else{

$query =  "ALTER TABLE mstusers CHANGE $old_name $new_name $datatype ($size)".";<br/>";
}
echo $query;


}

// Query paste in your  phpmyadmin

Please check this link for more detail

http://myphpinformation.blogspot.in/2016/10/convert-column-name-into-lowercase-in-mysql-and-php.html

oh we use mysql function i will suggest that. and that why you give - rating. why. code is working. if you can test in your brwoser. — Jydipsinh Parmar, Oct 05 '16 at 05:49

How to convert MySql column names to lowercase for better readability?

3 Answers3

Warning: