-- ===============================================================
-- Error Code: 1449.
-- ===============================================================
通过如下sql取得所有表的批量数据导入sql,web_cdp是视图库。
select v.*,concat('replace into sync_cdp.',table_name,' select * from web_cdp.',table_name,';') from
information_schema.tables v
where table_schema='sync_cdp' order by table_name ;
查出所以批量导入sql语句:
replace into sync_cdp.accountding_std select * from web_cdp.accountding_std;
replace into sync_cdp.comp_results select * from web_cdp.comp_results;
replace into sync_cdp.auditor select * from web_cdp.auditor;
replace into sync_cdp.cpny_view_record select * from web_cdp.cpny_view_record;
replace into sync_cdp.doc_trace select * from web_cdp.doc_trace;
replace into sync_cdp.exchange select * from web_cdp.exchange;
replace into sync_cdp.industry select * from web_cdp.industry;
replace into sync_cdp.listed_cpny_fin_item select * from web_cdp.listed_cpny_fin_item;
replace into sync_cdp.listed_cpny_fin_rpt_prd select * from web_cdp.listed_cpny_fin_rpt_prd;
replace into sync_cdp.listed_cpny_trading_data select * from web_cdp.listed_cpny_trading_data;
........
执行如下sql:
replace into sync_cdp.accountding_std select * from web_cdp.accountding_std
报错,卡住了,error如下:
Error Code: 1449. The user specified as a definer ('skyman.man'@'%') does not exist
一开始以为是账号的权限玩呢提,查了好久,才找到原因,因为sync_cdp.accountding_std是视图,所以不能replace into操作,但是
mysql不会报说这个表不存在,因为在目前的mysql版本里面,view是作为一个临时表看待的。
我的取得所以表的sql应该再加一个限制and table_type !='VIEW'才行。
select v.*,concat('replace into sync_cdp.',table_name,' select * from web_cdp.',table_name,';') from
information_schema.tables v
where table_schema='sync_cdp' and table_type !='VIEW' order by table_name ;
这样取得的sql就run起来,完全OK了。
阅读(3307) | 评论(0) | 转发(0) |