【mysql】Navicat工具导出结构及数据问题|IT运维中文博客

【mysql】Navicat工具导出结构及数据问题

时间:2015-07-22 10:07:55 作者:beebol 标签: 分类: mysql

最近一个开发同学用navicat连接数据库，进行管理。他想从远程的一个库中导出一份数据，然后再恢复到本地机器，进行开发。在导入后中总是缺少几个view。后来发现navicat导出有问题，如下都是个人的实验复现过程。

首先创建一个模拟的数据库，后面会贴上。

然后，正常地导出一份sql文件，操作：右键库名--转存sql文件--结构和数据--保存为beebol.sql文件到桌面。

直接选择另一个库test,操作：右键库名--运行sql文件，如下图：

开始执行后，报如下错误：

（这是在恢复一个视图，创建视图时会调用这个total函数）

很明显是test.total的function不存在报的。（还有很多view没有创建成功的原因就知道了。）为什么function不存在呢，到beebol.sql文件看看。

-- View structure for a_viewtotal
-- ----------------------------
DROP VIEW IF EXISTS `a_viewtotal`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `a_viewtotal` AS select `navicat`.`num1` AS `num1`,`navicat`.`num2` AS `num2`,`total`(`navicat`.`num1`,`navicat`.`num2`) AS `total(num1,num2)` from `navicat` ;

-- ----------------------------
-- Function structure for total
-- ----------------------------
DROP FUNCTION IF EXISTS `total`;
DELIMITER ;;

;;DELIMITER ;
DELIMITER ;

发现确实有问题，只有一句drop function，没有create function。问题找到了，但为什么会没有导出呢？于是我尝试导出了好几次，看选项，都没有找到原因，可能这是个bug，主要是后面view在function后面，因为在view中会调用function，如果按照这样的导出顺序，导入的时候也会报ERROR 1305 (42000) at line 162: FUNCTION test.total does not exist。所以这是两个问题：

1、function没有导出2、view和function的顺序没有创建对

2、view和function的顺序没有创建对

用mysqldump试试：

[root@mysql.dmc.com ~]$mysqldump -q -R --triggers --single-transaction -B beebol >beebol.sql

然后再看看里面的顺序及function是否存在

[root@mysql.dmc.com ~]$cat -n beebol.sql|  egrep 'VIEW|FUNCTION' 
    31  /*!50001 DROP VIEW IF EXISTS `a_viewtotal`*/;
   224  /*!50003 DROP FUNCTION IF EXISTS `total` */;
   234  /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `total`(i_num1 int,i_num2 int) RETURNS int(11)
   253  /*!50001 DROP VIEW IF EXISTS `a_viewtotal`*/;262  /*!50001 VIEW `a_viewtotal` AS select `navicat`.`num1` AS `num1`,`navicat`.`num2` AS `num2`,`total`(`navicat`.`num1`,`navicat`.`num2`) AS `total(num1,num2)` from `navicat` */;
   262  /*!50001 VIEW `a_viewtotal` AS select `navicat`.`num1` AS `num1`,`navicat`.`num2` AS `num2`,`total`(`navicat`.`num1`,`navicat`.`num2`) AS `total(num1,num2)` from `navicat` */;

很明显是没有问题的，Create function在234行，Create View在262行。

当然导入也就没有问题了

[root@mysql.dmc.com ~]$mysql test < beebol.sql [root@mysql.dmc.com ~]$
[root@mysql.dmc.com ~]$

后面我也是通过mysqldump出一份文件让他正常恢复的，我就建议他还是到命令提示符上操作吧，他的回答是“这太高端了吧”，我也只能“呵呵”了。

贴上准备：

[root@mysql.dmc.com ~]$mysql beebol
Your MySQL connection id is 195944
Server version: 5.6.6-m9-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root:mysql.dmc.com:Tue Jul 21 20:21:38 2015)[beebol]> create table navicat (id int not null auto_increment,num1 int not null,num2 int not null,primary key(id))engine=innodb;
Query OK, 0 rows affected (1.02 sec)

(root:mysql.dmc.com:Tue Jul 21 20:24:19 2015)[beebol]> insert into navicat (num1,num2)values(1,2),(2,3),(6,4),(123,12),(3,45);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

(root:mysql.dmc.com:Tue Jul 21 20:25:09 2015)[beebol]> delimiter //

(root:mysql.dmc.com:Tue Jul 21 20:32:30 2015)[beebol]> set global log_bin_trust_function_creators =1//
Query OK, 0 rows affected (0.00 sec)

(root:mysql.dmc.com:Tue Jul 21 20:32:38 2015)[beebol]> create function total(i_num1 int,i_num2 int) returns int begin declare total_num int; set total_num=(i_num1+i_num2)/2; return(total_num); end//
Query OK, 0 rows affected (0.02 sec)

(root:mysql.dmc.com:Tue Jul 21 20:32:41 2015)[beebol]> delimiter ;
(root:mysql.dmc.com:Tue Jul 21 20:32:52 2015)[beebol]> create view a_viewtotal as select num1,num2,total(num1,num2) from navicat;
Query OK, 0 rows affected (0.27 sec)

(root:mysql.dmc.com:Tue Jul 21 20:34:00 2015)[beebol]> select * from navicat;
+----+------+------+
| id | num1 | num2 |
+----+------+------+
|  1 |    1 |    2 |
|  2 |    2 |    3 |
|  3 |    6 |    4 |
|  4 |  123 |   12 |
|  5 |    3 |   45 |
+----+------+------+
5 rows in set (0.00 sec)

(root:mysql.dmc.com:Tue Jul 21 20:34:13 2015)[beebol]> select * from a_viewtotal;
+------+------+------------------+
| num1 | num2 | total(num1,num2) |
+------+------+------------------+
|    1 |    2 |                2 |
|    2 |    3 |                3 |
|    6 |    4 |                5 |
|  123 |   12 |               68 |
|    3 |   45 |               24 |
+------+------+------------------+
5 rows in set (0.03 sec)

(root:mysql.dmc.com:Tue Jul 21 20:35:21 2015)[beebol]> grant all privileges on *.* to navicat@'%' identified by 'navicat';
Query OK, 0 rows affected (0.00 sec)

(root:mysql.dmc.com:Tue Jul 21 20:36:34 2015)[beebol]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

(root:mysql.dmc.com:Tue Jul 21 20:36:45 2015)[beebol]> exit
Bye

表名和函数名，视图的名字特意按字母顺序给取的，主要是将函数名的字母顺序放到视图的后面。

时间:2015-07-22 10:07:55 作者:beebol 标签: 分类: mysql