MySQL 如何在mysql中一次更改多个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8934779/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I alter multiple tables at once in mysql?
提问by John Doe
I am trying to alter multiple tables and change the size of the usernameVARCHAR column to 999 as its current size is too small and now things are screwed up. How can I do this?
我正在尝试更改多个表并将usernameVARCHAR 列的大小更改为 999，因为它的当前大小太小，现在事情搞砸了。我怎样才能做到这一点？
I have tried the following and it worked for one table but when trying to update multiple table names it returned errors:
我尝试了以下方法，它适用于一张表，但在尝试更新多个表名时，它返回了错误：
ALTER TABLE `TABLE_NAME` CHANGE `username` VARCHAR( 999 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL
采纳答案by Mchl
You can't do it with a single query. You need to query information_schemaviews to get the list of tables and columns to change. You will then use the resulting resultset to create ALTERqueries (either in an external application/script or within MySQL using cursors and prepared statements)
你不能用一个查询来做到这一点。您需要查询information_schema视图以获取要更改的表和列的列表。然后，您将使用生成的结果集来创建ALTER查询（在外部应用程序/脚本中或在 MySQL 中使用游标和准备好的语句）
回答by Raath
I found the only way to do this was via an external file. This is my implimentation :
我发现唯一的方法是通过外部文件。这是我的暗示：
function changeSchema($oldName, $newName, $type, $len)
{
$res = mysql_query("SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = '$oldName' AND
TABLE_SCHEMA = 'your_database_name'");
if($res)
while($line=mysql_fetch_object($res))
mysql_query("ALTER TABLE `$line->TABLE_NAME` CHANGE `$oldName` `$newName` $type( $len ) NOT NULL ");
}
}
I then was able to modify any table I wanted easily.
然后我就可以轻松修改我想要的任何表格。
回答by Naveen Kumar
Write a query file to alter all tables and execute that file.
编写一个查询文件来更改所有表并执行该文件。
