本文共 4309 字,大约阅读时间需要 14 分钟。
1、首先我们通过mysqldump 的help 帮助我们了解一下两个参数的定义是什么
--opt
Same as --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys. Enabled by default, disable with
--skip-opt
Disable --opt. Disables --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys.
通过上面的意思,我们可以知道上面两个的表达的是 --opt 打开这些选项, --skip-opt 表达的是关闭这些选项,下面我们通过一些实验了解一些意义
2 实验 证明结果
创建表 :
CREATE TABLE myisam (
id int(11) NOT NULL auto_increment, name varchar(50) default NULL, post text, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE innodb (
id int(11) NOT NULL auto_increment, name varchar(50) default NULL, post text, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;1、创建了一个innodb表,一个myisam 表
2 、两个表都用了 auto_increment
[root@dg ~]# mysqldump -uroot -pmysql --database test --opt
Warning: Using a password on the command line interface can be insecure. Warning: Using unique option prefix database instead of databases is deprecated and will be removed in a future release. Please use the full name instead. -- MySQL dump 10.13 Distrib 5.6.27, for Linux (x86_64) -- Current Database: `test` CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `test`; -- Table structure for table `innodb` DROP TABLE IF EXISTS `innodb`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `innodb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `post` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- Dumping data for table `innodb` LOCK TABLES `innodb` WRITE; /*!40000 ALTER TABLE `innodb` DISABLE KEYS */; INSERT INTO `innodb` VALUES (1,'a','aaaaaaaaaaaaaaaaaaaaaaaaaa'),(2,'a','aaaaaaaaaaaaaaaaaaaaaaaaaa'),(3,'a','aaaaaaaaaaaaaaaaaaaaaaaaaa'); /*!40000 ALTER TABLE `innodb` ENABLE KEYS */; UNLOCK TABLES; -- Table structure for table `myisam` DROP TABLE IF EXISTS `myisam`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `myisam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `post` text, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- Dumping data for table `myisam` LOCK TABLES `myisam` WRITE; /*!40000 ALTER TABLE `myisam` DISABLE KEYS */; /*!40000 ALTER TABLE `myisam` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;[root@dg ~]# mysqldump -uroot -pmysql --database test --skip-opt
Warning: Using a password on the command line interface can be insecure. Warning: Using unique option prefix database instead of databases is deprecated and will be removed in a future release. Please use the full name instead. -- MySQL dump 10.13 Distrib 5.6.27, for Linux (x86_64) -- Current Database: `test` CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `test`; -- Table structure for table `innodb` /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `innodb` ( `id` int(11) NOT NULL, `name` varchar(50) DEFAULT NULL, `post` text, PRIMARY KEY (`id`) ); /*!40101 SET character_set_client = @saved_cs_client */; -- Dumping data for table `innodb` INSERT INTO `innodb` VALUES (1,'a','aaaaaaaaaaaaaaaaaaaaaaaaaa'); INSERT INTO `innodb` VALUES (2,'a','aaaaaaaaaaaaaaaaaaaaaaaaaa'); INSERT INTO `innodb` VALUES (3,'a','aaaaaaaaaaaaaaaaaaaaaaaaaa'); -- Table structure for table `myisam` /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `myisam` ( `id` int(11) NOT NULL, `name` varchar(50) DEFAULT NULL, `post` text, PRIMARY KEY (`id`) ); /*!40101 SET character_set_client = @saved_cs_client */;结论:
通过对比两个dump,可以得出结论如下:
1、--opt 在创建表结构之前 会有 DROP TABLE IF EXISTS
2、原表在创建的时候指定了AUTO_INCREMENT,在使用了--opt 仍然和建表时候一样存在参数,在使用参数--skip-opt的时候,忽略了此参数AUTO_INCREMENT,
3 在使用参数--opt的时候,创建表的类型,字符集等等都是默认参数ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;,当使用了--skip-opt的时候,这些参数都给忽略了
4导出原表中的数据,--opt是一个insert多个value,在使用了--skip-opt的时候,是多个insert组成的;
转载地址:https://blog.csdn.net/wll_1017/article/details/54134817 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!