一、产生原因
在从别的机器导出数据库转存文件.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` ;