在所有mysql表中添加新列 [英] Add a new column in all mysql tables
问题描述
我有一个练习表格，如下所示:
I have a practice table like this:
id | otherColumn1 | otherColumn2 | otherColumn3
--------------------------------------------------------
1 | othervalue1 | othervalue2 | othervalue3
2 | othervalue1 | othervalue2 | othervalue3
我还有许多其他表格:
Person
Address
Contact
------
------
------
现在，我的任务是在每张其他表中添加 practiceId 列，这将是来自练习表的外键.请记住，有些表已经具有 practiceId 列，因此我不需要更改这些表.
Now my task is to add practiceId column in every other table which will be a foreign key from practice table. Remember that some tables already have practiceId column so I don't need to change those tables.
问题是有很多表.我可以用更少的查询来做到这一点，还是可以为此编写高效的PHP脚本?
Problem is that there are many tables. Can I do this with less queries Or can I write a efficient PHP script for this ?
我已经有一个php脚本，可以从特定数据库中获取表名.
I already have a php script that can get tables names from a particular database.
谢谢
推荐答案
不，实际上没有一种将字段一次添加到多个表的方法.您将需要为其编写脚本.不过，这不应该是一个很难编写的脚本:
No, there really isn't a way to add a field to multiple tables at once. You will need to write a script for it. It shouldn't be a hard script to write, though:
一个简单的SHOW TABLES查询将为您提供一个表列表，您可以在PHP中循环遍历这些表.
A simple SHOW TABLES query will get you a list of tables which you can then loop through in PHP.
在循环中，一个SHOW COLUMNS FROM tablename查询将为您提供当前表中的字段，您可以使用该查询来检查它是否已经具有practiceId字段，然后在必要时添加它.
Within the loop, a SHOW COLUMNS FROM tablename query will give you the fields in the current table, which you can use to check if it already has the practiceId field, and then add it if necessary.
要添加该字段，请使用ALTER TABLE tablename ADD COLUMN practiceId INT(我假设为INT，但是如果要使用其他数据类型，则用替换，但是需要定义您的字段)
To add the field, use ALTER TABLE tablename ADD COLUMN practiceId INT (I've assumed INT, but replace with however your field needs to be defined if you're using a different data type)
要在新列上添加索引，还可以使用ALTER TABLE或使用CREATE INDEX.要创建外键约束，请将ALTER TABLE与ADD FOREIGN KEY参数一起使用(但我相信这仅适用于InnoDB表).
To add an index on the new column, you can also use ALTER TABLE, or use CREATE INDEX. To create a foreign key contraint use ALTER TABLE with ADD FOREIGN KEY argument (But I believe this only works with InnoDB tables).
参考:
- SHOW TABLES
- SHOW COLUMNS
- ALTER TABLE
- CREATE INDEX
- Foreign key contraints
这篇关于在所有mysql表中添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
