我有一个数据库,里面有十几个表,这些表中的小写和大写字母用法都不正常。有没有一种简单的方法可以将所有mysql-表中的所有列名转换为小写?也许除了字符串以"_ID“结尾?
我知道mysql不区分大小写。,这都是关于更好的可读性。如果在一个表中有像"Author“、"AUTHOR_ID”和"AuthorName“这样的列名,那么很难阅读,我希望它使用小写更一致。
发布于 2014-02-09 08:16:57
编辑:-打开phpMyAdmin。->按下导出,并将文件导出为*.sql文件。->编辑SQL,您将发现大量的Create和类似的查询,->编辑文本编辑器中的名称并保存它。->打开phpMyAdmin并删除所有表并导入您先前保存的*.sql文件并运行它,它就可以完成这个任务了!
否则:
不同的数据类型有不同的需求,因此您需要联合:
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发布于 2014-02-09 09:01:23
这是:
$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>";
}将为每个表中的每一列提供所有ALTER语句的列表。
警告:
我对打印了屏幕上的所有ALTER语句进行了上述查询,但是,要由您来决定是否执行它。我不确定您的数据库在执行它们之后是否会保持不变,因为它们可能不会涵盖为每一列选择的所有可能的数据类型和条件。
执行上述操作,它将不会对数据库进行更改,并查看您的任何列是否缺少任何重要的数据类型或条件值。
发布于 2016-10-04 12:56:06
你好,示例代码,您可以帮助您。
$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请查看此链接以获得更多详细信息。
http://myphpinformation.blogspot.in/2016/10/convert-column-name-into-lowercase-in-mysql-and-php.html
https://stackoverflow.com/questions/21656723
复制相似问题