将列添加到mysql数据库中的所有表(未知表名) [英] adding column to all tables in a mysql database (unknown table names)
问题描述
我需要将主键添加到给定数据库中的一组表中.我不知道会有多少张桌子,或者它们的具体名称是什么.它们都是datatable _ ##的,其中##会根据我的脚本运行的位置而有所不同.
I need to add a primary key to a set of tables in a given database. I do not know how many tables there will be or what their specific names are. They will all be of the for datatable_##, where ## will vary based everywhere my script will be run.
要添加主键,我正在使用以下查询:
To add the primary key, I am using this query:
alter table datatable_??
add column ID int auto_increment not null first,
add primary key (ID);
因此,我需要它在数据库中的每个表上运行.看来我可以用PHP脚本之类的东西来做到这一点,但是他们只是在sql脚本中做到这一点的更简单的方法吗?
So, I need this to run on every table in the database. It seems like I can do this in a PHP script or something, but is their a simpler way to do this just in sql script?
推荐答案
如果要完全编写脚本,可以执行以下操作:
If you want to fully script this, you can do something like this:
SELECT CONCAT('ALTER TABLE ',
table_schema,
'.',
table_name,
' ADD COLUMN id INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;') AS ddl
INTO OUTFILE '/tmp/alter_table.sql'
FROM information_schema.tables
WHERE table_schema = 'db_name'
AND table_type = 'base table';
\. /tmp/alter_table.sql
这篇关于将列添加到mysql数据库中的所有表(未知表名)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!