Mysql有几个内部表,存放schema,表名,主键,索引,列名等信息。
此次做数仓迁移,用到这些,顺手整理一下:
select t.table_schema,t.table_name,concat('vnm_db01_',t.table_name) targettable,k.column_name,group_concat(c.column_name) as col
from information_schema.tables t
join information_schema.columns c
on c.table_name=t.table_name
left join (select table_name,column_name from information_schema.key_column_usage where constraint_name='primary') k
on c.table_name=k.table_name
where t.table_schema='metis_prod'
and t.table_name in ('account_acl','account_item')
group by c.table_name
order by c.table_name
;
解释:查询schema,表名,拼接目标表名,主键,字段名(已列转行)
information_schema.tables:存放表名的
information_schema.columns:存放表字段的
information_schema.key_column_usage:存放表主键、索引之类的
标签:information,name,column,表名,Mysql,table,主键,schema
来源: https://blog.csdn.net/jsbylibo/article/details/97657647