There is no 'root'@'%' registered

一、产生原因

在从别的机器导出数据库转存文件.sql拿到自己机器导入数据库后，在执行sql文件时创建的视图view就会是如下创建

-- ----------------------------
-- View structure for v_work_receipt_emp
-- ----------------------------
DROP VIEW IF EXISTS `v_work_receipt_emp`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER  VIEW `v_work_receipt_emp` AS select `t1`.`WORK_ID` AS `WORK_ID`,`t1`.`ORDER_ID` AS `ORDER_ID`,`t1`.`SLIP_NO` AS `SLIP_NO`,count(1) AS `EMP_CNT`,sum(`t1`.`OUTPUT_VAL`) AS `TOTAL_OUTPUT_VAL`,group_concat(cast(`t1`.`EMPLYEE_ID` as char charset utf8) separator ',') AS `GROUP_EMP_ID`,group_concat(`t2`.`VNAME` separator ',') AS `GROUP_EMP_NAME` from (`bus_work_receipt_emp` `t1` join `ptf_bas_emp` `t2` on((`t1`.`EMPLYEE_ID` = `t2`.`RID`))) group by `t1`.`WORK_ID`,`t1`.`ORDER_ID`,`t1`.`SLIP_NO` ;

在以上代码中：difiner的值`root`@`%`

报错输出：

二、解决方案

将definer==`root`@`%`修改成definer==`root`@`localhost`，再放到查询里面执行视图创建，示例如下

-- ----------------------------
-- View structure for v_work_receipt_emp
-- ----------------------------
DROP VIEW IF EXISTS `v_work_receipt_emp`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER  VIEW `v_work_receipt_emp` AS select `t1`.`WORK_ID` AS `WORK_ID`,`t1`.`ORDER_ID` AS `ORDER_ID`,`t1`.`SLIP_NO` AS `SLIP_NO`,count(1) AS `EMP_CNT`,sum(`t1`.`OUTPUT_VAL`) AS `TOTAL_OUTPUT_VAL`,group_concat(cast(`t1`.`EMPLYEE_ID` as char charset utf8) separator ',') AS `GROUP_EMP_ID`,group_concat(`t2`.`VNAME` separator ',') AS `GROUP_EMP_NAME` from (`bus_work_receipt_emp` `t1` join `ptf_bas_emp` `t2` on((`t1`.`EMPLYEE_ID` = `t2`.`RID`))) group by `t1`.`WORK_ID`,`t1`.`ORDER_ID`,`t1`.`SLIP_NO` ;