CODEWITHSUNDEEP
mysql
Hexodus
Hexodus

Reputation: 12927

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

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.

Upvotes: 0

Views: 5342

Answers (3)

Jydipsinh Parmar
Jydipsinh Parmar

Reputation: 502

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

Upvotes: -1

Hassan
Hassan

Reputation: 111

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

Upvotes: 3

AyB
AyB

Reputation: 11665

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.

Upvotes: 1

Related Questions